Goal: compute month-by-month retention for cohorts defined by users' signup month, for months 0–11 after signup.High-level approach1. Define cohort_month = date_trunc('month', signup_date).2. For each user, compute month_index = months_between(activity_date, signup_date) where month_index ∈ [0..11].3. Mark a user as “retained in month k” if they have at least one qualifying activity in that month (define qualifying = any session, paid event, or other business-specific action).4. Aggregate unique users per cohort and month_index to produce retention rate = retained_users / cohort_size.SQL pseudocodesql
WITH events AS (
SELECT user_id, signup_date, date_trunc('month', signup_date) AS cohort_month,
date_trunc('month', activity_date) AS activity_month,
DATE_PART('year', activity_date)*12 + DATE_PART('month', activity_date)
- (DATE_PART('year', signup_date)*12 + DATE_PART('month', signup_date)) AS month_index
FROM user_events
WHERE activity_date IS NOT NULL
),
filtered AS (
SELECT user_id, cohort_month, month_index
FROM events
WHERE month_index BETWEEN 0 AND 11
GROUP BY user_id, cohort_month, month_index
),
cohort AS (
SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
FROM users
GROUP BY cohort_month
)
SELECT f.cohort_month, f.month_index,
COUNT(DISTINCT f.user_id) AS retained,
c.cohort_size,
COUNT(DISTINCT f.user_id)::float / c.cohort_size AS retention_rate
FROM filtered f
JOIN cohort c USING (cohort_month)
GROUP BY f.cohort_month, f.month_index, c.cohort_size
ORDER BY f.cohort_month, f.month_index;
Pandas sketchpython
df['cohort_month'] = df.signup_date.dt.to_period('M')
df['month_index'] = ((df.activity_date.dt.to_period('M') - df.cohort_month).apply(lambda x: x.n))
df = df[df.month_index.between(0,11)]
retained = df.groupby(['cohort_month','month_index'])['user_id'].nunique().reset_index()
cohort_size = users.groupby('cohort_month')['user_id'].nunique().reset_index()
merge and compute rates
Handling special cases- Reactivations: treat any activity in month k as retention; reactivated users count in months after inactivity. If you want "consecutive retention" vs "ever-active", compute separate flags.- Intermittent activity: use dedup by (user, month) to avoid overcount; consider thresholds (≥N events or ≥T minutes) for meaningful engagement.- Trial-to-paid conversions: compute separate retention curves for trial-only, paid-converted cohorts, or add segmentation dimension (is_paid_at_month_k). For revenue retention use MRR cohorts.- Incomplete observation windows (censoring): for recent cohorts, mark months beyond data export as "unobserved" and exclude them from cohort averages or use right-censoring techniques. When reporting, show cohort sizes per month and use shaded/NA cells where observation missing.- Edge considerations: timezone/alignment, user timezones, user merges, multiple signups—use first signup as cohort anchor. Validate with counts and visual heatmap.