Klaviyo
Opened Email
Customer opened an email
Type: Activity Transformation
SELECT
e.id AS "activity_id"
, e.timestamp AS "ts"
, NULL AS "anonymous_customer_id"
, p.email AS "customer"
, 'opened_email' AS "activity"
, e.property_subject AS "feature_1"
, e.property_client_os AS "feature_2"
, e.property_client_type AS "feature_3"
, NULL AS "revenue_impact"
, 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(NVL(p.first_name,'')) || '-' || lower(NVL(p.last_name,'')) AS "link"
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Opened Email'
SELECT
e.id AS activity_id
, e.timestamp AS ts
, NULL AS anonymous_customer_id
, p.email AS customer
, 'opened_email' AS activity
, e.property_subject AS feature_1
, e.property_client_os AS feature_2
, e.property_client_type AS feature_3
, NULL AS revenue_impact
, 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(coalesce(p.first_name,'')) || '-' || lower(coalesce(p.last_name,'')) AS link
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Opened Email'
Clicked Email
Customer clicked an email
Type: Activity Transformation
SELECT
e.id AS activity_id
, e.timestamp AS ts
, NULL AS anonymous_customer_id
, p.email AS customer
, 'clicked_email' AS activity
, e.property_subject AS feature_1
, e.property_client_os AS feature_2
, e.property_client_type AS feature_3
, NULL AS revenue_impact
, e.property_url AS link
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Clicked Email'
SELECT
e.id AS activity_id
, e.timestamp AS ts
, NULL AS anonymous_customer_id
, p.email AS customer
, 'clicked_email' AS activity
, e.property_subject AS feature_1
, e.property_client_os AS feature_2
, e.property_client_type AS feature_3
, NULL AS revenue_impact
, e.property_url AS link
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Clicked Email'
Unsubscribed Email
Customer opened an email
Type: Activity Transformation
SELECT
e.id AS activity_id
, e.timestamp AS ts
, NULL AS anonymous_customer_id
, p.email AS customer
, 'unsubscribed_email' AS activity
, e.property_subject AS feature_1
, NULL AS feature_2
, NULL AS feature_3
, NULL AS revenue_impact
, NULL AS link
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Unsubscribed'
SELECT
e.id AS activity_id
, e.timestamp AS ts
, NULL AS anonymous_customer_id
, p.email AS customer
, 'unsubscribed_email' AS activity
, e.property_subject AS feature_1
, NULL AS feature_2
, NULL AS feature_3
, NULL AS revenue_impact
, NULL AS link
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Unsubscribed'
Received Email
Customer received an email to their inbox
Type: Activity Transformation
SELECT
e.id AS "activity_id"
, e.timestamp AS "ts"
, NULL AS "anonymous_customer_id"
, p.email AS "customer"
, 'received_email' AS "activity"
, e.property_campaign_name AS "feature_1"
, e.property_subject AS "feature_2"
, e.property_tags AS "feature_3"
, NULL AS "revenue_impact"
, 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(NVL(p.first_name,'')) || '-' || lower(NVL(p.last_name,'')) AS "link"
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Received Email'
SELECT
e.id AS activity_id
, e.timestamp AS ts
, NULL AS anonymous_customer_id
, p.email AS customer
, 'received_email' AS activity
, e.property_campaign_name AS feature_1
, e.property_subject AS feature_2
, e.property_tags AS feature_3
, NULL AS revenue_impact
, 'https://www.klaviyo.com/profile/'||p.id || '/' || lower(coalesce(p.first_name,'')) || '-' || lower(coalesce(p.last_name,'')) AS link
FROM klaviyo.event AS e
JOIN klaviyo.person p
on (p.id = e.person_id)
WHERE e.type = 'Received Email'
Contribute to the Narrator Community
Help the Narrator community by suggesting edits or contributing SQL transformations. Use the "Suggest Edits" button at the top right or visit our Contributors page for more info on how to get involved.
Have questions?
Our data team is here to help! Here are a couple ways to get in touch...
š¬ Chat us from within Narrator
š Email us at [email protected]
š Or schedule a 15 minute meeting with our data team
Updated almost 3 years ago
Don't see what you're looking for? Browse the Activity Index for more examples...