Compare performance of RSAs vs ETAs at the query level in an ad group to help decide which ad format is driving the best results

ETA vs RSA Query Report

/******************************************
* ETA vs RSA Test
* @version: 1.0
* @author: Naman Jindal (Optmyzr)
* March 2, 2020
*******************************************/

var LOOKBACK_DAYS = 30; // Number of Days to look back for stats
var EMAILS = ['example@example.com']; // Array of Emails separated by comma
var CAMPAIGN_NAME_CONTAINS = ''; // Case Insensitive
var CAMPAIGN_NAME_DOES_NOT_CONTAIN = ''; // Case Insensitive


// Do not edit anything below this line

function main() {
  
  var DATE_RANGE = getAdWordsFormattedDate(LOOKBACK_DAYS, 'yyyyMMdd') + ',' + getAdWordsFormattedDate(1, 'yyyyMMdd');
  
  var adMap = {}, agMap = {};
  var query = [
    'SELECT Id, AdGroupId, AdType, Status FROM AD_PERFORMANCE_REPORT',
    'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED',
    'and AdType IN [RESPONSIVE_SEARCH_AD, EXPANDED_TEXT_AD]',
    CAMPAIGN_NAME_CONTAINS ? 'and CampaignName CONTAINS_IGNORE_CASE "' + CAMPAIGN_NAME_CONTAINS + '"' : '',
    CAMPAIGN_NAME_DOES_NOT_CONTAIN ? 'and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE "' + CAMPAIGN_NAME_DOES_NOT_CONTAIN + '"' : '',
    'DURING', DATE_RANGE
  ].join(' ');
  
  var rows = AdsApp.report(query, {'includeZeroImpressions': false }).rows();
  while(rows.hasNext()) {
   var row = rows.next();
    var key = [row.AdGroupId, row.Id].join('-');
    adMap[key] = row.AdType;
    
     if(!agMap[row.AdGroupId] ) { 
       agMap[row.AdGroupId] = {
         'Responsive search ad': 0, 'Expanded text ad': 0
       }; 
     }
    
    if(row.Status == 'enabled') {
     agMap[row.AdGroupId][row.AdType]++;
    }
  }
  
  
  var agIds = Object.keys(agMap)
  var initMap = {
    'ETA': 0, 'RSA': 0,
    'Responsive search ad': { 'Impressions': 0, 'Clicks': 0, 'Conversions': 0, 'Cost': 0, 'ConversionValue': 0 } ,
    'Expanded text ad': { 'Impressions': 0, 'Clicks': 0, 'Conversions': 0, 'Cost': 0, 'ConversionValue': 0 } 
  };
    
  var statsMap = {};
  var query = [
    'SELECT',
    'Query, CampaignName, AdGroupName, AdGroupId, CreativeId, Impressions, Clicks, Conversions, Cost, ConversionValue',
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT',
    'WHERE CampaignStatus = ENABLED and AdGroupStatus = ENABLED',
    CAMPAIGN_NAME_CONTAINS ? 'and CampaignName CONTAINS_IGNORE_CASE "' + CAMPAIGN_NAME_CONTAINS + '"' : '',
    CAMPAIGN_NAME_DOES_NOT_CONTAIN ? 'and CampaignName DOES_NOT_CONTAIN_IGNORE_CASE "' + CAMPAIGN_NAME_DOES_NOT_CONTAIN + '"' : '',
    agIds.length < 10000 ? 'and AdGroupId IN [' + agIds.join(',') + ']' : '',
    'DURING', DATE_RANGE
  ].join(' ');
  
  var rows = AdsApp.report(query, {'includeZeroImpressions': false }).rows();
  while(rows.hasNext()) {
   var row = rows.next();
    var adKey = [row.AdGroupId, row.CreativeId].join('-');
    var adType = adMap[adKey];
    if(!initMap[adType]) {
      continue; 
    }
    
    var agRow = agMap[row.AdGroupId];
    if(!agRow) {
      agRow = { 'Responsive search ad': 0, 'Expanded text ad': 0 }
    }
    
    var key = [row.Query, row.AdGroupName, row.CampaignName].join('!~!');
    
    if(!statsMap[key]) {
      statsMap[key] = JSON.parse(JSON.stringify(initMap));
    }
    
    statsMap[key]['ETA'] = agRow['Expanded text ad'];
    statsMap[key]['RSA'] = agRow['Responsive search ad'];
    
    row.Impressions = parseFloat(row.Impressions);
    row.Clicks = parseFloat(row.Clicks);
    row.Cost = parseFloat(row.Cost.toString().replace(/,/g, ''));
    row.Conversions = parseFloat(row.Conversions.toString().replace(/,/g, ''));
    row.ConversionValue = parseFloat(row.ConversionValue.toString().replace(/,/g, ''));
    
    statsMap[key][adType].Impressions += row.Impressions;
    statsMap[key][adType].Clicks += row.Clicks;
    statsMap[key][adType].Cost += row.Cost;
    statsMap[key][adType].Conversions += row.Conversions;
    statsMap[key][adType].ConversionValue += row.ConversionValue;
  }
  
  var output = [];
  var results = {
    'rsa':  { 'incrementalClicks': 0, 'incrementalConversions': 0 },
    'all':  { 'incrementalClicks': 0, 'incrementalConversions': 0 }
  }
  
  for(var key in statsMap) {
    var row = statsMap[key];
    var out =  key.split('!~!');
    out.push(row['ETA'], row['RSA']);
    
    var rsaRow = row['Responsive search ad']; 
    rsaRow.Ctr = rsaRow.Impressions > 0 ? rsaRow.Clicks / rsaRow.Impressions : 0;
    rsaRow.CPI = rsaRow.Impressions > 0 ? rsaRow.Conversions / rsaRow.Impressions : 0;
    rsaRow.CPA = rsaRow.Conversions > 0 ? rsaRow.Cost / rsaRow.Conversions : 0;
    rsaRow.Cpc = rsaRow.Clicks > 0 ? rsaRow.Cost / rsaRow.Clicks : 0;
    rsaRow.ROAS = rsaRow.Cost > 0 ? rsaRow.ConversionValue / rsaRow.Cost : 0;
    
    out.push(rsaRow.Impressions, rsaRow.Clicks, rsaRow.Conversions, rsaRow.Ctr, rsaRow.CPI, rsaRow.Cost, rsaRow.CPA, rsaRow.ROAS);
    
    var etaRow = row['Expanded text ad'];
    etaRow.Ctr = etaRow.Impressions > 0 ? etaRow.Clicks / etaRow.Impressions : 0;
    etaRow.CPI = etaRow.Impressions > 0 ? etaRow.Conversions / etaRow.Impressions : 0;
    etaRow.CPA = etaRow.Conversions > 0 ? etaRow.Cost / etaRow.Conversions : 0;
    etaRow.Cpc = etaRow.Clicks > 0 ? etaRow.Cost / etaRow.Clicks : 0;
    etaRow.ROAS = etaRow.Cost > 0 ? etaRow.ConversionValue / etaRow.Cost : 0;
    
    out.push(etaRow.Impressions, etaRow.Clicks, etaRow.Conversions, etaRow.Ctr, etaRow.CPI, etaRow.Cost, etaRow.CPA, etaRow.ROAS);
    
    var incrementalClicks = rsaRow.Impressions * (rsaRow.Ctr - etaRow.Ctr);
    var incrementalConversions = rsaRow.Impressions * (rsaRow.CPI - etaRow.CPI);
    var incrementalCost = rsaRow.Clicks * (rsaRow.Cpc - etaRow.Cpc);
    
    var recommendation = '';
    if(row['ETA'] == 0) {
      recommendation = 'Test ETAs';
    } else if(row['RSA'] == 0) {
      recommendation = 'Test RSAs';
    } else if(incrementalConversions > 0) {
      recommendation = 'SKAG query with existing RSA';
    } else if(incrementalConversions < 0) {
      recommendation = 'SKAG query with existing ETA';
    } else if(etaRow.Impressions == 0) {
      recommendation = 'Test ETAs';
    } else if(rsaRow.Impressions == 0) {
      recommendation = 'Test RSAs';
    } 
    
    if(row['ETA'] == 0 && etaRow.Impressions == 0) {
      results['rsa'].incrementalClicks += incrementalClicks;
      results['rsa'].incrementalConversions += incrementalConversions;
    }
    
    if(etaRow.Impressions > 0 && rsaRow.Impressions > 0) {
      results['all'].incrementalClicks += incrementalClicks;
      results['all'].incrementalConversions += incrementalConversions;
    }
    
    out.push(incrementalClicks, incrementalConversions, incrementalCost, recommendation);
    
    output.push(out);
  }
  
  if(!output.length) {
    Logger.log('No Data found');
    return;   
  }
  
  var TEMPLATE_URL = 'https://docs.google.com/spreadsheets/d/1AAk_Vsdq0LezvezOarzhOzVXsQq5BN-EKMRtGX-eDVQ/edit';
  var template = SpreadsheetApp.openByUrl(TEMPLATE_URL);
  
  var now = getAdWordsFormattedDate(0, 'MMM d, yyyy')
  var name = AdsApp.currentAccount().getName() + ' - ETA vs RSA (' + now + ')';
  var ss = template.copy(name);
  var reportUrl = ss.getUrl();
  Logger.log('Report URL: ' + reportUrl);
  ss.addEditors(EMAILS);
  
  ss.getSheets()[0].getRange(3,1,output.length,output[0].length).setValues(output);
  
  sendEmail(results, reportUrl);
}

function sendEmail(results, reportUrl) {
  var sub = AdsApp.currentAccount().getName() + ' - ETA vs RSA Test Report';
  var msg = 'Hi,\n\nPlease find below summary of latest test results:\n';
  
  var rsaResults = results['rsa'];
  if(rsaResults.incrementalClicks > 0) {
    msg += 'You got ' + Math.round(rsaResults.incrementalClicks) + 'more clicks and ' + Math.round(rsaResults.incrementalConversions)  + ' more convversions from queries that only triggered with RSA ads.\n';
  }
  
  var change = ''
  if(results['all'].incrementalConversions > 0) {
    change = 'gained';
  } else if(results['all'].incrementalConversions < 0) {
    change = 'lost';
  }
  
  if(change) {
    msg += 'For queries that showed both RSA and ETA ads, you ' + change + ' ' + Math.round(results['all'].incrementalConversions) + ' conversions.\n';
  }
  
  msg += '\n\n' + reportUrl;
  msg +- '\n\nThanks';
  
  MailApp.sendEmail(EMAILS.join(','), sub, msg);
}

function getAdWordsFormattedDate(d, format){
  var date = new Date();
  date.setDate(date.getDate() - d);
  return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format);
}