Tracking AdWords Competitors Over Time

Tracking AdWords Competitors Over Time

We’ve got a handy little Google Apps script to help you make sense of your Auction Insights data. The Auctions Insight report isn’t available in AdWords Scripts, so we’ve had to do a bit of a work around – rather than put this script into AdWords, this goes into a Google Sheet.

If you’ve not run a script before please read our Introduction to AdWords Scripts. For more open-source fun check out our AdWords Scripts directory. And if you want to use some of our technology that’s arrived back from the future then sign up for one of our paid plans on the Brainlabs Tech Stack.

First create your sheet and go to the Script editor in the Tools menu.

Find 'Script editor...' in the Tools menu

Then Script Editor opens in a new tab, and you need to click ‘Google Sheets Add-on’.

Click 'Google Sheets Add-on'

The script editor will already have sample code, which you can delete. Paste in the code below. If you want, you can change the dateFormat and currencySymbol variables, so dates and costs will be shown with the right format. Also, if you’re going to add in CPCs and your reports aren’t in English, change costColumnName and clicksColumnName to whatever the Cost and Clicks columns are called in your language. Go to the ‘File’ menu and click ‘Save’ – you’ll be asked for a project name, and you can call it whatever you want.

script editor

Then go to AdWords and download your Auction Insights report, segmented by month (or week, if you prefer). Open the report in a text editor, like Notepad – spreadsheet programs like Excel can mess up the dates – and copy it into Sheet1 of your Google Sheet, starting in cell A1.

in google sheets

The onEdit function from the script should fire, creating a bunch of sheets and charts based on your data.

without cpcs

Want more data? You can add in your CPCs, so you can see if your changes improved your impression share, or if changes in competitor activity added to your costs. From AdWords download a campaign report with the columns Clicks and Cost, segmented the same way as the Auction Insights report. Add in Sheet2 to your spreadsheet  (next to Sheet1) and paste in the report (again, open the report in a text editor to avoid screwing up the dates).

with cpc data

The script should update all the worksheets, adding your CPC as a grey dotted line.

the result

For more fun & games check out all of our open-source AdWords scripts and sign up for our newsletter for new scripts. And if you want to use some of our technology that’s arrived back from the future then sign up for one of our paid plans on the Brainlabs Tech Stack.

Or follow us on Twitter and Facebook for more Brainlabs fun and updates.

Code maintained below:

Version 1.1: We’ve made a little update to correct a bug and improve the graphs.

Version 2.0: Fixed issues for using languages other than English and some locale settings (thanks to Marco for pointing out the issue there!). The script also now checks to see if extra data has been added to the Auction Insights report on Sheet1, and redraws the graphs if necessary.

[code lang=”js”]
/**
* Brainlabs Auctions 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: 2.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

var costColumnName = "Cost";
var clicksColumnName = "Clicks";
// The name of the columns for cost and clicks (used if CPC data is provided)
// Can be replaced if your AdWords report is in another language
// Note this is case sensitive!

//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Information about the different columns of the Auctions Insights report
// (Will only be included if column names are given in English)
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() {

// Finds Sheet1 – it is assumed this is the first sheet with a name ending in ‘1’
// If no sheet names end with ‘1’ then the first sheet is used
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
for (var i=0; i<sheets.length; i++) {
if (sheets[i].getSheetName().substr(sheets[i].getSheetName().length-1) == "1") {
var sheet1 = sheets[i];
break;
}
}
var sheet1Name = sheet1.getSheetName();

// Finds Sheet2 – it is assumed this is the first sheet with a name ending in ‘2’
for (var i=0; i<sheets.length; i++) {
if (sheets[i].getSheetName().substr(sheets[i].getSheetName().length-1) == "2") {
var sheet2 = sheets[i];
var sheet2Name = sheet2.getSheetName();
break;
}
}

var columnHeaders = sheet1.getRange(2, 3, 1, 10).getValues()[0];

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

// If the header is blank, we have reached the end of the headers, so the loop ends
if (columnHeaders[g] == "") {
break;
}

// 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
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(columnHeaders[g]);
}

// 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 (typeof sheet2 == ‘undefined’ ||
sheet2.getRange("A1").getValue().length == 0) {
sheet.getRange(1,1).setValue(‘=REARRANGE("’ + columnHeaders[g] + ‘";’ + sheet1Name +’!A:H;’ + sheet1Name +’!A1)’);
} else {
if ((sheet.getRange(1,1).getFormula() == ‘=REARRANGE("’ + columnHeaders[g] + ‘";’ + sheet1Name +’!A:H;’ + sheet1Name +’!A1)’ ||
sheet.getRange(1,1).getFormula() == ‘=REARRANGE("’ + columnHeaders[g] + ‘",’ + sheet1Name +’!A:H,’ + sheet1Name +’!A1)’)
&& sheet.getCharts().length > 0) {
// If there is an old chart that didn’t include the CPC, remove it so a new one (with the CPC) will be created
sheet.removeChart(sheet.getCharts()[0]);
}
sheet.getRange(1,1).setValue(‘=REARRANGE("’ + columnHeaders[g] + ‘";’ + sheet1Name +’!A:H;’ + sheet2Name +’!A:Z)’);
}

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<2000; i++) {
if (sheet.getRange(startRow+i,startColumn).isBlank()) {
numRows = i;
break;
}
}

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

if (sheet.getCharts().length > 0) {
var oldChartLastRow = sheet.getCharts()[0].getRanges()[0].getLastRow();

if (oldChartLastRow != startRow+numRows-1) {
// If the last row of the chart’s range isn’t startRow+numRows-1, then there is new data
// The old chart is removed, so a new one (that covers all the data) will be created
sheet.removeChart(sheet.getCharts()[0]);
}
}

// If there aren’t any charts then we need to add formatting and charts
if (sheet.getCharts().length < 1) {
// Format the first column (the dates) as dates
sheet.getRange(startRow+1, startColumn, numRows, 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 (sheet.getRange(startRow+1, startColumn+2, 1,1).getValue() > 1) {
// If the data is over 1, it can’t be a percentage, so it must be the average postion.
// So it is formatted 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 chartTitle = columnHeaders[g];
if (typeof subtitle[columnHeaders[g]] != ‘undefined’) {
chartTitle = chartTitle + " – " + subtitle[columnHeaders[g]];
}

var chartBuilder = 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, 1))
.addRange(sheet.getRange(startRow, startColumn+3, numRows, numColumns-2))
.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’, chartTitle)
.setOption(‘legend’, {position: ‘top’})
.setOption(‘vAxes’, {
// Adds titles to each axis.
0: {title: ‘Percentage’} ,
1: {title: ‘CPC’}
});

// Find out if there are any CPCs
var cpcValues = sheet.getRange(startRow+1, startColumn+1, numRows-1, 1).getValues();
var thereAreCPCs = false;
for (var i=0; i<numRows-1; i++) {
if (cpcValues[i][0] > 0) {
thereAreCPCs = true;
break;
}
}

// Find out if there is data for ‘You’
var thereIsYou = sheet.getRange(startRow, startColumn+2).getValue() == "You";

// If the CPCs are all 0, then remove it from the chart (as it isn’t useful). Otherwise it
// is shown as a grey dashed line.

// If one of the domains is ‘You’, that will be the second series of data (after the CPC) –
// this is formatted differently to the competitors to stick out.

if (thereIsYou && thereAreCPCs) {
chartBuilder.setOption(‘series’, {
// The CPC
0: {targetAxisIndex: 1,
lineDashStyle: [10,5],
color: ‘#999999’},

// ‘You’
1: {targetAxisIndex: 0,
color: ‘#000000’,
lineWidth: 4},

// Competitors
2: {targetAxisIndex: 0}
});

} else if (thereIsYou && !thereAreCPCs) {
chartBuilder.removeRange(sheet.getRange(startRow, startColumn+1, numRows, 1));
chartBuilder.setOption(‘series’, {
// ‘You’
0: {targetAxisIndex: 0,
color: ‘#000000’,
lineWidth: 4},

// Competitors
1: {targetAxisIndex: 0},
2: {targetAxisIndex: 0}
});

} else if (!thereIsYou && thereAreCPCs) {
chartBuilder.setOption(‘series’, {
// The CPC
0: {targetAxisIndex: 1,
lineDashStyle: [10,5],
color: ‘#999999’},

// Competitors
1: {targetAxisIndex: 0},
2: {targetAxisIndex: 0}
});

} else if (!thereIsYou && !thereAreCPCs) {
chartBuilder.removeRange(sheet.getRange(startRow, startColumn+1, numRows, 1));
chartBuilder.setOption(‘series’, {
// Competitors
0: {targetAxisIndex: 0},
1: {targetAxisIndex: 0},
2: {targetAxisIndex: 0}
});
}

// Creates the specified chart and inserts it into the sheet
var chart = chartBuilder.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 = [];

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

// 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 (Utilities.formatDate(stringToDate(date), "UTC", bigendianDate) == "1970-01-01") {
//This means it isn’t a proper date, so the row is skipped
continue;
}

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;
}

// If g is 2 then the stat is impression share, so that is recorded
if (g == 2) {
domains[domainName][date] = imprShare;
} else {
// Otherwise the gth value of the row is recorded
domains[domainName][date] = auditInsights[i][g];
}

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(costColumnName);
var clicksIndex = performance[1].indexOf(clicksColumnName);

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;
}
}

var includeYou = false;
for (var i=0; i<dates.length; i++) {
if (domains["You"][dates[i]] != "–") {
includeYou = true;
break;
}
}

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

if (includeYou) {
// If this graph is supposed to include ‘You’, then it’s added to the start of the array
domainNames.unshift("You");
}

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]]);
}
}
}

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;
}
[/code]

Share this post