Cohort Analysis for Data Analysts

Acquisition cohorts, retention curves, cohort heatmaps in SQL and Python, rolling vs classic retention, behavioural cohorts, revenue cohorts, LTV calculation, and how to read and present cohort findings.

-- Step 1: Assign each user their cohort month
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
    FROM   user_activity
    GROUP  BY user_id
),

-- Step 2: For each user-activity combination, calculate period number
-- (months since cohort_month)
user_activities AS (
    SELECT
        a.user_id,
        c.cohort_month,
        DATE_TRUNC('month', a.activity_date)               AS activity_month,
        DATEDIFF('month', c.cohort_month,
                 DATE_TRUNC('month', a.activity_date))      AS period_number
    FROM   user_activity      a
    JOIN   user_cohorts       c  USING (user_id)
),

-- Step 3: Count distinct active users per cohort per period
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM   user_cohorts
    GROUP  BY cohort_month
)

-- Step 4: Pivot into retention percentages
SELECT
    ua.cohort_month,
    cs.cohort_size,
    ua.period_number,
    COUNT(DISTINCT ua.user_id)                                   AS retained_users,
    ROUND(COUNT(DISTINCT ua.user_id) * 100.0 / cs.cohort_size, 1) AS retention_pct
FROM   user_activities  ua
JOIN   cohort_sizes     cs  USING (cohort_month)
GROUP  BY ua.cohort_month, cs.cohort_size, ua.period_number
ORDER  BY ua.cohort_month, ua.period_number;

-- Result shape:
-- cohort_month | cohort_size | period_number | retained_users | retention_pct
-- 2026-01-01   |    1240     |      0        |     1240       |   100.0
-- 2026-01-01   |    1240     |      1        |      744       |    60.0
-- 2026-01-01   |    1240     |      2        |      558       |    45.0
-- 2026-02-01   |    1380     |      0        |     1380       |   100.0
-- 2026-02-01   |    1380     |      1        |      855       |    61.9

-- BigQuery uses DATE_DIFF(date1, date2, MONTH) instead of DATEDIFF
DATE_DIFF(DATE_TRUNC(activity_date, MONTH),
          DATE_TRUNC(cohort_date, MONTH), MONTH) AS period_number

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.colors as mcolors

# Assume df has columns: cohort_month, period_number, retention_pct
# (output from the SQL above, loaded into a DataFrame)

# Pivot to cohort heatmap matrix
cohort_pivot = df.pivot_table(
    index='cohort_month',
    columns='period_number',
    values='retention_pct'
)

# Format index as month strings
cohort_pivot.index = cohort_pivot.index.strftime('%b %Y')

# Plot
fig, ax = plt.subplots(figsize=(14, 7))

cmap = mcolors.LinearSegmentedColormap.from_list(
    'retention', ['#fff', '#0d9488'], N=256
)

im = ax.imshow(cohort_pivot.values, cmap=cmap, aspect='auto',
               vmin=0, vmax=100)

# Annotate cells with percentage values
for i in range(cohort_pivot.shape[0]):
    for j in range(cohort_pivot.shape[1]):
        val = cohort_pivot.values[i, j]
        if not np.isnan(val):
            ax.text(j, i, f'{val:.0f}%',
                    ha='center', va='center',
                    color='white' if val > 50 else '#1e293b',
                    fontsize=8, fontweight='bold')

ax.set_xticks(range(cohort_pivot.shape[1]))
ax.set_xticklabels([f'M{i}' for i in cohort_pivot.columns], fontsize=9)
ax.set_yticks(range(cohort_pivot.shape[0]))
ax.set_yticklabels(cohort_pivot.index, fontsize=9)

ax.set_title('Monthly Retention by Acquisition Cohort', fontsize=14, pad=15)
ax.set_xlabel('Months since acquisition')
ax.set_ylabel('Acquisition cohort')

plt.colorbar(im, ax=ax, label='Retention %', shrink=0.8)
plt.tight_layout()
plt.savefig('cohort_heatmap.png', dpi=150, bbox_inches='tight')
plt.show()

-- Classic Day-7 retention: active on exactly day 7 (±1 day tolerance)
SELECT
    cohort_date,
    COUNT(DISTINCT user_id)                               AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN activity_date BETWEEN cohort_date + INTERVAL '6 days'
                               AND cohort_date + INTERVAL '8 days'
        THEN user_id END)                                 AS day7_retained,
    ROUND(COUNT(DISTINCT CASE
        WHEN activity_date BETWEEN cohort_date + INTERVAL '6 days'
                               AND cohort_date + INTERVAL '8 days'
        THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 1) AS day7_retention_pct
FROM cohort_base
GROUP BY cohort_date;


-- Rolling (bounded) Day-7 retention: active at any point in days 7–13
SELECT
    cohort_date,
    COUNT(DISTINCT user_id)                               AS cohort_size,
    COUNT(DISTINCT CASE
        WHEN activity_date BETWEEN cohort_date + INTERVAL '7 days'
                               AND cohort_date + INTERVAL '13 days'
        THEN user_id END)                                 AS day7_rolling_retained
FROM cohort_base
GROUP BY cohort_date;

-- Revenue cohort: total revenue generated by each acquisition cohort over time
WITH revenue_by_cohort AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', o.order_date)       AS revenue_month,
        DATE_DIFF(
            DATE_TRUNC('month', o.order_date),
            c.cohort_month, MONTH
        )                                        AS months_since_acquisition,
        SUM(o.order_total)                       AS revenue
    FROM   orders          o
    JOIN   user_cohorts    c  USING (user_id)
    GROUP  BY 1, 2, 3
),

cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM   user_cohorts GROUP BY 1
)

-- Cumulative LTV per user: running total of revenue / cohort_size
SELECT
    r.cohort_month,
    r.months_since_acquisition,
    cs.cohort_size,
    r.revenue,
    SUM(r.revenue) OVER (
        PARTITION BY r.cohort_month
        ORDER BY r.months_since_acquisition
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                            AS cumulative_revenue,
    ROUND(
        SUM(r.revenue) OVER (
            PARTITION BY r.cohort_month
            ORDER BY r.months_since_acquisition
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) / cs.cohort_size, 2
    )                                            AS cumulative_ltv_per_user
FROM   revenue_by_cohort r
JOIN   cohort_sizes      cs  USING (cohort_month)
ORDER  BY r.cohort_month, r.months_since_acquisition;