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.

Daniel Gilbert on April 8, 2015 at 9:54 am

• 748
• MORE

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! AtBrainlabs (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!

/**
* Brainlabs Auction Insights Report Tool
*
* This script will take data from an Auctions Insights report and use
* it to create a sheet for each column heading, with the data for the
* your domain and the top 5 competitors over time.
*
* Version: 1.0
* Google Apps Script maintained on brainlabsdigital.com
**/

var dateFormat = 'yyyy-MM-dd';
// The date format to be used in the tables and charts
// Can be replaced with 'dd/MM/yyyy' or 'MM/dd/yyyy' if preferred

var currencySymbol = "£";
// The symbol used for formatting cells as currency
// Can be replaced with "$", "€", etc
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Information about the different columns of the Auctions Insights report

var searchColumns = ["Impr. share", "Avg. position", "Overlap rate", "Position above rate", "Top of page rate", "Outranking share"];
// These are the different columns from the Search Auctions Insight report (in order)

var shoppingColumns = ["Impr. share", "Overlap rate", "Outranking share"];
// The columns for the Shopping campaign Auctions Insight report (in order)

var includeYou = ["Impr. share", "Avg. position", "Top of page rate"];
// These are the columns where there is data for your domain (referred to as "You").

var subtitle = {};
subtitle["Impr. share"] = "How often a participant received an impression, as a proportion of the auctions in which you were also competing.";
subtitle["Avg. position"] = "The average position on the search results page for the participant’s ads when they received an impression.";
subtitle["Overlap rate"] = "How often another participant's ad received an impression when your ad also received an impression.";
subtitle["Position above rate"] = "When you and another participant received an impression in the same auctions, % when participant’s ad was shown in a higher position.";
subtitle["Top of page rate"] = "When a participant’s ads received impressions, how often it appeared at the top of the page above the search results.";
subtitle["Outranking share"] = "How often your ad ranked higher in the auction than another participant's ad, or your ad showed when theirs did not.";
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to create new sheets and charts
function onEdit() {
// We first check what sort of Auctions Insight report there is
// as the Shopping report has a different set of column headers
var title = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue();
if (title.substr(0,7) == "Auction") {
// This means the Auctions Insights report is for Search campaigns
var columnHeaders = searchColumns;
} else if (title.substr(0,8) == "Shopping") {
// The report is for Shopping campaigns
var columnHeaders = shoppingColumns;
} else {
// The title presumably hasn't been copied in yet
// The columnHeaders array is set to an empty array so the for loop below won't try to run
var columnHeaders = [];
}

// Loop through all the columns
for (var g=0; g<columnHeaders.length; g++) {

// We try to go to the sheet for the current column
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(columnHeaders[g]);
if (sheet == null) {
// If the sheet doesn't exist, create it
var newSheet = true;
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnHeaders[g]);
} else {
var newSheet = false;
}

// Because there may not be a Sheet2 yet, or it might not have the CPC information yet, we check
// whether we should include CPCs or not, and only use a REARRANGE function with Sheet2 if it's there.
// REARRANGE has three inputs: the column name, the Auctions Insights report and the CPC performance
// If there is no CPC performance then the third input is set as a single cell
// The REARRANGE function (see below) will check for this
if (SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2") == null ||
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("A1").getValue().length == 0) {
sheet.getRange(1,1).setValue('=REARRANGE("' + columnHeaders[g] + '",Sheet1!A:H,Sheet1!A1)');
} else {
sheet.getRange(1,1).setValue('=REARRANGE("' + columnHeaders[g] + '",Sheet1!A:H,Sheet2!A:Z)');
}

// If we just created this sheet we need to add formatting and charts
if (newSheet) {
var numRows = 0;
var numColumns = 0;

// These are set to 2 and 1 because the data from REARRANGE starts in cell A2
var startRow = 2;
var startColumn = 1;

// Look at cells to the right of the starting position until a blank one is found,
// to find the number of columns that contain data
for (var i=0; i<10; i++) {
if (sheet.getRange(startRow+i,startColumn).isBlank()) {
numRows = i;
break;
}
}

// Find the number of rows that contain data
for (var i=0; i<50; i++) {
if (sheet.getRange(startRow,startColumn+i).isBlank()) {
numColumns = i;
break;
}
}

// Format the first column (the dates) as dates
sheet.getRange(startRow+1, startColumn, numRows-1, 1).setNumberFormat(dateFormat);

// Format the second column (the CPCs) as currency
sheet.getRange(startRow+1, startColumn+1, numRows-1, 1).setNumberFormat(currencySymbol + "0.00");

if (columnHeaders[g] == "Avg. position") {
// If the data is average positions, format it as a number
sheet.getRange(startRow+1, startColumn+2, numRows-1, numColumns-1).setNumberFormat("0.0");
} else {
// Otherwise format it as a percentage
sheet.getRange(startRow+1, startColumn+2, numRows-1, numColumns-1).setNumberFormat("0.00%");
}

// Get the width in pixels for the chart, based on the chart being a few columns wider than the data
var width = 0;
for (var i= startColumn; i< startColumn+numColumns+2; i++) {
width += sheet.getColumnWidth(i);
}

// Creates the chart
var chart = sheet.newChart()
.setChartType(Charts.ChartType.LINE)
.addRange(sheet.getRange(startRow, startColumn, numRows, 1))
.addRange(sheet.getRange(startRow, startColumn+1, numRows, 1))
.addRange(sheet.getRange(startRow, startColumn+2, numRows, numColumns-2))

.setOption('series', {
0: {targetAxisIndex: 1},
1: {targetAxisIndex: 0}
})

.setOption('vAxes', {
// Adds titles to each axis.
0: {title: 'Percentage'} ,
1: {title: 'CPC'}
})
.setOption('chartArea', {left:'10%',top:'15%',width:'80%',height:'70%'})

.setPosition(startRow + numRows + 1, startColumn, 0, 0)
.setOption('width', width)
.setOption('height', 500)
.setOption('title', columnHeaders[g] + " - " + subtitle[columnHeaders[g]])
.setOption('legend', {position: 'top'})
.build();

sheet.insertChart(chart);
}

}
} //end function onEdit
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// The function to create new sheets and charts
function REARRANGE(columnHeader,auditInsights,performance) {

// Dates are stored as bigendian date strings, then converted back to dates at the end
var bigendianDate = 'yyyy-MM-dd';
// The timezone is used to convert them back
var timezone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();

var domains = {};
var dates = [];
var domainNames = [];

// First we record the stats for each domain, by month
// and record each domain's highest impression share
for (var i = 2; i<auditInsights.length; i++) {
// auditInsights is a multi-dimensional array containing the values of the auditInsights cells.
// So auditInsights[i] is a row on the Auction Insights report
// The loop starts at 2 as auditInsights[0] is the title and auditInsights[1] is the headers.

var date = auditInsights[i][0];

if (!date) {
// If the date field is blank, we have reached the end of the data
// so we end the for loop
break;
}

if (typeof date != "string") {
// The date is converted into a string
date = Utilities.formatDate(date, timezone, bigendianDate);
}

if (dates.indexOf(date) < 0) {
// If the current row's date isn't in the dates array, it's added
dates.push(date);
}

var imprShare = auditInsights[i][2]; //the impression share
if (imprShare == "< 10%") {
// If the impression share is "< 10%" (a string) it is changed to 5% (a float)
// so it can be displayed in the graph.
imprShare = 0.05;
}

var domainName = auditInsights[i][1];

if (domainNames.indexOf(domainName) < 0) {
// If the current row's domain name isn't in the domainNames array, it is added,
// and an entry for it is entered into the domains object.
domainNames.push(domainName);
domains[domainName] = [];
domains[domainName]["Max Impr Share"] = imprShare;
}

domains[domainName][date] = [imprShare].concat(auditInsights[i].slice(3));
// This is (in order) the impression share, avg. position, overlap rate, position above rate, top of page rate, outranking share

if (imprShare > domains[domainName]["Max Impr Share"]) {
// If the current imprShare is bigger than the last recorded max impr share,
// the current one is recorded as being the max
domains[domainName]["Max Impr Share"] = imprShare;
}

} // end of for loop

// Next we get the costs and clicks from Sheet2 (if it exists), to get the CPC
// If Sheet2 exists, performance will be a multidimensional array. If it doesn't it will only contain 1 cell.
var hasCPC = (performance.length > 1);
var costTotals = [];
var clicksTotals = [];

if (hasCPC) {
// Dates should be in the first column, but the position of the cost and clicks columns varies depending on AdWords settings
// So we set variables to record the required column numbers
var costIndex = performance[1].indexOf("Cost");
var clicksIndex = performance[1].indexOf("Clicks");

for (var i = 2; i<performance.length; i++) {
var date = performance[i][0];

if (!date) {
// If there's no date we've reached the end of the data
break;
}

if (typeof date != "string") {
// If the date isn't a string, convert it into one
date = Utilities.formatDate(date, timezone, bigendianDate);
}

if (costTotals[date] == undefined) {
costTotals[date] = performance[i][costIndex];
clicksTotals[date] = performance[i][clicksIndex];
} else {
costTotals[date] += performance[i][costIndex];
clicksTotals[date] += performance[i][clicksIndex];
}

} // end of for loop
}

dates.sort();
// Sorts the dates alphabetically - as they're in bigendian format, this means they are sorted oldest to newest

domainNames.sort(compareDomainNames);
// Sorts the domain names by their highest impression share, using the function below

function compareDomainNames(a,b) {
if (domains[a]["Max Impr Share"] != domains[b]["Max Impr Share"]) {
// If the max impression shares are different, the domain with the highest is put first
return domains[b]["Max Impr Share"] - domains[a]["Max Impr Share"];
} else {
// If both domains have the same max impression share, the one with data for the most dates is put first
return Object.keys(domains[b]).length - Object.keys(domains[a]).length;
}
}

domainNames.splice(domainNames.indexOf("You"),1);
// Removes "You" from the array

if (includeYou.indexOf(columnHeader) > -1) {
// If this graph is supposed to include 'You', then it's added to the start of the array
domainNames.unshift("You");
}

var g = auditInsights[1].indexOf(columnHeader)-2;
// The index of the required stat

if (g < 0) {
// Error checking - if the columnHeader wasn't a recognised title, we output an error message
return [[columnHeader + " not recognised."]];
}

// 'output' is a multi-dimensional array that will become cells in the spreadsheet
output = [];

// The first row of the output is the column name
output[0] = [columnHeader];

// The second row of the output is the headings
output[1] = ["Date","Avg. CPC"];
for (var d = 0; d<domainNames.length && d<6; d++) {
output[1].push(domainNames[d]);
}

// We loop though the dates to make their lines of output
// (the date, the CPC, then each domain's metric)
for (var i = 0; i<dates.length; i++) {
output[i+2] = [stringToDate(dates[i])];

// Calculate the average CPC
if (costTotals[dates[i]] == undefined || clicksTotals[dates[i]] == undefined || clicksTotals[dates[i]] == 0) {
output[i+2].push(0);
} else {
output[i+2].push(costTotals[dates[i]]/clicksTotals[dates[i]]);
}

for (var d = 0; d<domainNames.length && d<6; d++) {
if (domains[domainNames[d]][dates[i]] === undefined) {
output[i+2].push(0);
} else {
output[i+2].push(domains[domainNames[d]][dates[i]][g]);
}
}
}

return output;

}// end function REARRANGE
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// This function is used by REARRANGE to convert date-strings back into dates
function stringToDate(string) {
var dateBits = string.split("-");

var date = new Date();
date.setFullYear(dateBits[0]);
date.setMonth(parseInt(dateBits[1],10)-1);
date.setDate(parseInt(dateBits[2],10));

return date;
}

---

Some opinions expressed in this article may be those of a guest author and not necessarily Search Engine Land. Staff authors are listed here.

---

SMX Advanced is the only conference designed exclusively for experienced paid search advertisers and SEOs. You'll participate in experts-only sessions and network with fellow internet marketing thought leaders. Check out the tactic-packed agenda!