Pardot
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
Updated over 2 years ago
Don't see what you're looking for? Browse the Activity Index for more examples...