AdWords Scripts - Evaluate Ad Text Line Performance

evaluate-ad-text-line-performance.js

// Comma-separated list of recipients. Comment out to not send any emails.
var RECIPIENT_EMAIL = 'YOUR_EMAIL';

// URL of the default spreadsheet template. This should be a copy of http://goo.gl/pxaZio
var SPREADSHEET_URL = 'SPREADSHEET_URL';

/**
 * This script computes an Ad performance report
 * and outputs it to a Google spreadsheet
 */
function main() {
  var spreadsheet = copySpreadsheet(SPREADSHEET_URL);
  var headlineSheet = spreadsheet.getSheetByName('Headline');
  headlineSheet.getRange(1, 2, 1, 1).setValue('Date');
  headlineSheet.getRange(1, 3, 1, 1).setValue(new Date());
  var descriptionLine1Sheet = spreadsheet.getSheetByName('Description Line 1');
  descriptionLine1Sheet.getRange(1, 2, 1, 1).setValue('Date');
  descriptionLine1Sheet.getRange(1, 3, 1, 1).setValue(new Date());
  var descriptionLine2Sheet = spreadsheet.getSheetByName('Description Line 2');
  descriptionLine2Sheet.getRange(1, 2, 1, 1).setValue('Date');
  descriptionLine2Sheet.getRange(1, 3, 1, 1).setValue(new Date());
  spreadsheet.getRangeByName('account_id_headline').setValue(
      AdWordsApp.currentAccount().getCustomerId());
  spreadsheet.getRangeByName('account_id_description_1').setValue(
      AdWordsApp.currentAccount().getCustomerId());
  spreadsheet.getRangeByName('account_id_description_2').setValue(
      AdWordsApp.currentAccount().getCustomerId());

  outputSegmentation(headlineSheet, 'Headline', function(ad) {
    return ad.getHeadline();
  });
  outputSegmentation(descriptionLine1Sheet, 'Description Line 1', function(ad) {
    return ad.getDescription1();
  });
  outputSegmentation(descriptionLine2Sheet, 'Description Line 2', function(ad) {
    return ad.getDescription2();
  });
  
  Logger.log('Ad performance report available at\n' + spreadsheet.getUrl());
  if (RECIPIENT_EMAIL) {
    MailApp.sendEmail(RECIPIENT_EMAIL,
      'Ad 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(
      'Ad Performance Report ' + new Date());
}

/**
 * Generates statistical data for this segment.
 * @param {Sheet} sheet Sheet to write to.
 * @param {string} segmentName The Name of this segment for the header row.
 * @param {function(AdWordsApp.Ad): string} segmentFunc Function that returns
 *        a string used to segment the results by.
 */
function outputSegmentation(sheet, segmentName, segmentFunc) {
  // Output header row
  var rows = [];
  var header = [
    segmentName,
    'Num Ads',
    'Impressions',
    'Clicks',
    'CTR (%)',
    'Cost',
    'Conversions',
    'Conv. Rate (%)',
    'Cost per Conv.'
  ];
  rows.push(header);

  var segmentMap = {};

  // Compute data
  var adIterator = AdWordsApp.ads()
      .forDateRange('LAST_14_DAYS')
      .withCondition('Impressions > 0').get();
  while (adIterator.hasNext()) {
    var ad = adIterator.next();
    var stats = ad.getStatsFor('LAST_14_DAYS');
    var segment = segmentFunc(ad);
    if (!segmentMap[segment]) {
      segmentMap[segment] = {
        numAds: 0,
        totalImpressions: 0,
        totalClicks: 0,
        totalCost: 0.0,
        totalConversions: 0,
      };
    }
    var data = segmentMap[segment];
    data.numAds++;
    data.totalImpressions += stats.getImpressions();
    data.totalClicks += stats.getClicks();
    data.totalCost += stats.getCost();
    data.totalConversions += stats.getConvertedClicks();
  }

  // Write data to our rows.
  for (var key in segmentMap) {
    if (segmentMap.hasOwnProperty(key)) {
      var ctr = 0;
      var convRate = 0;
      var costPerConv = 0;
      if (segmentMap[key].numAds > 0) {
        ctr = (segmentMap[key].totalClicks /
          segmentMap[key].totalImpressions) * 100;
      }
      if (segmentMap[key].totalConversions > 0) {
         convRate = (segmentMap[key].totalConversions /
          segmentMap[key].totalClicks) * 100;
         costPerConv = (segmentMap[key].totalCost /
          segmentMap[key].totalConversions);
      }
      var row = [
        key,
        segmentMap[key].numAds,
        segmentMap[key].totalImpressions,
        segmentMap[key].totalClicks,
        ctr.toFixed(2),
        segmentMap[key].totalCost,
        segmentMap[key].totalConversions,
        convRate.toFixed(2),
        costPerConv.toFixed(2)];
      rows.push(row);
    }
  }
  sheet.getRange(3, 2, rows.length, 9).setValues(rows);
}