Keyword Performance Report from AdWords Scripts tutorial...here is slightly modified...

[Oleg Amirdzhanov]

Keyword Performance Report from AdWords Scripts tutorial @ https://developers.google.com/adwords/scripts/docs/tutorials/keyword-performance-report - by far my most favorite script from the tutorial....  Here is a slightly modified version I thought few people might find handy... It will work as is in any AdWords account, the only variable that needs to be updated is var RECIPIENT_EMAIL, which needs a real email address attached to it...  This script does not modify any AdWords  data! 

  Have fun...

// Original script @ https://developers.google.com/adwords/scripts/docs/tutorials/keyword-performance-report

// This is a slightly modified version for performance by match type rather than quality score

//  Comma-separated list of recipients..Please substitute exa...@example.com below with at least one valid email address

var RECIPIENT_EMAIL = 'exa...@example.com';

// Spreadsheet template.  You will generate your own spreadsheet URL and substitute the one below with yours.

// This is achieved at lines 15 and 16

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheet/ccc?key=0AnBK2lulXmehdEo3YUFfc3RxakZ6OGhDOHd3bVYtQlE#gid=0';

/**

* This script computes a keyword performance report

* and outputs it to a Google spreadsheet. The spreadsheet

* url is logged and emailed.

*/

function main() {

var spreadsheet = copySpreadsheet(SPREADSHEET_URL);

var sheet = spreadsheet.getSheetByName('Report');

outputMatchTypeData(sheet);

outputPositionData(sheet);

Logger.log('Keyword Performance by Match Type & Position (ALL TIME)- ' + spreadsheet.getUrl());

MailApp.sendEmail(

  RECIPIENT_EMAIL, 'New Report is ready.', spreadsheet.getUrl());

}

/**

* Retrieves the spreadsheet identified by the URL.

* @param {string} spreadsheetUrl The URL of the spreadsheet.

* @return {SpreadSheet} The spreadsheet.

*/

function copySpreadsheet(spreadsheetUrl) {

var matches = new RegExp('key=([^&#]*)').exec(spreadsheetUrl);

if (!matches || !matches[1]) {

  throw 'Invalid spreadsheet URL: ' + spreadsheetUrl;

}

var spreadsheetId = matches[1];

return SpreadsheetApp.openById(spreadsheetId).copy(

  'Keyword Performance Report ' + new Date());

}

/**

* Outputs Match Type data splitting broad and broad modified to better illustrate the value of each

* @param {Sheet} sheet The sheet to output to.

*/

function outputMatchTypeData(sheet) {

  /* Output header row*/

 

var header = [

  'Match Type',

  'Active Keywords',

  'Impressions',

  'Clicks',

  'CTR (%)',

  'Cost'

];

sheet.getRange(1, 1, 1, 6).setValues([header]);

  /* Initialize match type map

  who said we cant filter by match type??? */

  var matchTypeMap = [];                                                                  

   matchTypeMap[0] = {

        matchType: "EXACT",

    numKeywords: 0,

    totalImpressions: 0,

    totalClicks: 0,

    totalCost: 0.0

  };

    matchTypeMap[1] = {

      matchType: "PHRASE",

    numKeywords: 0,

    totalImpressions: 0,

    totalClicks: 0,

    totalCost: 0.0

  };

   matchTypeMap[2] = {

      matchType: "BROAD",

    numKeywords: 0,

    totalImpressions: 0,

    totalClicks: 0,

    totalCost: 0.0

  };

    matchTypeMap[3] = {

      matchType: "BROADMOD",

    numKeywords: 0,

    totalImpressions: 0,

    totalClicks: 0,

    totalCost: 0.0

  };

  /* Compute data...  Use a different variable for date range if necessary.. this one is all time*/

  var keywordIterator = AdWordsApp.keywords()

.forDateRange('ALL_TIME')

.withCondition('Status = ENABLED')

.get();

while (keywordIterator.hasNext()) {

  var keyword = keywordIterator.next();

  var stats = keyword.getStatsFor('ALL_TIME');

  var switchMatchType = keyword.getMatchType();

  switch (switchMatchType) {

               case "EXACT":

                  var data = matchTypeMap[0];

                    break;

         case "PHRASE":

                 var data = matchTypeMap[1];

                    break;

         case "BROAD":

                  if (keyword.getText().charAt(0) == "+") {

                              var data = matchTypeMap[3];

                            }

                              else {var data = matchTypeMap[2];

                              }

                              break;

  }                    

        if (data) {

    data.numKeywords++;

    data.totalImpressions += stats.getImpressions();

    data.totalClicks += stats.getClicks();

    data.totalCost += stats.getCost();

  }

 

  }

  /* Output data to spreadsheet*/

  var rows = [];

for (var key in matchTypeMap) {

  var ctr = 0;

  var cost = 0.0;

  if (matchTypeMap[key].numKeywords > 0) {

    ctr = (matchTypeMap[key].totalClicks /

      matchTypeMap[key].totalImpressions) * 100;

  }

  var row = [

    matchTypeMap[key].matchType,

    matchTypeMap[key].numKeywords,

    matchTypeMap[key].totalImpressions,

    matchTypeMap[key].totalClicks,

    ctr.toFixed(2),

    matchTypeMap[key].totalCost];

  rows.push(row);

}

sheet.getRange(2, 1, rows.length, 6).setValues(rows);

}

//  Outputs average position related data to the spreadsheet

// Below this point there is no additional modifications that I made and code

// is identical to that in tutorial

function outputPositionData(sheet) {

  // Output header row

  headerRow = [];

var header = [

  'Avg Position',

  'Active Keywords',

  'Impressions',

  'Clicks',

  'CTR (%)',

  'Cost'

];

headerRow.push(header);

sheet.getRange(14, 1, 1, 6).setValues(headerRow);

  // Initialize

  var positionMap = [];

for (i = 1; i <= 12; i++) {

  positionMap[i] = {

    numKeywords: 0,

    totalImpressions: 0,

    totalClicks: 0,

    totalCost: 0.0

  };

}

  // Compute data

  var keywordIterator = AdWordsApp.keywords()

.forDateRange('ALL_TIME')

.withCondition('Status = ENABLED')

.withCondition('Impressions > 0')

.get();

while (keywordIterator.hasNext()) {

  var keyword = keywordIterator.next();

  var stats = keyword.getStatsFor('ALL_TIME');

  if (stats.getAveragePosition() <= 11) {

    var data = positionMap[Math.ceil(stats.getAveragePosition())];

  } else {

      // All positions greater than 11

      var data = positionMap[12];

  }

  data.numKeywords++;

  data.totalImpressions += stats.getImpressions();

  data.totalClicks += stats.getClicks();

  data.totalCost += stats.getCost();

}

  // Output data to spreadsheet

  var rows = [];

for (var key in positionMap) {

  var ctr = 0;

  var cost = 0.0;

  if (positionMap[key].numKeywords > 0) {

    ctr = (positionMap[key].totalClicks /

      positionMap[key].totalImpressions) * 100;

  }

  var row = [

    key <= 11 ? key - 1 + ' to ' + key : '>11',

    positionMap[key].numKeywords,

    positionMap[key].totalImpressions,

    positionMap[key].totalClicks,

    ctr.toFixed(2),

    positionMap[key].totalCost

  ];

  rows.push(row);

}

sheet.getRange(15, 1, rows.length, 6).setValues(rows);

}

[PPC Gorilla]

Thanks Oleg!

How do I put in my own spreadsheet? I want to change this so that it is based off a blank spreadsheet. But when I copy-paste one of my own URL's into the SPREADSHEET_URL constant, I get the following error:

TypeError: Cannot call method "getRange" of null. (Line xx)

[Oleg Amirdzhanov]

I would do it the following way... Try running the script as is first time. This will automatically create a copy of my sheet in your Google script cloud with URL automatically generated, which you can scrap from the log and substitute mine with yours.  I'm pretty sure this will work, but will test it just to be sure Monday morning and will update if there are any surprises...

Thanks!

_oleg