{"id":4487,"date":"2020-07-27T10:04:04","date_gmt":"2020-07-27T07:04:04","guid":{"rendered":"https:\/\/trackingchef.com\/?p=4487"},"modified":"2020-12-14T16:11:29","modified_gmt":"2020-12-14T13:11:29","slug":"blending-google-and-facebook-ads-data-in-data-studio-with-supermetrics","status":"publish","type":"post","link":"https:\/\/trackingchef.com\/facebook\/blending-google-and-facebook-ads-data-in-data-studio-with-supermetrics\/","title":{"rendered":"Blending Google and Facebook Ads data in Data Studio with Supermetrics"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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<\/a>.<\/p>\n\n\n\n

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

The Good, the Bad and the inaccurate<\/h2>\n\n\n\n

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<\/a>‘.<\/p>\n\n\n\n

\"\"
Supermetrics for Data Studio connectors<\/figcaption><\/figure>\n\n\n\n

These connectors are great when trying to report on a single account independently.<\/p>\n\n\n\n

When wanting to merge data from multiple sources, these connectors often causes issues.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Blending multiple sources<\/h2>\n\n\n\n

The recommended alternative to these single connectors is to use the ‘Supermetrics for Google Sheets<\/a>‘ 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.<\/p>\n\n\n\n

\"\"
Supermetrics for Google Sheets connectors<\/figcaption><\/figure>\n\n\n\n

This method is slightly more complex than simply “spitting out” the data directly in GDS, but far more accurate<\/p><\/blockquote>\n\n\n\n

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).<\/p>\n\n\n\n

\"\"
Installing the Supermetrics Add-on for Google Sheets<\/figcaption><\/figure>\n\n\n\n

Step 1: Create a sheet for each platform<\/h3>\n\n\n\n

For sake of example, let’s say you want to merge data from Google Ads and Facebook Ads.<\/p>\n\n\n\n

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

\"\"
Launching the Supermetrics Add-on<\/figcaption><\/figure>\n\n\n\n

Then, launch the Supermetrics add-on on each sheet and select the data to show.<\/p>\n\n\n\n

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).<\/p>\n\n\n\n

\"\"
Selecting the metrics<\/figcaption><\/figure><\/div>\n\n\n\n

The Dimension for the report should be Date.<\/p>\n\n\n\n

\"\"
Selecting the Date dimension<\/figcaption><\/figure><\/div>\n\n\n\n

The end should look something similar to this:<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

Repeat this for additional platforms as required. I highly recommend keeping the column order consistent for sake of simple calculation in the next step.<\/p>\n\n\n\n

Step 2: Create a Blended sheet<\/h3>\n\n\n\n

Now that we two (or more sheets) with the data extracted from the various platforms, we can merge them together.<\/p>\n\n\n\n

First, create a new sheet, e.g. “Blended”.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

\"\"
Date column<\/figcaption><\/figure><\/div>\n\n\n\n

I recommend adding some 1000 rows, almost 3 years, so the sheet won’t break any time soon<\/p><\/blockquote>\n\n\n\n

Next, add whichever columns you want to sum on this sheet, just Cost or also Impressions etc.<\/p>\n\n\n\n

On the first row of data on the table (probably row 2), paste in the following formula:<\/p>\n\n\n\n

=SUMIF('FB Data'!$A:$A,$A:$A,'FB Data'!B:B)+SUMIF('AW Data'!$A:$A,$A:$A,'AW Data'!B:B)<\/code><\/pre>\n\n\n\n
Now let’s break down what this formula does.<\/p>\n\n\n\n
SUMIF is a function that, well, sums a cell if it answers a certain criteria.<\/p>\n\n\n\n
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. <\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Pro tip:<\/strong> For additional platforms, add another SUMIF function at the end of the formula and adjust the sheet it references (e.g. ‘OB Data’)<\/p><\/blockquote>\n\n\n\n
Drag this cell downwards so that all cells are calculated using the same formula.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The end result should look something like this. Note that the cell’s content is a formula.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Step 3: Connect to GDS<\/h3>\n\n\n\n
Now that we the data ready, we can hop over to GDS.<\/p>\n\n\n\n
Open up your dashboard and add a new data source (Resource -> Manage added data sources -> Add a data source)<\/p>\n\n\n\n
Select Google Sheets, then the spreadsheet and specific worksheet with the blended data.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Pro tip:<\/strong> Select the right field type in advance (Currency, Date etc.) to save you time formatting the data in your dashboard<\/p><\/blockquote>\n\n\n\n
That’s it, you can now add any data point you like using data merged from multiple account.<\/p>\n\n\n\n
\"\"
Reporting per platform and blended<\/figcaption><\/figure>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4,46],"tags":[],"_links":{"self":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/4487"}],"collection":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/comments?post=4487"}],"version-history":[{"count":7,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/4487\/revisions"}],"predecessor-version":[{"id":4511,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/4487\/revisions\/4511"}],"wp:attachment":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/media?parent=4487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/categories?post=4487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/tags?post=4487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}