If you’re running a SaaS business, you don’t need a fancy BI stack to get honest answers on churn, upsell, and payback. You need clean inputs and three tables you can trust.
SaaS Cohort Analysis is simply tracking groups of customers who started at the same time, then measuring what happens to them month by month.
This matters to UK founders and finance leads because it turns “growth” into something you can plan around. It helps you forecast cash, focus product work, and answer investor questions without hand-waving.
Set up your cohorts and get clean input data
You can build cohort tables in Excel or Google Sheets with a minimum set of fields. The key is to keep it consistent and auditable.
Minimum customer-level data:
- Customer ID (unique and stable)
- Cohort date (more on this below)
- Month (invoice month or subscription month)
- Revenue for that month (MRR or recognised subscription revenue)
- Status that month (active or not)
Where to pull it from (tool-agnostic):
- Billing (Stripe, Chargebee, Paddle): subscriptions, invoices, credit notes, refunds
- Accounting (Xero, QuickBooks): invoice dates, net revenue, VAT treatment
- CRM (HubSpot, Salesforce): segment, channel, sales owner (optional but helpful)
A simple way to structure your raw export is one row per customer per month. Think of it like a bank statement, but for subscriptions: “Customer X, Month Y, paid Z”.
Pick the right cohort type for the job: signup, first payment, or first invoice
The cohort “start” date changes what your table tells you. Pick one, write it down, and stick to it.
Rules of thumb:
- Signup cohorts: best for freemium and trials where product usage matters before money. Useful for onboarding and activation work.
- First payment cohorts: best for most subscription SaaS, because it anchors the cohort to real revenue and reduces noise from long trials.
- First invoice cohorts: often best for annual plans, where payment timing can be lumpy and you want a clean “month 0”.
One business can run more than one cohort view, but don’t start with three. Start with the one that matches your biggest question today (churn, upsell, or payback), then expand.
Define time buckets and a consistent revenue metric
Most B2B SaaS teams should use monthly buckets. Weekly buckets only help if you have high-volume self-serve signups and fast churn patterns.
For revenue, keep it plain:
MRR is recurring subscription revenue per month from active subscriptions.
To keep the cohort tables useful, set simple rules:
- Exclude VAT from revenue, unless you’re doing tax reporting.
- Exclude one-off setup fees from MRR. If services matter, track them in a separate view.
- Treat upgrades and downgrades as changes to the same customer’s MRR (don’t create a new customer).
- For refunds, net them in the month they occur, then keep the rule consistent.
- For pauses, count them as inactive if they’re not paying.
The goal is not perfection. The goal is a table where two people get the same answer.
Build a retention table that highlights churn you can act on
A retention cohort table is a heatmap waiting to happen, but even in plain numbers it shows you where customers fall away.
This is a logo retention view: it tracks customers, not pounds. Rows are cohorts, columns are months since start.
Here’s a tiny example shape (numbers are illustrative):
| Cohort (first payment month) | New customers (M0) | M1 active | M2 active | M3 active |
|---|---|---|---|---|
| 2025-09 | 50 | 44 | 41 | 39 |
| 2025-10 | 60 | 52 | 49 | 46 |
| 2025-11 | 55 | 46 | 42 | 40 |
You then convert each month to a percentage of M0. The “shape” matters more than the exact figures. A steep early drop is like a leaky bucket with a crack near the top, you won’t fix it by pouring more water in.
Create the table in a spreadsheet using counts, not complex formulas
Keep this boring. Boring is good when you want reliable numbers.
- Create a column for cohort month (for example, 2025-11).
- Count new customers in each cohort month (M0).
- For each cohort, count how many of those same customers are active in M1, M2, M3 and so on.
- Compute retention % as: active in month N divided by M0.
Define active in one line and don’t drift: active means “paid in the month and not cancelled”.
You can do this with a PivotTable if your data is one row per customer per month, or with COUNTIFS if you prefer. The only non-negotiable check is no double counting. If a customer can appear twice in a month due to invoice quirks, dedupe on Customer ID and Month before you start.
Turn the retention pattern into decisions: onboarding fixes, ICP tweaks, and pricing flags
Retention tables aren’t there to impress a board pack. They’re there to tell you what to do on Monday.
Common patterns and what they often signal:
- Sharp early drop (month 0 to 2): weak activation, poor handover from sales, or customers buying for the wrong use case.
- Steady decline: customers get some value but not enough to keep paying, often a product adoption or outcomes problem.
- Cliff at month 12: annual renewals failing, price rises biting, or procurement friction.
Practical questions worth asking:
- Which plan churns fastest, and is it priced like a serious product or a “cheap test”?
- Which channel brings customers that don’t stick (paid search, affiliates, outbound)?
- Are SMB customers leaving while mid-market stays (or the other way round)?
- Does churn cluster under one sales rep or onboarding flow?
- What changed in the product or pricing before the worst cohort started?
If you only slice one way, slice by segment. One blended table can hide a lot.
Add revenue expansion so you can separate churn from growth
Logo retention answers, “Do customers stay?” Revenue expansion answers, “Do they grow?”
A business can have decent logo retention and still lose money if customers downgrade. It can also lose logos but grow revenue if remaining customers expand fast.
To see this, build a revenue-based cohort table using the same cohort customers.
Build a Net Revenue Retention style table using simple monthly MRR sums
This is cohort-based NRR. It’s not the company-wide NRR you might report each month, it’s cleaner for learning because it follows a single cohort through time.
Steps:
- For each cohort, calculate starting MRR in month 0 (sum of MRR for those new customers).
- For month 1, month 2, month 3, sum the MRR from the same cohort customers only.
- Divide each month’s cohort MRR by starting MRR to get a percentage.
Mini example:
| Cohort | Starting MRR (M0) | M3 MRR | Cohort NRR at M3 |
|---|---|---|---|
| 2025-10 | £12,000 | £13,800 | 115% |
| 2025-11 | £10,500 | £9,900 | 94% |
Two consistency rules keep this stable:
- New customers never enter an existing cohort.
- Reactivations: choose a rule (either keep them in the original cohort or treat as a new cohort on rejoin), then stick to it.
Spot where expansion is coming from and whether it is repeatable
When a cohort expands, don’t stop at the percentage. Find the engine.
Check for:
- Seat growth (teams adding users)
- Feature adoption (a paid module customers only use after onboarding)
- Usage-based charges (volume rising as customers depend on you)
- Price increases (planned uplift versus ad-hoc discount roll-off)
If your billing system supports it, split expansion into upgrade, add-on, and price rise. If it doesn’t, a simple proxy works: track plan changes and ARPA movement.
What “good” looks like depends on your model, but as a working range:
- Many SMB SaaS products aim for 100% to 110% cohort NRR by month 12.
- Mid-market SaaS with seats and add-ons often targets 110% to 130%. If you’re below 100% early, it’s a warning. Either churn is too high, downgrades are common, or discounting is masking fit.
Calculate CAC payback by cohort using one extra table
Payback answers a cash question: how many months of gross profit does it take to earn back what you spent to win customers?
This is where cohort analysis becomes a finance tool, not just a product metric. It supports hiring plans, ad budgets, and fundraising timelines.
Allocate acquisition cost to each cohort in a way you can defend
You don’t need perfection, you need something you can explain.
Two simple methods:
- Blended CAC by month: (sales and marketing spend in the month) divided by (new customers in that month).
- CAC by channel: if you track leads and spend by channel, allocate spend to the customers acquired through it.
Blended CAC is quick and good enough early on. Channel CAC is better once spend is material and channels behave differently.
Be clear about “odd” spend. A one-off brand campaign can be excluded, or included. Either is fine if you label it and don’t move the goalposts.
Compute payback months using cumulative gross profit from the cohort
Steps:
- Start with the cohort’s MRR by month (from your revenue cohort table).
- Convert to gross profit: MRR times your gross margin (for example 80%).
- Calculate cumulative gross profit over time.
- Payback month is when cumulative gross profit exceeds cohort CAC.
Small illustration:
| Month | Cohort MRR | Gross margin | Gross profit | Cumulative gross profit |
|---|---|---|---|---|
| M0 | £8,000 | 80% | £6,400 | £6,400 |
| M1 | £8,200 | 80% | £6,560 | £12,960 |
| M2 | £8,500 | 80% | £6,800 | £19,760 |
If cohort CAC is £18,000, payback lands in month 2. If a cohort never pays back within a sensible window (say 18 to 24 months for many B2B SaaS models), flag it. That cohort is telling you the unit economics don’t work at that spend level.
When payback gets worse, the cause is usually one of three things: CAC rising, retention weakening, or discounting. The cohort tables help you see which one it is.
Conclusion
You can get real answers with three simple cohort tables: retention (who stays), revenue expansion (who grows), and payback (when you get your cash back). Together, they point to practical moves: fix onboarding, tighten your ICP, improve packaging, and protect cash.
Start with one cohort definition, run it monthly, and track what changes after each product or go-to-market experiment. If you want help setting this up cleanly and turning it into investor-ready reporting, Consult EFC can support the build, the checks, and the story behind the numbers so your SaaS Cohort Analysis drives better decisions.
