Conversion By Breakdown

1-- Overall conversion rate broken down by a first-touch property (here: signup channel).
2-- Attribution = first-touch: each person's breakdown value is taken from their first step-1 event.
3WITH first_touch AS (
4    SELECT
5        person_id,
6        argMin(properties.$initial_utm_source, timestamp) AS channel  -- value at first event
7    FROM events
8    WHERE event = 'signed_up'
9      AND timestamp >= now() - INTERVAL 90 DAY
10    GROUP BY person_id
11),
12per_person AS (
13    SELECT
14        person_id,
15        windowFunnel(1209600)(
16            toDateTime(timestamp),  -- toDateTime: `timestamp` is DateTime64, which windowFunnel rejects
17            event = 'signed_up',
18            event = 'purchased'
19        ) AS steps_completed
20    FROM events
21    WHERE event IN ('signed_up', 'purchased')
22      AND timestamp >= now() - INTERVAL 90 DAY
23    GROUP BY person_id
24)
25SELECT
26    coalesce(f.channel, '(none)')                                            AS channel,
27    count()                                                                  AS entered,
28    countIf(p.steps_completed >= 2)                                          AS converted,
29    round(countIf(p.steps_completed >= 2) / nullif(count(), 0), 4)           AS conversion_rate
30FROM per_person AS p
31LEFT JOIN first_touch AS f ON p.person_id = f.person_id
32GROUP BY channel
33ORDER BY entered DESC