Combining Data from Google Ads, Bing Ads, and Facebook Ads in Google Data Studio

Today I want to show you how you can combine performance metrics from three popular ad networks, Google Ads, Bing Ads, and Facebook Ads without exporting a CSV or writing a single line of code. Then I'll show you how you can connect Panoply to Google Data Studio to create an ad spend report that combines data from each of these ad networks. Now let's dive in.

This tutorial will show you how to:

• Collect data from Google Ads, Bing Ads, Facebook Ads
• Integrate the data from the three ad networks
• Build a dashboard in Google Data Studio that track how much each ad network is spending each day

Integrating Google Ads, Bing Ads, and Facebook Ads Data

Now I'm inside the Panoply app. This is what your new data warehouse will look like when you start your free trial. Now all we have to do is collect the ad network data into my data warehouse so we can start analyzing. I've preselected the data sources we are going to use.

Now I have to authenticate with each ad network to collect the data. Like any good cooking show, there will be some time lapses while I log in. Let's start with the Google Adwords API. Now that I've logged in, I can see all the reports I can collect from the Google Adwords API. If I select all, this means I'll collect all the data that's available to me. There are advanced options for setting up tables in your data warehouse, but I'm going to just move on without those at this time.

It's asking me to create another data source, and of course I am because we need to get Bing Ads and Facebook Ads going. I will collect Bing Ads. The Bing Ads API provides a little bit more data on your ad performance, and again I'm going to collect all of it because everybody knows more data is better. I will pick the last 30 days time.

Finally, I've authenticated with the Facebook Ads API and I am going to collect my Facebook Ads account for the last 30 days, and I'm going to have it broken down by country. Again, I'm not going to worry about the advanced settings because we make some pretty smart choices for you so that you don't have to worry about that, and I'm going to collect that.

Now that we've collected the data, I want to demonstrate how easy it is to query the data in Panoply. I've written a query in advance that unions the Bing Ads and Google Ads tables together and classifies keywords based on if they're a branded keyword or a non-branded keyword.

with ads as (
  select 
    'BING ADS' as adnetwork, 
    CASE WHEN b.keyword like '%panoply%' THEN 'BRAND' ELSE 'NON-BRAND' END branded, 
    date(b.gregoriandate) as dt, 
    sum(b.impressions) impressions, 
    sum(b.clicks) clicks 
  from bingads_search_query_performance b 
  group by 2, 3 
  union all 
  select 
    'GOOGLE ADS' as adnetwork, 
    CASE WHEN g.keyword like '%panoply%' THEN 'BRAND' ELSE 'NON-BRAND' END branded, 
    date(g.day) as dt, 
    sum(g.impressions) impressions, 
    sum(g.clicks) clicks 
  from googleads_search_query_performance_report g 
  group by 2, 3
)   
select 
  branded, 
  adnetwork, 
  round(
    1.0 * sum(clicks)/ sum(impressions), 
    3
  ) ctr 
from ads 
group by 1, 2

Let's run the report and see what it looks like. As we see there is it classifies in groups the keywords into branded or non-branded, and by the ad network and the associated click through rate. As you'll notice, the click through rate is much different from Bing Ads to Google Ads, but that's because they're very different ad networks and you're probably going to get a lot more impressions out of Google Ads in general. 

Now if I wanted to combine them both together I can remove the ad network from the equation and run the report again. Now I'll see that branded and non-branded performance looks a lot more similar to that Google click through rate because it's so heavily weighted in terms of impressions and clicks to that side. There you have it. It's just that easy to get Bing Ads and Google Ads performance together in one report.

Building an Ad Spend Report in Google Data Studio

The last thing we're going to do is create a report in Google Data Studio. I've written a query that models the data in a way that makes it easy to create the report that I showed in the beginning.

select 
  * 
from 
  (
    (
      select 
        'FACEBOOK' as network, 
        date(fb.date_start) as "date", 
        sum(fb.spend) spend 
      from facebookads fb 
      group by 2
    ) 
    UNION ALL 
      (
        select 
          'BING ADS' as network, 
          date(b.gregoriandate) as "date", 
          sum(b.spend) spend 
        from bingads_search_query_performance b 
        group by 2
      ) 
    UNION ALL 
      (
        select 
          'GOOGLE ADS' as network, 
          date(g.day) as "date", 
          sum(g.cost / 1000000) spend 
        from googleads_search_query_performance_report g 
        group by 2
      )
  )

There are three columns: an ad network column, a date column, and a spend column for the amount that we spent that day.

The next thing I'm going to do is connect my database to Google Data Studio. I'm going to grab these values and go over to Google Data Studio. Here I will add a data source by going to the resource menu and selecting add data source. Just add data source again, and selecting the PostgreSQL data selector. Now it's just a matter of filling in the details for my Panoply database.

And just like that I've connected Panoply to Google Data Studio. I can see all the tables that are now in my data warehouse, and I can use any of these, but what I'm going to do instead is use the custom query that I've written in Panoply. All I have to do now is connect, make sure all the fields look right, and they do, and I can then add it to my report.

Now you can see the data looks just like the data I modeled in Panoply and setup in the custom query on Google Data Studio. I have created a table that pivots the data into a column for each ad network and rows for each day. It makes it easy to create this graph in the same way.

Now it's up to you. You can start your free trial and add all your data, including your application database or any other ad networks you might be using. Have fun.