Recap — Building a Retention Table

# Building a Retention Table The output every PM wants: | Cohort | Month 0 | Month 1 | Month 2 | Month 3 | |---|---|---|---|---| | Jan 2026 | 100% | 62% | 48% | 41% | | Feb 2026 | 100% | 58% | 45% | — | | Mar 2026 | 100% | 60% | — | — | It's called the *retention triangle*. It's pure SQL. Here's the full pattern. ## Step 1 — Pin each user to a cohort ```sql WITH cohorts AS ( SELECT user_id, DATE_TRUNC('month', MIN(event_date)) AS cohort_month FROM events GROUP BY user_id ) SELECT * FROM cohorts; ``` Every user gets one row with their *first* event month as their cohort. ## Step 2 — Compute each user's months-since-signup for every activity month ```sql WITH cohorts AS ( SELECT user_id, DATE_TRUNC('month', MIN(event_date)) AS cohort_month FROM events GROUP BY user_id ), activity AS ( SELECT c.user_id, c.cohort_month, DATE_TRUNC('month', e.event_date) AS activity_month, EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', e.event_date), c.cohort_month)) AS months_since_signup FROM cohorts c JOIN events e ON e.user_id = c.user_id GROUP BY c.user_id, c.cohort_month, DATE_TRUNC('month', e.event_date) ) SELECT * FROM activity; ``` Each row says: "user 7 was in the Jan cohort and was active in month 2 (so months_since_signup = 2)." ## Step 3 — Count distinct users per cohort × months-since-signup ```sql WITH cohorts AS (...), activity AS (...), retention AS ( SELECT cohort_month, months_since_signup, COUNT(DISTINCT user_id) AS active_users FROM activity GROUP BY cohort_month, months_since_signup ) SELECT * FROM retention; ``` ## Step 4 — Divide by cohort size ```sql WITH cohorts AS (...), activity AS (...), retention AS (...), cohort_size AS ( SELECT cohort_month, COUNT(*) AS size FROM cohorts GROUP BY cohort_month ) SELECT r.cohort_month, r.months_since_signup, r.active_users, cs.size AS cohort_size, ROUND(100.0 * r.active_users / cs.size, 1) AS retention_pct FROM retention r JOIN cohort_size cs ON cs.cohort_month = r.cohort_month ORDER BY r.cohort_month, r.months_since_signup; ``` You can pivot the final output into the triangle shape using `CASE WHEN` or in your BI tool. Most analysts leave it in long format and let Looker/Tableau pivot. ## What to look for in the triangle - **The Month 1 drop** — your activation problem. If it's >70%, your onboarding is broken. - **The curve from Month 1 onward** — the *shape* matters. A flattening curve is healthy. A still-declining curve at month 6 means churn never stabilises. - **The "smile"** — some products see retention curve UP after a dip. This is rare and is your strongest signal of product-market fit. - **Compare cohorts** — if Feb is meaningfully worse than Jan at the same age, something broke in Feb (acquisition channel, product change, market shift). ## Common interview mistakes 1. **Forgetting DISTINCT in step 3** — `COUNT(*)` instead of `COUNT(DISTINCT user_id)` counts events, not users. 2. **Computing retention from activity tables alone** — without joining to a cohort definition, you can't normalize. 3. **Using a fixed cohort size instead of a per-cohort one** — if cohorts vary in size (they do), you have to divide each cohort by its own size. ## Takeaway Memorize the 4-step pattern (cohort → activity → retention → size). In any interview that asks about cohort analysis, you'll have the bones of the query in your head in 30 seconds.