1

Salesforce + Segment – popular content leading to closed deals

Question

What blog content is viewed by successful closed deals?

Sources

Salesforce (see full visual schema here)

Analytics.js (docs here)

Results

 

Query

select p.url,
 count(distinct p.id) as page_views

from segment.pages p

-- connect users to groups
join etl3.groups g
on p.user_id = g.user_id

-- only blog pages
and p.url ilike '%segment.com/blog%'

-- segment groups to salesforce accounts
join salesforce.accounts as a
on a.external_id_c = g.group_id

-- accounts to oportunities
join salesforce.opportunities o
on a.id = o.account_id

-- only closed won and no upsells
and o.stage_name = 'Closed Won'
and o.upsell_c = 'false'

-- viewed page before deal closed
where o.close_date > p.received_at

group by p.url
order by 2 desc