Activation Flag

1-- Per-user activation flag + activation rate by signup cohort.
2-- Definition (example): activated = ran the key action >= 3 times within 7 days of first-ever event.
3-- Replace 'key_action' and thresholds with the validated definition (see activation-method.md).
4--
5-- signup_at must be the user's FIRST-EVER event, computed over full history. Filtering events to a
6-- reporting window BEFORE the min() would hand a long-lived, recently-active user a brand-new
7-- "signup" date and wrongly count them as freshly activated. So compute first-ever first, then
8-- restrict the report to recent cohorts. (Materialize first_seen if the full-history scan is heavy.)
9WITH first_seen AS (
10    SELECT person_id, min(timestamp) AS signup_at
11    FROM events
12    GROUP BY person_id
13),
14cohort AS (
15    -- the signup cohorts to report on; this filter does not affect signup_at itself
16    SELECT person_id, signup_at
17    FROM first_seen
18    WHERE signup_at >= now() - INTERVAL 180 DAY
19),
20early_actions AS (
21    SELECT
22        c.person_id,
23        countIf(e.event = 'key_action'
24                AND e.timestamp <= c.signup_at + INTERVAL 7 DAY) AS key_actions_first_week
25    FROM events AS e
26    INNER JOIN cohort AS c ON e.person_id = c.person_id
27    GROUP BY c.person_id
28)
29SELECT
30    toStartOfWeek(c.signup_at)                                       AS signup_week,
31    count()                                                          AS signups,
32    countIf(ea.key_actions_first_week >= 3)                          AS activated,
33    round(countIf(ea.key_actions_first_week >= 3) / nullif(count(), 0), 4) AS activation_rate
34FROM cohort AS c
35LEFT JOIN early_actions AS ea ON c.person_id = ea.person_id
36GROUP BY signup_week
37ORDER BY signup_week