Auction Insights Script
Auction Insights is great, but our script makes it quite a lot greater. This is the best way to build up your understanding of your PPC competitors. And as Sun Tzu says, ‘know thy enemy’. He’s talking about war, but I think it applies to Google Ads too.
What’s included?
How does the script work?
Use the script to add in your own performance data, measure changes over time, and filter the Google Ads report to focus on particular competitors. The script will also convert boring tables into beautiful graphs, just in case you need to show it to anyone important.
How do I use it?
Fill in your data
The first thing you need to do is make a copy of the Auction Insights template sheet linked below. It’s got the script already embedded in it.
Then, go to your Google Ads 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.)
Adjust your settings
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:
- You can only add, at most, two stats to the chart. If you select more, then only the first two are included.
- Note that if you want something in the chart, it has to be in the table (because that’s where the chart gets its data from).
- If you haven’t copied anything into the Performance Data sheet, this section will be ignored. You can just leave all of these blank.
- “Searches” is (approximately) the total number of available impressions. It is calculated as impressions divided by impression share; as the impression share is rounded, it is not a precise figure, especially if your impression share is low.
Competitor Settings can be used if you have too many competitors to fit in the Competitors To Include section.
- Set “Include all competitors” to yes if you want all competitors in your reports (regardless of what’s marked with a “Yes” in the the Competitors To Include section).
- Set “Auto refresh the list” to “No” if you want to be able to change the Competitors To Include section manually. If you don’t want all competitors, but there’s a name you want included that’s missing from the list, stopping the auto refresh means you can replace the names in the list yourself.
- Be careful — the names have to match what’s in the Auctions Insight report. If you mistype a name, it won’t show up in reports.
- If this is set to “Yes,” then the competitor list will automatically update whenever the spreadsheet is edited, and you’ll lose any changes you’ve made there.)
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.
- All selected competitors will be in the data tables.
- To prevent the charts from being too crowded, only the first six selected competitors are included.
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).
- The Total row gives you a report of your and your selected competitors’ performance (alongside your selected stats) for all devices. This uses data from the “Auction Insights” sheet.
- There are then three rows for devices, using the names from the “Auction Insights By Device” sheet. Putting a “Yes” for these rows gives you a report of your and your selected competitors’ performance (alongside your selected stats) for the named device.
- The last row is Compare All Devices, which gives you your total average performance and performance segmented by device. This report does not include competitor data.
- You can’t make a Compare All Devices report for columns like “Position above rate,” as they don’t have any data on your performance.
And then run the script!
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.