Cohort analysis groups users by a shared starting event, such as signup month, and tracks how many remain active over time. A single blended retention number hides whether your product is improving. A cohort table shows exactly when users leave and whether recent signups behave better than older ones. This guide builds a full retention pipeline in SQL, from raw event tables to a pivoted retention matrix and a heatmap.
Quick Takeaways
- Cohort = users grouped by a first-event date (signup, first purchase). Retention = share of that cohort active in period N after the start.
- Build it in four steps: assign cohorts, compute activity periods, calculate the period offset, divide by cohort size.
- Use classic (bounded) retention for “active exactly on day N.” Use rolling (unbounded) retention for “active on or after day N.”
- Always divide by cohort size (all users in the cohort), not by period-0 active users, or your percentages will be inflated.
| Retention Type | Definition | Best For | Typical Result |
|---|---|---|---|
| Classic (N-day) | Active exactly on day N | Daily-use apps (social, games) | Lower, spiky |
| Range (bracket) | Active within a window (e.g., days 7–13) | Weekly-use products | Moderate |
| Rolling (unbounded) | Active on or after day N | Low-frequency products (travel, B2B) | Higher, smooth |
Data Model and Assumptions
All queries use PostgreSQL syntax. Two tables are enough:
-- One row per user, with the cohort-defining event
CREATE TABLE users (
user_id BIGINT PRIMARY KEY,
signup_date DATE NOT NULL
);
-- One row per activity event (login, purchase, session start)
CREATE TABLE events (
event_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(user_id),
event_time TIMESTAMP NOT NULL
);
-- Index to keep joins and date filters fast at scale
CREATE INDEX idx_events_user_time ON events (user_id, event_time);
Define “active” before you write any SQL. A login, a purchase, and a pageview produce three different retention curves. Pick the event that represents real product value and keep it consistent across reports.
Step 1: Assign Users to Monthly Cohorts
Truncate the signup date to the month. DATE_TRUNC(‘month’, …) collapses every date in a month to the first day, which gives you a clean cohort key.
SELECT
user_id,
DATE_TRUNC('month', signup_date)::date AS cohort_month
FROM users;
Choose cohort granularity to match your product cycle. Daily cohorts suit consumer apps with fast feedback loops. Weekly cohorts balance noise and speed. Monthly cohorts suit subscription and B2B products.
Step 2: Compute Activity Periods Per User
Deduplicate activity to one row per user per month. This prevents a user with 50 sessions from being counted 50 times.
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_time)::date AS activity_month
FROM events;
SELECT DISTINCT is the simplest approach. At larger volumes, replace it with a GROUP BY user_id, activity_month inside a CTE, which is easier for planners to optimize.
Step 3: Calculate the Period Offset
The offset (month_number) is the gap between the activity month and the cohort month. Month 0 is the signup month, month 1 is the following month, and so on.
WITH cohorts AS (
SELECT user_id,
DATE_TRUNC('month', signup_date)::date AS cohort_month
FROM users
),
activity AS (
SELECT DISTINCT user_id,
DATE_TRUNC('month', event_time)::date AS activity_month
FROM events
),
joined AS (
SELECT
c.user_id,
c.cohort_month,
a.activity_month,
-- Convert both dates to a month index, then subtract
(EXTRACT(YEAR FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month))
- (EXTRACT(YEAR FROM c.cohort_month) * 12 + EXTRACT(MONTH FROM c.cohort_month))
AS month_number
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id
WHERE a.activity_month >= c.cohort_month -- drop pre-signup noise
)
SELECT * FROM joined LIMIT 10;
The WHERE filter removes events that predate the signup record, which happen with backfilled or migrated data. Check how many rows it drops; a large number signals a data quality problem.
Step 4: Build the Retention Table (Long Format)
Count distinct active users per cohort and offset, then divide by cohort size.
WITH cohorts AS (
SELECT user_id,
DATE_TRUNC('month', signup_date)::date AS cohort_month
FROM users
),
activity AS (
SELECT DISTINCT user_id,
DATE_TRUNC('month', event_time)::date AS activity_month
FROM events
),
joined AS (
SELECT
c.user_id,
c.cohort_month,
((EXTRACT(YEAR FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month))
- (EXTRACT(YEAR FROM c.cohort_month) * 12 + EXTRACT(MONTH FROM c.cohort_month)))::int
AS month_number
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id
WHERE a.activity_month >= c.cohort_month
),
cohort_size AS (
SELECT cohort_month, COUNT(*) AS cohort_users
FROM cohorts
GROUP BY cohort_month
)
SELECT
j.cohort_month,
cs.cohort_users,
j.month_number,
COUNT(DISTINCT j.user_id) AS active_users,
ROUND(100.0 * COUNT(DISTINCT j.user_id) / cs.cohort_users, 1) AS retention_pct
FROM joined j
JOIN cohort_size cs USING (cohort_month)
GROUP BY j.cohort_month, cs.cohort_users, j.month_number
ORDER BY j.cohort_month, j.month_number;
This long format is ideal for BI tools and for pandas. Each row holds one cohort-period cell. The denominator is cohort_users, the full cohort, so month 0 may land below 100% if some users never triggered an activity event.
Step 5: Pivot Into a Retention Matrix
Analysts prefer a triangle: cohorts as rows, months as columns. PostgreSQL’s FILTER clause makes conditional aggregation readable.
-- Reuse the CTEs from Step 4 (cohorts, activity, joined, cohort_size), then:
SELECT
j.cohort_month,
cs.cohort_users,
ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 0) / cs.cohort_users, 1) AS m0,
ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 1) / cs.cohort_users, 1) AS m1,
ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 2) / cs.cohort_users, 1) AS m2,
ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 3) / cs.cohort_users, 1) AS m3,
ROUND(100.0 * COUNT(DISTINCT j.user_id) FILTER (WHERE month_number = 6) / cs.cohort_users, 1) AS m6
FROM joined j
JOIN cohort_size cs USING (cohort_month)
GROUP BY j.cohort_month, cs.cohort_users
ORDER BY j.cohort_month;
Cells for periods that have not happened yet return NULL. Keep them blank rather than showing 0%. A zero implies churn, while a blank means “too early to tell.”
Dialect Differences
| Task | PostgreSQL | BigQuery | MySQL 8 | Snowflake |
|---|---|---|---|---|
| Truncate to month | DATE_TRUNC(‘month’, d) | DATE_TRUNC(d, MONTH) | DATE_FORMAT(d,’%Y-%m-01′) | DATE_TRUNC(‘month’, d) |
| Month difference | Year×12 + month math | DATE_DIFF(a, b, MONTH) | TIMESTAMPDIFF(MONTH, b, a) | DATEDIFF(‘month’, b, a) |
| Conditional count | FILTER (WHERE …) | COUNTIF(…) | SUM(CASE …) | COUNT_IF(…) |
| Distinct count at scale | Exact | APPROX_COUNT_DISTINCT | Exact | APPROX_COUNT_DISTINCT |
Day-N Retention for Daily Products
Monthly buckets are too coarse for apps where users return daily. Compute classic D1, D7, and D30 retention with date arithmetic.
WITH daily_activity AS (
SELECT DISTINCT user_id, event_time::date AS event_date
FROM events
)
SELECT
u.signup_date AS cohort_date,
COUNT(DISTINCT u.user_id) AS cohort_users,
ROUND(100.0 * COUNT(DISTINCT d.user_id) FILTER (WHERE d.event_date = u.signup_date + 1)
/ COUNT(DISTINCT u.user_id), 1) AS d1_pct,
ROUND(100.0 * COUNT(DISTINCT d.user_id) FILTER (WHERE d.event_date = u.signup_date + 7)
/ COUNT(DISTINCT u.user_id), 1) AS d7_pct,
ROUND(100.0 * COUNT(DISTINCT d.user_id) FILTER (WHERE d.event_date = u.signup_date + 30)
/ COUNT(DISTINCT u.user_id), 1) AS d30_pct
FROM users u
LEFT JOIN daily_activity d ON d.user_id = u.user_id
GROUP BY u.signup_date
ORDER BY u.signup_date;
The LEFT JOIN keeps users who never returned in the denominator. An INNER JOIN would silently drop them and overstate retention.
Rolling (Unbounded) Retention
Swap the equality test for a comparison against each user’s last activity date:
WITH last_seen AS (
SELECT user_id, MAX(event_time)::date AS last_active
FROM events
GROUP BY user_id
)
SELECT
u.signup_date,
COUNT(*) AS cohort_users,
ROUND(100.0 * COUNT(*) FILTER (WHERE l.last_active >= u.signup_date + 7) / COUNT(*), 1) AS rolling_d7,
ROUND(100.0 * COUNT(*) FILTER (WHERE l.last_active >= u.signup_date + 30) / COUNT(*), 1) AS rolling_d30
FROM users u
LEFT JOIN last_seen l ON l.user_id = u.user_id
GROUP BY u.signup_date
ORDER BY u.signup_date;
Rolling retention never rises as N grows. It only flattens or falls, which makes it a stable metric for low-frequency products.
Visualize the Retention Matrix in Python
Pull the long-format result into pandas and render a heatmap.
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
engine = create_engine("postgresql+psycopg2://user:password@host:5432/db")
# Paste the Step 4 query into this string
query = open("retention_long.sql").read()
df = pd.read_sql(query, engine)
# Pivot: rows = cohort, columns = months since signup
matrix = df.pivot(index="cohort_month", columns="month_number", values="retention_pct")
plt.figure(figsize=(12, 7))
sns.heatmap(
matrix,
annot=True, fmt=".0f", # show rounded percentages in each cell
cmap="Blues",
vmin=0, vmax=100,
cbar_kws={"label": "Retention %"},
)
plt.title("Monthly Cohort Retention")
plt.xlabel("Months Since Signup")
plt.ylabel("Cohort Month")
plt.tight_layout()
plt.savefig("retention_heatmap.png", dpi=150)
Read the heatmap in two directions. Reading across a row shows the decay curve of one cohort. Reading down a column compares cohorts at the same age, which is how you detect whether product changes improved retention.
Real-World Use Cases
Predicting Churn Risk for a SaaS Product
A B2B SaaS team defines the cohort as signup month and “active” as any session with a core feature. The matrix shows month-1 retention falling from 62% to 48% for cohorts after a pricing change. The team then splits cohorts by acquisition channel (add channel to the cohorts CTE and GROUP BY) and finds paid-social users drive the drop.
Measuring Onboarding Impact in a Mobile App
An app ships a new onboarding flow on March 1. Weekly cohorts before and after the date are compared on D1 and D7 retention. A lift in D1 with a flat D7 suggests the flow improves first-session success but not long-term habit formation.
Repeat Purchase Analysis in E-commerce
Define the cohort by first order month and activity by any later order. The matrix becomes a repeat-purchase curve. Add SUM(order_value) instead of COUNT(DISTINCT user_id) to produce revenue retention, which reveals whether surviving customers spend more over time.
Common Pitfalls and Fixes
| Pitfall | Symptom | Fix |
|---|---|---|
| Dividing by period-0 actives | Month 0 always shows 100% | Divide by full cohort_users |
| INNER JOIN to events | Retention inflated | Use LEFT JOIN from users |
| Counting events, not users | Retention above 100% | Use COUNT(DISTINCT user_id) |
| Incomplete current period | Latest cohort looks like it churned | Exclude or flag the in-progress month |
| Timezone mismatch | Day-N counts shift by one | Convert to one timezone before ::date |
| Tiny cohorts | Wild percentage swings | Show cohort_users beside every row |
Performance Tips for Large Event Tables
- Pre-aggregate events into a daily or monthly user-activity table. Joining billions of raw events on every refresh is wasteful.
- Partition the events table by month so date filters prune partitions.
- Materialize the retention result with a scheduled job, and let dashboards read the small output table.
- Use APPROX_COUNT_DISTINCT on warehouses that support it when exact counts are not required.
Strategic FAQ
What is the difference between cohort analysis and retention analysis?
Cohort analysis is the grouping method: it segments users by a shared start event or attribute. Retention analysis is the metric applied to those groups: it measures the share of each cohort still active after a given period. Retention cohort tables combine both.
How do you calculate retention rate in SQL?
Assign each user a cohort date, compute the period offset between each activity date and the cohort date, count distinct active users per offset, and divide by cohort size. Multiply by 100 for a percentage. Use COUNT(DISTINCT user_id) with a LEFT JOIN from the users table.
What is a good retention rate?
It depends on product type. Consumer mobile apps commonly see low double-digit D30 retention, while B2B SaaS products often retain well over half of customers after the first month. Compare your own cohorts against each other over time rather than chasing a universal benchmark.
Should I use classic or rolling retention?
Use classic retention for products with a natural daily or weekly usage rhythm, since it shows exact return behavior. Use rolling retention for products people use irregularly, such as travel booking or tax software, where a missed day does not mean churn.



