How to Build a Cohort LTV Table Without a Data Team
A single, blended lifetime value number hides more than it reveals. It mixes customers who signed up two months ago with customers who've been around for two years, and it treats every acquisition channel as if it produced the same kind of customer.
A cohort table fixes that by grouping customers by the month they signed up, so you can watch how each group's revenue actually develops over time instead of staring at one flattened average.
What a cohort table actually looks like
Rows are signup months, columns are months since signup, and each cell shows average revenue per customer in that cohort by that point in their lifecycle. Say your January cohort averaged fifty dollars a month in revenue by month three, and your April cohort averaged sixty dollars by the same point. That gap tells you something changed, in your product, your pricing, or the kind of customer you're now acquiring, worth investigating directly.
Build it once by hand before you automate anything. Seeing the actual numbers populate the grid teaches you more about where to look next than a polished dashboard would on the first pass.
Don't worry about making the first version perfect. A rough table with a few gaps in it, built this week, tells you more than a polished version you keep delaying because the data isn't fully clean yet.
Start with a spreadsheet, not a tool
You can build a useful version of this in a spreadsheet with a signup date and monthly revenue per customer exported from your billing system. It's tedious the first time and fast every month after, once the formulas are set up. Don't wait for a dedicated analytics tool to start looking at this data; the spreadsheet version is genuinely good enough for a small customer base.
The first export usually takes the longest, mostly because you're deciding what counts as revenue for a given customer in a given month. Pick a consistent definition and stick with it, since switching definitions partway through makes older cohorts impossible to compare fairly against newer ones.
To build a first cohort table in a spreadsheet:
- Export each customer's signup date and monthly revenue from your billing system into one sheet.
- Pick one consistent definition of revenue per customer per month and write it down, so older and newer cohorts stay comparable.
- Group customers into rows by signup month, with columns for months since signup.
- Fill each cell with the average revenue per customer in that cohort at that point in its lifecycle.
- Plot a few cohorts on one chart to compare their shapes, then split the table by acquisition channel once it works.
Watch cohort shape, not just the final number
Two cohorts can reach the same lifetime value through very different paths, one through strong early expansion that plateaus, another through slow, steady growth that keeps climbing. The second shape is usually healthier even at an identical final number, since it suggests ongoing expansion rather than a one-time upsell that already happened.
Plot a few cohorts on the same chart, months since signup on one axis, revenue on the other. The visual comparison makes a flattening curve obvious in a way a table of numbers alone often doesn't, especially once you're comparing more than three or four cohorts at once.
Segment by acquisition channel once the basic table works
Once your cohort table is running, split it by how each cohort was acquired, paid, organic, referral, and so on. It's common to discover that one channel brings in customers with a noticeably lower lifetime value even when the initial cost per acquisition looked comparable, which changes how you'd actually weight that channel's budget.
This is usually the single most useful cut of the data for a small marketing team, since it turns an abstract channel mix debate into a concrete comparison you can point to when deciding where next quarter's spend should go.
What to do once you see a declining trend
If recent cohorts are tracking below older ones at the same point in their lifecycle, don't just watch it happen. Check whether onboarding, pricing, or the mix of customer segments you're acquiring has shifted recently, since a declining trend caught within a quarter is fixable in a way a declining trend caught a year later usually isn't.
Martha, MeetMyCMO's AI CMO, can help you build a starting version of this table from a spreadsheet export if you want a template to work from rather than building the row and column structure from scratch.
For example, suppose your last few cohorts all trail the older ones at the same point in their lifecycle. A common mistake is reacting to the whole trend at once, by changing pricing and rebuilding onboarding in the same month. A better approach is to test one explanation at a time. Compare the cohorts by acquisition channel first, since a shift in channel mix is the quickest thing to confirm. Then look at any onboarding changes made around the same time, and only then consider pricing. Write down what you checked and what you found, so the next quarterly review starts from evidence instead of guesses.
What Good Looks Like
A good cohort LTV practice tracks revenue by signup month rather than one blended average, reviews the shape of each cohort's growth, and breaks the picture down by acquisition channel to catch quality differences that a single number would hide.
Building The Capability (5-Stage Skill Ladder)
How to Get Started
Frequently Asked Questions
How many months of cohort data do we need before this is useful?
You can start seeing useful patterns with as few as three or four months of cohorts, though the picture gets more reliable the longer you track. Don't wait a full year to start; early signal, even if imperfect, beats no visibility at all into how revenue develops.
What counts as a good cohort shape for a subscription business?
A cohort that holds steady or grows slightly after the first couple of months, rather than declining sharply, generally indicates healthy retention and expansion. A steep early drop usually points to an onboarding or fit problem worth investigating before you spend more to acquire similar customers.
Do we need a dedicated analytics tool to track this?
Not at first. A spreadsheet with monthly revenue exports handles a cohort table well for a smaller customer base. A dedicated tool becomes worth the cost once manual exports and formula maintenance start eating a meaningful chunk of someone's week.
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
A Content Syndication Checklist That Protects Your SEO
A checklist for syndicating content to partner sites and publications without hurting your own search rankings or splitting your authority.
What Conversion Rate to Expect From a PR Placement
A working set of benchmarks for judging whether your thought leadership, PR, and syndicated placements are converting well or falling flat.
What Leadership Actually Needs to See From Your PR Program
A walkthrough for building an executive dashboard on thought leadership, PR, and syndication that shows real performance instead of vanity metrics.
Building Expansion Triggers Before You Automate Them
Five behavior-based expansion triggers worth building and running manually first, so you catch edge cases before you automate any cross-sell campaign.
Organic Search Priorities When You Don't Have an SEO Team
A stage-based guide to what to fix first in organic search when your marketing team is small and nobody owns SEO full time.
Why a Press Mention Is So Hard to Attribute Correctly
First-touch and multi-touch models both struggle with PR and syndication, especially when a lead calls in instead of clicking through. Here's what to try.