Salesforce
Submitted Lead
A lead was created in salesforce.
Type: Activity Transformation
SELECT
l.id as activity_id,
l.created_date as ts,
NULL as anonymous_customer_id,
l.email as customer,
'sumitted_lead' as activity,
l.source_c as feature_1, -- lead source
l.city as feature_2,
NULL as feature_3,
NULL as revenue_impact,
'https://emea.salesforce.com/' || l.id as link
FROM salesforce.lead l
where not l.is_deleted
Note: Do not try to get a unique lead in the transformation cause it never works, just use First in dataset
Received Contract
A contract was sent via Salesforce. We will use opportunity history to capture the multiple times a contract gets sent.
Type: Activity Transformation
SELECT
h.id as activity_id,
h.created_date as ts,
NULL as anonymous_customer_id,
a.person_email as customer,
'received_contract' as activity,
NULL as feature_1,
NULL as feature_2,
NULL as feature_3,
o.amount as revenue_impact,
'https://emea.salesforce.com/' || o.id as link
from salesforce_fivetran.opportunity_field_history h
left join salesforce_fivetran.opportunity o
on (h.opportunity_id = o.id)
left join salesforce_fivetran.account a
on (a.id = o.account_id)
WHERE h.new_value = 'Contract Sent'
Opportunity Created
An opportunity was created for that customer in Salesforce
Type: Activity Transformation
SELECT
o.id as activity_id,
o.created_date as ts,
NULL as anonymous_customer_id,
a.person_email as customer,
'became_opportunity' as activity,
NULL as feature_1,
NULL as feature_2,
NULL as feature_3,
o.amount as revenue_impact,
'https://emea.salesforce.com/' || o.id as link
from salesforce_fivetran.opportunity o
JOIN salesforce_fivetran.account a
on (a.id = o.account_id)
where not a.is_deleted
Closed Opportunity
An Opporutnity was closed won or closed lost via Salesforce. We will use opportunity history to capture the multiple times an opportunity changes its states.
Type: Activity Transformation
SELECT
h.id as activity_id,
h.created_date as ts,
NULL as anonymous_customer_id,
a.person_email as customer,
CASE
WHEN h.new_value = 'Closed Won' THEN 'won_opportunity'
WHEN h.new_value = 'Closed Lost' THEN 'lost_opportunity'
END as activity,
NULL as feature_1,
NULL as feature_2,
NULL as feature_3,
o.amount as revenue_impact,
'https://emea.salesforce.com/' || o.id as link
from salesforce_fivetran.opportunity_field_history h
left join salesforce_fivetran.opportunity o
on (h.opportunity_id = o.id)
left join salesforce_fivetran.account a
on (a.id = o.account_id)
WHERE h.new_value in ('Closed Won', 'Closed Lost')
Started Call
A call was placed. I am using Tasks in Salesforce because it is most common to add your call system to saleforce
Type: Activity Transformation
SELECT
t.id as activity_id,
t.created_date as ts,
l.phone as anonymous_customer_id,
l.email as customer,
'started_call' as activity,
t.call_disposition as feature_1,
t.call_type as feature_2,
t.subject as feature_3,
NULL as revenue_impact,
NULL as link
from salesforce_fivetran.task t
join salesforce_fivetran.lead l
on (l.id = t.who_id)
where t.type = 'Call'
Completed Call
A call was completed.
Type: Activity Transformation
SELECT
t.id as activity_id,
date_add('seconds', t.call_duration_in_seconds, t.created_date) as ts,
l.phone as anonymous_customer_id,
l.email as customer,
'completed_call' as activity,
t.call_disposition as feature_1,
t.call_type as feature_2,
t.subject as feature_3,
NULL as revenue_impact,
NULL as link
from salesforce_fivetran.task t
join salesforce_fivetran.lead l
on (l.id = t.who_id)
where t.type = 'Call' and NULLIF(t.call_duration_in_seconds, 0) > 0
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 almost 3 years ago
Don't see what you're looking for? Browse the Activity Index for more examples...