Reporting & Analytics
Jul 2, 2021
TLDR
To merge data from Google Ads and Google Analytics, start by importing the relevant data from both platforms into a Google Sheet, ensuring you arrange it by day. Next, create a new tab to combine the data, adjusting date formats as needed. Use pivot tables to consolidate data with the same dates. Then, apply the `vlookup` function to combine data from Google Ads, Google Analytics, and the pivot table based on the date. Drag these formulas down the rows to complete the process. Alternatively, Airboxr makes this super easy by giving you access to a number of ready to use reports to track your ad performance.
The hard way and the easy way
There is no doubt that analytics tools are powerful. They do all the hard work to give us insights into our raw data, and all we have to do is to look at it. If that's the case, the more tools the merrier, right? Unfortunately, using multiple sources to analyze numbers makes combining data complicated. This is no different when it comes to merging figures from Google Ads and Google Analytics. That's exactly what I'm going to show you today. Buckle up because it's going to be a long one.
Let's say this is the outcome we are aiming for—the data in columns A, B and C are from Google Ads, while the data in columns D and E are from Google Analytics arranged based on days.
Importing data from Google Ads
First, we will go to Google Ads to get columns A, B and C. Go to campaigns, where we will be setting the time period for the last 30 days.
Notice how Google Ads automatically arranges your data by campaigns. To arrange it by day, open the report editor.
Remove all the existing parameters before replacing them with day.
Now that we have what we need, we can download the data as a CSV file.
Import the CSV file onto a new Google Sheet.
Importing data from Google Analytics
Next, we will download columns D and E from Google Analytics. Similar to before, we are setting the time period for the last 30 days. Let's go to Acquisition, All Traffic and Channels, where you will find that everything is arranged by channel group.
Change that to date by adding it as a secondary dimension, and now we have our users and sessions arranged by day.
Before we go ahead and export this report as a CSV file, make sure you show as many rows of data as you need—Google Analytics only exports whatever is shown on screen.
Same as before, import the CSV file onto another tab. Rename your tabs accordingly to avoid confusion.
Cleaning and combining data
Create a new tab to combine the data from the two sources. Copy the days column from the Google Ads data to the new tab.
Dates in Google Analytics have a different format from dates in the Google Ads data.
So we have to change the date format in our Google Analytics data first. We have copied the dates over to column L, and we will use three formulas to separate them by year, month and day. Let's type this to get the year:
=left(L2, len(L2)-4)
This to get the month:
=mid(L2, 5, 2)
And this to get the day:
=right(L2, len(L2)-6)
Now simply drag these formulas across the rows.
Concatenate the values from these three columns, adding a dash between each of them. To do so, we are typing:
=concatenate(M2, "-", N2, "-", O2)
And drag this across the rows again.
As you can see, the dates now match the ones in the newest tab.
Copy the dates and paste their values to column B where they were at originally. Make sure to reformat the dates so that our vlookup formula will work later when combining the data together.
There might be instances where there are multiple entries with the same date since they can come from different default channels. To combine the users and sessions with the same dates, we will first create a pivot table.
Add the date field under rows, making sure it is in ascending order and sorted by date. Add the users and sessions fields under values as columns and summarize both fields as a sum.
Now we will be using vlookup to combine the data from the Google Ads tab and the pivot table onto the new sheet. Let's type this into column B of the new sheet:
=vlookup(A2, 'Google Ads'!A:C, 3, false)
This formula into column C:
=vlookup(A2, 'Google Ads'!A:M, 13, false)
This into column D:
=vlookup(A2, 'Pivot Table 1'!A:B, 2, false)
And lastly, this line into column E:
=vlookup(A2, 'Pivot Table 1'!A:C, 3, false)
Drag these formulas down the rows and you're done.
Phew, that took a while, didn't it?
Improve your DTC game. Sign up for weekly tips.
With Airboxr, you can do all of this in seconds.
After going through that tutorial, you would've realized just how tedious and complicated it is to combine data from different sources. In Airboxr, there are number of hops (reports) that are ready to be made using Google Ads, Google Analytics and other source of your e-commerce data all in a matter of seconds. The hops available that use Google Ads and Google analytics data are;
D2C Marketing KPI report.
DTC Marketing KPI's over Time.
Sales Performance by Date.
Sales vs. Marketing Spend over Time.
Feel free to explore our hops marketplace for more ready to use reports for your Shopify store.
Christabel Wong
About the Author