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


Whatā€™s Next

Don't see what you're looking for? Browse the Activity Index for more examples...