1

Facebook Ads / Google Adwords + Google Adwords + Segment - Cross Platform Ad Level Comparisons

Question

How can I compare the success of my ads at the individual ad level?

Sources

Facebook Ads

Google Adwords 

Query

with
fb_perf as (
  select  a.id as ad_id,
          a.name as ad_name,
          ad.name as adset_name,
          c.name as campaign_name,
          sum(i.spend)::int as spend,
          sum(i.impressions) as impresssions,
          sum(i.clicks) as clicks,
          'facebook' as source
    from  facebook_ads.ads a
    join  facebook_ads.insights i
      on  a.id = i.ad_id
    join  facebook_ads.campaigns c
      on  a.campaign_id = c.id
    join  facebook_ads.ad_sets ad
      on  a.adset_id = ad.id
group by  1,2,3,4
),
google_perf as (
  select  a.id as ad_id,
          'google_ad_default' as ad_name,
          g.name as adset_name,
          c.name as campaign_name,
          sum(apr.average_cost)::int as spend,
          sum(apr.impressions) as impresssions,
          sum(apr.clicks) as clicks,
          'google' as source
    from  adwords.ads a
    join  adwords.ad_performance_reports apr
      on  a.id = apr.ad_id
    join  adwords.ad_groups g
      on  a.ad_group_id = g.id
    join  adwords.campaigns c
      on  g.campaign_id = c.id
group by  1,2,3,4
)
  select * from google_perf
union
  select * from fb_perf