Airboxr - No-code querying and data-enrichment interface for GSheets. | Product Hunt

How to combine data from Facebook Ads and Google Ads

by Christabel Wong in July 8th, 2021

The hard way and the easy way

If you're reading this, you're probably using both Facebook ads and Google ads. You target different audiences on their respective platforms. Facebook ads target users who show interest in products similar to yours. On the other hand, Google ads focus on users who search for your product. The former expands the brand, and the latter converts the leads.

But it isn't wise to look at each source in isolation—you have to get a combined look at both sources to see where your pipeline is strong and where it needs improvement. This post will walk you through the process of comparing data without spending hours cleaning the data.

This is the outcome we want to achieve—to arrange the number of impressions and cost from both Facebook ads and Google ads by day. 

Importing data from Facebook Ads

First, we'll go to Facebook ads manager to get the data we need. Choose the category you want to pull your data from—campaign, ad set or ad—and set the time period you want. For simplicity's sake, we're looking at the past 7 days of our ad set.

We want the data to be arranged by a common metric—date, so let's change the breakdown to day before exporting the table data as a CSV file.

Import the CSV file onto a new Google Sheet and rename the tab accordingly to avoid confusion.

Importing data from Google Ads

Next, we're moving on to Google ads manager and go to campaigns. Similar to before, let's choose the category you want to pull your data from—campaign, ad group or ad—and select the time period. To keep things constant, we're looking at our ad group data for the past 7 days.

To sort the table data by day, we're first going to open it in the report editor.

Remove all the existing parameters and replace them with day.

Export this table data as a CSV file.

Import this into your Google Sheet like we did previously and give this tab a fitting name.

Cleaning and combining data

Notice how the dates in both tabs are in the opposite order. But that's okay, since the dates are already in the same format, we can use the vlookup function to easily solve that.

Create a new tab to combine the data sources. We've named it F&G. Copy the days over from either tab. It's up to you if you want them to be arranged in ascending or descending order. This time, we're sticking with the former.

Now to get the number of Facebook ad impressions on the F&G tab, type this into column B2:

=vlookup(A2, 'Facebook Ads'!A:N, 14, false)

This formula into C2 to find the Facebook ad cost:

=vlookup(A2, 'Facebook Ads'!A:P, 16, false)

This into D2 to find the number of Google ad impressions:

=vlookup(A2, 'Google Ads'!A:E, 5, false)

And this line into E2 to get the Google ad cost:

=vlookup(A2, 'Google Ads'!A:G, 7, false)

Drag the formulas down and you're done.

Now that you've tried it once, imagine repeating the same exporting and importing every month, every week or worse, every day! There has to be a simpler way, right?

Never do the same analysis again with Airboxr.

We know how complicated it is to combine data from multiple sources, we've been there. That's why we created a template to track your ad metrics from both Facebook and Google all in one click. Not only that, this template automatically updates with Airboxr. That means no more repeated work!

All you need to do is make a copy of the template, launch Airboxr, and click Refresh Template! ⚡️

It's really that simple. Watch data from Facebook ads and Google ads populate your screen in a single click. Download the add-on and make a copy of the template now.

Get the template here for free →

Hacks vlookup Google Sheets

Your cart
    Checkout