Auction Insights 3: The final script

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:

• take reports from the new AdWords interface (OMG YES!).
• try out defaults for column names, if those in the Settings sheet aren’t perfect (…WOO?).
• find out more about your competitors (NOW YOU’RE TALKING!).

What’s new?

New interface

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.

Default column names

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.

Competitor settings

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

• First, you can choose to include all competitors in the data tables. It will still only include the top six in the chart, though. You wouldn’t be able to see anything if there were too many lines in there.
• But what if you don’t want them all, and you just want that one guy who’s not listed on the Settings sheet? You can keep the list from automatically filling, and then you can manually edit the list to include whoever you want to see.

How do I use it?

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.

Fill in your data

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

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.