How to Calculate Customer Lifetime Value From a CSV (Step-by-Step)
To calculate customer lifetime value from a transaction CSV, work out average order value, purchase frequency and customer lifespan. Multiply the three together, then apply your gross margin. The arithmetic is straightforward. The judgment calls — which customers count as churned, and whether to look backwards or forwards — decide whether the answer is useful.
Two teams can run the same formula on the same file and land two figures that differ by a factor of three. Neither is wrong. They answered different questions without noticing.
This guide covers the inputs you need, the manual calculation in three stages, and where the spreadsheet route stops holding up.
What you need before you start
You need a transaction-level export: one row per order, with a customer identifier, an order date and an order value. A summary by month will not work, because lifetime value is fundamentally about how individual customers behave over time.
Two decisions come before any formula, and they change the answer more than any calculation does.
Historical or predictive. Historical customer lifetime value totals what customers have actually spent so far. Predictive estimates what they will spend in future. Historical is defensible and always understates; predictive is more useful for budgeting and depends entirely on your retention assumption.
What counts as churned. For a subscription this is easy — the cancellation date. For transactional businesses there is no cancellation event, so you need a rule. A customer who has not purchased in twelve months is treated as lapsed, for instance. Pick a window and write it down.
A good result looks like this. You get a per-customer table with total spend, order count, first and last order date, and a segment label. Alongside it sits a headline figure with the margin assumption stated next to it.
How to do it manually
Option 1: Work out average order value and purchase frequency
Group the transaction file by customer identifier. For each customer, sum the order values and count the orders. Average order value is total revenue divided by total orders across the base. Purchase frequency is total orders divided by the number of unique customers over the period.
The trap here is the period. Frequency measured over twelve months and frequency measured over the full history are different numbers, and mixing them into one formula produces something meaningless. Fix the window first and use it consistently.
Option 2: Add the retention curve
Customer lifespan is the input most people guess. The defensible version comes from your own data: group customers by acquisition month, then count how many were still active in each subsequent month.
That gives a retention curve. Average lifespan is roughly the area under it — in a spreadsheet, the sum of the monthly retention rates. If 100 customers retain at 80%, 65% and 55% over three months, you are accumulating lifespan month by month rather than assuming a round number. Our guide to building a cohort retention chart covers the chart side of this, and what cohort analysis is covers the concept.
Option 3: Turn it into LTV and the LTV:CAC ratio
Multiply average order value by purchase frequency by lifespan, then multiply by gross margin. Revenue-based lifetime value overstates the figure, sometimes badly, because it treats a low-margin product the same as a high-margin one.
Then divide by customer acquisition cost. That ratio is what the number is for. Lifetime value in isolation is trivia; lifetime value against what you paid to acquire it is a decision.
Where the manual route slows down
Segmentation is where it breaks. A single blended figure averages your best and worst cohorts into one uninformative number. The useful version is per channel, per plan or per acquisition month. Each segment means rebuilding the whole chain of formulas against a filtered subset. There are usually more segments worth checking than anyone has patience for, so most go unchecked.
Re-running is the second problem. Next quarter's file has more rows and, often, differently named columns. The retention grid that took an afternoon to build needs its ranges extended and its formulas rechecked. A missed range is invisible in the output, which is what makes it dangerous.
And the assumptions are invisible. Six months later the file shows a number, and nobody remembers whether it was revenue or margin based, or what churn window was used. That is how a stale figure ends up in a board deck.
There is a subtler failure too. Because the model is expensive to rebuild, it tends to be run once and then quoted for a year. Lifetime value is not a constant. It moves when pricing changes, when the acquisition mix shifts towards a cheaper channel, and when a product change alters retention. A figure calculated last spring may describe a business that no longer exists.
How to calculate customer lifetime value with Powerdrill Bloom
Step 1: Upload your data
Upload the transaction CSV directly. Powerdrill Bloom profiles the columns, so you can confirm you have a usable customer identifier and a clean date column before any grouping happens.
Step 2: Describe the calculation in natural language
State the rules as you would to a colleague. Treat customers with no purchase in twelve months as lapsed, apply this gross margin percentage, and calculate lifetime value by acquisition channel. Then ask the follow-ups that matter — how the figure differs between the top and bottom quartile, or how it has moved across acquisition cohorts.
Step 3: Export the chart, report, or deck
Take out the per-segment table, the retention curve behind it, or a written summary with the assumptions recorded alongside the number.
Why this beats rebuilding the model each quarter
| Spreadsheet route | Powerdrill Bloom | |
|---|---|---|
| Segmenting by channel | Rebuild the formula chain per segment | Add it to the request |
| Changing the churn window | Rework the retention grid | Ask again with a new window |
| Recording assumptions | Manual note, often missing | Stated in the request |
| Next quarter's file | Extend ranges, check formulas | Upload and re-ask |
The second row is the one that changes what you learn. A twelve-month churn window and a six-month one can produce very different lifetime values. The honest way to handle that is to run both and see whether your conclusion survives. In a spreadsheet, nobody does that twice.
Common mistakes
Using revenue instead of margin. Revenue-based lifetime value flatters every business and badly distorts any comparison against acquisition cost.
Blending all customers into one figure. The average across a base with very different segments describes no actual customer. Segment before you conclude.
Guessing the lifespan. "About two years" is the most common input and the least defensible one. Derive it from your own retention curve instead, even if the curve is short.
Ignoring the survivor effect. Historical lifetime value counts only customers who already existed, which skews towards long-tenured ones. Cohort-based calculation avoids this.
Reporting it without CAC. A lifetime value of $900 means nothing until you know whether acquisition cost $90 or $800.
Treating the figure as durable. Recalculate whenever pricing, product or acquisition mix changes materially. A number carried forward from last year is a guess wearing a decimal point.
Conclusion
Calculating customer lifetime value from a CSV takes three stages. Average order value and frequency, a retention curve from your own cohorts, then margin and the ratio against acquisition cost. Do it in a spreadsheet and it works — right up until you need it segmented, or need to test a different churn window.
If the model is only rebuilt when someone asks for it in a meeting, try Powerdrill Bloom on your transaction export. Our CSV AI assistant page covers the wider workflow, and turning a CSV into a chart covers the visual output.
Frequently asked questions
What is the formula for customer lifetime value?
Multiply average order value by purchase frequency by average customer lifespan, then multiply by gross margin. Using revenue rather than margin inflates the result and makes any comparison against acquisition cost unreliable.
How do I calculate customer lifetime value in Excel?
Group the transaction file by customer identifier to get order counts and totals. Build a cohort retention grid to derive lifespan, then combine the three inputs. The retention grid is the laborious part and needs rebuilding whenever the window changes.
What is the difference between historical and predictive LTV?
Historical totals what customers have already spent, so it always understates and is easy to defend. Predictive estimates future spend from a retention assumption, which makes it more useful for planning and more sensitive to being wrong.
What is a good LTV to CAC ratio?
The commonly cited target is three to one, but it depends on your margin structure and payback period. The more useful question is how many months it takes for a customer to repay their acquisition cost.
How do I handle customers who have not purchased recently?
Define a churn window and apply it consistently — twelve months without a purchase is a common rule for transactional businesses. Record the window next to the figure, since changing it can move lifetime value substantially.