Chapter 13 · 13 min · Basic
Cohorts and retention curves
A retention curve is the most quoted chart in a subscription business and the least defined. It looks like a measurement. It is four decisions stacked on top of each other.
Who is in the cohort. When month zero begins. What "still here" means for somebody who came back after a gap. And which cells of the grid you are allowed to read at all.
The SQL is a join and a GROUP BY. The chapter is about the four decisions, because they are what a stakeholder will disagree with — and the query cannot defend itself.
The dataset
Nine users signed up across January, February and March 2026, with seventeen purchases. Today is 30 April 2026 for every query here. Two users are deliberately awkward: Devi signed up in January but did not buy until February, and Devi and Gopal both went quiet for a month and then came back.
Schema
| user_id | int |
| name | text |
| signup_date | date |
| purchase_id | int |
| user_id | int |
| purchase_date | date |
| amount_myr | numeric(8,2) |
Example data
Which cohort somebody belongs to is a choice
"Group users by cohort" sounds like a lookup. There is no cohort column. You are about to invent one.
The two obvious definitions are signup month and first-purchase month, and they disagree for anyone who joined and then hesitated. Run the query: Devi signed up 28 January and bought on 2 February.
One user, and the January cohort is either 4 people or 3, and the February cohort is either 3 or 4. On cohorts this small that is a 25% swing in a denominator that every percentage below depends on.
Which is right depends on the question:
- Signup month answers "how good is our acquisition, including the people who never activated?" Month zero will not be 100%, and that is a finding, not a bug.
- First-purchase month answers "once someone is a customer, how long do they stay?" Month zero is 100% by construction, so it tells you nothing about activation.
The rest of this chapter uses signup month. What matters is that the write-up says so.
Nine users. How many of them land in a different cohort depending on which definition you pick?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. Both data-science tracks are the paid half — this one is the level-up: grain, reshaping, change over time, and whether the number really moved.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02The triangle, and why month zero is not 100%
- 03The denominator is the cohort, and it never moves
- 04Active this month, or never left?
- 05Half the grid has not happened yet
- 06Averaging cohorts is a third decision
- 07Now do it where nobody tells you what a cohort is