Get an alert when performance in a particular location deviates from normal

Geo Anomaly Detector for Google Ads

/******************************************
* 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);
}

Updated Version- Create the Sheet in Specific Folder
// example https://docs.google.com/spreadsheets/d/afa087a986D96GS0dg607sgsdgsg-sgsg-dgdsg/
var FOLDERID = 'afa087a986D96GS0dg607sgsdgsg-sgsg-dgdsg';
and added
var folder = DriveApp.getFolderById(FOLDERID) // added by Jimbolo 03-09-22
var ss = SpreadsheetApp.create(name);
var URL = ss.getUrl();
DriveApp.getFileById(ss.getId()).moveTo(folder); // added by Jimbolo 03-09-22