Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.