LTV Cohort Analysis From a Single CSV Export

The only columns you need

Three columns: customer ID, order date, and net revenue (after discounts and refunds). Everything else is a bonus. From those three you can build the whole cohort table.

The sample store's order file, ecom-orders.csv, has 15 columns. It carries customer_id and order_date directly, and net revenue comes from its amount, discount and order status columns. If your export is wider, ignore the extra columns until the first table is done.

The pivot

Rows are acquisition months, columns are months since first purchase, and each cell is the cumulative revenue per customer from that cohort. Read across a row and you watch one cohort mature. Read down a column and you compare cohort quality over time.

Reading one row

Take a single cohort, say the customers whose first order was in month 1. Offset 0 is what they spent in that first month, and every later offset adds what the same customers spent in the months that followed. The numbers only go up, because each cell is cumulative. What matters is how fast. A row that climbs for three months and then flattens tells you repeat buying happens early and fades. A row that keeps climbing tells you the product earns loyalty. Two cohorts with the same first-month number can have very different rows, and that difference is the reason to build the table at all.

Net revenue matters

Use revenue after refunds. In the sample store, gross sales were $5,199,611 over 180 days and refunds were $477.6K, a return rate of 9.2%. Net of refunds that is about $4.72M. A cohort table built on gross sales would overstate every cell by roughly a tenth, and the overstatement is worst in the early months, when returns land.

What a short file can and cannot show

The sample file covers 180 days, which is six months of orders. That limits the table, and your own export will have the same limit if it is short:

That is a triangle of 21 cells from six months of orders. The most recent cohort tells you almost nothing yet, and it would be a mistake to call it underperforming. It simply has not had time. The sample store's repeat rate is 23.4%, so about one customer in four returns, and a cohort needs several months before that shows up.

If you want a full year of curves, export at least 12 months. Fourteen is better, because it gives the oldest cohort a full year of follow-up.

The two readings that matter

• Payback window. The column where cumulative LTV crosses your blended CAC. If older cohorts paid back quickly and recent ones have not paid back at the same age, your acquisition quality dropped, and blended ROAS never told you.
• Cohort decay. Look at whether newer rows are lower than older ones at the same offset. That points to product or audience decline, visible quarters before it shows up in total revenue.

The CAC and ROAS guide goes into the payback side and shows how a steady blended number can sit on top of a rising cost per customer.

How to use the table

Once the table exists, three decisions get easier. Budget: cap acquisition spend at what a cohort returns within the payback window you can afford. Timing: send your win-back message at the offset where repeat orders usually start, and not on a fixed calendar. Mix: if one first-order product produces cohorts that keep buying, send more of your ad budget to that product.

A table is also a good tripwire. Review it after every large promotion, since a heavy discount month often brings in a cohort that buys once and does not return. That shows up as a row that is flat after offset 0.

Traps

Leave out incomplete months when comparing cohorts, since a 12-day-old cohort is not underperforming. Segment by first-order product when you can, because LTV by entry product is where merchandising decisions live. And treat gross LTV as a vanity curve. Contribution-based LTV, which is revenue minus the variable costs of serving the order, is the one that should set ad budgets.

Try it on a real file

ProfitFalcon builds the cohort view from an orders export, and you can open the sample store to see it on the Cascade data first. The LTV calculator gives a quick estimate when you only have averages. If you are comparing tools for lifetime value, see ProfitFalcon next to Lifetimely on price and data access.