Plot PPC data with local COVID-19 measures

PPC Data with Covid-19 measures

/******************************************
* Ad Performance With Covid Timeline
* @version: 1.0
* @author: Naman Jindal (Optmyzr)
* Event data courtsey epidemicforecasting.org used under the MIT license
********************************************/


// Select One Metric to plot on Chart from below
// 'Impressions','Clicks','Conversions','Cost','ConversionValue','Ctr','ConversionRate','AverageCpc'
var METRIC = 'Impressions'; 

// Multiple emails separated by comma
var EMAILS = 'frederick@optmyzr.com';


// Do not edit below this line

function main() {
  
  var cols = [
    'Impressions','Clicks','Conversions','Cost','ConversionValue','Ctr','ConversionRate',
    'AverageCpc','CountryCriteriaId','RegionCriteriaId','Date'
  ];
  
  var reportCols = [METRIC];
  for(var k in cols) {
    if(cols[k] != METRIC) {
       reportCols.push(cols[k]); 
    }
  }
  
  var query = [
    'SELECT', cols.join(','),
    'FROM GEO_PERFORMANCE_REPORT',
    'DURING LAST_30_DAYS'
  ].join(' '); 
  
  var TEMPLATE_URL = 'https://docs.google.com/spreadsheets/d/18ir0O5RhyN5CeHFMD2Ns6vn6pMYxR5EKSQcP13n0zqQ/edit';
  
  var template = SpreadsheetApp.openByUrl(TEMPLATE_URL);
  var ss = template.copy(AdsApp.currentAccount().getName() + ' -  Ad Performance with Covid timeline');
  Logger.log(ss.getUrl());
  
  importCovidMeasures(ss);
  
  var tab = ss.getSheetByName('Ads Report')
  
  AdsApp.report(query).exportToSheet(tab);
  
  tab.getRange(1,12,1,3).setValues([['Cleaned Region Name',	'Key',	'Measure Implemented']]);
  tab.getRange(2,12,tab.getLastRow()-1,1).setFormula("=if(AND(R[0]C[-3]=\"United States\",R[0]C[-2]<>\"--\"),CONCAT(\"US:\",R[0]C[-2]),R[0]C[-3])")  
  tab.getRange(2,13,tab.getLastRow()-1,1).setFormula("=concat(R[0]C[-1],R[0]C[-2])");
  tab.getRange(2,14,tab.getLastRow()-1,1).setFormula("=IFERROR(vlookup(R[0]C[-1],'COVID 19 Containment measures'!$A$2:$G,7,false),\"\")");

  beacon();
  
  if(EMAILS) {
    var sub = AdsApp.currentAccount().getName() + ' - Ad Performance Report with Covid timeline';
    var msg = 'Please find the report below:\n' +ss.getUrl();
    MailApp.sendEmail(EMAILS, sub, msg);
  
    ss.addEditors(EMAILS.split(','));
  }
}

function importCovidMeasures(ss) {
  var url = 'https://docs.google.com/spreadsheets/d/150jU-coYaLMFhM-ctSrtRaLfFIFYo8vDGB2jideLiII/edit#gid=141150354';
  var csv = SpreadsheetApp.openByUrl(url).getSheets()[0].getDataRange().getValues();
  
  var tab = ss.getSheetByName('COVID 19 Containment measures');
  tab.clearContents();
  tab.getRange(1,2,csv.length,csv[0].length).setValues(csv);
  tab.getRange('A1').setValue('Key');
  tab.getRange(2,1,tab.getLastRow()-1,1).setFormulaR1C1('=CONCAT(R[0]C[3],R[0]C[4])');
}

function beacon() {
  var TAG_ID = 'UA-XXXXXXXX-X';
  var CAMPAIGN_SOURCE = 'scripts';
  var CAMPAIGN_MEDIUM = 'Covid Timeline v1';
  var CAMPAIGN_NAME = AdsApp.currentAccount().getCustomerId();
  //var DOMAIN_LINK = 'http://'+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);
}

Hi Fred! Thank you so much for sharing this. Very interesting and useful script indeed :) I've tried to implement it across some of my European accounts (1 market/country per account only and different from U.S.) and although I'm receiving the scheduled email with all information related to Geo and search data, unfortunately the "Measure Implemented" column is always coming as blank. I'm quite new to scripts so not sure if I'm doing things all right or where the problem is if any (the vlookup function maybe?) but if you or someone else could help me a little bit I'd be very grateful. Thank you so much in advanced. Best

Pawi