Cohort analysis

Every PM asks for it eventually: “How sticky are our users? What’s month-1 retention by signup month?” The answer is a cohort retention table — a triangle where each row is a cohort (everyone who started in the same month), each column is a month-since-signup, and each cell is the share of that cohort still active then.

It is the analyst’s flagship deliverable, and it is entirely SQL once you break it into four named steps: assign each user a cohort, flag their active months, join activity against tenure, and pivot into the wide shape. CTEs are made for this — one per step.

The build

Cohort and activity month come from truncating dates to the month (SQLite’s strftime('%Y-%m', …)). Tenure is the gap between them, and retention is active users over cohort size:

WITH cohorts AS (
  SELECT id AS user_id, strftime('%Y-%m', signup_date) AS cohort_month
  FROM   users
),
sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size FROM cohorts GROUP BY cohort_month
),
activity AS (
  SELECT DISTINCT user_id, strftime('%Y-%m', order_date) AS active_month
  FROM   orders
),
retention AS (
  SELECT c.cohort_month,
         CAST((julianday(a.active_month || '-01') - julianday(c.cohort_month || '-01')) / 30 AS INT) AS m,
         COUNT(DISTINCT c.user_id) AS active_users
  FROM   cohorts c
  JOIN   activity a ON a.user_id = c.user_id
  GROUP BY c.cohort_month, m
)
SELECT r.cohort_month, s.cohort_size,
       ROUND(100.0 * MAX(CASE WHEN m = 0 THEN active_users END) / s.cohort_size, 1) AS m0,
       ROUND(100.0 * MAX(CASE WHEN m = 1 THEN active_users END) / s.cohort_size, 1) AS m1
FROM   retention r
JOIN   sizes s ON s.cohort_month = r.cohort_month
GROUP BY r.cohort_month, s.cohort_size
ORDER BY r.cohort_month;

The one new idiom is MAX(CASE WHEN m = N THEN active_users END) — conditional aggregation, which pulls each tenure month into its own column (SQLite has no PIVOT). On a tiny dataset — a 3-user January cohort and a 4-user February cohort — it produces the triangle:

Two things make this a real artifact rather than a pile of counts. Dividing by cohort_size turns raw active-user counts into percentages, which is the only way to compare cohorts of different sizes — 50 actives means nothing until you know the cohort was 100 or 10,000. And the triangle shape is not a bug: a cohort that signed up last month simply cannot have a month-1 number yet, so the bottom-right is empty.

Practice