Narrator Documentation

Opened Email

Customer opened an email

Type: Activity Transformation

SELECT
     e.id AS "activity_id"
     , e.timestamp AS "ts"
     
     , NULL  AS "source"
     , NULL  AS "source_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 source
     , NULL  AS source_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 source
     , NULL  AS source_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 source
     , NULL  AS source_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 source
     , NULL  AS source_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 source
     , NULL  AS source_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 "source"
     , NULL  AS "source_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 source
     , NULL  AS source_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-min meeting with our data team

Updated 2 minutes ago


What's Next

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

Transformations by Activity

Klaviyo


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.