Lead Created

A lead was created for a potential customer


Note: the naming convention is changed because sometimes leads are created without the customer doing anything to trigger the activity (ie. email list uploads). Because the action is not necessarily created by a customer action, we change the naming convention to be "Lead Created" instead of "Created Lead".

The timestamp for this activity is adjusted by 2 seconds earlier to ensure that this activity doesn't occur at the same time an "opportunity" is created in another system like Salesforce. This will ensure that the lead always happens in sequence before the opportunity.

Type: Activity Transformation

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



Email Interactions

Creates:

  • Received Email
  • Bounced Email
  • Opened Email
  • Unsubscribed Email

Type: Activity Transformation

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)



Clicked Email

Customer clicked a link in an email


Type: Activity Transformation

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)



šŸ‘

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


Whatā€™s Next

Don't see what you're looking for? Browse the Activity Index for more examples...