Narrator Docs

Email Interactions

Creates:

  • Clicked Email
  • Opened Email
  • Unsubscribed Email

Type: Activity Transformation

select 
    t.id as activity_id 
    , t.timestamp as ts 
    , NULL and source_id 
    , NULL as source 
    , t.context_traits_email as customer 
    , CASE 
        WHEN t.event = 'email_link_clicked' then 'clicked_email' 
        WHEN t.event = 'email_opened' then 'opened_email' 
        WHEN t.event = 'unsubscribed' then 'unsubscribed_email' 
        END as activity 
    , NVL(c.email_subject, o.email_subject, u.email_subject) as feature_1 --subject
    , NVL(c.campaign_name, o.campaign_name, u.unsub_source) as feature_2 -- campaign or unsubscribe reason
    , NVL( c.id, o.id, u.id) as feature_3 -- email_uuid 
    , NULL as revenue_impact
    , c.url as link -- link_clicked

from iterable.tracks t 
left join iterable.email_link_clicked c 
    on t.id = c.id and t.event = 'email_link_clicked'
left join iterable.email_opened o
    on t.id = o.id and t.event = 'email_opened'
left join iterable.unsubscribed u 
    on t.id = u.id and t.event = 'unsubscribed'
where t.event in (
    'email_link_clicked'
    , 'email_opened'
    , 'unsubscribed' )
    and t.context_traits_email not like 'test%'



Tips:

šŸ‘

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 3 months ago


What's Next

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

Transformations by Activity

Iterable


Suggested Edits are limited on API Reference Pages

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