Salesforce + Segment – popular content leading to closed deals


What blog content is viewed by successful closed deals?


Salesforce (see full visual schema here)

Analytics.js (docs here)




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
Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular