Narrator Docs

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 source,
    NULL as source_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 source,
    NULL as source_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 source,
    NULL as source_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 source,
    NULL as source_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,

    'phone_number' as source,
    l.phone as source_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,

    'phone_number' as source,
    l.phone as source_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-min meeting with our data team

Updated 3 months ago


What's Next

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

Activity Index

Salesforce


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.