Email Events
1select
2 id as event_id
3 , timestamp as event_timestamp
4 , event_name
5 , custom_properties
6 , klaviyo_properties
7 , uuid
8 , person_id
9 , metric_id
10 , campaign_id
11 , flow_id
12 , json_extract_scalar(klaviyo_properties, '$.flow') as flow
13 , json_extract_scalar(
14 replace(klaviyo_properties, '$', '')
15 , "$['attribution'].attributed_event_id"
16 ) as attributed_event_id
17 , json_extract_scalar(replace(klaviyo_properties, '$', ''), '$.message') as message_id
18 , json_extract_scalar(
19 replace(klaviyo_properties, '$', '')
20 , '$._cohortmessage_send_cohort'
21 ) as send_cohort
22 , json_extract_scalar(
23 replace(klaviyo_properties, '$', '')
24 , "$['attribution'].send_ts"
25 ) as send_ts
26 , case
27 when event_name like 'Placed Order' then json_extract_scalar(klaviyo_properties, '$.event_id')
28 end as order_id
29from
30 {{raw.klaviyo_import.event}}
31order by
32 event_timestamp desc
event_id | event_timestamp | event_name | custom_properties | klaviyo_properties | uuid | person_id | metric_id | campaign_id | flow_id | flow | attributed_event_id | message_id | send_cohort | send_ts | order_id
---------+-------------------------+--------------+-------------------+--------------------+----------+-----------+-----------+-------------+---------+--------+---------------------+------------+-------------+-------------+---------
123 | 2022-01-01 10:00:00 | Placed Order | { "color": "red" }| { "flow": "A", "attribution": { "attributed_event_id": "456", "send_ts": "2022-01-01 09:30:00" }, "message": "Welcome!" } | abc123 | 789 | 111 | 222 | 333 | A | 456 | Welcome! | B | 2022-01-01 09:30:00 | 789
456 | 2022-01-01 09:30:00 | Sent Email | { "size": "M" } | { "flow": "A", "attribution": { "attributed_event_id": "789", "send_ts": "2022-01-01 09:00:00" }, "message": "Promotion!" } | def456 | 789 | 111 | 222 | 333 | A | 789 | Promotion! | B | 2022-01-01 09:00:00 | NULL
789 | 2022-01-01 09:00:00 | Sent Email | { "size": "S" } | { "flow": "B", "attribution": { "attributed_event_id": "123", "send_ts": "2022-01-01 08:30:00" }, "message": "Newsletter!" } | ghi789 | 789 | 111 | 222 | 333 | B | 123 | Newsletter!| A | 2022-01-01 08:30:00 | NULL
The SQL template named "email_events" is designed to work with the Klaviyo integration. This SQL model retrieves various event-related information from the Klaviyo platform. The selected fields include event ID, event timestamp, event name, custom properties, Klaviyo properties, UUID, person ID, metric ID, campaign ID, flow ID, flow, attributed event ID, message ID, send cohort, send timestamp, and order ID. By executing this SQL template, you can gain insights into the events that occur within the Klaviyo platform. It allows you to analyze and understand the details of email events, such as the specific event types, associated properties, and relevant identifiers. The SQL code also includes sorting the results in descending order based on the event timestamp, providing a chronological view of the events.
