Funnel Conversion

1-- Funnel conversion with a fixed conversion window, using ClickHouse windowFunnel.
2-- windowFunnel(window_seconds)(timestamp, cond1, cond2, ...) returns the count of steps completed IN ORDER
3-- within window_seconds of the first step, per aggregation unit.
4--
5-- NOTE: wrap the time arg in toDateTime() — PostHog's `timestamp` is DateTime64, which windowFunnel rejects.
6-- Replace the event names with the real ones (confirm via read-data-schema first).
7-- Here: signed_up -> activated -> purchased, 14-day window (1209600s), per person.
8WITH per_person AS (
9    SELECT
10        person_id,
11        windowFunnel(1209600)(
12            toDateTime(timestamp),
13            event = 'signed_up',
14            event = 'activated',
15            event = 'purchased'
16        ) AS steps_completed
17    FROM events
18    WHERE event IN ('signed_up', 'activated', 'purchased')
19      AND timestamp >= now() - INTERVAL 90 DAY
20    GROUP BY person_id
21)
22SELECT
23    countIf(steps_completed >= 1)                                            AS entered_step_1,
24    countIf(steps_completed >= 2)                                            AS reached_step_2,
25    countIf(steps_completed >= 3)                                            AS reached_step_3,
26    round(countIf(steps_completed >= 3) / nullif(countIf(steps_completed >= 1), 0), 4) AS overall_conversion,
27    round(countIf(steps_completed >= 2) / nullif(countIf(steps_completed >= 1), 0), 4) AS step1_to_step2,
28    round(countIf(steps_completed >= 3) / nullif(countIf(steps_completed >= 2), 0), 4) AS step2_to_step3
29FROM per_person