Real-world recipes · Product analytics
Retention by signup cohort
A cohort is everyone who signed up in the same month. Retention asks: of each cohort, how many were still logging in 1, 2, 3 months later?
Step one tags each login with how many months after signup it happened. Step two counts distinct people per cohort and month offset.
Try itEdit the query, then Run
| cohort | month_0 | month_1 | month_2 | month_3 |
|---|---|---|---|---|
| 2026-01 | 43 | 37 | 32 | 30 |
| 2026-02 | 43 | 37 | 34 | 27 |
| 2026-03 | 65 | 59 | 55 | 51 |
| 2026-04 | 53 | 43 | 39 | 0 |
| 2026-05 | 77 | 68 | 0 | 0 |
| 2026-06 | 70 | 0 | 0 | 0 |
6 rows
Turning year and month into "number of months since year zero" makes the subtraction easy. Later cohorts have empty months simply because those months haven't happened yet in the data.
Your turn
Cohort sizes: how many customers signed up each month? Columns cohort and customers, in order.
GROUP BY strftime('%Y-%m', signup_date).
This page on one card
