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%'