Weld logo
klaviyo logo
Klaviyo

Email Events

Retrieves various event details from the Klaviyo integration, including the event ID, 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 (if the event name is "Placed Order"). The results are ordered by the event timestamp in descending order.
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
Example of output from model:

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.

Ready to start modeling your own klaviyo data?

Get started building your data warehouse with klaviyo and connect all your apps and databases.

klaviyo logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.