Stop 04 · Data & Metrics for PMsTopics · 6/12

Cohort Analysis

In short

Averages mix new users with old ones and hide what's really changing. Cohort analysis groups users by when they joined, so you can see if things are getting better.

Grouping users by a shared starting point (e.g. signup week) and tracking their behavior over time — the standard way to see whether retention is actually improving, not just growth masking churn.

The purple blob as a teacher stands between two groups of students in different coloured uniforms.

Think of it like this

Comparing school batches

A teacher doesn't compare this year's Class 10 with last year's Class 12. She compares the 2025 batch at the end of Class 10 with the 2026 batch at the same point. Same stage, fair comparison.

Watch it happen

Week-2 retention by signup month

Jan 31%

Feb 30%

Mar 32%

Apr 41%

May 42%

1/4Each cell is one cohort: users who signed up that month. The number is the share still active in their second week.

The big ideas

What you need to know

Group by start date

A cohort is everyone who did something first in the same period, like signing up in March.

Compare at the same age

Look at each cohort at the same point, like week 2, so old and new users don't mix.

Read the triangle

In a cohort table, rows are cohorts and columns are weeks since joining. Read down a column to compare.

See it in code

Walk through the code

sql

1SELECT DATE_TRUNC('month', u.signup_date) AS cohort,2       COUNT(DISTINCT u.id) AS users,3       COUNT(DISTINCT o.user_id) AS active_week_24FROM users u5LEFT JOIN orders o6  ON o.user_id = u.id7 AND o.created_at >= u.signup_date + INTERVAL '7 days'8 AND o.created_at <  u.signup_date + INTERVAL '14 days'9GROUP BY cohort10ORDER BY cohort;

1/4Round each signup date down to its month. That month is the cohort.

PostgreSQL. For each signup month, count users and how many placed an order in their second week. Divide the two to get week-2 retention.

Save yourself the pain

Common mistakes

Oops

Comparing this month's overall retention with last month's.

Do this instead

Overall numbers mix many cohorts. Compare cohorts at the same age instead.

Oops

Calling a one-month jump a win.

Do this instead

Check that the next cohort holds the gain before you credit a feature.

Your mission

Make a cohort table

~35 min

Quick check

Why compare cohorts at the same week since signup?

Go deeper

1Sketch a cohort retention table (weeks since signup vs % still active) by hand.
2Learn why raw user-count growth can hide a serious retention problem.
3Compare cohorts from different acquisition channels and reason about why they'd differ.