Scheduled events

1-- core.calendly.scheduled_events
2-- Every scheduled meeting, classified, with its hosts, invitee outcomes and booking
3-- lead time on one row.
4--
5-- Grain: one row per calendly_account x scheduled event.
6-- Depends on: staging.calendly.scheduled_event, .scheduled_event_host, .invitee
7--
8-- This is the meeting-grain model the other reports read. Classify once here
9-- rather than repeating the cancel / reschedule / no-show logic downstream.
10--
11-- OUTCOME is one of:
12--   rescheduled   canceled because it was moved. Every canceled invitee on it has
13--                 rescheduled = TRUE. Calendly's own analytics does not count
14--                 these as cancellations, and neither does this model.
15--   canceled      canceled for any other reason.
16--   no_show       active, in the past, and every active invitee was marked a
17--                 no-show.
18--   completed     active and already over.
19--   upcoming      active and not over yet.
20-- A group meeting where only some invitees did not show is 'completed', with the
21-- count in no_show_invitees.
22--
23-- No-shows are only as complete as your hosts' habit of marking them in Calendly.
24-- 'completed' means "not canceled and not marked", not "attended".
25
26WITH invitee_rollup AS (
27    -- Aggregated to event grain BEFORE joining, so the host join below cannot fan
28    -- invitee counts out.
29    SELECT
30        calendly_account,
31        event_uri,
32        COUNT(*)                                              AS invitees,
33        COUNTIF(status = 'active')                            AS active_invitees,
34        COUNTIF(status = 'canceled' AND is_rescheduled)       AS rescheduled_invitees,
35        COUNTIF(status = 'canceled' AND NOT is_rescheduled)   AS canceled_invitees,
36        COUNTIF(is_no_show)                                   AS no_show_invitees,
37        COUNTIF(is_new_booking)                               AS new_bookings,
38        MIN(booked_at)                                        AS first_booked_at
39    FROM {{staging.calendly.invitee}}
40    GROUP BY 1, 2