Lesson 4 of 5 · 9 min · Behaviour over time
Cohorts and retention tables
What you will learn
- What a cohort is and why cohort analysis beats simple averages
- Read a retention table and a retention curve
- Build a cohort retention table in SQL and pandas
- Compare cohorts fairly, and spot improving or worsening behaviour
- Avoid incomplete-period and survivor traps
In simple words
Imagine every batch of students who joined in a particular month as a class. Cohort analysis follows each class separately through time and asks: how many were still showing up one month later, two months later? Comparing classes tells you whether newer ones are doing better than older ones, which a single overall number cannot.
Words to know
- Cohort: a group of users who share a starting event in the same period, such as signing up in January.
- Retention: the share of a cohort that is still active after a given time.
- Cohort age: how long since the cohort started (month 0, month 1, month 2).
- Churn: the share who stopped, the opposite of retention.
Why cohorts
A total of "active users this month" mixes brand-new users with people who joined a year ago. If it grows, is the product better, or are you just adding new users faster? If it shrinks, is it old users leaving, or a weak new batch? Following each signup month separately answers these questions and shows whether changes you made (a better onboarding) helped the later cohorts.
A retention table
Take students grouped by the month they signed up, counting how many were active in each month since:
| Cohort (signup month) | Users | Month 0 | Month 1 | Month 2 |
|---|---|---|---|---|
| January | 100 | 100% | 40% | 30% |
| February | 120 | 100% | 45% | |
| March | 150 | 100% |
How to read it: rows are cohorts, columns are how many months after joining. January: 40 of 100 were active in the month after joining and 30 in the month after that. February's month-1 retention is 45 percent (54 of 120): better than January's 40 percent, so something changed for the better. The blank cells are the future: February has not had two months yet, so there is no month-2 figure.
Month 0 is always 100 percent when "active" includes the signup month. Define active clearly: logged in, completed a test, placed an order. A common refinement is N-day retention (active on day 7, or between day 7 and 14) for products used often, and monthly retention for ones used rarely.
Plot retention curves, one line per cohort, with cohort age on the x-axis. A healthy product's curves flatten at a level above zero (a loyal core stays). Curves that keep falling to zero mean no one sticks around.
Building it in SQL
Tables: users(user_id, signup_date) and activity(user_id, activity_date).
WITH cohorts AS (
SELECT user_id, DATE_TRUNC('month', signup_date)::date AS cohort_month
FROM users
),
active AS (
SELECT DISTINCT user_id, DATE_TRUNC('month', activity_date)::date AS active_month
FROM activity
),
joined AS (
SELECT c.cohort_month,
(DATE_PART('year', a.active_month) - DATE_PART('year', c.cohort_month)) * 12
+ (DATE_PART('month', a.active_month) - DATE_PART('month', c.cohort_month)) AS months_since,
c.user_id
FROM cohorts c
JOIN active a ON a.user_id = c.user_id AND a.active_month >= c.cohort_month
),
sizes AS (
SELECT cohort_month, COUNT(*) AS cohort_size FROM cohorts GROUP BY 1
)
SELECT j.cohort_month,
s.cohort_size,
j.months_since,
COUNT(DISTINCT j.user_id) AS active_users,
ROUND(100.0 * COUNT(DISTINCT j.user_id) / s.cohort_size, 1) AS retention_pct
FROM joined j
JOIN sizes s USING (cohort_month)
GROUP BY j.cohort_month, s.cohort_size, j.months_since
ORDER BY j.cohort_month, j.months_since;
The result is "long" (one row per cohort and age). Pivot it for display in Excel or pandas. Take care to count distinct users and to divide by the cohort size (all users who started), not by the number who were ever active.
Building it in pandas
users["cohort"] = users["signup_date"].dt.to_period("M")
activity["month"] = activity["activity_date"].dt.to_period("M")
df = activity.merge(users[["user_id", "cohort"]], on="user_id")
df["age"] = (df["month"] - df["cohort"]).apply(lambda d: d.n)
counts = (df.groupby(["cohort", "age"])["user_id"].nunique()
.unstack(fill_value=0))
sizes = users.groupby("cohort")["user_id"].nunique()
retention = counts.div(sizes, axis=0).round(3) * 100
Subtracting two monthly periods gives an offset; .n extracts the number of months. Show the table as a heatmap: retention.style.background_gradient(cmap="Blues", axis=None); colour makes patterns obvious.
Comparing cohorts fairly
- Compare the same age: month 1 of January with month 1 of February, never January's month 3 with February's month 1.
- Exclude incomplete periods. The latest calendar month is partial; a user who signed up on the 28th has not had a full month. Show blank rather than a falsely low number.
- Compare like with like: a cohort from a festival-season campaign differs from organic signups. Split cohorts by channel, plan or device.
- Watch the size: a cohort of 12 users swings wildly with each user; report the count beside the percentage.
- Look for step changes: if every cohort after 15 March has better month-1 retention, check what shipped then. This is the best evidence you will get from observational data, but still confirm with an experiment where you can.
Variants of cohort analysis
- Behavioural cohorts: group by what people did (used the mock interview in week one) instead of when they joined.
- Revenue retention: the share of the cohort's first-month revenue still arriving later; it can exceed 100 percent if customers spend more over time.
- Lifetime value (LTV): cumulative revenue per user by cohort age, compared with the acquisition cost to judge whether a channel pays back.
- Rolling retention: the share active at day N or later, which is higher than classic retention and suits products with irregular use.
Common mistakes
- Dividing by active users instead of the cohort size.
- Mixing ages when comparing cohorts.
- Including the unfinished current period.
- Averaging across all cohorts, which hides improvement or decline.
- Defining "active" too loosely (opening the app) so retention looks great but means little.
- Ignoring tiny cohorts' noise.
Try it yourself
- Work out the percentages in the example table from the counts: January has 100 users with 40 active in month 1 and 30 in month 2, and February has 120 users with 54 active in month 1.
- Build the retention table from a dataset of signups and activity using SQL or pandas.
- Plot one retention curve per cohort and describe whether curves flatten.
- Split the cohorts by acquisition channel and see which channel retains best.
- Write a sentence for a manager that states a finding and its uncertainty (cohort sizes, definition of active).
Check yourself
What is a cohort?
A group of users who started in the same period, for example everyone who signed up in January.
Why divide by the cohort size, not by the number of active users?
Retention is the share of everyone who started that is still active; dividing by the active users would always give 100 percent.
Why can't you compare January's month 3 with February's month 1?
Retention falls with age, so different ages are not comparable; compare equal cohort ages.
What does a retention curve that flattens above zero indicate?
A core group of users who keep coming back, which is a sign of lasting value.
Interview questions
How would you measure whether a new onboarding flow improved retention?
Compare cohorts that joined before and after, at the same cohort age, with the same definition of active and similar mix; better still, run an A/B test with a control group.
How do you calculate month-1 retention in SQL?
Group users by signup month, count how many were active in the following month (distinct users), and divide by the cohort size.
What is the difference between classic and rolling retention?
Classic retention counts users active exactly on day (or month) N; rolling retention counts users active on day N or any later day, so it is higher and suits irregular use.
Summary
- A cohort follows a group that started together; retention tables put cohorts in rows and age in columns.
- Build with distinct users per cohort and age, divided by cohort size, in SQL or pandas.
- Compare equal ages, exclude incomplete periods, watch small cohorts and define "active" carefully.
- Flattening curves show a loyal core; changes between cohorts hint at what worked.
Going deeper
- PostgreSQL date/time functions:
DATE_TRUNCandDATE_PARTfor cohort months. - pandas Period and time spans: monthly periods and differences.
- pandas Styler background_gradient: heatmap tables.
See how much of this you can explain
A free mock interview asks you new questions on Metrics, KPIs and business analysis for Data Analyst and scores every answer, with what to fix.