Email Events

1-- staging.klaviyo.email_events
2-- Email Events. Retrieves various event details from the Klaviyo integration,
3-- including the event ID, timestamp, event name, custom properties, Klaviyo
4-- properties, UUID, person ID, metric ID, campaign ID, flow ID, flow, attributed
5-- event ID, message ID, send cohort and order ID (if the event name is "Placed
6-- Order"). The results are ordered by the event timestamp in descending order.
7--
8-- The event table holds the metric id, not the event name; the name comes from
9-- the metric table. person_id is the event's Klaviyo profile id. campaign_id and
10-- flow_id come from Klaviyo's attribution data for the event and are null for
11-- events with no attribution.
12--
13-- Klaviyo prefixes its own property keys with $ ($flow, $message, $event_id).
14-- Each lookup strips the $ from the JSON first so plain paths can reach them.
15--
16-- From the weld.app template library, corrected and ported to the current synced
17-- schema. Not reconciled against Klaviyo; check the output against the source
18-- before relying on it.
19
20select
21    e.id as event_id
22  , e.timestamp as event_timestamp
23  , m.name as event_name
24  , e.custom_properties
25  , e.klaviyo_properties
26  , e.uuid
27  , e.profile_id as person_id
28  , e.metric_id
29  , e.campaign_id
30  , e.flow_id
31  , json_extract_scalar(replace(e.klaviyo_properties, '$', ''), '$.flow') as flow
32  , json_extract_scalar(
33        replace(e.klaviyo_properties, '$', '')
34      , "$['attribution'].attributed_event_id"
35    ) as attributed_event_id
36  , json_extract_scalar(replace(e.klaviyo_properties, '$', ''), '$.message') as message_id
37  , json_extract_scalar(
38        replace(e.klaviyo_properties, '$', '')
39      , '$._cohortmessage_send_cohort'
40    ) as send_cohort