Aggregate churn hides when customers leave; a cohort table shows it. How to build one from billing data (columns, rows, what goes in each cell), how to read the retention curve it produces, the three shapes the curve takes and what each means, which metrics to derive from it, and a template structure you can reproduce in a spreadsheet or SQL.
Short answer
How do you do a churn cohort analysis?
Group customers by their first-payment month, then for each group count the share still paying at month 0, 1, 2, 3 and onward. Rows are cohorts, columns are months of age, cells are retention percentages. Plot the columns and you get the retention curve; read down a column and you see whether newer cohorts retain better than older ones at the same age. One SQL query or one spreadsheet of COUNTIFS produces the whole table.
An aggregate churn rate says how many customers left this month. It does not say whether they were three weeks old or three years old, and those are different problems with different fixes. A cohort table separates them. It is the single most useful churn analysis a SaaS company can do with data it already has, and it needs nothing more than a list of customers with a start date and, where there is one, an end date.
One row per customer with three fields: customer id, first paid date, and cancellation date(empty if still active). From billing, the first paid date is the first successful invoice, not the signup and not the trial start; the cancellation date is when the subscription ended. If you want the revenue version, add each customer's MRR at start and MRR now.
Two decisions before building anything. First, a customer who churned and came back is two customers in this analysis: the first ends at the cancellation, the second starts at the reactivation. Second, a subscription that lapsed on a failed card is a churn in the table; tag it, because a cohort with heavy involuntary churn needs a billing fix, not a product one.
For each customer, compute cohort = the month of first paid date, and age at exit = months between first paid date and cancellation date (or “still active”). Then for each cohort and each age N, retention at N = customers in the cohort whose age at exit is greater than N (or who are still active and at least N months old) ÷ cohort size.
In SQL, against a table of customers with those three fields:
In a spreadsheet, one sheet holds the customer list with a cohort column and an age-at-exit column; the table sheet has cohorts down the left, ages across the top, and each cell is COUNTIFS(cohort = this row, age-at-exit > this column) divided by the cohort size. The WHERE first_paid_at + n months <= now() line has a spreadsheet equivalent too: leave a cell blank when the cohort is not yet old enough to have reached that age, or the bottom-right of the table fills with retention figures that mean nothing.
| Cohort | Size | M0 | M1 | M2 | M3 | M6 | M12 | M13 |
|---|---|---|---|---|---|---|---|---|
| Jan | 42 | 100% | 88% | 83% | 81% | 79% | 76% | 64% |
| Feb | 51 | 100% | 86% | 82% | 80% | 78% | 75% | · |
| Mar | 47 | 100% | 89% | 85% | 83% | 81% | · | · |
| Apr | 58 | 100% | 91% | 88% | 86% | 84% | · | · |
| May | 63 | 100% | 92% | 89% | 88% | · | · | · |
| Jun | 60 | 100% | 93% | 90% | · | · | · | · |
Illustrative numbers. Three things are visible in it that no aggregate rate shows. Reading across the January row: most of the loss is in months one and two (12 points), then the curve flattens near 80%, then it drops 12 points between month 12 and 13. That is early-life churn, a healthy plateau, and an annual-renewal cliff, in one line. Reading down the M1 column: 88, 86, 89, 91, 92, 93. Newer cohorts are retaining better at the same age, which is what a working onboarding change looks like three months before it shows in the aggregate.
Churn cohort analysis groups customers by when they started (usually the month of first payment) and tracks what share of each group is still paying one, two, three and more months later. The result is a table with one row per cohort and one column per month of age, and a curve when you plot it. It answers the question aggregate churn cannot: when in a customer's life do they leave, and is that changing for newer customers.
To build a churn cohort table, take every customer's first-payment month as their cohort and their cancellation month (if any) as their exit. For each cohort, count how many were active at month 0, 1, 2 and so on after their start, and divide by the cohort's starting size. Rows are cohorts, columns are months of age, cells are retention percentages. In SQL it is one query grouped by cohort month and age; in a spreadsheet it is a COUNTIFS per cell.
A good retention curve in cohort analysis drops in the first one to three months and then flattens: the customers who never activated leave early and the rest stay. The height where it flattens is the number to manage. A curve that keeps declining at a steady rate means nothing is retaining customers durably, and a curve that is flat then drops at month 12 or 13 shows an annual-renewal cliff.
A cohort analysis yields month-N retention (the share of a cohort still active at age N), the flattening point and level of the curve, early-life churn (the drop between month 0 and month 3), the annual cliff (the drop at month 12 to 13), and cohort-over-cohort change, which is whether newer cohorts retain better or worse than older ones at the same age. The last one is the earliest measurable signal that a product or onboarding change worked.
Both, and they answer different questions. A customer cohort table shows whether accounts stay; a revenue cohort table, where each cell is the cohort's current MRR as a share of its starting MRR, shows whether money stays, and it can exceed 100% at later ages when survivors expand. If the customer curve slopes down while the revenue curve stays flat, the small accounts are leaving and the large ones are growing.
The table is illustrative and the SQL is a Postgres pattern; adjust the date arithmetic for your warehouse. The distinction between a churn that was a decision and one that was a failed card comes from how Exeechain reads a billing account, where the two are tagged separately because the fix is different; the Stripe measurement notes cover how to pull both from subscription data.
Keep reading
Retention metrics
9 min read · Sep 18, 2026
Retention metrics
9 min read · Sep 18, 2026
Retention metrics
9 min read · Sep 18, 2026
First scores in 15 minutes. Full accuracy in 24 hours. From $299/mo. Churn read from billing with the involuntary share tagged, and every at-risk account scored with its drivers.
Not ready to switch? Size your leak from your MRR and churn rate, with no billing access at all.