Retention curves & cohort tables: the chart that tells you if a product really works

A single number like "our retention is 40%" hides more than it reveals: 40% of whom, measured when? A cohort groups users by when they joined (say, everyone who signed up the week of June 1st), and a retention curve tracks what percentage of that group is still active 1 week later, 2 weeks later, and so on.

🎯 Explain Like I'm Hired A cohort is just a group of users who all started at the same time. A retention curve follows one cohort forward in time and asks, week by week, "what fraction of them are still around?" And you plot a separate line per cohort so you can compare whether newer signups are sticking around better than older ones. Example: "is our new onboarding flow actually working?" is answered by comparing the retention curve of people who signed up after the new flow shipped against people who signed up before, a single "our retention is 40%" number can never answer that question.

WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('week', MIN(activity_date)) AS cohort_week
  FROM user_activity GROUP BY user_id
),
activity AS (
  SELECT
    c.cohort_week,
    DATE_PART('week', AGE(a.activity_date, c.cohort_week)) AS week_number,
    COUNT(DISTINCT a.user_id) AS active_users
  FROM user_activity a
  JOIN cohorts c ON a.user_id = c.user_id
  GROUP BY 1, 2
)
SELECT * FROM activity ORDER BY cohort_week, week_number;

Read this query as three plain steps: first work out each user's cohort (the week of their very first activity), then for every activity row work out how many weeks after their cohort start it happened, then count distinct active users per cohort per week-number. The result is exactly the shape you'd plot: one line per cohort, showing active users at week 0, 1, 2, 3...