Blending Google and Facebook Ads data in Data Studio with Supermetrics

Google Data Studio (GDS) is a super-efficient (and free!) tool to visualize your data from various sources. One of its key features is the ability to blend various data sources into one stream for simple reporting.

A classic example would be an advertiser that wants to blend his spend across multiple accounts into a single dashboard, easily control the overall ad spend used across multiple accounts. This can span ad accounts from Google, Bing, Facebook, Quora, and many more.

Being a Google tool, Google Ads is natively integrated into GDS. Other platforms can be integrated to GDS using third party connectors, with the most popular (and stable) being Supermetrics.

Full disclosure, the links to Supermetrics are with an affiliate tag, but I truly believe their solution is 10X better than the alternatives.

The Good, the Bad and the inaccurate

Supermetrics offers several ways to fetch data from ad accounts. For most popular platforms they offer a native integration that offers easy reporting with the platform, known as ‘Single Data Studio Connector‘.

These connectors are great when trying to report on a single account independently.

When wanting to merge data from multiple sources, these connectors often causes issues.

The key issue with these, is that when merging a various sources you need to select a Dimension for which you are merging, usually the Date. This makes sense since the data is blended on a single day level.

The issue with this condition is that if you’re blending two account, e.g. Google Ads and Quora Ads, if one of the accounts lacks data for a specific date, while the other has data, this can lead to discrepancies. GDS tries to match a row for each date, and in absence of one simply skips that row.

Blending multiple sources

The recommended alternative to these single connectors is to use the ‘Supermetrics for Google Sheets‘ connector. This connector, which comes at a higher price point (99$/month vs. 39$/month) does a better job at making such merges. It also comes with the option to add 32 data sources across unlimited reports, so really gives a good bang for the buck.

This method is slightly more complex than simply “spitting out” the data directly in GDS, but far more accurate

After signing up for the connector, create a new Google Sheet and install the Supermetrics add-on for Google Sheets (Add-ons -> Get add-ons. -> Search for Supermetrics).

Step 1: Create a sheet for each platform

For sake of example, let’s say you want to merge data from Google Ads and Facebook Ads.

First, you’ll need to create a sheet for each platform in Google Sheets, e.g. “FB Data” and “AW Data”.

Then, launch the Supermetrics add-on on each sheet and select the data to show.

A classic setup will select the metrics:
Impressions, Link Click (Clicks in Google Ads), Actions (Conversions in Google Ads), and Amount spent (Cost in Google Ads).

The Dimension for the report should be Date.

The end should look something similar to this:

Repeat this for additional platforms as required. I highly recommend keeping the column order consistent for sake of simple calculation in the next step.

Step 2: Create a Blended sheet

Now that we two (or more sheets) with the data extracted from the various platforms, we can merge them together.

First, create a new sheet, e.g. “Blended”.

Add a Date column (column A) and add rows with dates from the initial date you want to report on. You can fill in 3-4 fields and then just drag it down and it will autofill.

I recommend adding some 1000 rows, almost 3 years, so the sheet won’t break any time soon

Next, add whichever columns you want to sum on this sheet, just Cost or also Impressions etc.

On the first row of data on the table (probably row 2), paste in the following formula:

=SUMIF('FB Data'!$A:$A,$A:$A,'FB Data'!B:B)+SUMIF('AW Data'!$A:$A,$A:$A,'AW Data'!B:B)

Now let’s break down what this formula does.

SUMIF is a function that, well, sums a cell if it answers a certain criteria.

The criteria set is whether the Date column on the ‘FB Data’ sheet matches the Date on column A of the current sheet (‘Blended’), then sum column B on ‘FB Data’ sheet.

A similar function operates on the ‘AW Sheet’, and finally, both are summed together. If you’ve chosen other names for your sheets make sure you edit the names accordingly.

Pro tip: For additional platforms, add another SUMIF function at the end of the formula and adjust the sheet it references (e.g. ‘OB Data’)

Drag this cell downwards so that all cells are calculated using the same formula.

If you have multiple columns (e.g. Impressions, Clicks etc.) you can drag the formula to the adjacent cell. Assuming you kept the columns consistent, this should calculate the next columns just the same.

The end result should look something like this. Note that the cell’s content is a formula.

Step 3: Connect to GDS

Now that we the data ready, we can hop over to GDS.

Open up your dashboard and add a new data source (Resource -> Manage added data sources -> Add a data source)

Select Google Sheets, then the spreadsheet and specific worksheet with the blended data.

Pro tip: Select the right field type in advance (Currency, Date etc.) to save you time formatting the data in your dashboard

That’s it, you can now add any data point you like using data merged from multiple account.