SQL Cohort Retention Analysis

Retention asks whether a defined actor returns after a starting milestone. A useful retention query states the actor, cohort event, qualifying return activity, interval, and observation window. “Weekly retention” is ambiguous until those five choices are explicit.

Build cohort and activity tables separately

WITH cohorts AS (
  SELECT
    account_id,
    date_trunc('week', MIN(timestamp_utc)) AS cohort_week
  FROM product_events
  WHERE event_name = 'activation_completed'
  GROUP BY account_id
),
activity AS (
  SELECT DISTINCT
    account_id,
    date_trunc('week', timestamp_utc) AS activity_week
  FROM product_events
  WHERE event_name IN ('query_completed', 'dashboard_viewed')
)
SELECT
  cohort_week,
  activity_week,
  COUNT(DISTINCT cohorts.account_id) AS retained_accounts
FROM cohorts
JOIN activity
  ON cohorts.account_id = activity.account_id
 AND activity.activity_week >= cohorts.cohort_week
GROUP BY cohort_week, activity_week
ORDER BY cohort_week, activity_week;

This result preserves cohort and activity weeks. A reporting layer can convert the gap into week zero, week one, and later periods after confirming the date arithmetic supported by the selected SQL engine. Keeping the intermediate dates visible makes boundary mistakes easier to catch.

Define the denominator once

The denominator is the number of eligible actors in the cohort, not the number that happened to produce a later activity event. Calculate cohort size separately and join it to the retained count. Protect the percentage with NULLIF, and preserve both counts in the output.

Recent cohorts have not had time to reach later intervals. Mark those cells incomplete instead of treating them as zero retention. Also decide whether week zero means activation itself or a separate return after activation.

Use an event that represents durable value rather than any background heartbeat. Exclude test accounts and automated activity with controlled fields. Validate identity merges, duplicate events, late arrivals, and time-zone boundaries with a small fixture.

Read Funnel Analysis for milestone progression, Time Bucketing for interval rules, and the DataFusion SQL reference before adapting date calculations from another SQL dialect.