Events Flow Campaign Report

Retrieves data from the Klaviyo integration to generate an Events Flow Campaign Report. It combines information from the events, campaign, and flow tables to provide details such as event timestamps, event names, custom properties, Klaviyo properties, metric IDs, campaign IDs, person IDs, flow IDs, attributed event IDs, message IDs, send cohorts, send timestamps, order IDs, flow names, flow statuses, campaign subjects, and campaign sent timestamps. The final result is sorted in descending order based on the event timestamps.

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

Example of output from model:

+----------+-----------------+-------------+-------------------+-------------------+-----------+-------------+----------+--------+-------------------+------------+------------+----------+
| 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.

Events Flow Campaign Report

Ready to start modeling your own klaviyo data?