Part 177: Retention & Cohort Analysis in SQL: N-Day Retention Matrices

Constructing the classic product retention heatmap in pure SQL.

1. What is Cohort Retention?

Users are grouped into cohorts based on the date of their initial signup or first action. We then track what percentage of each cohort returns to the product on Day 1, Day 7, Day 14, and Day 30.

2. The Complete SQL Cohort Query

WITH first_activity AS (
    -- Step 1: Determine the acquisition cohort date for each user
    SELECT 
        user_id,
        MIN(DATE(activity_time)) AS cohort_date
    FROM user_activities
    GROUP BY user_id
),
activity_days AS (
    -- Step 2: Calculate days elapsed between cohort date and subsequent activities
    SELECT 
        f.user_id,
        f.cohort_date,
        DATE(a.activity_time) - f.cohort_date AS days_since_signup
    FROM user_activities a
    JOIN first_activity f ON a.user_id = f.user_id
    GROUP BY f.user_id, f.cohort_date, DATE(a.activity_time)
),
cohort_sizes AS (
    -- Step 3: Count initial user size of each cohort (Day 0)
    SELECT 
        cohort_date,
        COUNT(DISTINCT user_id) AS cohort_size
    FROM first_activity
    GROUP BY cohort_date
)
-- Step 4: Pivot active counts across retention milestones
SELECT 
    s.cohort_date,
    s.cohort_size,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.days_since_signup = 1 THEN a.user_id END) / s.cohort_size, 1) AS day_1_retention,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.days_since_signup = 7 THEN a.user_id END) / s.cohort_size, 1) AS day_7_retention,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.days_since_signup = 14 THEN a.user_id END) / s.cohort_size, 1) AS day_14_retention,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.days_since_signup = 30 THEN a.user_id END) / s.cohort_size, 1) AS day_30_retention
FROM cohort_sizes s
LEFT JOIN activity_days a ON s.cohort_date = a.cohort_date
GROUP BY s.cohort_date, s.cohort_size
ORDER BY s.cohort_date DESC;

Key Takeaways

• The combination of MIN(...) for cohort assignment and LEFT JOIN on days difference produces standard retention matrices.

In Part 178, we explore Customer Lifetime Value (LTV) & RFM Segmentation in SQL!