Cohort Retention: why the topline looks healthy while every cohort leaks worse

The data in this project is synthetic — generated locally by data/make_data.py, with no external source. That is deliberate: to score a reconstructed retention matrix you need to know the true retention of every cell, and to show the “blended looks flat” trap cleanly you need a known, deliberate mismatch between the company-wide view and the per-cohort reality. No signup, no downloads, no GPU.

Before you start

New to retention analysis? Start here.

A cohort is a group of users bucketed by when they signed up — everyone who joined in January is the “2025-01 cohort.” Retention is the share of a cohort still around some number of months later.

The mistake almost everyone makes first is to compute one blended number for the whole company: “we’re retaining 53% of users at three months.” That single number mixes together cohorts of every age and quality, and it can stay flat — or even improve — while every new cohort is actually getting worse. The fix is a cohort retention matrix: one row per signup cohort, one column per months since signup, each cell the share still active.

A tiny example — three monthly cohorts, tracked by months since signup:

cohort      month 0   month 1   month 2   month 3
2025-01       100%       80%       68%       60%
2025-02       100%       75%       60%        —      (only 2 months old)
2025-03       100%       65%        —         —      (only 1 month old)

Read across a row: how one cohort decays. Read down a column: whether newer cohorts retain better or worse than older ones. Here month-1 retention falls 80% → 75% → 65% — each cohort is leaking faster. A blended “average retention” would have blurred that into one flat-looking figure, especially if the newer, worse cohorts are also the biggest.

What you’ll learn: build that matrix with a pivot, and read it against the blended view to see what pooling hides. Section 3 lists exactly what you’ll be able to do.

New to Python too? See Start Here — it starts from zero, and shows how to run this in your browser with nothing to install.

1. The Brief

You’re handed a year of subscription activity and asked the question a board actually asks: is the product getting stickier or leakier over time? The topline says stickier — active users are climbing fast. You’ll build the cohort matrix that tells the real story, and show why the company-wide number was lying.

This is the analysis every subscription business lives or dies by, and the trap is a genuine one: a fast-growing company keeps adding users faster than it loses them, so total actives climb and a blended retention curve flattens out — right up until growth slows and the leak everyone ignored becomes the whole story. Cohorting is how you see it a year early. It’s the same skill whether the “users” are subscribers, accounts, or repeat buyers.

2. Difficulty & time

Difficulty 3 / 10. One tool (pandas), one core mechanic (pivot activity into a cohort × months-since matrix), and a fully specified path in Part A — no algorithm, no modelling. Calibrated against the ledger (G10): a peer of silent-row-loss, idempotent-loads, and duplicate-invoices (all 3) — single-mechanic builds where the lesson is a specific data-handling judgment (here: don’t pool cohorts of different ages; and count retention is not revenue retention), not a pipeline or a model. It sits below maverick-spend (4), which layers a conditional policy rule and a second detection type into one screen. It stays above the 1–2 band because the blended view genuinely fools you and Part B needs a real design decision (measure dollars, not heads), not a one-liner.

Time is separate from difficulty: Part A is about an hour; Part B is 2–3 hours of your own work. The scripts run in under a second — the time is comprehension.

3. What you’ll be able to do after

Build a cohort retention matrix from an activity log with a single pivot_table, and explain why it comes out triangular.
Explain the “blended looks flat” trap — why a pooled retention curve and a climbing active-user count can both hide a steady decline in cohort quality.
Read the matrix both ways — across a row (one cohort decaying) and down a column (newer cohorts better or worse than older ones).
Compute net revenue retention and say why it differs from logo retention — and why it can exceed 100%.

The finished result

By the end of Part A you’ll have the cohort matrix and the one view that breaks the “we’re fine” story — month-3 retention, cohort by cohort:

cohort     month-3 retention
2025-01          79.8%
2025-04          62.1%
2025-07          44.3%
2025-09          36.3%

The single company-wide blended figure is 53.3% — one number that hides the slide from ~80% down to ~36%. Active users grew about 12× over the same period, which is exactly what let the leak stay invisible. Seeing that gap is the whole point.

4. Prereqs & time box

You can write a function and use a pandas DataFrame (groupby, pivot_table, read_csv). No subscription-metrics background needed — the primer above covers it.

Where to write and run code

Two ways to run this project — pick either:

In your browser (no install). If the project page shows a ▶ Run button, click it to run Part A right here — nothing to set up. Part A uses only pandas, which runs in the browser.
On your computer. The one-time setup — installing Python, downloading the code from the course site (no GitHub, no git), opening a terminal, creating a virtual environment, installing the requirements, and running Part A step by step — is covered once in Start Here. Come back here once python -c "import pandas" runs without error. Part B (writing your own solution) needs this local setup.

Time box

Part A — Guided Build: 60–90 minutes. Hard cap.
Part B — Your Turn: 2–4 hours. Hard cap.

5. Setup & data

The data is synthetic and generated locally — see the banner above and SOURCES.md for full provenance. One command creates both parts’ files:

python data/make_data.py

That writes two CSVs into data/ (git-ignored; regenerate anytime, byte-for-byte identical because the seed is fixed):

Part A — activity_a.csv (50,084 rows, 9,840 users). Columns user_id, cohort, months_since, active — one row per user per month observed, active is 0/1.
Part B — revenue_b.csv (55,523 rows, 10,465 users). Columns user_id, cohort, months_since, mrr — the same shape but with each user’s monthly revenue in dollars (mrr, 0 when churned).

Both cover 12 monthly cohorts (2025-01 … 2025-12) observed through the end of 2025-12, so later cohorts have fewer months of history — the matrix is triangular by construction.

6. Part A — Guided Build

You’ll work the 9,840-user activity log. Run the whole thing, or (better) one # %% cell at a time — see Start Here:

python part_a/build_cohorts.py

Prefer the browser? If this page has a ▶ Run button, click it instead of the command above — Part A runs in your browser with nothing to install. The STEP output and every checkpoint below are identical either way.

▶ Try it now — run the real Part A right here in your browser. This one button makes the data and runs Part A for you — you do not need to install anything, and you do not need to type any of the python … commands anywhere on this page (those are only for running on your own computer instead). First run loads Python + this project’s libraries: usually ~10–20 s, a little longer for machine-learning projects.

The full, commented script is in part_a/build_cohorts.py. The spine:

Step 1 — The activity log. Rows, users, cohorts; note later cohorts have fewer rows.

Checkpoint: 50,084 rows, 9,840 users, 12 cohorts.

Step 2 — Two company-wide views that both say “we’re fine.” Monthly active users over calendar time, and blended retention pooled across all cohorts.

Checkpoint: active users climb 387 → 4,667 (~12×), and the blended curve decays to ~40% then flattens and ticks up at months 10–11. Both look healthy — and both are traps.

Step 3 — Split by signup cohort: the retention triangle. One pivot_table into cohort × months-since.

Checkpoint: a triangular matrix; each later cohort has one fewer column. The blended curve was this table collapsed down its columns.

Step 4 — Read down a column. Month-3 retention, cohort by cohort.

Checkpoint: month-3 retention falls from 79.8% (2025-01) to 36.3% (2025-09) — it halved. The blended “53.3%” hid a 2× spread and a steady slide. Writes part_a/cohort_matrix_a.csv.

Why this and not that

Why the blended tail rises. Only the oldest cohorts are old enough to reach months 10–11, and they’re the best ones (the product degraded later). Pooling lets those survivors define the tail — survivorship bias baked straight into the chart. The cohort matrix has no tail to hide behind: each row stops where its data does.
Why growing actives is not retention. Monthly active users climb because acquisition outruns churn, not because users stay. The two move independently; reading stickiness off a total-actives chart is the classic mistake this project exists to break.
Why pivot_table and not a manual loop. Cohort analysis is a pivot: group by (cohort, months_since), aggregate, reshape to a grid. One call expresses the whole mechanic and won’t drift out of sync the way hand-rolled bucketing does.

Functions you’ll meet (and where to read more)

Function	What it does here	Docs
`pd.read_csv(path)`	Load the activity log	read_csv
`pd.PeriodIndex(s, freq="M")`	Turn `2025-01` into a month you can add to	PeriodIndex
`df.pivot_table(index, columns, values, aggfunc)`	Build the cohort × months-since matrix	pivot_table
`df.groupby(keys)[c]`	Pool for the blended view; size the cohorts	groupby
`s.nunique()`	Count distinct active users per month	nunique

If something looks off

The matrix has blank cells (or NaNs) in the top-right — is that a bug? No — the matrix is triangular by design. A cohort that signed up in September has only had a few months to be observed, so it has no month-8 column yet. A blank means “not observed,” not “broken.”
A checkpoint number doesn’t match. The data is seeded, so every number here is exact. Re-run python data/make_data.py (you may have skipped or edited it), then re-run Part A.
Setup problems (Python not found, pip, FileNotFoundError, re-activating the venv): see the troubleshooting list in Start Here.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already handles the easy version; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Same core skill, same triangular matrix — but now the cells are dollars, not head-counts. Build the net revenue retention (NRR) matrix from revenue_b.csv: for each (cohort, months_since), the cohort’s total revenue that month divided by its month-0 revenue. Because a retained user’s spend can grow, NRR can climb past 100%. The starter (part_b/starter.py) reuses Part A’s count-retention idea and scores ~0.15 — far below the floor.

The transfer: logo retention counts how many users stayed; NRR measures how much revenue stayed. A user who churns takes their dollars to zero, but a user who upgrades brings more than they started with. Counting heads throws that away — and worse, it’s capped at 100%, so it can’t even represent a cohort whose revenue expanded (one here reaches 165%).

Acceptance criteria (checkable)

Your solution writes into part_b/out/:

nrr.csv — columns cohort, months_since, nrr (nrr as a fraction: 1.0 = 100%, 1.25 = 125%), one row per observed cell.
report.json — with keys cohorts, cells, achieved_metric (you can leave achieved_metric null; the grader scores you).

Then:

python part_b/starter.py
pytest part_b/test_solution.py -q

If pytest reports everything as skipped, it just means the output files don’t exist yet — run python part_b/starter.py first (once your solution fills in the TODOs), then re-run.

You’re done when the tests pass: you reconstruct ≥ 90% of the 78 cells within 1 percentage point of the true NRR (the reference reaches 1.00). Count retention reconstructs ~15% and fails — it’s the dollar calculation that clears the floor.

Constraints — what must be true of your solution, not how to get there

Weight by dollars: sum mrr, don’t count users.
The denominator is each cohort’s month-0 revenue (a fixed base), not its current revenue.
Let NRR exceed 100% where expansion outruns churn — don’t clamp it.
Produce a value for every observed (cohort, months_since) cell (the triangle), not just month 0.

Hints

Hint 1 — the base and the numerator

You need two group-bys: the base (months_since == 0, summed mrr per cohort) and the numerator (summed mrr per cohort, months_since). NRR is the second divided by the first, matched on cohort.

Hint 2 — why the starter is capped

The starter counts users with mrr > 0. That ratio can never exceed 1.0, so every cohort whose revenue grew past its start is wrong by construction. Swap the head-count for a dollar sum.

Hint 3 — aligning the divide (still not the code)

num = rev.groupby(["cohort","months_since"])["mrr"].sum() gives a Series indexed by (cohort, months_since); base = rev[rev.months_since==0].groupby("cohort")["mrr"].sum() is indexed by cohort. Divide each numerator cell by its cohort’s base — a .get(cohort) lookup or a level-aligned divide both work.

8. Self-check

You don’t need the answer key. You’re done when all of these hold:

pytest reports cell accuracy ≥ 0.90 (aim for ~1.00 — the calculation is exact once it’s dollar-based).
At least one early cohort shows NRR above 100% (expansion beat churn); if none do, you’re still counting users, not summing dollars.
Re-running from python data/make_data.py reproduces the same numbers — the data is seeded.

And the tell-tale: your NRR curve for the oldest cohort should rise over time while its logo retention falls. If both fall, you haven’t switched to dollars.

9. Stretch

Quarterly cohorts. Re-bucket signups into quarters and rebuild the matrix. Fewer, larger cohorts trade resolution for less noise — decide which you’d show a board and why.
Split the NRR into its parts. Decompose each cohort’s NRR into starting revenue, expansion, contraction, and churned revenue (a “bridge”). The four must reconcile back to the NRR — the standard way SaaS finance explains a retention number.
(Genuinely hard) Reactivation. Extend the generator so a churned user can resubscribe in a later month, then decide how that should count in both logo retention and NRR — point-in-time vs “survived continuously” give different matrices, and you’ll have to defend the choice.

10. Ship it

Put this in a portfolio repo and it counts. In that repo’s README:

State the finding in one sentence an exec gets: “active users grew 12×, but month-3 retention halved across cohorts — the growth was hiding a worsening leak.”
Show the blended view next to the cohort matrix, so the reader sees exactly what pooling hid.
Include the pivot and the NRR calculation, and one paragraph on why logo retention and net revenue retention can point in opposite directions. That paragraph is the part that signals you’ve actually done cohort analysis, not just plotted a line.

Keep make_data.py and the tests in the repo so anyone can reproduce your numbers from zero.

11. Sources

Rendered from SOURCES.md.

Field	Value
Dataset	Synthetic SaaS subscription cohorts: monthly activity (Part A, 9,840 users) and monthly revenue/MRR (Part B, 10,465 users)
Type	Synthetic
Generated by	`data/make_data.py` (this repo), seeds `20260720` (activity) and `20260721` (revenue)
Source URL	Not applicable — generated, not fetched
License	Not applicable — synthetic, no third-party rights
Access / generation date	2026-07-18
Redistribution	Permitted — synthetic; output regenerated from the seeded script, never committed
robots.txt checked	Not applicable — no scraping
ToS URL	Not applicable — no external source

Synthetic data teaches the mechanic; it does not prove a finding about the real world. The growth-masks-decline pattern was injected on purpose — recovering it demonstrates the technique, not a claim about any real company’s retention.

Next up

Finished this one? Continue the Data Analytics track:

Duplicate Invoices: catch the payments that went out twice → · Browse all courses