Want to see where your ad tests are finished, and what traffic you’re losing by letting them run on? Columnist Daniel Gilbert of Brainlabs has a script for that.
Ad testing is massively important.
But you knew that already. You’ve set up all your ad groups with multiple ad copy variants.
But the thing is, you don’t want to test for its own sake. You can’t just write a bunch of ads and leave them. At some point, you have to make decisions — keep the winners and lose the losers. If you keep showing losers, you’re missing valuable clicks and conversions.
But if you’ve got a big account, it’s easy to lose track of all the tests going on. And if you’ve just inherited an account, you have no idea what tests were set up in the first place!
And that’s where the latest AdWords script from the team at Brainlabs comes in…
An AdWords script for finding your best ads
The script below goes through each ad group and finds the best ad. Then, it works out what could have happened if the other ads had been paused and all their impressions had gone to the winning ad. That tells you how much you’re missing out on (and which ad groups should be priorities when setting up new tests).
To do this, the script assumes the same number of total impressions for the ad group, but with the average performance — CTR, CPC and so on — of the winning ad (or ads, if there’s a tie).
Obviously, these are just simple assumptions. Realistically, the performance would vary because those impressions may have been of different quality (and even the number of impressions could have been different, because this ad/keyword combination could have a higher Quality Score and be eligible for more searches). However, it’s enough to give you a rough estimate.
You get to choose which metric makes an ad a winner — CTR is an obvious choice, but if you have enough converting traffic, then you can use conversions per impression, or value per impression, or something entirely different.
Note that there’s no statistical significance checking in the script — it just checks against click and impression thresholds — so if there are ad groups where the performance is close, you may want to check for yourself whether the winner really is the winner.
Oh, and the script looks at mobile-preferred ads separately from all device ads. After all, the mobile-preferred ads should be picking up all of the mobile traffic. If we treated mobile ads like regular ones, it would be inaccurate, as mobile traffic tends to be very different from desktop and tablet traffic.
To try this out for yourself, make a blank Google Sheet and take note of the URL. Then copy the script below, paste it into a new script in your account and change a few settings at the top:
- Put your blank spreadsheet’s URL into spreadsheetUrl. This is where the results will go.
- If you only want to look at ads in a few campaigns, you can enter phrases contained in campaign names which you would like to include in campaignNameContains. Or, if there are campaigns you don’t want to look at, you can list phrases contained in campaign names which you would like to exclude in campaignNameDoesNotContain. Or leave these both as [] to look at your whole account.
- If ignorePausedCampaigns is true, then the script will only look at currently active campaigns. Set them to false if you want to look at currently paused campaigns as well as the active ones.
- Similarly, if ignorePausedAdGroups is true, then the script will only look at currently active ad groups, and if it’s false, then paused groups will be included.
- conversionMetrics lists which conversion metrics will appear in the spreadsheet. Your options for this are “ConvertedClicks,” “Conversions” and “ConversionValue” — you can pick of many of these as you like.
- You have to pick which metric is used to determine the winning ad. Put that metric’s name in winningMetricName — this is used to name columns in the spreadsheet.
- The winning metric is calculated by dividing one metric (winningMetricMultiplier) by another (winningMetricDivisor). For example, if the winning metric is CTR, the winningMetricMultiplier is “Clicks,” and the winningMetricDivisor is “Impressions.”
- The script assumes the the largest winning metric is the best. You want the highest CTR or the highest conversion per impression.
- impressionThreshold and clickThreshold are the minimum number of impressions and clicks ads need to be considered.
- The script will look at the performance of the ads over a time period specified by dateRange.
- You can use predefined date ranges like “LAST_30_DAYS,” “LAST_MONTH” or “THIS_MONTH.”
- Or you can make your own with the format “yyyymmdd, yyyymmdd” — the first date is the start and the second is the end. For example “20160101, 20160630” would cover the 1st of January to the 30th of June 2016.
- currencySymbol will be stuck in front of any currency values (i.e., costs and conversion values) in the spreadsheet.
Once you’ve authorized and run the script, you should get a report with three worksheets. There’ll be an overview sheet, a bit like this:
Additionally, there will be All Device Ads and Mobile-Preferred Ads sheets, which give you ad-group-level detail (including which ad was the best):
[Click to enlarge]
Some notes:
- Only enabled ads that aren’t disapproved are considered.
- Ad groups where all ads are tied with the same winning metric are ignored. For example if all winning metric values are 0, the ad group will be ignored.
- The totals shown will not be the same as your account’s totals. This only reports the performance of ad groups with winning and non-winning ads.
- If the winningMetricMultiplier or winningMetricDivisor are not already one of the reported metrics, they will also be reported on. For example, if conversionMetrics was “Conversions,” but the winningMetricMultiplier was “ConversionValue,” then both conversions and conversion value would be reported in the spreadsheet.
| /** | |
| * | |
| * 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. | |
| * | |
| * Version: 1.1 | |
| * Updated 2016-10-11: removed ‘ConvertedClicks’ | |
| * Updated 2017-01-05: changed ‘CreativeApprovalStatus’ to ‘CombinedApprovalStatus’ | |
| * 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 witha 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 dateRange = “20160101, 20160630”; | |
| // 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”); | |
| // Make all of the required sheets | |
| var sheetNames = [“Overview”,“All Device Ads”,“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(); | |
| } | |
| } | |
| // 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); | |
| } | |
| // Run the analysis | |
| rotateAnalysis(campaignIds, sheets, 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, sheets, 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 adGroupReport = 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 ‘ + | |
| ‘DURING ‘ + dateRange); | |
| var rows = adGroupReport.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; | |
| } | |
| } | |
| } | |
| if (Object.keys(groupsWithMultipleAds).length == 0 && Object.keys(groupsWithMultipleMobileAds).length == 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 “ + Object.keys(groupsWithMultipleAds).length + ” ad groups with multiple all device ads, and “ + | |
| Object.keys(groupsWithMultipleMobileAds).length + ” ad groups with multiple mobile preferred ads”); | |
| // 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 | |
| } | |
| } | |
| // This goes thorugh 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, 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 + ‘ ‘ + | |
| ‘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; | |
| } | |
| 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. | |
| // 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; | |
| } | |
| 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]; | |
| } | |
| } | |
| } else { | |
| // There’s one ad which is the best. | |
| var bestStats = adsToCompare[0][1]; | |
| 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]; | |
| } | |
| } | |
| } | |
| // 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); | |
| } | |
| // Output the ad groups’ data | |
| printFormattedRows(sheet, outputRows, formatRow); | |
| return runningTotals; | |
| } |