Events Flow Campaign Report

1-- core.klaviyo.events_flow_campaign_report
2-- Events Flow Campaign Report. Retrieves data from the Klaviyo integration to
3-- generate an Events Flow Campaign Report. It combines information from the events,
4-- campaign, and flow tables to provide details such as event timestamps, event
5-- names, custom properties, Klaviyo properties, metric IDs, campaign IDs, person
6-- IDs, flow IDs, attributed event IDs, message IDs, send cohorts, order IDs, flow
7-- names, flow statuses, campaign names, and campaign send times. The final result
8-- is sorted in descending order based on the event timestamps.
9--
10-- From the weld.app template library, corrected and ported to the current synced
11-- schema. Not reconciled against Klaviyo; check the output against the source
12-- before relying on it.
13
14with
15    events as (
16        select
17            event_id
18          , cast(event_timestamp as date) event_timestamp
19          , event_name
20          , custom_properties
21          , klaviyo_properties
22          , metric_id
23          , campaign_id
24          , person_id
25          , flow_id
26          , flow
27          , attributed_event_id
28          , message_id
29          , send_cohort
30          , order_id
31        from
32            {{staging.klaviyo.email_events}}
33    )
34  , campaign as (
35        select
36            id as campaign_id
37          , name as campaign_name
38          , send_time as campaign_sent_at
39        from
40            {{raw.klaviyo.campaign}}