Customer Lifetime Value: How to Calculate CLV From an Order Export
How to calculate customer lifetime value in Excel from an order export, and why the usual CLV formula gave over three times the real 12-month figure.
- E-commerce
- Retention
- Excel

On this page
- What is customer lifetime value?
- How do you calculate customer lifetime value?
- How do you calculate CLV from an order export in Excel?
- What does a real order export show?
- Why does the formula give a higher CLV than the export?
- Why is the average CLV misleading?
- How do you use CLV with customer acquisition cost?
- Where does Glow Reports fit?
- Common questions
Customer lifetime value (CLV) is the revenue, or better the margin, a customer brings over the whole relationship. It tells you how much you can spend to win a customer and still make money. Most guides give the formula, average order value times purchase frequency times lifespan, and stop there. Run that formula and a measure from the actual order export side by side, and one answer can be more than three times the other. Here is how to calculate both in Excel from an order export, what they gave on a real retailer's data, and which one to trust for which decision.
What is customer lifetime value?
Customer lifetime value is the total a customer spends with you from the first order to the last. You can measure it two ways: from history, adding up what customers already spent over a set period, or with a formula that multiplies average order value, purchase frequency and expected lifespan.
Shopify describes the same split. Historical CLV counts purchases already made. The formula version estimates the rest of the relationship from purchase patterns Shopify. The first is a fact about the past. The second is an estimate built on assumptions, and the assumptions decide the result.
Revenue CLV is the easier number to get from an export. Profit CLV is the useful one. A customer who spends 1,000 on a 30% gross margin brings you 300 to pay for acquisition and overheads. Multiply any revenue CLV by your gross margin before you compare it with what you spend to win customers.
How do you calculate customer lifetime value?
Multiply average order value by the number of orders a customer places per year, then by the number of years a customer stays. With yearly figures, CLV = AOV × annual purchase frequency × lifespan in years Shopify.
Each input comes from the export:
- Average order value is revenue divided by orders. Count orders, not product lines. How to calculate average order value covers exports with one row per product.
- Purchase frequency is orders in the year divided by the customers who ordered that year.
- Lifespan is the hardest. Without years of history, estimate it as one divided by the yearly churn rate HubSpot. If 36.4% of last year's customers didn't come back, the lifespan is 1 / 0.364, or 2.75 years.
On the retailer data used below, the year from December 2009 to November 2010 gives these inputs:
| Input | Value |
|---|---|
| Customers who ordered | 4,266 |
| Average order value | £460.36 |
| Orders per customer in the year | 4.32 |
| Revenue per customer in the year | £1,989.59 |
| Customers still ordering the next year | 63.6% |
| Lifespan, 1 / (1 - 63.6%) | 2.75 yrs |
| Customer lifetime value (formula) | £5,465 |
The figures are in pounds sterling. A subscription business uses the same logic per month: average revenue per account times gross margin, divided by the monthly churn rate Wikipedia.
How do you calculate CLV from an order export in Excel?
Build one row per customer with the date of the first order and the revenue in the 12 months that follow. The average of that column is a 12-month historical CLV. It needs three functions: MINIFS for the first order, EDATE for the date 12 months later, and SUMIFS for the revenue in between.
Step by step, with the export turned into a table named Orders, with columns Invoice, Date, Customer and Revenue:
- Add a Revenue column if the export only has quantity and unit price: . Remove cancelled orders and rows without a customer ID first.
- On a new sheet, list each customer once in A2 with Microsoft Support.
- In B2, the first order date: . MINIFS returns the smallest value that meets the criteria Microsoft Support.
- In C2, the revenue in the first 12 months: . EDATE returns the date a given number of months later Microsoft Support, and SUMIFS adds the rows that meet every condition Microsoft Support.
- Keep only customers you can follow for 12 full months, and drop the first month of the export. Customers in the first month were already buying before the export started, so they aren't new. Flag the rest in D2 with .
- The 12-month CLV is . Add the median next to it with .
On a large export, SUMIFS across every customer gets slow. The same steps in SQL, for customers whose first order fell between January and November 2010 in the data below:
WITH first_orders AS (
SELECT customer_id, MIN(order_date) AS first_date
FROM orders
GROUP BY customer_id
),
first_year AS (
SELECT f.customer_id, SUM(o.revenue) AS revenue_12m
FROM first_orders f
JOIN orders o
ON o.customer_id = f.customer_id
AND o.order_date < f.first_date + INTERVAL '12 months'
WHERE f.first_date >= TIMESTAMP '2010-01-01'
AND f.first_date < TIMESTAMP '2010-12-01'
GROUP BY f.customer_id
)
SELECT COUNT(*) AS customers,
ROUND(AVG(revenue_12m), 2) AS clv_12m
FROM first_year;What does a real order export show?
New customers of a UK online gift retailer spent £1,521 on average in their first 12 months. More than a quarter of that came with the first order. The data is Online Retail II, a public dataset whose customers are often wholesalers UCI Machine Learning Repository.
After removing cancellations, duplicate lines, lines without a customer ID and lines with a zero or negative quantity or price, it holds 36,969 orders from 5,878 customers, from 1 December 2009 to 9 December 2011. These are the same orders as in our customer retention cohort report. We computed every figure here with our own code, then checked the 12-month CLV with the SQL above.
The 3,311 customers who placed a first order between January and November 2010 can all be followed for 12 months:
| Time after the first order | Average revenue per customer |
|---|---|
| First order | £418 |
| 3 months | £705 |
| 6 months | £994 |
| 12 months | £1,521 |
£1,521 is the 12-month CLV of a new customer. The formula above gives £5,465.
Why does the formula give a higher CLV than the export?
Because its inputs come from your whole active base, and your best customers dominate it. The formula above used every customer who ordered between December 2009 and November 2010. More than a fifth of them, 955, were already buying when the data starts, and loyal wholesale customers stay longer and buy more than new ones.
The data shows the gap. Of the 955 customers who ordered in December 2009, 76.0% ordered again between December 2010 and November 2011. Of the 3,311 new customers of 2010, 60.0% did. Over the full two years, the first group spent £8,917 each, and the second £2,142.
The formula also assumes the same retention every year, forever. Real retention is lowest right after the first order and higher among customers who stay. Fed with whole-base averages, the formula describes a typical existing customer, not the new customer you are paying to acquire.
Rebuild it from new customers only and the estimate drops: £1,521 in the first year, divided by 1 - 60.0%, gives £3,804. That is still an estimate. Use the 12-month historical CLV for decisions about acquisition spend, because it is a fact you can check. Treat the formula result as an upper bound, and compute its inputs on new customers.
Why is the average CLV misleading?
Because a few customers carry it. Among the 3,311 new customers of 2010, the median first-year revenue was £727, less than half the £1,521 average. The top 10% of customers brought 48% of first-year revenue, and the largest one spent £208,296 on its own.
At the other end, 28.7% of these customers ordered only once in their first year. An average built over both groups describes almost nobody. Report the median next to the average, and split CLV by the month of the first order, the acquisition channel or the first product bought. Shopify makes the same point: grouping customers shows that some are far more valuable than others Shopify.
Retention tells you which segments come back. The churn report guide shows how to measure who leaves.
How do you use CLV with customer acquisition cost?
Compare the two on the same basis: margin, not revenue, and the same time window. If a customer brings £1,521 of revenue in 12 months at a 30% margin, that is £456 to cover the cost of acquiring them. Spend more than that per new customer and the first year loses money.
The LTV-to-CAC ratio compares lifetime value with acquisition cost. Shopify suggests comparing it by acquisition channel, because a healthy blended ratio can hide a channel that brings low-value customers Shopify. The dataset above has no costs or channels, so its figures stay as revenue CLV. With your own data, add the channel to each customer row and run the 12-month CLV per channel.
Where does Glow Reports fit?
The Excel steps above work on one export. They get slow on hundreds of thousands of rows, and they have to be rebuilt each time someone asks for CLV by first-order month or by country.
With Glow Reports, you upload the order export as CSV or Excel and ask for the 12-month CLV of new customers by first-order month, with the median next to the average. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can check which customers count as new and which window applies, and edit the query. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.
Common questions
What is the difference between CLV and LTV?
None. Customer lifetime value (CLV) and lifetime value (LTV) name the same measure. LTV is more common in SaaS and in the LTV-to-CAC ratio, CLV in e-commerce and marketing.
What is a good customer lifetime value?
One that is well above what you pay to acquire a customer, once both are on a margin basis. A CLV figure on its own says little. Compare it with acquisition cost per channel, and with the same figure a year earlier.
Should customer lifetime value use revenue or profit?
Profit, for any decision about spend. Revenue CLV is easier to compute from an export, so start there and multiply by your gross margin. The subscription formula on Wikipedia multiplies revenue by gross margin before dividing by churn.
How long a period should CLV cover?
Pick a window you can observe, such as 12 or 24 months after the first order, and only include customers you can follow for the whole window. A shorter window is a fact. A lifetime figure is always an estimate.
How do I calculate CLV for a subscription business?
Take the average monthly revenue per account, multiply it by the gross margin, and divide by the monthly churn rate. A 5% monthly churn means an average lifespan of 20 months. Net revenue retention shows how expansion and contraction change the picture.
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your order export to Glow Reports and ask for the 12-month CLV of new customers, with the SQL behind each figure.


