Get an alert when performance in a particular location deviates from normal
/****************************************** | |
* Geo Anomalies Report | |
* @version: 1.0 | |
* @author: Naman Jindal and https://www.optmyzr.com | |
********************************************/ | |
// ------------- USER DEFINED SETTINGS --------------- | |
var LAST_N_WEEKS = 8; // The number of weeks used to calculate the typical performance for the specific day of the week | |
var EMAILS = ['example@example.com']; // Array of emails (comma separated list) | |
var MIN_CLICKS_PER_DAY = 2; // The minimum number of average daily clicks for a location over the historical lookback period. Any location with fewer clicks will not be considered. | |
//Add up to 3 alerts | |
//Metrics available: 'Impressions', 'Clicks','Cost','Conversions', 'ConversionValue','CPA','ROAS' | |
//INC_PCT: Percent increase or decrease. | |
//INC_ABS: Absolute increase or decrease. | |
// Alert will fire only when for the selected metric there is a % change greater than INC_PCT and an absolute change greater than INC_ABS | |
var ALERT_CONFIG = [ | |
{ 'METRIC': 'Impressions', 'INC_PCT': 5, 'INC_ABS': 5 }, | |
{ 'METRIC': 'Clicks', 'INC_PCT': 5, 'INC_ABS': 10 }, | |
{ 'METRIC': 'Cost', 'INC_PCT': 5, 'INC_ABS': 10 }//, | |
//{ 'METRIC': 'CPA', 'INC_PCT': 5, 'INC_ABS': 5 } | |
]; | |
// --------DON'T MAKE CHANGES TO THE CODE BELOW ---------- | |
function main() { | |
var SETTINGS = {}; | |
SETTINGS.COLS = ['Impressions','Clicks','Cost','Conversions','ConversionValue']; | |
SETTINGS.initMap = {}; | |
for(var z in SETTINGS.COLS) { | |
SETTINGS.initMap[SETTINGS.COLS[z]] = 0; | |
} | |
SETTINGS.yesterday = getAdWordsFormattedDate(1, 'yyyyMMdd'); | |
SETTINGS.dow = getAdWordsFormattedDate(1, 'EEEE').toUpperCase(); | |
SETTINGS.ed = getAdWordsFormattedDate(8, 'yyyyMMdd'); | |
SETTINGS.sd = getAdWordsFormattedDate((LAST_N_WEEKS*7+1), 'yyyyMMdd'); | |
SETTINGS.MIN_CLICKS = MIN_CLICKS_PER_DAY*LAST_N_WEEKS; | |
var output = [['Location', 'Location Type']]; | |
for(var z in SETTINGS.COLS) { | |
output[0].push( | |
SETTINGS.COLS[z] + ' (Yesterday)', | |
SETTINGS.COLS[z] + ' (Historical Avg)', | |
SETTINGS.COLS[z] + ' (% Change)', | |
SETTINGS.COLS[z] + ' (Change)' | |
); | |
} | |
var CALCULATED_METRICS = ['CPA', 'ROAS']; | |
for(var z in CALCULATED_METRICS) { | |
output[0].push( | |
CALCULATED_METRICS[z] + ' (Yesterday)', | |
CALCULATED_METRICS[z] + ' (Historical Avg)', | |
CALCULATED_METRICS[z] + ' (% Change)', | |
CALCULATED_METRICS[z] + ' (Change)' | |
); | |
} | |
//country, region, metro, city | |
var locations = { | |
'CountryCriteriaId': 'Country', | |
'RegionCriteriaId': 'Region', | |
'MetroCriteriaId': 'Metro', | |
'CityCriteriaId': 'City' | |
}; | |
var alerts = [['Location Name', 'Type', 'Metric', 'Yesterday', 'Historical Avg', 'Change %', 'Change Abs']]; | |
for(var locationCol in locations) { | |
runForLocationType(output, SETTINGS, locationCol, locations[locationCol], alerts) | |
} | |
var today = getAdWordsFormattedDate(0, 'MMM d, yyyy'); | |
var name = AdsApp.currentAccount().getName() + ' - Geo Anomalies Report ('+today+')'; | |
var ss = SpreadsheetApp.create(name); | |
var URL = ss.getUrl(); | |
Logger.log('Report Url: ' + URL); | |
if(EMAILS && EMAILS.length) { | |
try { ss.addEditors(EMAILS); } catch(ex) {} | |
} | |
var tab = ss.getSheets()[0]; | |
tab.getRange(1,1,output.length,output[0].length).setValues(output).setFontFamily('Calibri'); | |
tab.setFrozenColumns(2); | |
tab.setFrozenRows(1); | |
tab.getRange(1,1,1,tab.getLastColumn()).setWrap(true).setHorizontalAlignment('center').setVerticalAlignment('middle').setFontWeight('bold').setBackground('#efefef'); | |
tab.getRange(2,3,tab.getLastRow(),tab.getLastColumn()-1).setHorizontalAlignment('center').setVerticalAlignment('middle'); | |
var pctCol = 5; | |
while(pctCol <= tab.getLastColumn()) { | |
tab.getRange(1,pctCol,tab.getLastRow(),1).setNumberFormat('0.00%'); | |
pctCol += 4; | |
} | |
if(alerts.length > 1) { | |
sendAlertEmail(alerts, URL); | |
} else { | |
sendEmail(URL); | |
} | |
beacon(); | |
} | |
function runForLocationType(output, SETTINGS, locationCol, locationType, alerts) { | |
var map = {}; | |
var query = [ | |
'SELECT CountryCriteriaId,', locationCol, ',', SETTINGS.COLS.join(','), | |
'FROM GEO_PERFORMANCE_REPORT', | |
'WHERE DayOfWeek = ' + SETTINGS.dow, | |
'AND Clicks >= ' + SETTINGS.MIN_CLICKS, | |
'DURING', SETTINGS.sd+','+SETTINGS.ed | |
].join(' '); | |
var rows = AdsApp.report(query).rows(); | |
while(rows.hasNext()) { | |
var row = rows.next(); | |
if(!map[row[locationCol]]) { | |
map[row[locationCol]] = { | |
'avg': JSON.parse(JSON.stringify(SETTINGS.initMap)), | |
'yesterday': JSON.parse(JSON.stringify(SETTINGS.initMap)) | |
} | |
} | |
for(var z in SETTINGS.COLS) { | |
map[row[locationCol]]['avg'][SETTINGS.COLS[z]] = parseFloat(row[SETTINGS.COLS[z]]); | |
} | |
} | |
var query = [ | |
'SELECT CountryCriteriaId,', locationCol, ',', SETTINGS.COLS.join(','), | |
'FROM GEO_PERFORMANCE_REPORT', | |
'DURING', SETTINGS.yesterday+','+SETTINGS.yesterday | |
].join(' '); | |
var rows = AdsApp.report(query).rows(); | |
while(rows.hasNext()) { | |
var row = rows.next(); | |
if(!map[row[locationCol]]) { | |
map[row[locationCol]] = { | |
'avg': JSON.parse(JSON.stringify(SETTINGS.initMap)), | |
'yesterday': JSON.parse(JSON.stringify(SETTINGS.initMap)) | |
} | |
} | |
for(var z in SETTINGS.COLS) { | |
map[row[locationCol]]['yesterday'][SETTINGS.COLS[z]] = parseFloat(row[SETTINGS.COLS[z]]); | |
} | |
} | |
for(var location in map) { | |
if(location == 'Unspecified') { continue; } | |
var out = [location, locationType]; | |
var rowYest = map[location]['yesterday'], | |
rowHist = map[location]['avg']; | |
for(var metric in rowHist) { | |
rowHist[metric] = round(rowHist[metric]/LAST_N_WEEKS, 2); | |
var change = round(((rowYest[metric] - rowHist[metric]) / rowHist[metric]),4); | |
if(!rowHist[metric] && !rowYest[metric]) { | |
change = 0; | |
} else if(!rowHist[metric]) { | |
change = 1; | |
} | |
var diff = rowYest[metric] - rowHist[metric]; | |
out.push(rowYest[metric], rowHist[metric], change, diff); | |
for(var i in ALERT_CONFIG) { | |
if(ALERT_CONFIG[i]['METRIC'] == metric) { | |
if(100*change >= ALERT_CONFIG[i].INC_PCT && diff >= ALERT_CONFIG[i].INC_ABS) { | |
alerts.push([location, locationType, metric, rowYest[metric], Math.round(rowHist[metric]), round(100*change,2)+'%', Math.round(diff)]); | |
} | |
if(-100*change >= ALERT_CONFIG[i].INC_PCT && -diff >= ALERT_CONFIG[i].INC_ABS) { | |
alerts.push([location, locationType, metric, rowYest[metric], Math.round(rowHist[metric]), round(100*change,2)+'%', Math.round(diff)]); | |
} | |
} | |
} | |
} | |
rowYest.CPA = rowYest['Conversions'] > 0 ? rowYest['Cost'] / rowYest['Conversions'] : 0; | |
rowYest.ROAS = rowYest['Cost'] > 0 ? rowYest['ConversionValue'] / rowYest['Cost'] : 0; | |
rowHist.CPA = rowHist['Conversions'] > 0 ? rowHist['Cost'] / rowHist['Conversions'] : 0; | |
rowHist.ROAS = rowHist['Cost'] > 0 ? rowHist['ConversionValue'] / rowHist['Cost'] : 0; | |
var metric = 'CPA'; | |
var change = round(((rowYest[metric] - rowHist[metric]) / rowHist[metric]),4); | |
if(!rowHist[metric] && !rowYest[metric]) { | |
change = 0; | |
} else if(!rowHist[metric]) { | |
change = 1; | |
} | |
var diff = rowYest[metric] - rowHist[metric]; | |
out.push(rowYest[metric], rowHist[metric], change, diff); | |
for(var i in ALERT_CONFIG) { | |
if(ALERT_CONFIG[i]['METRIC'] == metric) { | |
if(100*change >= ALERT_CONFIG[i].INC_PCT && diff >= ALERT_CONFIG[i].INC_ABS) { | |
alerts.push([location, locationType, metric, rowYest[metric], Math.round(rowHist[metric]), round(100*change,2)+'%', Math.round(diff)]); | |
} | |
if(-100*change >= ALERT_CONFIG[i].INC_PCT && -diff >= ALERT_CONFIG[i].INC_ABS) { | |
alerts.push([location, locationType, metric, rowYest[metric], Math.round(rowHist[metric]), round(100*change,2)+'%', Math.round(diff)]); | |
} | |
} | |
} | |
var metric = 'ROAS'; | |
var change = round(((rowYest[metric] - rowHist[metric]) / rowHist[metric]),4); | |
if(!rowHist[metric] && !rowYest[metric]) { | |
change = 0; | |
} else if(!rowHist[metric]) { | |
change = 1; | |
} | |
var diff = rowYest[metric] - rowHist[metric]; | |
out.push(rowYest[metric], rowHist[metric], change, diff); | |
for(var i in ALERT_CONFIG) { | |
if(ALERT_CONFIG[i]['METRIC'] == metric) { | |
if(100*change >= ALERT_CONFIG[i].INC_PCT && diff >= ALERT_CONFIG[i].INC_ABS) { | |
alerts.push([location, locationType, metric, rowYest[metric], Math.round(rowHist[metric]), round(100*change,2)+'%', Math.round(diff)]); | |
} | |
if(-100*change >= ALERT_CONFIG[i].INC_PCT && -diff >= ALERT_CONFIG[i].INC_ABS) { | |
alerts.push([location, locationType, metric, rowYest[metric], Math.round(rowHist[metric]), round(100*change,2)+'%', Math.round(diff)]); | |
} | |
} | |
} | |
output.push(out); | |
} | |
} | |
function getAdWordsFormattedDate(d, format){ | |
var date = new Date(); | |
date.setDate(date.getDate() - d); | |
return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format); | |
} | |
function round(num, n) { | |
return +(Math.round(num + "e+"+n) + "e-"+n); | |
} | |
function sendAlertEmail(alerts, url) { | |
var count = alerts.length - 1; | |
var sub = AdsApp.currentAccount().getName() + " - Warning: "+ count +" locations have unusual metrics"; | |
var msg = ""; | |
var htmlBody = '<html><head></head><body>The Optmyzr geo anomaly detector script just finished and generated warnings.<br><br>'; | |
htmlBody += ' Account Name: ' + AdWordsApp.currentAccount().getName(); + '<br>'; | |
htmlBody += ' Account ID: ' + AdWordsApp.currentAccount().getCustomerId(); + '<br>'; | |
htmlBody += 'The following locations had unusually low or high metrics yesterday compared to the same day of the week historically:<br><br>'; | |
htmlBody += buildTable(alerts); | |
htmlBody += '<br><br>Up to 20 anomalies are included in the table above.'; | |
htmlBody += '<br><br>You can find the complete dataset we generated here: ' + url; | |
htmlBody += '<br><br>Thanks for using this script from www.optmyzr.com</body></html>'; | |
MailApp.sendEmail(EMAILS.join(','), sub, msg, { 'htmlBody': htmlBody }); | |
} | |
function sendEmail(url) { | |
var sub = AdsApp.currentAccount().getName() + " - Notification: Location performance report is ready"; | |
var msg = ""; | |
var htmlBody = '<html><head></head><body>Hi,<br><br>Location Performance Report is available here: '+url; | |
htmlBody += '<br><br>Thanks</body></html>'; | |
MailApp.sendEmail(EMAILS.join(','), sub, msg, { 'htmlBody': htmlBody }); | |
} | |
function buildTable(reportData) { | |
var table = new HTMLTable(); | |
table.setTableStyle([ | |
'font-family: "Lucida Sans Unicode","Lucida Grande",Sans-Serif;', | |
'font-size: 12px;', | |
'background: #fff;', | |
'margin: 45px;', | |
'width: 480px;', | |
'border-collapse: collapse;', | |
'text-align: left' | |
].join('')); | |
table.setHeaderStyle([ | |
'font-size: 14px;', | |
'font-weight: normal;', | |
'color: #039;', | |
'padding: 10px 8px;', | |
'border-bottom: 2px solid #6678b1' | |
].join('')); | |
table.setCellStyle([ | |
'border-bottom: 1px solid #ccc;', | |
'padding: 4px 6px' | |
].join('')); | |
var header = reportData.shift(); | |
for(var k in header) { | |
table.addHeaderColumn(header[k]); | |
} | |
var rowCount = 0; | |
for(var k in reportData) { | |
rowCount++; | |
table.newRow(); | |
for(var j in reportData[k]){ | |
table.addCell(reportData[k][j]); | |
} | |
if(rowCount>20) break; | |
} | |
return table.toString(); | |
} | |
function HTMLTable() { | |
this.headers = []; | |
this.columnStyle = {}; | |
this.body = []; | |
this.currentRow = 0; | |
this.tableStyle; | |
this.headerStyle; | |
this.cellStyle; | |
this.addHeaderColumn = function(text) { | |
this.headers.push(text); | |
}; | |
this.addCell = function(text,style) { | |
if(!this.body[this.currentRow]) { | |
this.body[this.currentRow] = []; | |
} | |
this.body[this.currentRow].push({ val:text, style:(style) ? style : '' }); | |
}; | |
this.newRow = function() { | |
if(this.body != []) { | |
this.currentRow++; | |
} | |
}; | |
this.getRowCount = function() { | |
return this.currentRow; | |
}; | |
this.setTableStyle = function(css) { | |
this.tableStyle = css; | |
}; | |
this.setHeaderStyle = function(css) { | |
this.headerStyle = css; | |
}; | |
this.setCellStyle = function(css) { | |
this.cellStyle = css; | |
if(css[css.length-1] !== ';') { | |
this.cellStyle += ';'; | |
} | |
}; | |
this.toString = function() { | |
var retVal = '<table '; | |
if(this.tableStyle) { | |
retVal += 'style="'+this.tableStyle+'"'; | |
} | |
retVal += '>'+_getTableHead(this)+_getTableBody(this)+'</table>'; | |
return retVal; | |
}; | |
function _getTableHead(instance) { | |
var headerRow = ''; | |
for(var i in instance.headers) { | |
headerRow += _th(instance,instance.headers[i]); | |
} | |
return '<thead><tr>'+headerRow+'</tr></thead>'; | |
}; | |
function _getTableBody(instance) { | |
var retVal = '<tbody>'; | |
for(var r in instance.body) { | |
var rowHtml = '<tr>'; | |
for(var c in instance.body[r]) { | |
rowHtml += _td(instance,instance.body[r][c]); | |
} | |
rowHtml += '</tr>'; | |
retVal += rowHtml; | |
} | |
retVal += '</tbody>'; | |
return retVal; | |
}; | |
function _th(instance,val) { | |
var retVal = '<th scope="col" '; | |
if(instance.headerStyle) { | |
retVal += 'style="'+instance.headerStyle+'"'; | |
} | |
retVal += '>'+val+'</th>'; | |
return retVal; | |
}; | |
function _td(instance,cell) { | |
var retVal = '<td '; | |
if(instance.cellStyle || cell.style) { | |
retVal += 'style="'; | |
if(instance.cellStyle) { | |
retVal += instance.cellStyle; | |
} | |
if(cell.style) { | |
retVal += cell.style; | |
} | |
retVal += '"'; | |
} | |
retVal += '>'+cell.val+'</td>'; | |
return retVal; | |
}; | |
} | |
function beacon() { | |
var TAG_ID = 'UA-37884000-1'; | |
var CAMPAIGN_SOURCE = 'scripts'; | |
var CAMPAIGN_MEDIUM = 'geo-anomalies'; | |
var CAMPAIGN_NAME = AdsApp.currentAccount().getName(); | |
var HOSTNAME = 'www.optmyzr.com'; | |
var PAGE = '/scripts/geo-anomalies/'; | |
var DOMAIN_LINK = 'https://'+HOSTNAME+PAGE; | |
if(TAG_ID == 'UA-XXXXXXXX-X') { return; } | |
//Pulled from: http://stackoverflow.com/questions/105034/how-to-create-a-guid-uuid-in-javascript | |
var uuid = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, | |
function(c) {var r = Math.random()*16|0,v=c=='x'?r:r&0x3|0x8;return v.toString(16);}); | |
var url = 'http://www.google-analytics.com/collect?'; | |
var payload = { | |
'v':1,'tid':TAG_ID,'cid':uuid, | |
't':'pageview','cs':CAMPAIGN_SOURCE,'cm':CAMPAIGN_MEDIUM,'cn':CAMPAIGN_NAME | |
}; | |
var qs = ''; | |
for(var key in payload) { | |
qs += key + '=' + encodeURIComponent(payload[key]) + '&'; | |
} | |
url += qs.substring(0,qs.length-1); | |
UrlFetchApp.fetch(url); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment