Script Code Keyword Performance Report

Adwords keyword performance report

Visit Link: https://developers.google.com/google-ads/scripts/docs/solutions/keyword-performance 

Template SpreadSheet URL :-

https://goo.gl/q4DFrt


/**
 * 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
// https://goo.gl/q4DFrt
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';

/**
 * The size of the quality score map to output.
 * DO NOT change this value.
 */
var QUALITY_SCORE_MAP_SIZE = 10;

/**
 * The size of the position map to output.
 * DO NOT change this value.
 */
var POSITION_MAP_SIZE = 12;

/**
 * This script computes a keyword performance report
 * and outputs it to a Google spreadsheet. The spreadsheet
 * url is logged and emailed.
 */
function main() {
  validateEmail(RECIPIENT_EMAIL)

;
  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());

  spreadsheet.getRangeByName('date_label').setValue('Date');
  spreadsheet.getRangeByName('date_value').setValue(new Date());
  spreadsheet.getRangeByName('account_id')
      .setValue(AdsApp.currentAccount().getCustomerId());
  outputQualityScoreData(spreadsheet);
  outputPositionData(spreadsheet);
  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) {
  var spreadsheet = validateAndGetSpreadsheet(spreadsheetUrl)
                        .copy(
                            'Keyword Performance Report - ' +
                            getDateStringInTimeZone('MMM dd, yyyy HH:mm:ss z'));

  // Make sure the spreadsheet is using the account's timezone.
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());
  return spreadsheet;
}

/**
 * Gets an iterator for keywords that had impressions last week.
 * @return {Iterator} an iterator of the keywords
 */
function getLastWeekKeywordsWithPositiveImpressions() {
  return AdsApp.keywords()
      .forDateRange('LAST_WEEK')
      .withCondition('Impressions > 0')
      .get();
}

/**
 * Outputs Quality score related data to the spreadsheet
 * @param {Spreadsheet} spreadsheet The sheet to output to.
 */
function outputQualityScoreData(spreadsheet) {
  // Output header row
  var header = [
    'Quality Score', 'Num Keywords', 'Impressions', 'Clicks', 'CTR (%)', 'Cost'
  ];
  spreadsheet.getRangeByName('quality_score_headings').setValues([header]);

  // Initialize
  var qualityScoreMap = getEmptyStatMap(QUALITY_SCORE_MAP_SIZE);

  // Compute data
  computeQualityData(
      getLastWeekKeywordsWithPositiveImpressions(), qualityScoreMap);

  // Output data to spreadsheet
  var rows = [];
  for (var key in qualityScoreMap) {
    var ctr = calculateCtr(qualityScoreMap[key]);
    var row = [
      key, qualityScoreMap[key].numKeywords,
      qualityScoreMap[key].totalImpressions, qualityScoreMap[key].totalClicks,
      ctr.toFixed(2), qualityScoreMap[key].totalCost
    ];
    rows.push(row);
  }
  spreadsheet.getRangeByName('quality_score_body').setValues(rows);
}

/**
 * Outputs average position related data to the spreadsheet.
 * @param {Spreadsheet} spreadsheet The spreadsheet to output to.
 */
function outputPositionData(spreadsheet) {
  // Output header row
  headerRow = [];
  var header = [
    'Avg Position', 'Num Keywords', 'Impressions', 'Clicks', 'CTR (%)', 'Cost'
  ];
  headerRow.push(header);
  spreadsheet.getRangeByName('position_headings').setValues(headerRow);

  // Initialize
  var positionMap = getEmptyStatMap(POSITION_MAP_SIZE);

  // Compute data
  computePositionData(
      getLastWeekKeywordsWithPositiveImpressions(), positionMap);

  // Output data to spreadsheet
  var rows = [];
  for (var key in positionMap) {
    var ctr = calculateCtr(positionMap[key]);
    var mapSizeLessOne = POSITION_MAP_SIZE - 1;
    var row = [
      key <= mapSizeLessOne ? key - 1 + ' to ' + key : '>' + mapSizeLessOne,
      positionMap[key].numKeywords, positionMap[key].totalImpressions,
      positionMap[key].totalClicks, ctr.toFixed(2), positionMap[key].totalCost
    ];
    rows.push(row);
  }
  spreadsheet.getRangeByName('position_body').setValues(rows);
}

/**
 * Calculates the click through rate given an entry from a map.
 * @param {object} mapEntry - an entry from the  map
 * @return {number} the click through rate
 */
function calculateCtr(mapEntry) {
  var ctr = 0;
  if (mapEntry.numKeywords > 0) {
    ctr = (mapEntry.totalClicks / mapEntry.totalImpressions) * 100;
  }
  return ctr;
}

/**
 * Gets an empty stat map.
 * @param {number} size - the number of entries in the stat map.
 * @return {array} the empty quality stat map.
 */
function getEmptyStatMap(size) {
  var qualityScoreMap = [];
  for (i = 1; i <= size; i++) {
    qualityScoreMap[i] =
        {numKeywords: 0, totalImpressions: 0, totalClicks: 0, totalCost: 0.0};
  }
  return qualityScoreMap;
}

/**
 * Uses the given keyword iterator and populates the given quality score map.
 * @param {Iterator} keywordIterator - the keywords to use for getting scores.
 * @param {array} qualityScoreMap - the score map to fill with keyword data.
 */
function computeQualityData(keywordIterator, qualityScoreMap) {
  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();
    }
  }
}

/**
 * Uses the given keyword iterator and populates the given position map.
 * @param {Iterator} keywordIterator - the keywords to use for getting scores.
 * @param {array} positionMap - the map to fill with keyword data.
 */
function computePositionData(keywordIterator, positionMap) {
  while (keywordIterator.hasNext()) {
    var keyword = keywordIterator.next();
    var stats = keyword.getStatsFor('LAST_WEEK');
    var index =
        Math.min(Math.ceil(stats.getAveragePosition()), POSITION_MAP_SIZE);
    var data = positionMap[index];
    data.numKeywords++;
    data.totalImpressions += stats.getImpressions();
    data.totalClicks += stats.getClicks();
    data.totalCost += stats.getCost();
  }
}

/**
 * Produces a formatted string representing a given date in a given time zone.
 *
 * @param {string} format A format specifier for the string to be produced.
 * @param {date} date A date object. Defaults to the current date.
 * @param {string} timeZone A time zone. Defaults to the account's time zone.
 * @return {string} A formatted string of the given date in the given time zone.
 */
function getDateStringInTimeZone(format, date, timeZone) {
  date = date || new Date();
  timeZone = timeZone || AdsApp.currentAccount().getTimeZone();
  return Utilities.formatDate(date, timeZone, format);
}

/**
 * Validates the provided email and throws a descriptive error if the user
 * has not changed the email from the default fake address.
 *
 * @param {string} email The email address.
 * @throws {Error} If the email is the default fake address.
 */
function validateEmail(email) {
  if (email == 'email@example.com') {
    throw new Error('Please use a valid email address.');
  }
}

/**
 * Validates the provided spreadsheet URL
 * to make sure that it's set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error(
        'Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);

}