Activation Retention Lift

1-- Validate a candidate activation definition: does it actually predict retention?
2-- Compares week-4 retention of users who met the criteria early vs those who didn't.
3-- A good definition shows large, stable lift (retained_activated - retained_not).
4--
5-- Two correctness guards:
6--  1. signup_at is the FIRST-EVER event (full history), not the first event in a lookback window —
7--     else a long-lived, recently-active user gets a false-new signup date.
8--  2. Only cohorts old enough for a full week-4 window count. Recent signups that haven't reached
9--     day 28 would score retained_w4 = 0 and drag the lift down (right-censoring), so exclude them.
10WITH first_seen AS (
11    SELECT person_id, min(timestamp) AS signup_at
12    FROM events
13    GROUP BY person_id
14),
15cohort AS (
16    -- eligible cohorts: recent enough to report on, but old enough to have a complete week 4
17    SELECT person_id, signup_at
18    FROM first_seen
19    WHERE signup_at >= now() - INTERVAL 180 DAY
20      AND signup_at <= now() - INTERVAL 28 DAY
21),
22activated AS (
23    SELECT
24        c.person_id,
25        countIf(e.event = 'key_action'
26                AND e.timestamp <= c.signup_at + INTERVAL 7 DAY) >= 3 AS is_activated
27    FROM events AS e
28    INNER JOIN cohort AS c ON e.person_id = c.person_id
29    GROUP BY c.person_id
30),
31week4 AS (  -- did the user do anything in their 4th week after signup?
32    SELECT
33        c.person_id,
34        max(e.timestamp >= c.signup_at + INTERVAL 21 DAY
35            AND e.timestamp < c.signup_at + INTERVAL 28 DAY) AS retained_w4
36    FROM events AS e
37    INNER JOIN cohort AS c ON e.person_id = c.person_id
38    GROUP BY c.person_id
39)
40SELECT
41    a.is_activated                                                   AS activated,
42    count()                                                          AS users,
43    round(avg(w.retained_w4), 4)                                     AS week4_retention
44FROM activated AS a
45LEFT JOIN week4 AS w ON a.person_id = w.person_id
46GROUP BY activated
47ORDER BY activated