0

Facebook Ads / Google Adwords + Stripe + Segment - Cross Platform Ad Comparison

Question

Where are my ads overlapping? Do I need to start thinking about an attribution model?

Sources

Facebook Ads

Google Adwords

Stripe

Query

with
signups as
(
      select  date_trunc('month', s.received_at) signup_month, s.user_id, s.anonymous_id
        from  segment.signup s
),

-- 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
),

-- get Facebook Campaign data
fb_campaigns as
(
    select  distinct
            pages.anonymous_id,
            s.user_id as signup,
            case when pu.plan_id is null then 0
                 else 1 end as is_paid,  -- binary state: paid acct or not
            'facebook' as source
      from  segment.pages as pages
      join  facebook_ads.ads as ads
        on  strpos(pages.context_page_search, 'tbfbadsid=' + ads.id)
 left join  signups as s
        on  s.anonymous_id = pages.anonymous_id
 left join  paid_users as pu
        on  pu.owners = s.user_id
),

-- get Google Adwords Campaign data
adwords_campaigns as
(
    select  distinct
            pages.anonymous_id,
            s.user_id as signup,
            case when pu.plan_id is null then 0
                 else 1 end as is_paid,  -- binary state: paid acct or not
            'google' as source
      from  segment.pages as pages
      join  adwords.campaigns as c
        on  strpos(pages.context_page_search, 'tractionboard=' + c.id)
 left join  signups as s
        on  s.anonymous_id = pages.anonymous_id
 left join  paid_users as pu
        on  pu.owners = s.user_id
)

    select  ads,
            count(distinct anonymous_id) as visitors,
            sum(signup) as signups,
            sum(paid_users) as paid_users
      from
      (
        select  anonymous_id,
                -- count number of valid signup ids
                count(signup) as signup,
                -- sum number of paid accounts, converted to 0,1 above
                sum(is_paid) as paid_users,
                -- combine instances where visitor has seen both fb and google ad
                listagg(source, ',') within group (order by source) as ads
          from
          (
          -- joining ad campaign information
            select  distinct
                    signup,
                    anonymous_id,
                    source,
                    is_paid
              from  fb_campaigns
             union
            select  distinct
                    signup,
                    anonymous_id,
                    source,
                    is_paid
              from  adwords_campaigns
          )
      group by  1
      )
  group by  1