How to Calculate Shopify Customer Lifetime Value: Formulas and Examples

A customer who spends $120 can still cost you money to acquire. To make Shopify customer lifetime value useful, you need to know what your number includes, how long you have observed the customer, and how much of their spending remains after costs.

Start with revenue per acquired customer over a fixed window, such as the first 90 days. Then use mature customer groups to inform forecasts and acquisition budgets. This guide covers Shopify’s built-in reports, a worked example, and SQL for BigQuery and Amazon Redshift.

Step 1: Choose the customer value you want to measure

Customer lifetime value, or CLV, estimates the value of a customer relationship. LTV and CLTV are alternative names. The distinction that matters is whether your calculation describes revenue already earned, expected future revenue, or contribution after costs.

Measure	Calculation	What it tells you
Historical revenue per customer	Net sales to date ÷ acquired customers	What the included customers have spent so far
90-day customer value	Net sales from each customer’s first 90 days ÷ acquired customers with a complete observation window	How much customers generate at the same age
Projected revenue CLV	Expected order value × expected orders per year × expected relationship in years	A forecast based on explicit assumptions
Contribution CLV	Expected revenue minus the variable costs of serving those sales	What remains to cover acquisition, overhead, and profit

For a simple planning estimate, suppose your average order generates $60 in net revenue, a customer makes 3 purchases a year, and you expect the relationship to last 2 years:

Projected revenue CLV = $60 × 3 × 2 = $360.

That is an illustrative forecast, not evidence that a newly acquired customer will spend $360. If you instead divide historical revenue by historical customer count, label the result as observed customer value and state the period.

Keep time units consistent. Monthly purchase frequency needs lifespan measured in months; annual frequency needs years. For a store without enough history to estimate lifespan, a measured 90-day or 12-month value is a more defensible starting point.

Step 2: Check customer value in Shopify Analytics

Open Shopify’s Customer cohort analysis report for an initial view of customer spending:

1. In your Shopify admin, open Analytics > Reports and find Customer cohort analysis.
2. Select Amount spent per customer.
3. Choose cohort intervals and dates. Compare groups at the same customer age.
4. Inspect a cohort cell and distinguish first-order spending from subsequent purchases. Check whether the view shows interval or accumulated values.
5. Activate Show projections for estimated future spending, if available. This requires 24 months of store data.

A single interval’s repeat-purchase spending is not the customer’s full value. Calendar-month intervals also differ from an exact 90-day window starting with each customer’s first purchase.

Use the SQL method below when you need that exact window or want to join customer spending to acquisition and cost data. Keep projections separate from observed results.

Step 3: Define your Shopify sales and customer data

Build one consistent order dataset before calculating CLV. A join that repeats an order across its line items can inflate customer value even when the final formula looks correct.

The SQL below uses an illustrative normalized table named shopify_orders_clv. This is a table you prepare in your warehouse, not a table Shopify automatically creates. Map your connector’s fields into this structure, using the Shopify data model guide for background on the relationships.

Field	Required meaning
order_id	Unique order identifier, with exactly one row per order
customer_id	Stable customer identifier for the purchasing customer
order_date	Order date converted to the store’s reporting timezone
net_sales	Product sales after discounts and sales reversals, in one reporting currency

Use the complete available purchase history to identify first orders. If your import starts halfway through the store’s life, a returning customer can look newly acquired. Backfill earlier orders or mark customers with uncertain acquisition dates separately.

Apply these rules before running the examples:

• Keep one-time and fully refunded purchasers. An acquired customer still belongs in the denominator when their eventual net sales are zero. Exclude only orders that do not qualify under your documented purchase policy.
• Set an order-eligibility policy. For this analysis, use genuine completed purchases and exclude test orders, unpaid attempts, and orders canceled before fulfillment. Keep later refunds against qualifying purchases.
• Resolve customer identity. Use stable IDs, not email addresses as the primary key. The queries exclude missing IDs; measure that excluded revenue and reconcile guest or merged records where possible.
• Normalize currency and dates. Convert sales into one currency using a documented exchange-rate policy. Use the same timezone and date boundaries throughout.
• Reconcile refunds and joins. Aggregate adjustments to the order before joining, avoid double-subtracting refunds, and confirm that row count equals distinct order count.

Shopify’s sales report definitions distinguish net sales from total sales, which also includes items such as tax and shipping. Sales reversals include more than physical returns, including cancellations and order edits.

There is also an AOV difference: Shopify’s native average order value uses gross sales minus discounts, excluding subsequent adjustments, divided by orders. Here, net_sales / orders is net revenue per order. Label it that way so readers do not expect it to match native AOV.

Step 4: Calculate 90-day customer value by cohort

Group customers by acquisition month, add the net sales from each customer’s first 90 days, and divide by everyone acquired in that group. A cohort is simply a group sharing a starting event, such as their first purchase in January.

Work through a simple example

Suppose a January cohort contains 100 acquired customers. Once every customer has completed their first 90 days, the illustrative results are:

Metric	Value
Acquired customers	100
Qualifying orders during customers’ first 90 days	200
Net sales from those orders	$12,000
Net revenue per order	$12,000 ÷ 200 = $60
Orders per acquired customer	200 ÷ 100 = 2
90-day revenue per acquired customer	$12,000 ÷ 100 = $120

Multiplying $60 by 2 produces the same $120 because both inputs describe the same orders and customers. No lifespan assumption is needed.

Include the customers who never ordered again. Removing them answers a different question: how valuable repeat purchasers are. It overstates the baseline you would use to assess acquiring a typical customer.

Run the BigQuery calculation

This version uses GoogleSQL date functions. Replace the table path with your prepared table. Set as_of_date to the first date outside your complete data snapshot; the example uses October 1, 2026, so September 30 is the last included order date.

-- BigQuery GoogleSQL
WITH params AS (
  SELECT DATE '2026-10-01' AS as_of_date
),
orders AS (
  SELECT order_id, customer_id, order_date, net_sales
  FROM `your_project.your_dataset.shopify_orders_clv`
  WHERE customer_id IS NOT NULL
    AND order_date < (SELECT as_of_date FROM params)
),
first_orders AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id
),
eligible_customers AS (
  SELECT customer_id, first_order_date,
         DATE_TRUNC(first_order_date, MONTH) AS cohort_month
  FROM first_orders
  WHERE DATE_ADD(LAST_DAY(first_order_date), INTERVAL 90 DAY)
        <= (SELECT as_of_date FROM params)
),
customer_values AS (
  SELECT c.customer_id, c.cohort_month,
         COUNT(o.order_id) AS orders_90d,
         SUM(o.net_sales) AS net_sales_90d
  FROM eligible_customers c
  JOIN orders o ON o.customer_id = c.customer_id
    AND o.order_date >= c.first_order_date
    AND o.order_date < DATE_ADD(c.first_order_date, INTERVAL 90 DAY)
  GROUP BY c.customer_id, c.cohort_month
)
SELECT cohort_month,
       COUNT(*) AS acquired_customers,
       SUM(orders_90d) AS orders_90d,
       SUM(net_sales_90d) AS net_sales_90d,
       SAFE_DIVIDE(SUM(net_sales_90d), SUM(orders_90d))
         AS net_revenue_per_order,
       AVG(orders_90d) AS orders_per_customer_90d,
       AVG(net_sales_90d) AS revenue_per_customer_90d
FROM customer_values
GROUP BY cohort_month
ORDER BY cohort_month;

Run the Amazon Redshift calculation

The same calculation uses Redshift’s date functions and explicit decimal division. Replace analytics.shopify_orders_clv with your prepared table.

-- Amazon Redshift
WITH params AS (
  SELECT DATE '2026-10-01' AS as_of_date
),
orders AS (
  SELECT order_id, customer_id, order_date, net_sales
  FROM analytics.shopify_orders_clv
  WHERE customer_id IS NOT NULL
    AND order_date < (SELECT as_of_date FROM params)
),
first_orders AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id
),
eligible_customers AS (
  SELECT customer_id, first_order_date,
         CAST(DATE_TRUNC('month', first_order_date) AS DATE)
           AS cohort_month
  FROM first_orders
  WHERE DATEADD(day, 90, LAST_DAY(first_order_date))
        <= (SELECT as_of_date FROM params)
),
customer_values AS (
  SELECT c.customer_id, c.cohort_month,
         COUNT(o.order_id) AS orders_90d,
         SUM(o.net_sales) AS net_sales_90d
  FROM eligible_customers c
  JOIN orders o ON o.customer_id = c.customer_id
    AND o.order_date >= c.first_order_date
    AND o.order_date < DATEADD(day, 90, c.first_order_date)
  GROUP BY c.customer_id, c.cohort_month
)
SELECT cohort_month,
       COUNT(*) AS acquired_customers,
       SUM(orders_90d) AS orders_90d,
       SUM(net_sales_90d) AS net_sales_90d,
       SUM(net_sales_90d) * 1.0 / NULLIF(SUM(orders_90d), 0)
         AS net_revenue_per_order,
       AVG(orders_90d * 1.0) AS orders_per_customer_90d,
       AVG(net_sales_90d * 1.0) AS revenue_per_customer_90d
FROM customer_values
GROUP BY cohort_month
ORDER BY cohort_month;

Interpret the output correctly

Both queries exclude acquisition months until even a customer acquired on that month’s final day could have completed 90 days. This avoids publishing a partial month’s cohort containing only its earliest customers.

The purchase date starts day 0, and the window excludes the date exactly 90 days later. These are calendar-date windows, not 90 periods of 24 hours from an order’s exact timestamp.

Each acquired customer contributes one row before the final average. There is no repeat-purchase filter, no email-based count, and no cap on the number of purchases included.

Use a non-null numeric net_sales field. For reproducible historical results, it must reflect adjustments known at the chosen snapshot date. The calculation attributes those adjustments to the original order: a refund after day 90 can change that customer’s 90-day order revenue when you refresh the snapshot. This differs from both revenue known on day 90 and a sales report that records reversals on their processing dates.

For comparisons sensitive to returns, allow the same refund-processing period after each customer’s window, then freeze their results. Otherwise, older cohorts have had more time for late refunds to appear.

Treat the output as 90-day observed revenue, not full lifetime revenue or a guaranteed forecast. Check a small customer sample against source orders before applying it to budgets.

Step 5: Build a forecast without overstating certainty

A forecast needs assumptions about purchases you have not observed yet. Rearranging historical revenue, orders, and customer counts cannot supply those assumptions.

Start by plotting comparable cohorts at 90 days, 180 days, and 12 months. Use only groups old enough to complete each window. If several mature groups show similar spending patterns, you can use those patterns to build a scenario for newer customers.

For example, suppose established cohorts average $120 after 90 days and $180 after 12 months. Applying the resulting 1.5 multiplier to a newer cohort’s $100 at 90 days gives a $150 first-year revenue estimate. That is a hypothetical cohort-based projection; changing products, acquisition channels, discounts, or retention can break the relationship.

Do not equate the time between a customer’s first and latest orders with their final lifespan. A customer whose most recent order was yesterday might keep buying for years. A one-time buyer’s zero-day observed span also does not mean they generated no value.

Before using a forecast operationally:

1. State its horizon and whether it predicts total or additional future value.
2. Compare previous predictions with actual results from cohorts that have since matured.
3. Show conservative and optimistic scenarios alongside the central estimate.
4. Revisit the assumptions when your acquisition mix or buying cycle changes.

Keep observed results and forecasts visibly separate in your dashboard. That lets you distinguish a real improvement in retention from a more generous modeling assumption.

Step 6: Compare contribution value with acquisition costs

Use contribution after variable costs when assessing how much you can spend to acquire customers. Revenue alone leaves out the cost of delivering those sales.

For the illustrative $120 customer value above, suppose your contribution margin is 40% after product costs, payment fees, fulfillment, shipping subsidies, and other variable costs you have chosen to include:

90-day contribution per customer = $120 × 40% = $48.

At a $60 customer acquisition cost, the cohort has not recovered acquisition spending within 90 days. It remains $12 short per customer, before fixed overhead. It may recover that cost later, but the 90-day result does not establish that it will.

Calculate CAC with a matching scope: acquisition spending attributed to the cohort divided by newly acquired customers. State whether it includes only advertising or also agency fees, creative production, and other acquisition costs. Avoid counting a cost in both contribution and CAC.

Compare channels at the same customer age

The following invented example shows why a higher spending customer is not automatically more profitable:

Acquisition channel	90-day revenue/customer	Contribution margin	90-day contribution/customer	CAC	Contribution after CAC
Paid social	$120	40%	$48	$60	-$12
Paid search	$100	50%	$50	$35	$15

Paid social generates more revenue per customer in this example. Paid search has covered acquisition costs by day 90, while paid social remains short. These totals do not reveal the exact payback date. Keep the attribution method and cost coverage consistent before interpreting a channel comparison.

Track CAC payback as the time when cumulative contribution per acquired customer reaches CAC. Positive contribution after CAC still needs to cover fixed costs; it is not the same as net profit.

For retention work, combine cohort value with RFM segmentation, which groups customers by recency, frequency, and spending. Use cohort comparisons to assess acquisition quality and RFM groups to choose whom to re-engage.

Start with one complete acquisition cohort, reconcile its orders and costs, and record its 90-day contribution alongside CAC. That gives your next budget decision an observable baseline.

Frequently asked questions

Does Shopify show customer lifetime value?

Shopify’s Customer cohort analysis report shows spending per customer and, with 24 months of data, offers projections. To calculate contribution CLV, also deduct the relevant costs of serving those sales.

Should one-time customers be included in CLV?

Yes, include one-time purchasers when calculating average value per acquired customer. Keep customers whose qualifying purchases were later fully refunded as well. Excluding either group makes the result more favorable than the experience of acquiring the full customer population. Analyze repeat buyers separately when that is the question you want to answer.

What is a good customer lifetime value for a Shopify store?

There is no single useful dollar target across stores. Compare contribution value with CAC, payback time, and your cash constraints. A $200 revenue CLV can be unattractive with thin margins and expensive acquisition, while a smaller value can work with stronger margins and lower acquisition costs.

Why does my SQL result differ from Shopify reports?

Check the revenue definition, eligible orders, customer identities, currency, timezone, and refund treatment. Also check whether you are comparing calendar periods, customer-age windows, or entire purchase histories. A custom net-revenue calculation should not be expected to match Shopify’s native AOV or total-sales measures without reconciling those definitions.