Related Links

Tuesday, September 1, 2015

AdWords Script for Keyword Performance Reporting

Here is the script code we need to use in shared library to run it for reporting -

Reference - Google Support



// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = 'email@example.com';
// URL of the default spreadsheet template. This should be a copy of http://goo.gl/cULxUX
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
/**
 * This script computes a keyword performance report
 * and outputs it to a Google spreadsheet. The spreadsheet
 * url is logged and emailed.
 */
function main() {
  Logger.log('Using template spreadsheet - %s.', SPREADSHEET_URL);
  var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
  Logger.log('Generated new reporting spreadsheet %s based on the template ' +
      'spreadsheet. The reporting data will be populated here.',
      spreadsheet.getUrl());

  var sheet = spreadsheet.getSheetByName('Report');
  sheet.getRange(1, 2, 1, 1).setValue('Date');
  sheet.getRange(1, 3, 1, 1).setValue(new Date());
  spreadsheet.getRangeByName('account_id').setValue(AdWordsApp.currentAccount().
      getCustomerId());
  outputQualityScoreData(sheet);
  outputPositionData(sheet);
  Logger.log('Keyword performance report available at\n' +
      spreadsheet.getUrl());
  if (RECIPIENT_EMAIL) {
    MailApp.sendEmail(RECIPIENT_EMAIL,
      'Keyword Performance 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) {
  return SpreadsheetApp.openByUrl(spreadsheetUrl).copy(
      'Keyword Performance Report ' + new Date());
}
/**
 * Outputs Quality score related data to the spreadsheet
 * @param {Sheet} sheet The sheet to output to.
 */
function outputQualityScoreData(sheet) {
  // Output header row
  var header = [
    'Quality Score',
    'Num Keywords',
    'Impressions',
    'Clicks',
    'CTR (%)',
    'Cost'
  ];
  sheet.getRange(3, 2, 1, 6).setValues([header]);

  // Initialize
  var qualityScoreMap = [];
  for (i = 1; i <= 10; i++) {
    qualityScoreMap[i] = {
      numKeywords: 0,
      totalImpressions: 0,
      totalClicks: 0,
      totalCost: 0.0
    };
  }

  // Compute data
  var keywordIterator = AdWordsApp.keywords()
      .forDateRange('LAST_WEEK')
      .withCondition('Impressions > 0')
      .get();
  while (keywordIterator.hasNext()) {
    var keyword = keywordIterator.next();
    var stats = keyword.getStatsFor('LAST_WEEK');
    var data = qualityScoreMap[keyword.getQualityScore()];
    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 qualityScoreMap) {
    var ctr = 0;
    var cost = 0.0;
    if (qualityScoreMap[key].numKeywords > 0) {
      ctr = (qualityScoreMap[key].totalClicks /
        qualityScoreMap[key].totalImpressions) * 100;
    }
    var row = [
      key,
      qualityScoreMap[key].numKeywords,
      qualityScoreMap[key].totalImpressions,
      qualityScoreMap[key].totalClicks,
      ctr.toFixed(2),
      qualityScoreMap[key].totalCost];
    rows.push(row);
  }
  sheet.getRange(4, 2, rows.length, 6).setValues(rows);
}
/**
 * Outputs average position related data to the spreadsheet.
 * @param {Sheet} sheet The sheet to output to.
 */
function outputPositionData(sheet) {
  // Output header row
  headerRow = [];
  var header = [
    'Avg Position',
    'Num Keywords',
    'Impressions',
    'Clicks',
    'CTR (%)',
    'Cost'
  ];
  headerRow.push(header);
  sheet.getRange(16, 2, 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('LAST_WEEK')
      .withCondition('Impressions > 0')
      .get();
  while (keywordIterator.hasNext()) {
    var keyword = keywordIterator.next();
    var stats = keyword.getStatsFor('LAST_WEEK');
    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(17, 2, rows.length, 6).setValues(rows);
}
Note - Need to change email ID (with your email ID for access) and URL with  https://docs.google.com/spreadsheets/d/1NH4_Xh7OpmKWlxZ-SucQHmSqf5lGGw0Va-GAogQkOD8/edit

Allow access to it.

Then take a preview and log it. Now you will be able to get shareable google drive url.