Events Flow Campaign Report
1with
2 events as (
3 select
4 event_id
5 , cast(event_timestamp as date) event_timestamp
6 , event_name
7 , custom_properties
8 , klaviyo_properties
9 , metric_id
10 , campaign_id
11 , person_id
12 , flow_id
13 , flow
14 , attributed_event_id
15 , message_id
16 , send_cohort
17 , send_ts
18 , order_id
19 from
20 select distinct
21 event_name
22 from
23 {{staging.klaviyo.email_events}}
24 )
25 , campaign as (
26 select
27 id as campaign_id
28 , created
29 , name
30 , status
31 , subject
32 , sent_at as campaign_sent_at
33 from
34 {{raw.klaviyo.campaign}}
35 )
36 , flow as (
37 select
38 id flow_id
39 , name flow_name
40 , status status_flow_status
41 , trigger_type
42 , trigger_filter
43 , trigger_list_id
44 , trigger_list_name
45 , customer_filter
46 , created flow_created_at
47 , updated
48 from
49 {{raw.klaviyo.flow}}
50 )
51select
52 events.*
53 , flow.flow_name
54 , flow.flow_status
55 , campaign.subject
56 , campaign.campaign_sent_at
57from
58 events
59 left join flow on events.flow_id = flow.flow_id
60 left join campaign on events.campaign_id = campaign.campaign_id
61order by
62 event_timestamp desc+----------+-----------------+-------------+-------------------+-------------------+-----------+-------------+----------+--------+-------------------+------------+------------+----------+
| event_id | event_timestamp | event_name | custom_properties | klaviyo_properties | metric_id | campaign_id | person_id | flow_id | flow | attributed_event_id | message_id | order_id |
+----------+-----------------+-------------+-------------------+-------------------+-----------+-------------+----------+--------+-------------------+------------+------------+----------+
| 1 | 2022-01-01 | Event 1 | {} | {} | 123 | 456 | 789 | 101 | Flow 1 | 1001 | 2001 | 3001 |
| 2 | 2022-01-02 | Event 2 | {} | {} | 234 | 567 | 890 | 202 | Flow 2 | 1002 | 2002 | 3002 |
| 3 | 2022-01-03 | Event 3 | {} | {} | 345 | 678 | 901 | 303 | Flow 3 | 1003 | 2003 | 3003 |
+----------+-----------------+-------------+-------------------+-------------------+-----------+-------------+----------+--------+-------------------+------------+------------+----------+The Events Flow Campaign Report SQL template is designed to retrieve information from the Klaviyo integration database. This SQL model combines data from three different tables: events, campaign, and flow. The events table contains details about individual events, such as event ID, timestamp, event name, custom properties, Klaviyo properties, metric ID, campaign ID, person ID, flow ID, and other relevant information. The campaign table provides information about campaigns, including campaign ID, creation date, name, status, subject, and the time the campaign was sent. The flow table contains details about flows, such as flow ID, flow name, flow status, trigger type, trigger filter, trigger list ID, trigger list name, customer filter, creation date, and update date. By joining these tables together, the SQL query retrieves a comprehensive dataset that includes all the event details, along with additional information from the flow and campaign tables. The output includes the event details, flow name, flow status, campaign subject, and campaign sent timestamp. The results are ordered by the event timestamp in descending order. This SQL template can be useful for generating reports that provide insights into the flow of events within campaigns in Klaviyo. It allows users to analyze the relationship between events, flows, and campaigns, and gain a better understanding of the effectiveness and performance of their email marketing campaigns.
