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)

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'