Cohort Analysis: Track User Behavior Over Time

SELECT
  DATE_TRUNC('month', signup_date) AS cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM users
WHERE signup_date >= '2026-01-01'
GROUP BY cohort_month
ORDER BY cohort_month;

WITH first_month_purchases AS (
  SELECT
    user_id,
    COUNT(*) AS purchase_count_month_0
  FROM orders
  WHERE order_date BETWEEN signup_date AND signup_date + INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  CASE
    WHEN purchase_count_month_0 = 1 THEN '1 purchase'
    WHEN purchase_count_month_0 BETWEEN 2 AND 4 THEN '2-4 purchases'
    WHEN purchase_count_month_0 >= 5 THEN '5+ purchases'
  END AS cohort,
  COUNT(*) AS users
FROM first_month_purchases
GROUP BY cohort;

SELECT
  acquisition_channel,
  COUNT(DISTINCT user_id) AS cohort_size,
  AVG(lifetime_value) AS avg_ltv
FROM users
WHERE signup_date >= '2026-01-01'
GROUP BY acquisition_channel
ORDER BY avg_ltv DESC;

users (user_id, signup_date)
events (user_id, event_date, event_type)

WITH cohorts AS (
  -- Define cohort (signup month)
  SELECT
    user_id,
    DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
user_activity AS (
  -- Get user activity by month
  SELECT
    user_id,
    DATE_TRUNC('month', event_date) AS activity_month
  FROM events
  WHERE event_type = 'purchase'  -- or 'login', 'session_start', etc.
  GROUP BY user_id, activity_month
),
cohort_activity AS (
  -- Join cohorts with activity, calculate months since signup
  SELECT
    c.cohort_month,
    c.user_id,
    a.activity_month,
    DATEDIFF('month', c.cohort_month, a.activity_month) AS months_since_signup
  FROM cohorts c
  LEFT JOIN user_activity a ON c.user_id = a.user_id
),
cohort_sizes AS (
  -- Count cohort size (Month 0)
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM cohorts
  GROUP BY cohort_month
),
retention_by_month AS (
  -- Count active users per cohort per month
  SELECT
    cohort_month,
    months_since_signup,
    COUNT(DISTINCT user_id) AS active_users
  FROM cohort_activity
  WHERE activity_month IS NOT NULL
  GROUP BY cohort_month, months_since_signup
)
-- Calculate retention rate
SELECT
  r.cohort_month,
  r.months_since_signup,
  r.active_users,
  cs.cohort_size,
  ROUND(100.0 * r.active_users / cs.cohort_size, 1) AS retention_pct
FROM retention_by_month r
JOIN cohort_sizes cs ON r.cohort_month = cs.cohort_month
ORDER BY r.cohort_month, r.months_since_signup;

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

# Load SQL results
df = pd.read_sql(query, connection)

# Pivot to cohort table format
cohort_table = df.pivot_table(
    index='cohort_month',
    columns='months_since_signup',
    values='retention_pct'
)

# Heatmap
plt.figure(figsize=(12, 8))
sns.heatmap(
    cohort_table,
    annot=True,         # Show percentages in cells
    fmt='.0f',          # Format as integers
    cmap='RdYlGn',      # Red (low) to Green (high)
    vmin=0,
    vmax=100,
    cbar_kws={'label': 'Retention %'}
)
plt.title('Monthly Retention Cohort Analysis')
plt.xlabel('Months Since Signup')
plt.ylabel('Cohort (Signup Month)')
plt.tight_layout()
plt.show()

WITH cohort_revenue AS (
  SELECT
    DATE_TRUNC('month', u.signup_date) AS cohort_month,
    DATEDIFF('month', DATE_TRUNC('month', u.signup_date),
             DATE_TRUNC('month', o.order_date)) AS months_since_signup,
    SUM(o.order_value) AS revenue
  FROM users u
  JOIN orders o ON u.user_id = o.user_id
  GROUP BY cohort_month, months_since_signup
),
cumulative AS (
  SELECT
    cohort_month,
    months_since_signup,
    SUM(revenue) OVER (
      PARTITION BY cohort_month
      ORDER BY months_since_signup
    ) AS cumulative_revenue
  FROM cohort_revenue
)
SELECT
  cohort_month,
  months_since_signup,
  cumulative_revenue /
    (SELECT COUNT(*) FROM users WHERE DATE_TRUNC('month', signup_date) = cohort_month)
    AS revenue_per_user
FROM cumulative;

WITH last_activity AS (
  SELECT
    user_id,
    MAX(event_date) AS last_active_date
  FROM events
  GROUP BY user_id
),
churn_time AS (
  SELECT
    u.user_id,
    DATE_TRUNC('month', u.signup_date) AS cohort_month,
    DATEDIFF('day', u.signup_date, la.last_active_date) AS days_to_churn
  FROM users u
  JOIN last_activity la ON u.user_id = la.user_id
  WHERE la.last_active_date < CURRENT_DATE - INTERVAL '30 days'  -- Inactive for 30+ days = churned
)
SELECT
  cohort_month,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_churn) AS median_days_to_churn,
  AVG(days_to_churn) AS avg_days_to_churn
FROM churn_time
GROUP BY cohort_month
ORDER BY cohort_month;

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', signup_date) AS cohort_month,
    CASE WHEN has_delivery_pass THEN 'Paid Pass' ELSE 'Free' END AS segment
  FROM users
),
retention AS (
  -- (Same retention logic as before, grouped by segment)
  ...
)
SELECT
  cohort_month,
  segment,
  months_since_signup,
  retention_pct
FROM retention
ORDER BY cohort_month, segment, months_since_signup;