Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to create smoothed heat maps from your AdWords data
/**
*
* Heat Map Creation Tool
*
* 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. It
* also makes suggested bid adjustments based on the conversion rate.
*
* Version: 1.1
* Updated 2016-10-11: removed 'ConvertedClicks'.
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
function main() {
//////////////////////////////////////////////////////////////////////////////
// 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 = ["2015-08-01,2015-10-31"];
// The start and end date of the date range for your data
// You can have multiple ranges, eg ["2015-06-01,2015-07-31","2015-09-01,2015-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 ["2014-12-25","2014-11-28"] would mean data from Christmas and Black Friday
// 2014 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 campaignNameContains = "";
// Use this if you only want to look at some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if not wanted.
var campaignNameDoesNotContain = "";
// Use this if you want to exclude some campaigns
// such as campaigns with names containing 'Brand' or 'Shopping'.
// Leave as "" if not wanted.
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 maxBidMultiplierSuggestion = 0.35;
var minBidMultiplierSuggestion = -0.35;
// The maximum and minimum for the suggested bidding multipliers.
//////////////////////////////////////////////////////////////////////////////
// Check the spreadsheet works.
try {
var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
} catch (e) {
throw "Problem with the spreadsheet URL, please check you have copied your URL in correctly. '" + e + "'";
}
// Check the field names are correct
var allowedFields = ["Conversions", "ConversionValue", "Impressions", "Clicks", "Cost"];
var allowedFields_lowerCase = allowedFields.map(function (str){return str.toLowerCase()});
var unrecognisedFields = [];
for (var i=0; i<fields.length; i++) {
var fieldIndex = allowedFields_lowerCase.indexOf(fields[i].toLowerCase().replace(" ","").trim());
if(fieldIndex === -1){
unrecognisedFields.push(fields[i]);
}
fields[i] = allowedFields[fieldIndex];
}
var calculatedFieldComponents = [];
var unrecognisedCalculatedFields = [];
for (var i=0; i<calculatedFields.length; i++) {
if (calculatedFields[i].indexOf("/") === -1) {
unrecognisedCalculatedFields.push(calculatedFields[i]);
continue;
}
var components = calculatedFields[i].split("/");
var toUse = [];
for (var j=0; j<components.length; j++){
components[j] = components[j].trim();
var fieldIndex = allowedFields_lowerCase.indexOf(components[j].toLowerCase().replace(" ",""));
if(fieldIndex === -1){
unrecognisedCalculatedFields.push(components[j] + "' in '" + calculatedFields[i]);
}
toUse.push(allowedFields[fieldIndex]);
if (fields.indexOf(allowedFields[fieldIndex]) === -1) {
calculatedFieldComponents.push(allowedFields[fieldIndex]);
}
}
calculatedFields[i] = toUse;
}
if (unrecognisedFields.length > 0 || unrecognisedCalculatedFields.length > 0) {
throw unrecognisedFields.length + " field(s) and " + unrecognisedCalculatedFields.length +
" calculated fields not recognised: '" + unrecognisedFields.concat(unrecognisedCalculatedFields).join("', '") +
"'. Please choose from '" + allowedFields.join("', '") + "'.";
}
var allFields = fields.concat(calculatedFieldComponents);
if (allFields.indexOf("Clicks") < 0) {
allFields.push("Clicks");
}
if (allFields.indexOf("Conversions") < 0) {
allFields.push("Conversions");
}
var dayNames = ["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"];
var dailyData = {}
var numberDays = {};
var smoothedData = {};
// Initialise data
for (var d=0; d<dayNames.length; d++) {
smoothedData[dayNames[d]] = {};
numberDays[dayNames[d]] = 0;
for (var h=0; h<24; h++) {
smoothedData[dayNames[d]][h+""] = {};
for (var f=0; f<allFields.length; f++) {
smoothedData[dayNames[d]][h+""][allFields[f]] = 0;
}
}
}
// Construct the report
if (ignorePausedCampaigns) {
var whereStatements = "CampaignStatus = ENABLED ";
} else {
var whereStatements = "CampaignStatus IN ['ENABLED','PAUSED'] ";
}
if (campaignNameDoesNotContain != "") {
whereStatements += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain + "' ";
}
if (campaignNameContains != "") {
whereStatements += "AND CampaignName CONTAINS_IGNORE_CASE '" + campaignNameContains + "' ";
}
for (var d=0; d<dateRanges.length; d++) {
var report = AdWordsApp.report("SELECT DayOfWeek, Date, HourOfDay, " + allFields.join(", ") + " " +
"FROM CAMPAIGN_PERFORMANCE_REPORT " +
"WHERE " + whereStatements +
"DURING " + dateRanges[d].replace(/-/g,"")
);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
if (ignoreDates.indexOf(row["Date"]) > -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<allFields.length; f++) {
dailyData[row["Date"]][h+""][allFields[f]] = 0;
}
}
}
for (var f=0; f<allFields.length; f++) {
dailyData[row["Date"]][row["HourOfDay"]][allFields[f]] += parseInt(row[allFields[f]].replace(/,/g,""),10);
}
} // end while
}// end for dateRanges
// Daily data is smoothed and totaled for each day of week
for (var date in dailyData) {
var day = dailyData[date]["Day"];
numberDays[day]++;
var dateBits = date.split("-");
var yesterday = new Date(dateBits[0],parseInt(dateBits[1],10)-1,parseInt(dateBits[2],10)-1);
var tomorrow = new Date(dateBits[0],parseInt(dateBits[1],10)-1,parseInt(dateBits[2],10)+1);
yesterday = Utilities.formatDate(yesterday, "UTC", "yyyy-MM-dd");
tomorrow = Utilities.formatDate(tomorrow, "UTC", "yyyy-MM-dd");
for (var h=0; h<24; h++) {
for (var f=0; f<allFields.length; f++) {
var totalWeight = 0;
var smoothedTotal = 0;
for (var w=0; w<smoothingWindow.length; w++) {
if (h + smoothingWindow[w] < 0) {
if (dailyData[yesterday] != undefined) {
totalWeight += smoothingWeight[w];
smoothedTotal += smoothingWeight[w] * dailyData[yesterday][(h + smoothingWindow[w] + 24)][allFields[f]];
}
} else if (h + smoothingWindow[w] > 23) {
if (dailyData[tomorrow] != undefined) {
totalWeight += smoothingWeight[w];
smoothedTotal += smoothingWeight[w] * dailyData[tomorrow][(h + smoothingWindow[w] - 24)][allFields[f]];
}
} else {
totalWeight += smoothingWeight[w];
smoothedTotal += smoothingWeight[w] * dailyData[date][(h + smoothingWindow[w])][allFields[f]];
}
}
if (totalWeight != 0) {
smoothedData[day][h][allFields[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;
for (var d=0; d<dayNames.length; d++) {
hourlyAvg[dayNames[d]] = {};
for (var h=0; h<24; h++) {
hourlyAvg[dayNames[d]][h+""] = {}
if (numberDays[dayNames[d]] == 0) {
for (var f=0; f<allFields.length; f++) {
hourlyAvg[dayNames[d]][h+""][allFields[f]] = "-";
}
continue;
}
for (var f=0; f<allFields.length; f++) {
hourlyAvg[dayNames[d]][h+""][allFields[f]] = smoothedData[dayNames[d]][h+""][allFields[f]]/numberDays[dayNames[d]];
}
for (var c=0; c<calculatedFields.length; c++) {
var multiplier = smoothedData[dayNames[d]][h+""][calculatedFields[c][0]];
var divisor = smoothedData[dayNames[d]][h+""][calculatedFields[c][1]];
if (divisor == 0 || divisor == "-" || multiplier == "-") {
hourlyAvg[dayNames[d]][h+""][calculatedFields[c].join("/")] = "-";
} else {
hourlyAvg[dayNames[d]][h+""][calculatedFields[c].join("/")] = multiplier / divisor;
}
}
// Add up the clicks and conversions, for generating the suggested bidding multipliers
totalConversions += smoothedData[dayNames[d]][h+""]["Conversions"];
totalClicks += smoothedData[dayNames[d]][h+""]["Clicks"];
}
}
// Calculate suggested bidding multipliers based on conversion rate
if (totalClicks == 0) {
var meanConvRate = 0;
} else {
var meanConvRate = totalConversions / totalClicks;
}
for (var d=0; d<dayNames.length; d++) {
for (var h=0; h<24; h++) {
if (meanConvRate == 0 || smoothedData[dayNames[d]][h+""]["Clicks"] == 0) {
hourlyAvg[dayNames[d]][h+""]["BiddingMultipliers"] = "-";
} else {
var convRate = smoothedData[dayNames[d]][h+""]["Conversions"] / smoothedData[dayNames[d]][h+""]["Clicks"];
// The suggested multiplier is generated from the mean.
// It is dampened by taking the square root.
var multiplier = Math.sqrt(convRate/meanConvRate)-1;
if (multiplier > maxBidMultiplierSuggestion) {
multiplier = maxBidMultiplierSuggestion;
} else if (multiplier < minBidMultiplierSuggestion) {
multiplier = minBidMultiplierSuggestion;
}
hourlyAvg[dayNames[d]][h+""]["BiddingMultipliers"] = multiplier;
}
}
}
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 allFieldNames = fields.concat(calculatedFieldNames,["BiddingMultipliers"]);
if (sheet0.getName() == "Template") {
sheet0.setName(allFieldNames[0].replace(/[A-Z\/]/g, function (x){return " " + x;}).trim());
}
for (var f=0; f<allFieldNames.length; f++) {
var fieldName = allFieldNames[f].replace(/[A-Z\/]/g, function (x){return " " + x;}).trim();
var sheet = spreadsheet.getSheetByName(fieldName);
if (sheet == null) {
sheet = sheet0.copyTo(spreadsheet);
sheet.setName(fieldName);
}
sheet.getRange(1, 1).setValue(fieldName);
//Post the heat map data
var sheetData = [];
sheetData.push([""].concat(dayNames)); // The header
var totalValue = 0;
for (var h=0; h<24; h++) {
var rowData = [h];
for (var d=0; d<dayNames.length; d++) {
if (hourlyAvg[dayNames[d]][h+""][allFieldNames[f]] == undefined) {
rowData.push("-");
} else {
rowData.push(hourlyAvg[dayNames[d]][h+""][allFieldNames[f]]);
}
totalValue += hourlyAvg[dayNames[d]][h+""][allFieldNames[f]];
}
sheetData.push(rowData);
}
sheet.getRange(3, 1, sheetData.length, sheetData[0].length).setValues(sheetData);
// Work out which format to use and format the numbers in the heat map
var averageValue = totalValue / (24*7);
if (averageValue < 50) {
var format = "#,##0.00";
} else {
var format = "#,##0";
}
if (allFieldNames[f].indexOf("/") > -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] == "BiddingMultipliers") {
format = "#,##0.00%";
}
var numberFormats = [];
for (var i=0; i<24; i++) {
var formatRow = [];
for (var j=0; j<7; j++) {
formatRow.push(format);
}
numberFormats.push(formatRow);
}
sheet.getRange(4, 2, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats);
// 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.");
}
@jsavitch

This comment has been minimized.

Copy link

commented Nov 19, 2015

Question for you Daniel... I am seeing the clicks and converted clicks data as fractions.
image

Is this because of a lack of data? It doesn't make sense that clicks would happen on either side of any given time period. Did I do something wrong when setting up the script?

@circleddie

This comment has been minimized.

Copy link

commented Jun 3, 2016

var campaignNameDoesNotContain = "";

I realized you're only allowed to populate this one at a time. I'll take a deeper look into making it multiple exclusions.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.