Script to find and label the best performing ads in each ad group, and label/pause the others.

Pause Losing Ads.js

/**
 *
 * Pause Losing Ads & Ad Rotate Analysis
 *
 * This script finds the best ad in each ad group (subject to thresholds) and 
 * calculates the performance you could have got if the impressions that went to
 * losing ads went to the winning ads instead.
 * Labels the winning and losing ads, and (optionally) pauses the losers.
 *
 * Version: 2.0
 * Google AdWords Script maintained on brainlabsdigital.com
 *
 **/


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options

var spreadsheetUrl = "https://docs.google.com/YOUR-SPREADSHEET-URL-HERE";
// The URL of the Google Doc the results will be put into.

var campaignNameDoesNotContain = [];
// Use this if you want to exclude some campaigns.
// For example ["Display"] would ignore any campaigns with 'Display' in the name,
// while ["Display","Shopping"] would ignore any campaigns with 'Display' or
// 'Shopping' in the name.
// Leave as [] to not exclude any campaigns.

var campaignNameContains = [];
// Use this if you only want to look at some campaigns.
// For example ["Brand"] would only look at campaigns with 'Brand' in the name,
// while ["Brand","Generic"] would only look at campaigns with 'Brand' or 'Generic'
// in the name.
// Leave as [] to include all campaigns.

var ignorePausedCampaigns = true;
// Set this to true to only look at currently active campaigns.
// Set to false to also include campaigns that are currently paused.

var ignorePausedAdGroups = true;
// Set this to true to only look at currently active ad groups.
// Set to false to also include ad groups that are currently paused.

var conversionMetrics = ["Conversions"];
// The spreadsheet will report clicks, impressions and cost and this conversion metric(s).
// Allowed fields: "Conversions", "ConversionValue"
// If you'd like more than one separate with a comma, eg ["Conversions", "ConversionValue"]

// These settings are to set which metric determines an ad is the best in its group
var winningMetricName = "CTR"; // The name used in the output spreadsheet
var winningMetricMultiplier = "Clicks";
var winningMetricDivisor = "Impressions";
// The metric will be calculated by dividing winningMetricMultiplier by winningMetricDivisor
// eg to compare conversions per impression, winningMetricMultiplier is "Conversions"
// and winningMetricDivisor is "Impressions".
// winningMetricMultiplier and winningMetricDivisor can be any of "Impressions", "Clicks",
// "Cost", "Conversions", "ConversionValue"

var impressionThreshold = 1000;
var clickThreshold = 0;
// This is used to weed out low traffic ad groups - only ads with this many
// impressions and clicks are considered

var labelAds = true;
var labelTextForWinningAds = "Winning Ad";
var labelTextForLosingAds = "Losing Ad";

var pauseLosingAds = false;


var dateRange = "LAST_30_DAYS";
// This is the date range for the ad's performance.
// Don't set it too short or there won't be enough traffic!
// Possible values: "LAST_30_DAYS", "LAST_MONTH", "THIS_MONTH"
// or custom date ranges formatted "yyyymmdd, yyyymmdd"

var currencySymbol = "£";
// Used for formatting currencies in the output spreadsheet.


//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Functions

function main() {

  // Check the spreadsheet URL works
  var spreadsheet = checkSpreadsheet(spreadsheetUrl, "the spreadsheet");

  // Get the campaign IDs (based on campaignNameDoesNotContain, campaignNameContains and ignorePausedCampaigns)
  var campaignIds = getCampaignIds();

  // Check all the required metrics are listed, and make sure they are trimmed and correctly capitalised
  var allowedFields = ["Conversions", "ConversionValue", "Impressions", "Clicks", "Cost"];
  var metricsToReport = ["Impressions", "Clicks", "Cost"].concat(conversionMetrics);
  var metrics = checkFieldNames(allowedFields, metricsToReport);
  winningMetricMultiplier = checkFieldNames(allowedFields, [winningMetricMultiplier])[0];
  winningMetricDivisor = checkFieldNames(allowedFields, [winningMetricDivisor])[0];
  if (metrics.indexOf(winningMetricMultiplier) == -1) {
    metrics.push(winningMetricMultiplier);
  }
  if (metrics.indexOf(winningMetricDivisor) == -1) {
    metrics.push(winningMetricDivisor);
  }

  // Create the necessary labels
  getOrCreateLabelId(labelTextForWinningAds);
  getOrCreateLabelId(labelTextForLosingAds);

  // Run the analysis
  rotateAnalysis(campaignIds, spreadsheet, metrics);

  Logger.log("Finished rotate analysis.");
}


// Check the spreadsheet URL has been entered, and that it works
function checkSpreadsheet(spreadsheetUrl, spreadsheetName) {
  if (spreadsheetUrl.replace(/[AEIOU]/g,"X") == "https://docs.google.com/YXXR-SPRXXDSHXXT-XRL-HXRX") {
    throw("Problem with " + spreadsheetName + " URL: make sure you've replaced the default with a valid spreadsheet URL.");
  }
  try {
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    return spreadsheet;
  } catch (e) {
    throw("Problem with " + spreadsheetName + " URL: '" + e + "'");
  }
}


// Get the IDs of campaigns which match the given options
function getCampaignIds() {
  var whereStatement = "WHERE ";
  var whereStatementsArray = [];
  var campaignIds = [];

  if (ignorePausedCampaigns) {
    whereStatement += "CampaignStatus = ENABLED ";
  } else {
    whereStatement += "CampaignStatus IN ['ENABLED','PAUSED'] ";
  }

  for (var i=0; i<campaignNameDoesNotContain.length; i++) {
    whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "' ";
  }

  if (campaignNameContains.length == 0) {
    whereStatementsArray = [whereStatement];
  } else {
    for (var i=0; i<campaignNameContains.length; i++) {
      whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g,'\\\"') + '" ');
    }
  }

  for (var i=0; i<whereStatementsArray.length; i++) {
    var adTextReport = AdWordsApp.report(
      "SELECT CampaignId " +
      "FROM   CAMPAIGN_PERFORMANCE_REPORT " +
      whereStatementsArray[i] +
      "DURING LAST_30_DAYS");

    var rows = adTextReport.rows();
    while (rows.hasNext()) {
      var row = rows.next();
      campaignIds.push(row['CampaignId']);
    }
  }

  if (campaignIds.length == 0) {
    throw("No campaigns found with the given settings.");
  }

  Logger.log(campaignIds.length + " campaigns found");
  return campaignIds;
}


// Verify that all field names are valid, and return a list of them with the
// correct capitalisation
function checkFieldNames(allowedFields, givenFields) {
  var allowedFieldsLowerCase = allowedFields.map(function (str){return str.toLowerCase()});
  var wantedFields = [];
  var unrecognisedFields = [];
  for (var i=0; i<givenFields.length; i++) {
    var fieldIndex = allowedFieldsLowerCase.indexOf(givenFields[i].toLowerCase().replace(" ","").trim());
    if(fieldIndex === -1){
      unrecognisedFields.push(fields[i]);
    } else {
      wantedFields.push(allowedFields[fieldIndex]);
    }
  }

  if (unrecognisedFields.length > 0) {
    throw unrecognisedFields.length + " field(s) not recognised: '" + unrecognisedFields.join("', '") + 
      "'. Please choose from '" + allowedFields.join("', '") + "'.";
  }

  return wantedFields;
}


// This returns an array of formats corresponding to the given array of metrics
// to use when formatting a Google Sheet
function getFormats(metrics) {
  // Note: while these formats use , to separate thousands and . as a decimal marker,
  // the way they are shown in the Google Sheet depends on the Sheet's locale setting.
  var metricFormats = {};
  metricFormats["Conversions"] = "#,###,##0";
  metricFormats["ConversionValue"] = currencySymbol + "#,###,##0.00";
  metricFormats["Impressions"] = "#,###,##0";
  metricFormats["Clicks"] = "#,###,##0";
  metricFormats["Cost"] = currencySymbol + "#,###,##0.00";

  var formats = [];
  for (var i=0; i<metrics.length; i++) {
    if (metricFormats[metrics[i]] == undefined) {
      formats.push("#,###,##0");
    } else {
      formats.push(metricFormats[metrics[i]]);
    }
  }

  return formats;
}


// Prints an array of rows into the spreadsheet
// and formats them all according to formatRow
function printFormattedRows(sheet, rows, formatRow) {
  try {
    if (rows.length == 0) {
      Logger.log("Nothing to output in " + sheet.getName());
      return;
    }

    var lastRow = sheet.getLastRow();

    sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+rows.length)
                   + "C" + (rows[0].length) ).setValues(rows);
    sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+rows.length)
                   + "C" + (rows[0].length) ).clearFormat();
    Logger.log("Printed " + rows.length + " rows in " + sheet.getName());

    if (formatRow.length > 0) {
      var formatRows = [];
      for (var i=0; i<rows.length; i++) {
        formatRows.push(formatRow);
      }

      sheet.getRange("R" + (lastRow + 1) + "C1:R" + (lastRow+formatRows.length)
                     + "C" + (formatRows[0].length) ).setNumberFormats(formatRows);
    }

  } catch (e) {
    Logger.log("Printing rows in " + sheet.getName() + " failed: " + e);

    if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
      try {
        sheet.appendRow(["Not enough space to write the data."]);
      } catch (e2) {
        Logger.log("Error writing 'not enough space' message: " + e2);
      }
    }
  }
}


// Prints an array of rows into the spreadsheet
// with the given title, headers and format
function printRowsWithTitle(sheet, title, headers, rows, formatRow) {
  try {
    sheet.getRange("R" + (sheet.getLastRow()+2) + "C1").setValue(title);
    sheet.getRange("R" + sheet.getLastRow() + "C1").clearFormat();
    sheet.getRange("R" + sheet.getLastRow() + "C1").setFontWeight("bold");

    if (rows.length == 0) {
      sheet.appendRow(["No data"]);
      sheet.getRange("R" + sheet.getLastRow() + "C1").clearFormat();
      Logger.log("Nothing to output for '" + title + "'");
      return;
    }

    if (headers.length > 0) {
      sheet.appendRow(headers);
      sheet.getRange("R" + sheet.getLastRow() + "C1:R" + sheet.getLastRow() + "C" + headers.length).clearFormat();
      sheet.getRange("R" + sheet.getLastRow() + "C1:R" + sheet.getLastRow() + "C" + headers.length).setFontStyle("italic");
    }

    printFormattedRows(sheet, rows, formatRow);

  } catch (e) {
    Logger.log("Printing rows '" + title + "' failed: " + e);

    if (e == "Exception: This action would increase the number of cells in the worksheet above the limit of 2000000 cells.") {
      try {
        sheet.appendRow(["Not enough space to write the data."]);
      } catch (e2) {
        Logger.log("Error writing 'not enough space' message: " + e2);
      }
    }
  }
}


// Goes through each ad group to perform the rotation analysis
function rotateAnalysis(campaignIds, spreadsheet, metrics) {
  // First we find the ad groups which have multiple ads with enough impressions.
  // We need to consider mobile preferred ads separately to desktop ones
  var groupsWithImpressions = {};
  var groupsWithMultipleAds = {};
  var groupsWithMobileImpressions = {};
  var groupsWithMultipleMobileAds = {};
  if (ignorePausedAdGroups) {
    var groupStatus = "AND AdGroupStatus = ENABLED ";
  } else {
    var groupStatus = "AND AdGroupStatus IN ['ENABLED','PAUSED'] ";
  }

  var adReport = AdWordsApp.report(
    'SELECT CampaignName, AdGroupId, AdGroupName, DevicePreference ' +
    'FROM   AD_PERFORMANCE_REPORT ' +
    'WHERE CampaignId IN [' + campaignIds.join(',') + '] ' + groupStatus +
    'AND Status = ENABLED AND Impressions >= ' + impressionThreshold + ' ' +
    'AND Clicks >= ' + clickThreshold + ' ' +
    'AND CombinedApprovalStatus != DISAPPROVED ' +
    'AND AdType IN [TEXT_AD, EXPANDED_TEXT_AD] ' +
        "AND AdGroupName = 'Performance Marketing Agency London' " + //!!!
    'DURING ' + dateRange);
  var rows = adReport.rows();
  while (rows.hasNext()) {
    var row = rows.next();
    if (row["DevicePreference"] != "30001") { // This is not mobile preferred ads
      if (groupsWithImpressions[row["AdGroupId"]] == undefined) {
        // This happens if it is the first time an ad group has appeared in the report
        groupsWithImpressions[row["AdGroupId"]] = true;
      } else if (groupsWithMultipleAds[row["AdGroupId"]] == undefined) {
        // This happens the second time an ad group appears in the report
        groupsWithMultipleAds[row["AdGroupId"]] = true;
      }
    } else { // These are mobile preferred ads. 
      // They are treated separately, as mobiles will perform differently to other devices
      if (groupsWithMobileImpressions[row["AdGroupId"]] == undefined) {
        groupsWithMobileImpressions[row["AdGroupId"]] = true;
      } else if (groupsWithMultipleMobileAds[row["AdGroupId"]] == undefined) {
        groupsWithMultipleMobileAds[row["AdGroupId"]] = true;
      }
    }
  }


  var groupsWithMultipleAdsCount = Object.keys(groupsWithMultipleAds).length;
  var groupsWithMultipleMobileAdsCount = Object.keys(groupsWithMultipleMobileAds).length;

  if (groupsWithMultipleAdsCount == 0 && groupsWithMultipleMobileAdsCount == 0) {
    Logger.log("No ad groups with more than 1 ad above the impression threshold of " + impressionThreshold + " in the given date range."); 
    return;
  }

  Logger.log("Found " + groupsWithMultipleAdsCount + " ad groups with multiple all device ads, and " +
             groupsWithMultipleMobileAdsCount + " ad groups with multiple mobile preferred ads");

  // Make all of the required sheets
  var sheetNames = ["Overview","All Device Ads"];
  if (groupsWithMultipleMobileAdsCount > 0) {
    sheetNames.push("Mobile Preferred Ads");
  }
  var sheets = {};
  for (var i=0; i<sheetNames.length; i++) {
    sheets[sheetNames[i]] = spreadsheet.getSheetByName(sheetNames[i]);
    if (sheets[sheetNames[i]] === null) {
      sheets[sheetNames[i]] = spreadsheet.insertSheet(sheetNames[i],i);
    } else {
      sheets[sheetNames[i]].clear();
    }
  }

  if (groupsWithMultipleMobileAdsCount > 0) {
    printDataIncludingMobileAds(sheets, metrics, groupsWithMultipleAds, groupsWithMultipleMobileAds);
  } else {
    printAllDeviceData(sheets, metrics, groupsWithMultipleAds);
  }

}


function printDataIncludingMobileAds(sheets, metrics, groupsWithMultipleAds, groupsWithMultipleMobileAds) {
  // Now we go through the all device ads which have multiple ads over the impression threshold
  // and record the stats for all ads
  var headers = ["Campaign","Ad Group","Number of Ads","Winning Ad","Winning Ad ID","Winning " + 
                 winningMetricName].concat(metrics.map(function(a){return "Actual " + a.replace(/([A-Z])/g, ' $&').trim();}), ["Actual " + winningMetricName],
                                           metrics.map(function(a){return "Possible " + a.replace(/([A-Z])/g, ' $&').trim();}));

  sheets["All Device Ads"].appendRow(headers);
  sheets["All Device Ads"].getRange("R" + sheets["All Device Ads"].getLastRow() + "C1:R" + sheets["All Device Ads"].getLastRow() + "C" + headers.length).setFontWeight("bold");
  var metricFormats = getFormats(metrics);
  var formatRow = ["#,###,##0","#,###,##0","#,###,##0","#,###,##0","#","#,##0.00%"].concat(metricFormats, ["#,##0.00%"], metricFormats);

  var initialisedArray = []; // This will be copied whenever we need an array to store metrics in
  for (var i = 0; i < metrics.length; i++) {
    initialisedArray[i] = 0;
  }
  var allDeviceTotals = {};
  allDeviceTotals['Actual'] = initialisedArray.slice();
  allDeviceTotals['Possible'] = initialisedArray.slice();

  var devicePreference = '!= "30001"';
  var allAdGroupIds = Object.keys(groupsWithMultipleAds);

  // We get the data in batches of ad groups, so we don't run out of memory
  // and because reports can only take 10,000 IDs at once.
  var batchSize = 10000;
  for (var i=0; i<allAdGroupIds.length; i+=batchSize) {
    // This function outputs the ad group level data, and returns a running total
    // of the stats and possible stats
    allDeviceTotals = calculateAdGroupPotential(sheets["All Device Ads"], allAdGroupIds.slice(i,i+batchSize), metrics, formatRow, devicePreference, allDeviceTotals);
  }

  // Sort the new rows in the spreadsheet
  if (sheets["All Device Ads"].getLastRow() > 1) {
    sheets["All Device Ads"].getRange(2,1,sheets["All Device Ads"].getLastRow()-1,headers.length).sort({column: 7, ascending: false});
  }

  // Do the same for mobile preferred ads
  sheets["Mobile Preferred Ads"].appendRow(headers);
  sheets["Mobile Preferred Ads"].getRange("R" + sheets["Mobile Preferred Ads"].getLastRow() + "C1:R" + sheets["Mobile Preferred Ads"].getLastRow() + "C" + headers.length).setFontWeight("bold");
  var mobilePreferredTotals = {};
  mobilePreferredTotals['Actual'] = initialisedArray.slice();
  mobilePreferredTotals['Possible'] = initialisedArray.slice();
  var devicePreference = '= "30001"';
  var allAdGroupIds = Object.keys(groupsWithMultipleMobileAds);
  for (var i=0; i<allAdGroupIds.length; i+=batchSize) {
    mobilePreferredTotals = calculateAdGroupPotential(sheets["Mobile Preferred Ads"], allAdGroupIds.slice(i,i+batchSize), metrics, formatRow, devicePreference, mobilePreferredTotals);
  }
  if (sheets["Mobile Preferred Ads"].getLastRow() > 1) {
    sheets["Mobile Preferred Ads"].getRange(2,1,sheets["Mobile Preferred Ads"].getLastRow()-1,headers.length).sort({column: 7, ascending: false});
  }

  // Total the data
  var total = {Actual: [], Possible: [], Difference: [], Percent: []};
  for (var i = 0; i < metrics.length; i++) {
    total['Actual'][i] = allDeviceTotals['Actual'][i] + mobilePreferredTotals['Actual'][i];
    total['Possible'][i] = allDeviceTotals['Possible'][i] + mobilePreferredTotals['Possible'][i];
  }

  // Calculate the average winning metric
  var data = [allDeviceTotals['Actual'], allDeviceTotals['Possible'], mobilePreferredTotals['Actual'], mobilePreferredTotals['Possible'], total['Actual'], total['Possible']];
  var multiplierIndex = metrics.indexOf(winningMetricMultiplier);
  var divisorIndex = metrics.indexOf(winningMetricDivisor);
  for (var i=0; i<data.length; i++) {
    if (data[i][divisorIndex] != 0) {
      data[i].push(data[i][multiplierIndex] / data[i][divisorIndex]);
    } else {
      data[i].push("-");
    }
  }

  // Calculate differences between actual and possible, and output an overview
  sheets["Overview"].appendRow(["Ad Rotate Analysis"]);
  sheets["Overview"].getRange("R1C1").setFontWeight("bold");
  var headers = [""].concat(metrics.map(function(a){return a.replace(/([A-Z])/g, ' $&').trim();}), [winningMetricName]);
  var rowNames = ["Actual", "Possible", "Difference", "Percent"];
  var formatRow = ["#,###,##0"].concat(metricFormats, ["#,##0.00%"]);

  allDeviceTotals['Difference'] = [];
  allDeviceTotals['Percent'] = [];
  mobilePreferredTotals['Difference'] = [];
  mobilePreferredTotals['Percent'] = [];
  var data = [total, allDeviceTotals, mobilePreferredTotals];
  var dataNames = ["Total", "All Device Ads", "Mobile Preferred Ads"];
  for (var j = 0; j < data.length; j++) {
    for (var i = 0; i < metrics.length + 1; i++) {
      if (data[j]['Possible'][i] == "-" || data[j]['Actual'][i] == "-") {
        data[j]['Difference'][i] = "-";
        data[j]['Percent'][i] = "-";
      } else {
        data[j]['Difference'][i] = data[j]['Possible'][i] - data[j]['Actual'][i];
        if (data[j]['Actual'][i] != 0) {
          data[j]['Percent'][i] = (data[j]['Difference'][i] / data[j]['Actual'][i]);
        } else {
          data[j]['Percent'][i] = "-";
        } 
      }
    }

    var totalRows = [];
    for (var r=0; r<rowNames.length; r++) {
      totalRows.push([rowNames[r]].concat(data[j][rowNames[r]]));
    }
    printRowsWithTitle(sheets["Overview"], dataNames[j], headers, totalRows, formatRow);
    sheets["Overview"].getRange("R" + sheets["Overview"].getLastRow() + "C1:R" + sheets["Overview"].getLastRow()
                                + "C" + (headers.length) ).setNumberFormat("#,###,##0.00%"); // Format the percent line as percentages
  }

}


function printAllDeviceData(sheets, metrics, groupsWithMultipleAds) {
  // Now we go through the all device ads which have multiple ads over the impression threshold
  // and record the stats for all ads
  var headers = ["Campaign","Ad Group","Number of Ads","Winning Ad","Winning Ad ID","Winning " + 
                 winningMetricName].concat(metrics.map(function(a){return "Actual " + a.replace(/([A-Z])/g, ' $&').trim();}), ["Actual " + winningMetricName],
                                           metrics.map(function(a){return "Possible " + a.replace(/([A-Z])/g, ' $&').trim();}));

  sheets["All Device Ads"].appendRow(headers);
  sheets["All Device Ads"].getRange("R" + sheets["All Device Ads"].getLastRow() + "C1:R" + sheets["All Device Ads"].getLastRow() + "C" + headers.length).setFontWeight("bold");
  var metricFormats = getFormats(metrics);
  var formatRow = ["#,###,##0","#,###,##0","#,###,##0","#,###,##0","#","#,##0.00%"].concat(metricFormats, ["#,##0.00%"], metricFormats);

  var initialisedArray = []; // This will be copied whenever we need an array to store metrics in
  for (var i = 0; i < metrics.length; i++) {
    initialisedArray[i] = 0;
  }
  var allDeviceTotals = {};
  allDeviceTotals['Actual'] = initialisedArray.slice();
  allDeviceTotals['Possible'] = initialisedArray.slice();

  var devicePreference = '!= "30001"';
  var allAdGroupIds = Object.keys(groupsWithMultipleAds);

  // We get the data in batches of ad groups, so we don't run out of memory
  // and because reports can only take 10,000 IDs at once.
  var batchSize = 10000;
  for (var i=0; i<allAdGroupIds.length; i+=batchSize) {
    // This function outputs the ad group level data, and returns a running total
    // of the stats and possible stats
    allDeviceTotals = calculateAdGroupPotential(sheets["All Device Ads"], allAdGroupIds.slice(i,i+batchSize), metrics, formatRow, devicePreference, allDeviceTotals);
  }

  // Sort the new rows in the spreadsheet
  if (sheets["All Device Ads"].getLastRow() > 1) {
    sheets["All Device Ads"].getRange(2,1,sheets["All Device Ads"].getLastRow()-1,headers.length).sort({column: 7, ascending: false});
  }

  // Calculate the average winning metric
  var data = [allDeviceTotals['Actual'], allDeviceTotals['Possible']];
  var multiplierIndex = metrics.indexOf(winningMetricMultiplier);
  var divisorIndex = metrics.indexOf(winningMetricDivisor);
  for (var i=0; i<data.length; i++) {
    if (data[i][divisorIndex] != 0) {
      data[i].push(data[i][multiplierIndex] / data[i][divisorIndex]);
    } else {
      data[i].push("-");
    }
  }

  // Calculate differences between actual and possible, and output an overview
  sheets["Overview"].appendRow(["Ad Rotate Analysis"]);
  sheets["Overview"].getRange("R1C1").setFontWeight("bold");
  var headers = [""].concat(metrics.map(function(a){return a.replace(/([A-Z])/g, ' $&').trim();}), [winningMetricName]);
  var rowNames = ["Actual", "Possible", "Difference", "Percent"];
  var formatRow = ["#,###,##0"].concat(metricFormats, ["#,##0.00%"]);

  allDeviceTotals['Difference'] = [];
  allDeviceTotals['Percent'] = [];
  var data = [allDeviceTotals];
  var dataNames = ["Total"];
  for (var j = 0; j < data.length; j++) {
    for (var i = 0; i < metrics.length + 1; i++) {
      if (data[j]['Possible'][i] == "-" || data[j]['Actual'][i] == "-") {
        data[j]['Difference'][i] = "-";
        data[j]['Percent'][i] = "-";
      } else {
        data[j]['Difference'][i] = data[j]['Possible'][i] - data[j]['Actual'][i];
        if (data[j]['Actual'][i] != 0) {
          data[j]['Percent'][i] = (data[j]['Difference'][i] / data[j]['Actual'][i]);
        } else {
          data[j]['Percent'][i] = "-";
        } 
      }
    }
    var totalRows = [];
    for (var r=0; r<rowNames.length; r++) {
      totalRows.push([rowNames[r]].concat(data[j][rowNames[r]]));
    }
    printRowsWithTitle(sheets["Overview"], dataNames[j], headers, totalRows, formatRow);
    sheets["Overview"].getRange("R" + sheets["Overview"].getLastRow() + "C1:R" + sheets["Overview"].getLastRow()
                                + "C" + (headers.length) ).setNumberFormat("#,###,##0.00%"); // Format the percent line as percentages
  }

}


// This goes through the given ad groups, finds their stats and possible stats and
// writes them to the spreadsheet. It also uses the runningTotals object to keep a
// running total of the stats for the Overview sheet.
function calculateAdGroupPotential(sheet, adGroupIds, metrics, formatRow, devicePreference, runningTotals) {
  var initialisedArray = []; // This will be copied whenever we need an array to store metrics in
  for (var i = 0; i < metrics.length; i++) {
    initialisedArray[i] = 0;
  }

  var groupData = {};
  var impressionIndex = metrics.indexOf('Impressions');
  var multiplierIndex = metrics.indexOf(winningMetricMultiplier);
  var divisorIndex = metrics.indexOf(winningMetricDivisor);
  var adReport = AdWordsApp.report(
    'SELECT CampaignName, AdGroupId, AdGroupName, Id, HeadlinePart1, HeadlinePart2, Description, Headline, Description1, Description2, ' + metrics.join(', ') + ' ' +
    'FROM   AD_PERFORMANCE_REPORT ' +
    'WHERE  Status = ENABLED AND Impressions >= ' + impressionThreshold + ' ' +
    'AND Clicks >= ' + clickThreshold + ' ' +
    'AND CombinedApprovalStatus != DISAPPROVED ' +
    'AND AdGroupId IN [' + adGroupIds.join(",") + '] ' +
    'AND DevicePreference ' + devicePreference + ' ' +
    'AND AdType IN [TEXT_AD, EXPANDED_TEXT_AD] ' +
    'DURING ' + dateRange);

  var rows = adReport.rows();
  while (rows.hasNext()) {
    var row = rows.next();

    if (groupData[row['AdGroupId']] == undefined) {
      // If this is the first time we've come across this ad group,
      // record its details
      groupData[row['AdGroupId']] = {};
      groupData[row['AdGroupId']]['Names'] = [row['CampaignName'],row['AdGroupName']];
      groupData[row['AdGroupId']]['Ads'] = [];
      groupData[row['AdGroupId']]['Total'] = initialisedArray.slice();
    }

    var adStats = [];
    for (var i = 0; i < metrics.length; i++) {
      var metric = parseFloat(row[metrics[i]].replace(/,/g, ""));
      adStats[i] = metric;
      groupData[row['AdGroupId']]['Total'][i] += metric;
    }

    if (row['Headline'] == "") {
      var adDetails = [row['HeadlinePart1'],row['HeadlinePart2'],row['Description']];
    } else {
      var adDetails = [row['Headline'],row['Description1'],row['Description2']];
    }

    if (adStats[divisorIndex] != 0) {
      var winningMetric = adStats[multiplierIndex] / adStats[divisorIndex];
    } else {
      var winningMetric = 0;
    }

    groupData[row['AdGroupId']]['Ads'].push([winningMetric,adStats,adDetails, row['Id']]);
  }

  var outputRows = []; // This will be written to a spreadsheet at the end of the function.

  var winningAdIds = [];
  var losingAdIds = [];

  // Go through the ad groups, find the best ad
  for (var j=0; j<adGroupIds.length; j++) {

    var groupPotential = initialisedArray.slice();
    // This will be the stats you could have got if the impressions went to the best ad
    // rather than the other ads

    var adsToCompare = groupData[adGroupIds[j]]['Ads']; // This is the list of ads

    // Order the ads from best to worst
    adsToCompare.sort(function(a, b){return b[0]-a[0];});

    if (adsToCompare[0][0] == 0) {
      // This means the best ad's winning metric has a value of 0.
      // So we skip this ad group.
      continue;
    }

    if (adsToCompare[0][0] == adsToCompare[1][0]) {
      // This means there are at least 2 ads tied for highest winning metric value
      // rather than a single winner. The best stats are the average of the stats
      // of these ads
      var bestStats = adsToCompare[0][1];
      var notBestAdsIndex = null;
      for (var a=1; a<adsToCompare.length; a++) {
        if (adsToCompare[0][0] == adsToCompare[a][0]) {
          for (var i = 0; i < metrics.length; i++) {
            bestStats[i] += adsToCompare[a][1][i];
          }
        } else {
          notBestAdsIndex = a;
          break;
        }
      }
      if (notBestAdsIndex == null) { // All ads were tied, so no ads lost
        continue;
      }

      // Record the best ads' IDs
      for (var a=0; a<notBestAdsIndex; a++) {
        winningAdIds.push([adGroupIds[j], adsToCompare[a][3]]);
      }

      var groupPotential = bestStats.slice();
      for (var n=notBestAdsIndex; n<adsToCompare.length; n++) {
        for (var i = 0; i < metrics.length; i++) {
          groupPotential[i] += adsToCompare[n][1][impressionIndex] * bestStats[i] / bestStats[impressionIndex];
        }
        losingAdIds.push([adGroupIds[j], adsToCompare[n][3]]);
      }

    } else {
      // There's one ad which is the best.
      var bestStats = adsToCompare[0][1];
      winningAdIds.push([adGroupIds[j], adsToCompare[0][3]]);
      for (var n=0; n<adsToCompare.length; n++) {
        for (var i = 0; i < metrics.length; i++) {
          groupPotential[i] += adsToCompare[n][1][impressionIndex] * bestStats[i] / bestStats[impressionIndex];
        }
        if (n>0) {
          losingAdIds.push([adGroupIds[j], adsToCompare[n][3]]);
        }
      }
    }

    // Update the running totals
    for (var i = 0; i < metrics.length; i++) {
      runningTotals['Possible'][i] += groupPotential[i];
      runningTotals['Actual'][i] += groupData[adGroupIds[j]]['Total'][i];
    }

    // Make a row for the output spreadsheet
    var line = groupData[adGroupIds[j]]['Names'].concat([adsToCompare.length, adsToCompare[0][2].join(" / "), adsToCompare[0][3], adsToCompare[0][0]], groupData[adGroupIds[j]]['Total']);
    if (groupData[adGroupIds[j]]['Total'][divisorIndex] != 0) {
      var avgWinningMetric = groupData[adGroupIds[j]]['Total'][multiplierIndex] / groupData[adGroupIds[j]]['Total'][divisorIndex];
    } else {
      var avgWinningMetric = 0;
    }
    line.push(avgWinningMetric);
    line = line.concat(groupPotential);
    outputRows.push(line);
  }

  if (labelAds) {
    applyLabelsToAds(winningAdIds, labelTextForWinningAds);
    Logger.log("Labelled " + winningAdIds.length + " winning ads");
    applyLabelsToAds(losingAdIds, labelTextForLosingAds);
    Logger.log("Labelled " + losingAdIds.length + " losing ads");
  }

  if (pauseLosingAds) {
    pauseAds(losingAdIds);
    Logger.log("Paused " + losingAdIds.length + " losing ads");
  }

  // Output the ad groups' data
  printFormattedRows(sheet, outputRows, formatRow);

  return runningTotals;
}


// Applies a label to all ads with the given ids
function applyLabelsToAds(ids, labelName) {
  for (var i=0; i<ids.length; i += 5000) {
    var iterator = AdWordsApp.ads()
    .withIds(ids.slice(i, i+5000))
    .get();
    while (iterator.hasNext()){
      var ad = iterator.next();
      ad.applyLabel(labelName);
    }
  }
}


// Pauses all ads with the given ids
function pauseAds(ids) {
  for (var i=0; i<ids.length; i += 5000) {
    var iterator = AdWordsApp.ads()
    .withIds(ids.slice(i, i+5000))
    .get();
    while (iterator.hasNext()){
      var ad = iterator.next();
      ad.pause();
    }
  }
}


// Create the label if it doesn't exist, and return its ID.
// (Returns a dummy ID if the label does not exist and this is a preview run,
// because we can't create or apply the label)
function getOrCreateLabelId(labelName) {
  var labels = AdWordsApp.labels().withCondition("Name = '" + labelName + "'").get();

  if (!labels.hasNext()) {
    AdWordsApp.createLabel(labelName);
    labels = AdWordsApp.labels().withCondition("Name = '" + labelName + "'").get();
  }

  if (AdWordsApp.getExecutionInfo().isPreview() && !labels.hasNext()) {
    var labelId = 0;
  } else {
    var labelId = labels.next().getId();
  }

  return labelId;
}

378 campaigns found
No ad groups with more than 1 ad above the impression threshold of 1 in the given date range.
Finished rotate analysis.

Can someone help, I have set impression threshold to 1.

Same issue here. It find no Ad Groups.

378 campaigns found
No ad groups with more than 1 ad above the impression threshold of 1 in the given date range.
Finished rotate analysis.

Can someone help, I have set impression threshold to 1.

I found the issue Line 328, they forgot to remove:
"AND AdGroupName = 'Performance Marketing Agency London' " + //!!!

Just comment it out and it works.