Cohort analysis for SaaS: build it in Excel, step by step

What cohort analysis actually tells you

A cohort is a group of customers who share a start date — for SaaS, almost always the month they first paid. Cohort analysis tracks each of those groups separately over time instead of blending them into one number.

Why that matters: suppose your overall monthly churn "improved" from 6% to 5%. Did retention actually improve — or did you just acquire a big batch of new customers whose churn hasn't happened yet? A blended number can't tell you. A cohort table can, instantly: you compare the March cohort at month 3 against the January cohort at month 3 — same age, different birth month. If newer cohorts consistently retain better at the same age, the product, onboarding, or audience quality genuinely improved. If not, the "improvement" was mix, not progress.

Three views, in order of usefulness for a subscription operator:

View	Question it answers	Built from
Customer (logo) retention	What share of each cohort still subscribes after 1, 3, 6, 12 months?	COUNTIFS on signup + cancel dates
Revenue retention	How much of each cohort's original MRR is still being paid?	SUMIFS on the price column
LTV curve	How much does a customer from cohort X pay before leaving?	Cumulative sum of the revenue matrix

Logo and revenue retention can disagree — a cohort can look fine on heads while bleeding money because its largest accounts are the ones leaving. That divergence is itself a finding: it tells you churn is concentrated in a specific plan or segment.

Build it in Excel, step by step

You don't need a BI tool for this. The whole analysis is one data sheet and three formulas. (Or skip the build: the free template below has everything already wired.)

Step 1 — the only data you need

One row per customer, three fields that every billing system (Stripe, Paddle, Chargebee) can export:

Column	Example	Notes
Signup Date	2025-01-12	First paid date. Don't cohort on trial start — see mistakes below.
Cancel Date	2025-03-07	Blank if still active. Use the date access actually ended.
Monthly Price	$49	For revenue cohorts. Annual plans: divide by 12 (and see mistakes below).

Step 2 — the cohort matrix (one COUNTIFS formula)

Set up a grid: one row per signup-month cohort (Jan 2025, Feb 2025 …), one column per month offset (M0 = the signup month itself, M1 = the following month, and so on). Each cell counts how many of the cohort's customers were still active in that offset month — meaning their cancel date is after the end of that month, or blank:

Two COUNTIFS added together: the first counts customers who cancelled later than the month being measured; the second counts customers who never cancelled at all (blank cancel date — COUNTIFS can't express "after this date OR blank" in one condition). The cohort's size is the same formula without the cancel conditions.

Step 3 — retention % and the triangle chart

Divide each cell by its cohort's size, format as a percentage, and apply a red→yellow→green color scale (Home → Conditional Formatting → Color Scales). You get the classic triangle chart — triangular because young cohorts haven't lived long enough to fill their right-hand columns yet. Here's the shape, using the sample data that ships in the template:

Cohort	Size	M0	M1	M3	M6
Jan 2025	12	100%	83%	58%	42%
Apr 2025	12	100%	83%	67%	50%
Jun 2025	10	100%	90%	70%	50%

Read it down a column, not just across a row: at the same age (M3), June retains 70% where January retained 58%. In the sample data that improvement is synthetic — in yours, it's the signal that whatever you changed between January and June is working.

Step 4 — revenue cohorts (swap COUNTIFS for SUMIFS)

Duplicate the matrix and replace COUNTIFS with SUMIFS over the price column, same conditions. Now each cell is the MRR that cohort still pays. Cumulative-sum a row and divide by cohort size and you have the realized LTV curve per cohort — the honest version of LTV, built from money already collected rather than a projection.

How to read the curves

• The month-1 cliff. The steepest drop is almost always M0→M1: people who bought, poked around, and left. This cliff is an onboarding and expectation problem — fix activation, not pricing.
• The plateau. Healthy subscription products flatten: after the early drops, the curve levels off into a loyal core that stays for years. If your curves never flatten, you don't have a retention problem — you have a product-value problem, and no amount of acquisition spend outruns it.
• Cohort-over-cohort improvement. The single most useful management signal in the table: are newer cohorts higher than older ones at the same offset? That's the compounding curve investors ask about.
• Divergence between logo and revenue retention. Revenue falling faster than logos means your bigger accounts churn more — segment the matrix by plan and look again.

The mistakes that corrupt the analysis

• Cohorting on trial start instead of first payment. Trials that never convert aren't churn — mixing them in makes M0→M1 look catastrophic and buries the real signal. Cohort paid customers; analyze trial conversion separately.
• Confusing calendar months with offsets. "Active in June" and "active at M3" are different questions. The matrix must be offset-based (age since signup), or cohorts of different ages aren't comparable.
• Cohorts too small to read. In a 10-customer cohort every individual moves the number 10 points. At low volume, group monthly cohorts into quarters before concluding anything.
• Annual plans in a monthly matrix. An annual subscriber can't churn until month 12, which flatters M1–M11. Either analyze annual and monthly plans as separate matrices or accept that the blended one is structurally smoothed.
• Chasing benchmarks instead of trend. Published "good retention" numbers mix B2B and consumer, monthly and annual, $9 and $900 price points. Your own cohorts at the same offset are the benchmark that matters.

Where cohorts meet your payment stack

One column most cohort analyses never add — and the reason a payments person is writing this guide: why each customer left. Cancellation is only one exit. Subscription businesses lose a substantial share of "churned" customers to involuntary churn — failed renewals, expired cards — and to disputes: a customer who chargebacks doesn't cancel, but they're gone (and they took a fee and your compliance ratio with them).

Tag each ended row with its exit type (voluntary cancel / payment failure / dispute) and rebuild the matrix filtered by each. Two patterns worth hunting:

• A cohort with concentrated disputes traces back to an acquisition channel. When one month's cohort shows a dispute spike, that month's marketing mix usually contains the answer — an affiliate, a geo, an ad angle that attracted buyers who never intended to stay. That's a friendly-fraud pattern you fix at acquisition, not at billing.
• Payment-failure churn clusters at renewal offsets (M1, M12). If the involuntary-churn matrix lights up where the voluntary one doesn't, the fix is retries, card-updater, and dunning — cheaper than any win-back campaign.

Frequently asked questions

What is cohort analysis for SaaS?

Grouping customers by signup month and tracking each group's retention and revenue separately over time. Blended metrics mix old loyalists with new signups and hide whether retention is actually improving; cohorts make the comparison honest — March at month 3 versus January at month 3.

How do I do cohort analysis in Excel?

One row per customer (signup date, cancel date — blank if active — and monthly price), then a matrix of signup-month rows × month-offset columns where each cell is the two-part COUNTIFS shown above. Divide by cohort size for percentages, add a color scale for the triangle chart, swap in SUMIFS for revenue. Or download the template with it already built.

Customer retention vs revenue retention — which matters?

Both, because they disagree in informative ways. Logo retention counts heads; revenue retention counts money and captures the fact that losing a $99 customer hurts more than a $29 one. Revenue falling faster than logos means churn is concentrated in your bigger accounts.

What's a good retention rate for SaaS?

There's no universal number — price point, market, and motion change it completely, and published benchmarks routinely mix B2B annual contracts with consumer monthly apps. The reliable read is your own trend: newer cohorts above older ones at the same offset means improving; curves that never plateau mean a value problem no benchmark will solve.

How many months of data do I need?

Three months gets you a first look; six or more makes it useful, because you can see whether curves flatten and compare several cohorts at the same age. With small cohorts (tens of customers), group months into quarters — single customers otherwise swing the percentages too hard to read.