Retention Matrix

1-- Weekly retention matrix: % active in each subsequent week, by acquisition cohort.
2-- Recurring definition (active IN week N, independent per week). Replace 'core_action' with the value event.
3-- Cohort = the person's first active week WITHIN the analysis window (the standard retention convention),
4-- not their first-ever week. Widen the window, or anchor on a true first-seen week, if you need lifetime cohorts.
5WITH activity AS (
6    SELECT
7        person_id,
8        toStartOfWeek(timestamp) AS week
9    FROM events
10    WHERE event = 'core_action'
11      AND timestamp >= now() - INTERVAL 12 WEEK
12    GROUP BY person_id, week
13),
14cohort AS (
15    SELECT person_id, min(week) AS cohort_week
16    FROM activity
17    GROUP BY person_id
18)
19SELECT
20    c.cohort_week                                                    AS cohort_week,
21    dateDiff('week', c.cohort_week, a.week)                          AS weeks_later,
22    count(DISTINCT a.person_id)                                      AS retained,
23    -- cohort size = retained at weeks_later = 0
24    round(count(DISTINCT a.person_id)
25          / max(count(DISTINCT a.person_id)) OVER (PARTITION BY c.cohort_week), 4) AS retention_rate
26FROM cohort AS c
27INNER JOIN activity AS a ON a.person_id = c.person_id
28WHERE a.week >= c.cohort_week
29GROUP BY cohort_week, weeks_later
30ORDER BY cohort_week, weeks_later