~/learn/SQL · Analytics patterns
Cohorts and retention
Group users by when they joined, then track what they did after.
why this matters
Retention is the metric investors and product teams care about most: of the people who joined in March, how many still come back in June?
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
A cohort analysis has two steps: label each user with a cohort (usually their signup month), then measure activity by how far it happened after that label.
Join the activity table on, compute the month offset between the event and the cohort, and count distinct users per (cohort, offset) cell. That grid is the retention table.
Try it: Count orders instead of distinct customers per cell.
Turn counts into retention rates by dividing each cell by the cohort's size — that is the number stakeholders actually read.
Note
Always count DISTINCT users in a cohort cell. Counting rows measures activity volume, not retention, and the two tell different stories.
The retention grid
cohort = month a user first appeared
period = months since that first month
M0 M1 M2
Jan 100 45 30
Feb 120 52 —Common gotcha
Define the cohort with MIN(date) per user, not the date of each event — otherwise every user lands in several cohorts.
key takeaways
- Cohort = the month a user first appeared.
- Period = months elapsed since then.
- Retention = active users in a period ÷ cohort size.
check your understanding
How do you assign a user to a cohort?
free practice
Build confidence with a question you can solve without Pro.