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.
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.
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.)
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.