How many hours do you spend a week on Google Ads reporting alone?
Marketers spend on average as 3.55 hours a week, just organizing and analyzing data. Add the client communications and other repetitive tasks, and you’ve got a big time sink for your team.
Perhaps the biggest of them all.
What would it mean if all your team members could automate a large part of their analysis and report creation tasks? Data that lines itself up the way you want it, no ctr-c, ctrl-v required.
A work process and office life that doesn’t revolve around repetitive mouse movements.
That’s what you can achieve by using the three Excel Macros we explain in this post.
What Is an Excel Macro (and Why Is It Useful For Digital Marketers?)
Microsoft Excel is a spreadsheet program that most business owners/regular employees will be familiar with.
If you’re in PPC and you don’t know your way around it, you must be young enough to have started with sheets and Data Studio.
A macro in Excel is a way to save and “replay” a recording of a set of actions. You create a command that sets a process in motion.
In essence, it is a way to create a script that does exactly what you did manually, without having to write a line of code.
You can save and reuse these replays/scripts across multiple spreadsheets, or save one in a single file. If you have ever created a marketing report, you probably intuitively grasp why this is so useful.
There’s a lot of repetitive tasks involved with moving data around and displaying it the way you want.
They are tools that can help you create reports without having to repeat yourself.
How to Create a Macro
First, you need to save the file you want to use macros in as a macro-enabled spreadsheet.
Then you need to select the “Record macros” option, from the “View” section of the toolbar.
One important thing to remember when you record macros is that you need to record yourself making changes to a document that is similar to the ones you want to change later.
For example, if you’ve got conversions lined up in column J on one account, and column B on another account, you won’t get the results you want.
#1 Macro That Highlights Underperforming Campaigns/Ad Groups/Keywords
One of the core tasks of an agency is to maintain the performance of the campaign through different seasons.
Teams will often highlight ad-groups, keywords, and campaigns that perform poorly to adjust bids or even pause for increasing the overall account performance.
Create a macro that automatically highlights underperforming Campaigns/Ad Groups/Keywords.
By highlighting keywords that spend a minimum amount, that have few or no conversions at a high CPA, you can easily find culprits to turn off or reduce bids for.
The first thing to do is download a keyword/ad group sheet for the data range you want.
Make sure that you select the “Excel .csv” option.
Then you have to make sure that the lines are formatted the same across the accounts/campaigns that you will use to create your report.
Once you have done that, either save the file as a macro-enabled spreadsheet or copy/paste the data into one.
You can now start recording the Macro from within the View section of the toolbar.
Make sure that you store the macro in either your personal macro workbook or a macro template that you will share with other employees in the office.
This hidden spreadsheet will allow you to use the macro again for multiple spreadsheets at a later time, as long as you are using the same computer.
Once you hit OK, you’ve started creating your very first spreadsheet playback.
Note: Remember that Excel is recording everything you do in the document, so don’t make unnecessary changes.
From here use the “Conditional Formatting” tool to highlight the columns that you want.
To find keywords that aren’t converting well, you can highlight low conversion rates or high CPAs.
If you care more about volume than CPA, you might want to highlight keywords that bring in a low amount of conversions. You can filter these to stand out by setting them to less than x.
If you arrange them by clicks or cost, this will instantly show you which keywords or ad groups aren’t bringing in enough conversions.
But you can also further qualify the data at a glance by only including keywords that have a cost of at least $450, or whichever amount is statistically significant at your CPC.
This filter will eliminate all the rows of insignificant keywords that won’t make a major impact on their own.
Repeat this for every aspect of the keywords/ad groups that you want to highlight, and you’ll be ready to save the Macro.
To do this go back to the View panel, right-click stop recording.
Now that you are finished recording your macro, you will be able to find it in the view panel.
One final step is that you will want to edit the file to remove unnecessary lines, and also run it on the document to test if it works.
First, select “View Macros” from the same menu.
Then you will want to edit the one that you created.
This act opens the file up in a visual basic editor, which shows you the lines of code that were created to mimic the actions you took.
As you can see, the macro has recorded everything, even my scrolling up and down the table to make sure the cell selection was correct.
That is definitely something we don’t want to include in the final script.
Remove any blatantly superfluous lines like the ActiveWindow.ScrollRow commands.’
Save the file with the macros.
Now it’s time to test it.
Remove all the edits that you made to the original data table.
View all the macros in the file, and select the appropriate one and run it.
If that brings the highlights back just like they were before, that means you’ve made your first working excel macro.
” underperform Macro
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
.Color = -16383844
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
Selection.FormatConditions(1).StopIfTrue = False
Range(“Table6[[#All],[Cost / conv,]]”).Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
.Color = -16383844
.TintAndShade = 0
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
The above is what the final script looked like for the changes I made to the keyword data. Just a few lines of code that will instantly show you the underperformers in lists of thousands of keywords.
Now that you have learned the basics of how to create, edit, test and use a macro, it’s time to move on to bigger and better things.
#2 Separate Branded From Non-Branded Campaigns and Ad Groups
Branded Search is often reported separately from other paid search campaigns as they are completely different channels.
Your client probably feels like the work you do shows up in unbranded search, and the work that they (or their branding agency) do show up in the branded search campaigns.
Create a macro that will instantly separate branded from non-branded campaigns/ad groups.
Instead of manually downloading separate files and copy-pasting, or downloading the aggregate overview and manually moving rows around, you can set up an automated script that takes care of everything for you.
Start by downloading the ad group report for the date range you want, and importing the data into the macro-enabled spreadsheet.
Once you have done this, set up a table (or wait until after) and start recording a new macro.
Now all you have to do is click the menu button on the header for the appropriate column. (It will be either “Campaign” or “Ad group” depending on how you structure your campaigns.)
Then use the text filter option. To watch the Branded breakdown, select contains, and enter “Brand” or whichever brand or product name the campaigns use.
This filter will show only the ad groups in branded campaigns.
Now you can move the data however you want, for example copying it to further down, moving it to a new sheet, etc.
Repeat this step with the unbranded campaigns.
Now you need to add a totals row that sums up each of the columns.
At this point, you can add the aesthetic updates you want to finalize the report (adding headers, changing font sizes etc), or add charts that compare the results from branded and regular paid search campaigns.
Once you have set up the branded/unbranded breakdown the way you want, stop recording the macro, test it and use it to save a few hours every month.
#3 Aggregate Weekday/Weekend Hour Conversion Data to Spot Schedules/Patterns
If you work with retail clients or a company that closes sales by phone, they probably want reliable data on exactly when the most conversions tend to roll in from Google Ads.
But there is just one problem.
The hourly data you export in the sheet is normally separated not only by campaigns but also days.
This usually means thousands of lines of data, and trying to sort through it and array it into neat categories like “Weekdays 8-11 AM” can take forever.
Even setting up a Macro for a manual table going through and adding and removing filter after filter would take hours.
That’s where pivot tables come in.
To quickly create reports that showcase and compare hours on weekdays and weekends, you can use a mix of pivot tables and macros.
First, download the hour & day conversion data.
Import it into the spreadsheet, and create a pivot table.
Then you want to filter the table by “Hour of the day” and “Day of the week.”
Then you select Values as columns and add as many fields as you want to include in your report.
When you set the filters like this, you can choose the hours and the days of the week that you want to showcase.
For example, we can choose all weekdays between 1 and 6 AM.
The only problem with this pivot table overview is that it’s not able to show the correct CPA or conversion rates since it simply aggregates values based on the numbers in the data.
As you can see here, even though there are 26 clicks and only one conversion, the conversion rate ends up as 100% because it defaults to sum. (And no matter which option you choose to display it, it doesn’t reach the correct % of 3,8%. Varp shows 1%, and StdDevp adds it up to 10%.)
The best solution is to output only the base numbers and then format the rest with macros.
Quickly fill out the relevant lines of data you need from the pivot table.
Move this to a separate sheet.
And then when you are ready to start formatting, start recording the macro.
Doing so will allow you to instantly format the data you have extracted from the pivot table every time you want to create a similar report.
This screenshot shows what the data might look like before it has been formatted.
Once the data has been formatted it might look something like this.
You can then clear the fields you pasted the data in, and stop the macro recording.
You will now have created a macro that perfectly moves the data into two tables.
You can use this for weekdays and weekends, as well as any other major insights you might want to glean from pivot tables (without having to get into the complicated mess that is using pivot tables and macros directly together).
Excel and in particular pivot tables is the best friend of many a marketer when it comes to reporting.
While these tools have made it faster than straight up manual copy-paste and template fuelled reporting used to be, it is still one of the biggest time sucks in the office.
By using Excel Macros, not only can you create reports faster, but you can create a procedure for data-handling that will help all your employees consistently find insights in the data, regardless of how proficient they are with spreadsheet software.