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.

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

Share this post