Blending Facebook Ads + Google Ads data on Google Data Studio: guide and template

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

There are some questions that marketers ask often:

  • What is our Customer Acquisition Cost?
  • How is our paid advertising performing?
  • How much budget are we spending on ads and how many conversions we get?

Answer these questions is fundamental, but answering is tricky. Most marketing blog posts talk about their relevance but a few talk about how you can do it automatically.

One of the main challenges is that to answer these questions we need to bring multiple data sources together.

In this guide, I will show you how to blend your Google Ads and Facebook Ads data to know your overall digital ads performance.

You will understand how data blending and Google Data Studio work so you can replicate it with every data source. We will also solve some caveats of data preparation.

Connecting Facebook Ads to Google Data Studio

If you haven’t try our Facebook Ads connector for Google Data Studio.

We also have a full setup guide to report Facebook Ads on Data Studio.

Connecting Google Ads to Google Data Studio

Google Data Studio has a free connector for Google Ads. You only need to the Google Data Studio connector gallery and connect from there.

Also read: Installing Google Data Studio templates: Google Ads

Setting up the Google Data Studio report

The first step is just about bringing both Facebook Ads and Google Ads data sources into a blank report.

On your new Google Data Studio report, just go to Resources – Manage added data sources.

Now, add the Google Ads and Facebook Ads accounts you want to combine.

Close the data sources tab and let’s build our first visualizations. I recommend you to always use tables to validate how your data looks like and spot possible treats.

Create a table for each data source where the dimension is date and the metric is the budget spent; on Google Ads it is Cost and on Facebook Ads it is Amount spent.

We will add more metrics like conversions once we test this connection.

In this case, we have spend data on every single day for our Facebook Ads connector; we only have have spent on Google Ads for 6 days during the chosen period.

When joining these tables, we want to combine the total spend for every day for each data source.

As such, most dates will remain the same, as there’s only spend from the Facebook Ads side.

But there are six days where we need to sum spend from both data sources.

As an example, for July 1st the total ad spend would be $2193.94 (21.82+2172.12).

Blending data sources

Create a new table and click on Blend Data, below the chart data source.

Replicate the following setup:

I will explain data blending.

When we are joining two data sources, just like Facebook Ads and Google Ads, we need a value in common so we can make sure such a union makes sense.

These values or fields are Join keys. Often, such join keys are IDs and dates.

In this case, we will tell Google Data Studio that our join key is the date; if a value such as May 5th, 2020 is in both data sources, the metrics that have data in such a date in both data sources are subject to be combined.

Once we have a Join Key, we can add the metrics and dimensions that we want to combine. You can customize this union at any time.

In this case, we will only bring the respective spend metrics from both source.

Now, set up the table to display your data like this:

Choose date as dimension and bring the spend metrics from both data sources within the same table! (Beautiful, isn’t it?)

It is time to create a new metric that sums Amount spent and Cost to get the total ad spend.

Create a custom formula like shown below:

Once you refresh the table, you will see it’s working! (Partially).

As expected, our new metric is summing the Amount Spent and the Cost.

But there’s a caveat. If one of the columns (or data sources) doesn’t have data for a date, it will display a null value and it cannot be operated. While on July 8th, we spent $1666,52, the Total ad spend shows as null.

A null value is not zero, but no value at all, and it cannot be operated. How can we fix it?

Null values troubleshooting on Google Data Studio

The objective is to transform these null values to 0, so there’s an actual number we can operate.

You can use the following formula to do so:

Copy and paste this formula: NARY_MAX([FIELD],0)

The new table will look like this!

Once we tested this formula work, we will create a new custom field that combines Amount spent and the transformed Cost.

Copy and paste this formula: Amount Spent+ (NARY_MAX(Cost,0))

Our new Total ad spend is working perfectly!

Google Ads + Facebook Ads Google Data Studio report template

We built a free Google Data Studio template that combines Facebook Ads and Google Ads data. With this tutorial, you can create your custom formulas to combine the metrics that you set as conversions in both platforms.

More posts