0

Facebook Ads / Google Adwords + Salesforce + Stripe + Segment - Returns on Ad Campaigns

Question

What’s the lifetime ROI and ROAS of my ad campaigns? What is the value that my ad campaigns are bringing in?

Sources

Facebook Ads

Google Adwords

Salesforce

Stripe

Query

with
page_hits as
(
      select  date_trunc('month', pages.received_at) as click_month,
              pages.anonymous_id,
              ads.campaign_id,
              i.spend
        from  segment.pages as pages
              -- join FB ads data to pageviews on the site by ad_id
        join  facebook_ads.ads as ads
          on  strpos(pages.context_page_search, 'tbfbadsid=' + ads.id)
              -- get more granular ad spend info by joining to data from FB Insights API
        join  facebook_ads.insights as i
          on  i.ad_id = ads.id
),

-- get Signup data
signups as
(
    select  date_trunc('month', s.received_at) signup_month,
            s.user_id,
            s.anonymous_id
      from  segment.signup as s
),

-- get Salesforce data
qualified_opportunities as
(
    select  date_trunc('month', o.qualified_date_c) as qual_opp_month,
            l.external_id_c,
            o.amount
      from  salesforce.leads as l
      join  salesforce.opportunities as o
        on  l.converted_opportunity_id = o.id
     where  o.qualified_date_c is not null
       and  renewal_opportunity_c not in ('Renewal', 'Churn', 'Downgrade')
),

-- get Stripe data
paid_users as
(
        select  date_trunc('month', s.created),
            s.plan_id,
            mw.owners,
            p.amount
            from  mongo.workspaces as mw
            join  billing.workspaces as bw
               on  mw.id = bw.id
            join  stripe.subscriptions as s
              on  bw.stripe_customer_id = s.customer_id
            join  stripe.plans as p
            on  s.plan_id = p.id
             and  p.amount > 0
)

    select  ph.click_month,
            ph.campaign_id,
            coalesce(sum(ph.spend)::int, 0) as ad_spend,
            coalesce(count(distinct ph.anonymous_id), 0) as clicks,
            coalesce(count(distinct si.user_id), 0) as signups,
            coalesce(count(distinct qo.external_id_c), 0) as qualified_opps,
            coalesce(sum(qo.amount), 0) as pipeline_dollars,
            coalesce(count(distinct pu.plan_id), 0) as paid_users,
            coalesce(sum(pu.amount), 0) as revenue_tied_to_ad,
            -- ROI: 100 * (Rev - Cost) / Cost
            coalesce((sum(pu.amount) - sum(ph.spend)) / sum(ph.spend) * 100, 0) as ROI,
            -- ROAS: Rev / Cost
            coalesce(sum(pu.amount) / sum(ph.spend), 0) as ROAS
      from  page_hits as ph
            -- join signups to pageviews from FB ads
 left join  signups as si
        on  ph.anonymous_id = si.anonymous_id
            -- join Salesforce data to signups originating from FB ads
 left join  qualified_opportunities as qo
        on  si.user_id = qo.external_id_c
            -- join Stripe data to signups originating from FB ads
 left join  paid_users as pu
        on  pu.owners = si.user_id
  group by  1, 2
  order by  1 desc