select 
    p.id as activity_id 
    
    , p.created_at as ts

    , NULL as anonymous_customer_id 
    
    , p.email as customer 
    
    , 'lead_created' as activity 
    
    , 'pardot' as feature_1 -- system
    , c.name as feature_2 -- lead source
    , NULL as feature_3
    
    , NULL as revenue_impact 
    , p.crm_url as link 
    
from pardot.prospect p 
LEFT JOIN pardot.campaign c
    ON p.campaign_id = c.id
WHERE NOT(p._fivetran_deleted) 
	and p.email is not null

select
    v.id as activity_id 
    , v.created_at  as ts
    
    , p.email as customer 
  
    , NULL as anonymous_customer_id 
    
    , CASE WHEN v.type = 6 THEN "received_email" -- email sent 
        WHEN v.type = 13 THEN "bounced_email" 
        WHEN v.type = 11 THEN "opened_email" 
        WHEN v.type = 12 THEN "unsubscribed_email" -- opted out 
        END as activity 
        
    , e.name as feature_1 -- internal email name 
    , v.details as feature_2 -- subject 
    , NULL as feature_3
    
    , NULL as revenue_impact
    , NULL as link 

from pardot.visitor_activity v
left join pardot.email e 
    on e.id = v.email_id 
left join pardot.prospect p 
    on p.id = v.prospect_id
where v.type_name like 'Email' 
    and v.type in (6,13,11,12)
    and NOT(p._fivetran_deleted)

select
    v.id as activity_id 
    , v.created_at  as ts
    
    , p.email as customer 
 
    , NULL as anonymous_customer_id 
    
    , "clicked_email" as activity

    , e.name as feature_1 -- internal email name 
    , e.subject as feature_2
    , email_id as feature_3 -- email  
    
    , NULL as revenue_impact
    , details as link  

from pardot.visitor_activity v
left join pardot.email e 
    on e.id = v.email_id 
inner join pardot.prospect p 
    on p.id = v.prospect_id
where v.type_name like 'Email Tracker' 
    and NOT(p._fivetran_deleted)