How To Track Your AdWords Competitors Over Time Using Auction Insights
Former Googler Daniel Gilbert shows you how to turn the static AdWords Auction Insights data into a report that tracks your closest competitors over time.
It’s good to know what the competition is doing. While you control your bids, it’s your competitors who determine what you actually need to pay and where your ad will be positioned.
There are third-party tools that provide some competitor information, but their data on average position, impression share, outranking share, etc., will never be as accurate as the actual AdWords data that you can get from the Auction Insights report. The only issue with this report is that it isn’t easy to see changes over time, which is what you want to do if, say, you’re trying to understand changes in cost-per-click (CPC) or average position.
Irritatingly, the Auction Insights report isn’t available in AdWords Scripts (or even in the API) — if you want to do anything with it, you have to download it from AdWords manually. However, that doesn’t mean you have to do everything manually! At Brainlabs (my employer), we use a simple Google Apps Script that we’ve shared below to turn the data into a readable format and add graphs like the ones featured below.
Google Apps Scripts are quite similar to AdWords Scripts. They allow you to automate things like creating sheets, charts and formatting. Our script below will pick out your top five competitors based on their highest impression share and show you how their presence has varied over time (once you’ve followed the steps below).
Note: Auction Insights are available for Search and Shopping campaigns, although Shopping campaigns get fewer columns. This script should work for either.
How To Use The Script
Go to Google Drive, click the red “NEW” button, and click on “Google Sheets.” Inside the new spreadsheet, click on “Tools” in the top menu and then “Script editor….”
This will open a script editor in a new tab. Click on “Google Sheets Add-on.”
There will be some sample code — delete all of this, and paste in the script that’s at the bottom of this article. You can change a couple of settings at the top if you want. There are variables called dateFormat and currencySymbol, which are used for formatting.
From there, go to “File” and save the script. It will ask for a project name, so call it something like “Auction Insights.” (It doesn’t matter what you name it.)
In AdWords, get the Auction Insights report: Go to the campaign, ad group or keyword tab and select the campaigns/ad groups/keywords for which you want the report. Click the “Details” button (it’s between “Edit” and “Bid strategy”); then, under Auction Insights, click “Selected.”
Make sure you’ve got the date range you want to cover, then download the report, adding a segment for “Month” or “Week” (depending on how long a time range you’re looking at).
This will give you a CSV file. Open it in a text editor (Notepad, for example), then copy it into Sheet1 of your Google spreadsheet. (We suggest using a text editor rather than Excel because Excel may reformat the dates in the report, and then when it’s pasted into the Google spreadsheet it may get confused over which digit is the day and which is the month.)
The onEdit function should trigger automatically after you paste the report in — it should create a sheet for each of the report column headings and fill them with data and charts.
If you want to add in CPC data, add another sheet to the spreadsheet (named Sheet2) and copy in any AdWords report that contains the clicks and costs over the date range (segmented by month or week, the same as the Auction Insights report). It doesn’t matter if the pasted report has multiple campaigns/ad groups/keywords: the script will add up everything for each date to calculate the average CPC.
The onEdit function will trigger again, updating the REARRANGE functions so that they take in the CPCs. Having your CPC is useful: you can see how your bid changes affected the auction, or if a new competitor caused clicks to become more expensive.
In the next version, we’re thinking of allowing more flexibility to choose which competitors to include. If you can think of any other improvements, let me know in the comments below!
Update: Edited to fix bug in code.
Opinions expressed in this article are those of the guest author and not necessarily Search Engine Land. Staff authors are listed here.