Pause underperforming ads with this updated AdWords script

admin

If you are running tests on your ads but don’t know how to interpret the results, contributor Daniel Gilbert has an updated AdWords script to help you pick which ads are performing best.

It’s no secret that split (A/B) testing is an essential part of a successful AdWords campaign.

I’m a big advocate of scientific testing, and it is a core part of how my company, Brainlabs, approaches paid search campaigns.

Some time ago, I shared our A/B testing script, which enables you to set up control and experiment campaigns that are alternately paused and resumed, and then emails you when the test has significant results for click-through rate (CTR) or conversion rate (CR).

However, there’s no point in running tests if you don’t know how to interpret the results. That’s why we made our Rotating Ad Copy script, which helps you choose which ads from your test have the best performance.

We’ve now updated the script to make it even better!

What’s new?

There are three main updates to the Rotating Ad Copy script:

  • It works better with the expanded text format — better late than never!
  • You can automatically label ads as winners or losers, allowing you to easily check which ones are underperforming.
  • The script can now also pause the losing ads, so you can be assured that you’re not wasting any more impressions.

and have lost track of all the tests going at the moment.

/**
*
* 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;
}
view rawPause Losing Ads.js hosted with ❤ by GitHub