Thought Leadership, Digital PR & Content SyndicationPlaybook3 min readUpdated September 2026

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:

  1. Export each customer's signup date and monthly revenue from your billing system into one sheet.
  2. Pick one consistent definition of revenue per customer per month and write it down, so older and newer cohorts stay comparable.
  3. Group customers into rows by signup month, with columns for months since signup.
  4. Fill each cell with the average revenue per customer in that cohort at that point in its lifecycle.
  5. 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.

Executive Capability Standard

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)

1. Learn:Export monthly revenue per customer for your last six months of signups and group them by signup month in a spreadsheet.
2. Do Manually:Build a basic cohort table by hand with signup month as rows and months since signup as columns.
3. Delegate:Have a revenue operations or finance-adjacent team member own updating and reviewing the cohort table monthly.
4. Automate:Move cohort tracking into a dedicated analytics tool once manual spreadsheet updates become a real time cost.
5. Buy:Bring in a fractional analytics consultant if you need to segment cohorts by channel, plan, or industry and don't have the internal bandwidth.

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