0

Facebook Ads / Google Adwords + Segment - First or Last Touch Attribution

Question

How do I start using some basic attribution to de-duplicate my conversions across platforms?

Sources

Facebook Ads

Google Adwords

Query

with
page_views as
(
    select  row_number()
              over (partition by anonymous_id
                        order by received_at desc) as visits,
            context_page_search,
            anonymous_id,
            received_at
      from  segment.pages
)

    select  distinct
            anonymous_id,
            last_value(last_ad_touch)
              over (partition by anonymous_id
                        order by visits
                        -- order by visits ASC -- uncomment this line for first touch & comment line above
                    rows between unbounded preceding and unbounded following) as last_last_ad_touch
      from
      (
        select  p.anonymous_id,
                visits,
                case when a.received_at is null and c.received_at is null then 'no ad'
                     when a.received_at > c.received_at or a.received_at is null then 'google'
                     when c.received_at > a.received_at or c.received_at is null then 'facebook' end as last_ad_touch
          from  page_views as p
                -- join FB ads info
     left join  facebook_ads.ads as a
            on  strpos(p.context_page_search, 'tbfbadsid=' + a.id)
                -- join Google Adwords info
     left join  adwords.campaigns as c
            on  strpos(p.context_page_search, 'tractionboard=' + c.id)
      order by  last_ad_touch, p.anonymous_id
      )
     where  last_ad_touch != 'no ad'