Who would have thought that Auction Insights could inspire an entire TRILOGY? Well, if you know AdWords, I suppose it’s not a huge shock that our original script has had to undergo a few adaptations over the last couple of years. Google does like to keep us PPC practitioners on our toes!
Changes to AdWords aside, it’s always nice to spruce things up. Optimization is an infinite process, after all. So, please read on for the latest script that puts the insight into “Auction Insights,” compliments of Brainlabs (my employer).
Basically, we’ve given the Auction Insights script a bit of an update. The latest version can:
New interface, new reports. You may not have noticed the difference, but there’s an extra line at the top with the date range. In the old version of the tool, it wouldn’t look far enough down the sheet to find the headings. And when you download campaign performance, there are a bunch of totals at the end that would make the old version overcount your stats. But the new version can tell what sort of report you’ve pasted in and cope with it; you can go back to not noticing the differences.
Something to note, though: In the old interface, if you downloaded a campaign report segmented by time and device, it would only give rows for time, device and campaign combinations that had traffic. In the new interface, it gives rows even when there are no impressions. This may be awkward to copy into the Performance sheet, and it may slow down running the tool. Speed things up by filtering out the zero impression rows before copying the data into the Performance sheet.
Also, you can’t mix and match reports from the old and new interfaces — they use different names for the device segments.
The old interface says “Impressions,” where the new one says “Impr.” Sometimes, reports say “Interactions” to mean “Clicks.” It’s easy to miss when you have to update the column names in the Settings sheet — so now, if the names in the Settings don’t work, the script will try some of the English column names as a default.
There’s a change to the list of competitors — you can just say “yes” next to the ones you want to include (as before), or you can give them a number. Competitors with a number will be shown in that order in the reports.
If you’ve got too many competitors to all show in the list on the Settings page, you’ve got two new options:.
Enough blather. You’re here because you want to use this for yourself!
The first thing is to make a copy of the new template sheet. It’s got the script already embedded in it.
Go to your AdWords account, select the campaigns you want to look at, and download the Auction Insights report, segmented by day, week or month. Copy it to the spreadsheet in the Auction Insights tab. Make sure you’ve included the headers.
(If you’re having problems with numbers or dates being wrong — for example, if Sheets is reading the day as the month or not recognizing numbers with decimal places — you may need to change the locale of the spreadsheet. To do this, go to File, click “Spreadsheet settings…” and select your country from the Locale drop-down. If you’re using Excel, also make sure the columns are wide enough to show the data when you copy them, otherwise you may find all your dates turned into #####.)
If you want separate device graphs, download the Auction Insights report again — but this time segmented by time period and device. Copy and paste that into the Auction Insights By Device sheet (again, make sure there are headers).
Lastly, if you want CTR, CPC, impressions or searches, then download a performance report for the same set of campaigns for the same date range, segmented by the same time period and (if you’re looking at device data) by device. Make sure there are clicks, impressions and cost columns — CTR, CPC and searches will be calculated from these. Copy this into the Performance Data sheet.
(If there are lots of campaigns, you may hit the limit for the number of cells in a Google Sheet. If that happens, then you can add up all the campaigns’ data for each day and device combination and copy that into the Sheet — just keep the column headers the same and have them on Row 2.)
From here, go to the Settings sheet. Some cells are filled in automatically — their text is in yellow. This includes the competitor names (listed in order of highest impression share), the device names and the column headings (both in the “Reports to Make” table).
The Names From Reports section at the top is used to make sure the script reads from the correct columns. Make sure that “Date” matches the name of the date column in your reports (which should be “Day,” “Week” or “Month” if the report is in English). Display URL Domain is the name of the column containing competitor names: “Display URL Domain” for Search campaigns or “Shop Display Name” for Shopping campaigns.
You shouldn’t need to change anything else if your reports are in English, but if you’re using a different language, you’ll need to update some additional elements — most are column names, and “You” is what the Auction Insights report shows as the domain/display name when it gives your performance.
The Formatting section is used to format the data. Feel free to replace the date format (e.g., with dd-MM-yyyy or MM/dd/yyyy) and the currency symbol. (Note that the script won’t do any currency conversion for you!)
The Stats To Report section lets you pick which extra statistics go in the data tables and which go into charts. Put “Yes” in the relevant cell to include a stat. Some things to note:
Competitor Settings can be used if you have too many competitors to fit in the Competitors To Include section.
The Competitors To Include section should have an automatically filled list of competitor display domains, drawn from the Auction Insights sheet. Put a number next to the names to have them appear in your reports in a specific order, or put “Yes” if you don’t mind the order. Leave the space next to them empty to ignore them.
The Reports To Make section lets you pick which reports are generated. The top row is filled out automatically with the column headers from the Auction Insights sheet (because the columns will be different if you’re looking at Shopping rather than Search campaigns, or if your report is in another language).
When you’re all ready, hit the “Click Here To Generate Reports” button. You’ll need to give authorization the first time you do this so the script can run. Your reports should all be generated, one report per sheet. If there are any issues, there should be a message box to say what the problem is.
Note that if you’ve run the report before, it will delete and remake any of the reports you’ve selected — so make sure you save the output somewhere!
If you’ve made a load of sheets, and it’s all too much, you can delete everything except the template sheets with the “Delete Reports” button.