Bookings by source

1-- core.calendly.bookings_by_source
2-- Where bookings come from and what became of them: new bookings by UTM source,
3-- medium and campaign, followed through any reschedules to their final outcome.
4--
5-- Grain: booking month x calendly_account x utm_source x utm_medium x utm_campaign
6--        x event type.
7-- Depends on: staging.calendly.invitee, core.calendly.scheduled_events
8--
9-- ONE BOOKING, ONE ROW OF FUNNEL. A reschedule in Calendly cancels the invitee and
10-- creates a new one, so counting invitee rows counts a meeting moved twice as three
11-- bookings, two of them "canceled". This model starts from the original booking
12-- (is_new_booking), walks the old_invitee -> new_invitee chain to the last
13-- invitee, and takes the outcome from there. Source, booking month and event type
14-- stay those of the ORIGINAL booking, because that is the click you are measuring.
15--
16-- UTM values come from the tracking parameters on the booking link. A booking
17-- without them groups under '(not set)'. Calendly only records what was on the URL:
18-- an embed or a link shared without UTM parameters lands in '(not set)' however the
19-- visitor actually arrived.
20--
21-- TIME ZONE: months are cut in UTC. Change the 'UTC' literal to your reporting zone.
22
23WITH RECURSIVE chain AS (
24    SELECT
25        i.calendly_account,
26        i.invitee_uri                 AS original_invitee_uri,
27        i.invitee_uri                 AS current_invitee_uri,
28        i.new_invitee_uri             AS next_invitee_uri,
29        0                             AS reschedules
30    FROM {{staging.calendly.invitee}} i
31    WHERE i.is_new_booking
32
33    UNION ALL
34
35    SELECT
36        c.calendly_account,
37        c.original_invitee_uri,
38        n.invitee_uri,
39        n.new_invitee_uri,
40        c.reschedules + 1