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:
This to get the month:
=mid(L2, 5, 2)
And this to get the day:
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?
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. Let's see how this process would work with Airboxr.
Launch Airboxr, and just like before, we are importing our Google Ads data first from the Ad Performance table.
Choose the columns you wish to pull. In this case, days, impressions and clicks.
Next, we will filter the data by selecting only the previous 30 days.
Similar to the pivot table, we will get the sum of impressions and clicks per day before clicking on import.
Moving on to the Google Analytics data. This time we will use the lookup function. Select the audience table and the columns you wish to pull, which are the date, users and sessions.
The next step is very similar to vlookup—we are basically saying match the day column on the active sheet to the date column of the source data (Google Analytics) and append users and sessions.
Complete the lookup and you're done! ⚡️
With Airboxr, you don't have to go through the trouble of downloading multiple CSVs, formatting dates, and making pivot tables. You save so much more time!
Try it yourself and be amazed by the results. Download the add-on now.
Or if you're more of a video person, you can watch our walkthrough here: