Reporting & Analysis
08.07.2021
TLDR
To efficiently compare data from Facebook Ads and Google Ads, start by importing the relevant data from both platforms into a Google Sheet, arranging it by day. Then, create a new tab to combine the data sources. Use the `vlookup` function to populate this tab with the desired metrics, such as impressions and costs, from the Facebook Ads and Google Ads tabs. This process can be time-consuming if done regularly. To simplify it, use Airboxr and prepare reports on your Facebook and Google Ads performance in seconds.
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.
Improve your DTC game. Sign up for weekly tips.
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 Facebook, Google and other ad channels, all in one click into your spreadsheet. We have different hops to choose from from our hop marketplace such as:
Ad Channel Conversion Performance over Time
It's really that simple. Use any of these hops and watch data from Facebook ads, Google ads, Tiktok and Klaviyo populate your screen in just a matter of seconds.
Christabel Wong
About the Author