Building a Multi-Year Retention Cohort Table From Scratch

A cohort is usually defined by signing period, month, quarter, or year depending on how many new accounts you sign in a typical period. A business signing a handful of accounts a month should group by quarter or year to keep each cohort large enough to show a meaningful pattern rather than noise from one or two accounts. A business signing dozens of accounts monthly can cohort by month and still have enough volume per group to trust the pattern. Pick the grouping that keeps each cohort big enough to mean something, and keep the definition consistent once you choose it.

The layout that actually lets you compare across years puts each signing cohort in its own row, and each column represents elapsed time since signing, quarter one, quarter two, quarter three, rather than a fixed calendar date. This lets you place a cohort that signed three years ago directly next to one that signed last quarter and compare them at the same point in their lifecycle, which a calendar based layout cannot do cleanly. Getting this layout right from the start saves you from rebuilding the whole table later once you realize calendar columns do not let you compare cohorts fairly.

Pull each cohort's actual starting ARR at signing and its actual ARR at each subsequent period from your billing or CRM system, rather than estimating from average deal size, which smooths over exactly the variation you are trying to see. Express each cell as that cohort's revenue at that period divided by its starting revenue, so every cohort starts at a common baseline regardless of its original size, and the percentages across a row become directly comparable to any other row in the table. Double check that expansion, contraction, and churn are each captured somewhere in the underlying pull, since a table that only reflects gross churn while quietly missing contraction will consistently overstate how healthy each cohort actually is.

The real value of a multi-year table is reading down a column, comparing every cohort at the same elapsed point, rather than only reading across one cohort's own row. If your most recent cohorts are retaining worse at the same elapsed point than cohorts from a couple of years ago, that is an early warning about something that changed, in your onboarding, your product, or the type of customer you are now signing, well before it would show up in a single blended quarterly number. A table read only row by row misses exactly this kind of cross cohort deterioration.

For example, suppose the cohort that signed two years ago kept most of its starting revenue by the fourth quarter, while last year's cohort has kept noticeably less at the same point. Reading across either row would look fine on its own. Reading down the column shows something changed, perhaps in onboarding, the product, or the type of customer you are now signing. A common mistake is explaining the gap away as noise before checking the size of each cohort. The decision rule: when a newer cohort trails older ones at the same elapsed point, investigate what changed before the trend spreads.

The most common failure with cohort tables is not building one, it is building one for a single board meeting and never updating it again. Set a recurring calendar reminder tied to your close process, and treat updating the table as part of closing the books, not a special project someone remembers to do occasionally. A table with a full history of consistent updates becomes genuinely valuable after a few years. A table rebuilt from scratch every time someone asks for it never accumulates that value and usually contains small definitional inconsistencies between versions.

Build and maintain the table in this order:

1. Choose a cohort grouping, by month, quarter or year, that keeps each cohort large enough to show a real pattern, and keep it consistent.
2. Put each signing cohort in its own row, and make each column the elapsed time since signing instead of a calendar date.
3. Pull actual starting revenue and actual revenue for each later period from your billing or CRM system, not an estimate.
4. Divide each period's revenue by the cohort's starting revenue, and confirm expansion, contraction and churn are all captured.
5. Update the table as part of every close, so it builds a consistent history instead of being rebuilt on request.