Iterable
Email Interactions
Creates:
- Clicked Email
- Opened Email
- Unsubscribed Email
Type: Activity Transformation
select
t.id as activity_id
, t.timestamp as ts
, NULL and anonymous_customer_id
, 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:
- Use the
context_ip
from iterable to improve your identity resolution: Using Ip to improve Identitity Resolution
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 over 2 years ago
Whatās Next
Don't see what you're looking for? Browse the Activity Index for more examples...