Building a Cohort Table That Actually Shows Where LTV Comes From
A single lifetime value number hides more than it reveals. It blends customers who signed up two years ago and expanded steadily with customers who signed up last month and haven't had time to do anything yet, and it treats a shrinking cohort the same as a growing one as long as the average comes out flat.
A cohort table fixes that by grouping customers by when they signed up and tracking each group's revenue over time separately. Here's how to build one from scratch, even in a spreadsheet, before you reach for a dedicated analytics tool.
Vendors Covered in this Article
Disclosure: We may earn a commission if you buy through some links on this page. It doesn't change what we recommend.
How do you set up a cohort table in a spreadsheet?
Put signup month down the left side of your spreadsheet as rows, and months since signup across the top as columns. Each cell holds that cohort's revenue (or active account count, if you'd rather track retention before revenue) at that point in its lifecycle. This is the standard cohort layout, and it's the same shape whether you're tracking ten customers or ten thousand.
Fill it with revenue, not just headcount
A cohort retention table alone tells you whether customers stayed. It doesn't tell you whether the ones who stayed spent more or less over time. Track net revenue per cohort at each point, including expansion (upsells, seat growth) and contraction (downgrades, partial cancellations) within the same customer relationship, not just whether the logo is still active.
How do you read the diagonal of a cohort table?
Compare the same point in the lifecycle across different cohorts: month six for the cohort that signed up in January versus month six for the cohort that signed up in June. If later cohorts consistently expand faster at the same point in their lifecycle than earlier ones, something you changed (onboarding, product, pricing, targeting) is working. If later cohorts are underperforming earlier ones at the same stage, something has quietly gotten worse, and the blended average number would have hidden it for months.
Separate the reasons expansion happens
Not all expansion is the same kind of signal. A customer expanding because they're using the product more and adding seats organically tells you something different than a customer expanding because a rep upsold them into a bigger package at renewal. Tag expansion revenue by its source if you can, since organic expansion is a much stronger predictor of long-term retention than a renewal-time upsell.
Turn the table into an action, not just a report
Once you can see which cohorts underperform at which lifecycle stage, use that to target intervention: a lifecycle email nudge at the exact month where a cohort typically stalls, or a customer success check-in timed to land before the point where past cohorts historically churned or downgraded. The table is only useful if it changes what your team does, not just what your team reports on.
For example, suppose the table shows that several consecutive cohorts stall around month four, with expansion flat and a few downgrades appearing right after. Instead of waiting for the churn, schedule a lifecycle email that points to a relevant next feature at the end of month three, and ask customer success to check in with the largest accounts before month four. Then watch the next cohort at that same point. If it stalls less, the intervention is helping. If the curve looks the same, the cause is probably elsewhere, such as onboarding or fit, so adjust the approach rather than repeating the nudge. Keep a short note beside the table recording what you changed and when.
Watch for a common trap: survivorship bias in the recent cohorts
A cohort from three months ago that already looks strong may just not have had enough time yet to show the contraction or churn that shows up later in a lifecycle. Comparing a young cohort's early numbers directly against an older cohort's full trajectory can make recent performance look better than it actually is. Wait until a cohort has passed the same lifecycle point as the one you're comparing it to before drawing a conclusion, and mark any comparison involving a very young cohort as provisional until then.
Segment the table by acquisition source once the basics work
Once the core cohort-by-month table is running reliably, add a second dimension: acquisition source. A cohort that came in through a referral or organic search often expands differently over its lifecycle than one that came in through a paid campaign, and blending them back into one row hides that difference the same way a single blended LTV number hides cohort differences in the first place. This extra dimension takes real setup work, so add it only after the basic table has proven useful and is being checked regularly, not as your first build.
Steps for building the cohort table:
- Put signup month down the rows and months since signup across the columns, with each cell holding that cohort's revenue or active accounts.
- Track net revenue per cohort, including expansion and contraction, instead of only whether each logo is still active.
- Compare the same lifecycle point across different cohorts to see whether later cohorts are improving or quietly getting worse.
- Tag expansion revenue by source, separating organic seat growth from an upsell at renewal.
- Mark comparisons involving very young cohorts as provisional, then add acquisition source as a second dimension once the basic table works.
What Good Looks Like
A good cohort table groups customers by signup period, tracks net revenue (not just headcount) at each point in the lifecycle, and gets used to trigger specific interventions rather than sitting as a report nobody acts on.
Building The Capability (5-Stage Skill Ladder)
How to Get Started
Disclosure: We may earn a commission if you buy through some links on this page. It doesn't change what we recommend.
Frequently Asked Questions
How many months of history do we need before this is useful?
You can start with whatever history you have and add to it monthly; even three or four cohorts give you a rough sense of whether trends are moving in the right direction. The comparisons get more reliable the more cohorts you accumulate, but don't wait a year to start building the table.
Should we build this by hand or use a tool?
Start by hand in a spreadsheet. Building the first version yourself forces you to understand exactly what the numbers mean and where the data comes from, which makes it much easier to spot a bug or a bad assumption later, whether you keep it manual or eventually move to a dedicated analytics tool.
What counts as a meaningful difference between cohorts?
Look for a consistent direction across several consecutive cohorts rather than a single month that looks off, since one cohort can be skewed by a handful of unusually large or small accounts. A trend that holds across three or more consecutive cohorts is much more trustworthy than a single data point.
About the numbers
This guide doesn't quote a sourced benchmark. Figures in it are estimates or general guidance, so check them against your own numbers.
Related Guides
Choosing the Right Cohorts for LTV and Expansion Analysis
A decision guide for picking cohort definitions when analyzing customer lifetime value and expansion revenue, and the mistakes that make the numbers misleading.
Lifecycle Email Conversion Benchmarks: What 'Good' Actually Means
Generic conversion benchmarks don't fit lifecycle email. Here's how to set a realistic target for onboarding, post-purchase, and win-back flows.
Writing an Onboarding Email Sequence New Users Actually Read
How to structure a post-signup nurture sequence around what a new user needs to do next, instead of a fixed schedule of feature announcements.
Reading Cohort LTV Data Without Fooling Yourself
How to build cohorts that actually reveal expansion opportunity, avoid the most common cohort analysis mistakes, and act on what the data shows.
A Runbook for Growing LTV Cohort by Cohort
A step-by-step way to group customers into cohorts by signup period and run targeted expansion plays against each one instead of one blanket campaign.
Reading Cohort Data to Find Where Customer Value Actually Grows
How to break customers into cohorts and read the resulting data to find which acquisition sources and segments produce lasting, expanding value.