Mailchimp

Opened Email, Clicked and Bounced

Customer opened or clicked into an email

Type: Activity Transformation

select 
    func_sha1(a.campaign_id || a.member_id || a.timestamp) as activity_id, 
    a.timestamp as ts, 

    null as source, 
    null as source_id,

    m.email_address as customer, 

    CASE a.action
        WHEN 'open' THEN 'opened_email'
        WHEN 'bounce' THEN 'bounced_email'
        WHEN 'click' THEN 'clicked_email'
    END activity, 

    c.title as feature_1, -- Campaign Name
    l.name as feature_2, -- list_name
    c.type as feature_3, -- campaign type

    NULL as revenue_impact, 
    c.archive_url as link

from mailchimp.campaign_recipient_activity a 
join mailchimp.member m 
    on (m.id = a.member_id and m.list_id = a.list_id)
join mailchimp.list l 
    on (l.id = a.list_id )
join mailchimp.campaign c 
    on (c.id = a.campaign_id)

Added to list

Because Mailchimp doesn't provide a timestamp, you can diff it with the activity stream to figure it out.

Type: Activity Transformation

SELECT
    MD5(l.name || m.email_address || max(sm._fivetran_synced)) AS activity_id,  -- create a unique id
    max(sm._fivetran_synced) AS ts,
    
    NULL AS source,
    NULL AS source_id,
    m.email_address AS customer,
    
    'added_to_mailchimp_list' AS activity,
    
    l.name AS feature_1, -- list name
    NULL AS feature_2,
    NULL AS feature_3,
    
    NULL AS revenue_impact,
    NULL AS link

FROM mailchimp.list l 
JOIN mailchimp.segment_member sm 
    on (sm.list_id = l.id and not sm._fivetran_deleted)
JOIN mailchimp.member m
    on (sm.member_id = m.id)
LEFT JOIN dw.activity_stream s 
    on (s.activity = 'added_to_mailchimp_list' and s.customer = lower(m.email_address) and s.feature_1 = l.name)
where sm._fivetran_synced >= '{min_date}'::TIMESTAMP -- filter before the join
    and s.ts is Null
group by l.name, m.email_address
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'

πŸ‘

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