/** * * Heat Map Creation Tool - with Devices * * This script calculates the smoothed average performance of each hour of each day * of the week, and outputs this into a heat map and graph in a Google Sheet. This * can be done for all data and for device data. It also suggests ad schedules and * device bid adjustments based on conversion rates. * * 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. // Copy the template at https://docs.google.com/spreadsheets/d/19OsCHG5JE_TqHHCZK1HNXyHizrJZ0_iT6dpqUOzvRB4/edit#gid=1022438191 // so you have the correct formatting and charts set up. var dateRanges = ["2016-09-01,2016-10-31"]; // The start and end date of the date range for your data // You can have multiple ranges, eg ["2016-06-01,2016-07-31","2016-09-01,2016-10-31"] // would get data from June, July, September and October 2015. // Format for each range is "yyyy-mm-dd,yyyy-mm-dd" (where the first date is the // start of the range and the second is the end). var ignoreDates = []; // List any single days that are within your date range but whose data you do not // want to use in calculations, for instance if they had atypical performance or // there were technical issues with your site. // eg ["2016-02-14","2016-03-27"] would mean data from Valentine's Day and Easter // 2016 would be ignored. // Format for each day is "yyyy-mm-dd" // Leave as [] if unwanted. var fields = ["Impressions", "Clicks", "Conversions"]; // Make heat maps of these fields. // Allowed values: "Impressions", "Clicks", "Cost", "Conversions", // "ConversionValue" var calculatedFields = ["Clicks/Impressions","Conversions/Clicks"]; // Make heat maps of a stat calculated by dividing one field by another. // For example "Clicks/Impressions" will give the average clicks divided by the // average impressions (ie the CTR). // Allowed fields: "Impressions", "Clicks", "Cost", "Conversions", // "ConversionValue" var devices = ["Mobile"]; // Make heat maps and bid modifier suggestions for these devices // Allowed fields: "Mobile", "Tablet", "Desktop" var suggestAdSchedules = true; // If true, the script will suggest hourly ad schedules, based on conversion rate. var suggestDeviceBidModifiers = true; // If true, the script will suggest bid modifiers for the devices specified above, // based on the devices' conversion rates. var baseDeviceModifiersOnBiddingMultiplier = true; // If true, then the device bid modifiers given will be adjusted to take into // account the suggested ad schedules. // For example suppose that at a certain hour device bids should be increased by // 30%, and the suggested ad schedule for that hour is 10%. // If this is false, the the device modifier will be given as 30%. // If this is true, then the device modifier will be given as 18%, because when // this and the 10% ad schedules are applied this increases the bid by 30%. 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","Competitors"] would ignore any campaigns with 'display' or // 'competitors' in the name. Case insensitive. // 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. Case insensitive. // Leave as [] to include all campaigns. var ignorePausedCampaigns = true; // Set this to true to only look at currently active campaigns. // Set to false to include campaigns that had impressions but are currently paused. ////////////////////////////////////////////////////////////////////////////// // Advanced settings. var smoothingWindow = [-2, -1, 0, 1, 2 ]; var smoothingWeight = [0.25, 0.75, 1, 0.75, 0.25]; // The weights used for smoothing. // The smoothingWindow gives the relative hour (eg 0 means the current hour, // -2 means 2 hours before the current hour) and the smoothingWeight gives the // weighting for that hour. var minBidMultiplierSuggestion = -0.35; var maxBidMultiplierSuggestion = 0.35; // The minimum and maximum for the suggested bidding multipliers. ////////////////////////////////////////////////////////////////////////////// function main() { // Check the spreadsheet works. var spreadsheet = checkSpreadsheet(spreadsheetUrl, "the spreadsheet"); // Check the field names are correct, and get a list with the correct capitalisation var allowedFields = ["Conversions", "ConversionValue", "Impressions", "Clicks", "Cost"]; var fieldsToCheck = []; for (var i=0; i -1) { continue; } if (dailyData[row["Date"]] == undefined) { dailyData[row["Date"]] = {}; dailyData[row["Date"]]["Day"] = row["DayOfWeek"]; for (var h=0; h<24; h++) { dailyData[row["Date"]][h+""] = {}; for (var f=0; f 23) { if (dailyData[tomorrow] != undefined) { totalWeight += smoothingWeight[w]; smoothedTotal += smoothingWeight[w] * dailyData[tomorrow][(h + smoothingWindow[w] - 24)][fieldsIncDevice[f]]; } } else { totalWeight += smoothingWeight[w]; smoothedTotal += smoothingWeight[w] * dailyData[date][(h + smoothingWindow[w])][fieldsIncDevice[f]]; } } if (totalWeight != 0) { smoothedData[day][h][fieldsIncDevice[f]] += smoothedTotal / totalWeight; } } } } // end for dailyData Logger.log("Collected daily data."); // Calculate the averages from the smoothed data var hourlyAvg = {}; var totalConversions = 0; var totalClicks = 0; var deviceClicks = {}; var deviceConversions = {}; for (var i=0; i maxBidMultiplierSuggestion) { multiplier = maxBidMultiplierSuggestion; } else if (multiplier < minBidMultiplierSuggestion) { multiplier = minBidMultiplierSuggestion; } hourlyAvg[dayNames[d]][h+""]["AdSchedules"] = multiplier; } } } // Device level bid modifiers if (suggestDeviceBidModifiers) { var deviceConvRate = {}; for (var i=0; i maxBidMultiplierSuggestion) { modifier = maxBidMultiplierSuggestion; } else if (modifier < minBidMultiplierSuggestion) { modifier = minBidMultiplierSuggestion; } hourlyAvg[dayNames[d]][h+""][devices[i] + "BidModifiers"] = modifier; } } } } } } // end if suggestAdSchedules or suggestDeviceBidModifiers Logger.log("Averaged and smoothed data."); // Make the heat maps on the spreadsheet var sheet0 = spreadsheet.getSheets()[0]; var calculatedFieldNames = calculatedFields.map(function (arr){return arr.join("/")}); var baseFields = checkFieldNames(allowedFields, fields, "", true).concat(calculatedFieldNames); var allFieldNames = baseFields.slice(); for (var i=0; i -1) { var components = allFieldNames[f].split("/"); var multiplierIsMoney = (components[0] == "Cost" || components[0] == "ConversionValue"); var divisorIsMoney = (components[1] == "Cost" || components[1] == "ConversionValue"); if ((!multiplierIsMoney && !divisorIsMoney) || (multiplierIsMoney && divisorIsMoney)) { // If neither component is monetary, or both components are, then the result is a percentage format = "#,##0.00%"; } } if (allFieldNames[f] == "AdSchedules" || allFieldNames[f].substr(-12) == "BidModifiers") { format = "#,##0.00%"; } sheet.getRange(4, 2, sheetData.length, sheetData[0].length).setNumberFormat(format); // Update the chart title var charts = sheet.getCharts(); if (sheet.getCharts().length === 0) { Logger.log("Warning: chart missing from the " + fieldName + " sheet."); } else { var chart = charts[0]; chart = chart.modify().setOption('title', fieldName).build(); sheet.updateChart(chart); } } Logger.log("Posted data to spreadsheet."); Logger.log("Finished."); } // 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); // Checks if you can edit the spreadsheet var sheet = spreadsheet.getSheets()[0]; var sheetName = sheet.getName(); sheet.setName(sheetName); 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 0) { throw unrecognisedFields.length + " field(s) not recognised in '" + souceName + "': '" + unrecognisedFields.join("', '") + "'. Please choose from '" + allowedFields.join("', '") + "'."; } return wantedFields; }