Close Variants Report.js

/* @version: 1.5
*
* Close variant report
*/
 
var SPREADSHEET_URL = "https://docs.google.com/spreadsheets/YOUR-SHEET-URL-HERE";  //insert a new blank spreadsheet url
var CAMPAIGNLABEL = ""; // Leave blank for all campaigns 
 
var DATE_RANGE = 'LAST_30_DAYS';
var MATCH_TYPE = '[EXACT, NEAR_EXACT]';
 
var KEYWORD_STATS = []; // array with calculated stats for keywords that have close variants
var CLOSE_VARIANTS = []; // array with close variants and metrics
 
var THRESHOLD = 0.4; // higlight percentages in sheet when above this value
  
function main() {
 
  // let's prepare the sheet
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var keywordStatsSheet = spreadsheet.getActiveSheet();
  keywordStatsSheet.clear();
  keywordStatsSheet.setName('Keyword Stats');
  if (spreadsheet.getSheetByName('Close Variants') != null) {
    Logger.log("CV sheet already exists");
    var closeVariantSheet = spreadsheet.getSheetByName('Close Variants');
    closeVariantSheet.clear();
  } else {
    Logger.log("creating CV sheet");
    var closeVariantSheet = spreadsheet.insertSheet('Close Variants');
  }
   
   
  // let's get the list of campaings to check
  var campaignSelector = AdWordsApp.campaigns()
        .withCondition("AdvertisingChannelType = SEARCH") // Search campaings only (no display/shopping, ...)    
        .withCondition("CampaignTrialType = BASE") //skip Drafts and Experiments
      .withCondition("Name DOES_NOT_CONTAIN_IGNORE_CASE 'DSA'") // skip DSA campaigns
        .withCondition("Status = ENABLED") ;
   
  if (CAMPAIGNLABEL!="") {
    campaignSelector=campaignSelector
        .withCondition("LabelNames CONTAINS_ANY ['"+CAMPAIGNLABEL+"']");
  }
      
  var campaignIterator = campaignSelector.get();
  var campaignIds=[] ;
  while (campaignIterator.hasNext()) {
    var campaign = campaignIterator.next();
    campaignIds.push(campaign.getId()) ;
  }
  Logger.log("  NR of campaigns to check: "+campaignIterator.totalNumEntities());
 
 
  // Let's check for close variants and get the stats
  checkCloseVariants(campaignIds);
 
  Logger.log("Nr of KWs: "+KEYWORD_STATS.length);
  Logger.log("Nr of CVs: "+CLOSE_VARIANTS.length);
 
  reportKeywordStats(keywordStatsSheet);
 
  reportCloseVariants(closeVariantSheet);
   
}
 
 
function checkCloseVariants(campaignIds) {
 
  var awql_query="SELECT KeywordTextMatchingQuery, QueryMatchTypeWithVariant, Query, Impressions, Clicks, Cost, Conversions"
                  +" FROM SEARCH_QUERY_PERFORMANCE_REPORT"
                  +" WHERE CampaignId IN ["+campaignIds.join(",")+"]"
                  +" AND QueryMatchTypeWithVariant IN "+MATCH_TYPE
                  +" DURING "+DATE_RANGE;
 
  var report=AdWordsApp.report(awql_query);
  var rows = report.rows();
 
  var map = {};
 
  var totalNrOfCloseVariants = 0;
  var totalImpressionsEM = 0;
  var totalImpressionsCV = 0;
  var totalClicksEM = 0;
  var totalClicksCV = 0;
  var totalCostEM = 0;
  var totalCostCV = 0;  
  var totalConversionsEM = 0;
  var totalConversionsCV = 0;  
 
 
  while (rows.hasNext()) {
 
    var row = rows.next();
    var keyword = row['KeywordTextMatchingQuery'];
 
    if( (keyword.indexOf('\"') == -1) && (keyword.indexOf('+') == -1) ) { // make sure the keyword is not MBM or Phrase (somehow KWs of these matchtypes can also generate "exact (close variant)'s" or "exact" matches ?!?    
 
      // build the map per keyword
      if ( !map.hasOwnProperty(keyword) ) {
          map[keyword] = new KeywordStatsObject(keyword);
      }
   
      if(row['QueryMatchTypeWithVariant']=='exact') {
        map[keyword].impressionsEM += numericalize(row['Impressions']);
        map[keyword].clicksEM += numericalize(row['Clicks']);
        map[keyword].costEM += numericalize(row['Cost']);
        map[keyword].conversionsEM += numericalize(row['Conversions']);
        totalImpressionsEM += numericalize(row['Impressions']);
        totalClicksEM += numericalize(row['Clicks']);
        totalCostEM += numericalize(row['Cost']);
        totalConversionsEM += numericalize(row['Conversions']);
      }
   
      if(row['QueryMatchTypeWithVariant']=='exact (close variant)') { // Query (search term) is a close variant
         
        totalNrOfCloseVariants++;
        map[keyword].nrOfCloseVariants++;
        map[keyword].impressionsCV += numericalize(row['Impressions']);
        map[keyword].clicksCV += numericalize(row['Clicks']);
        map[keyword].costCV += numericalize(row['Cost']);
        map[keyword].conversionsCV += numericalize(row['Conversions']);
        totalImpressionsCV += numericalize(row['Impressions']);
        totalClicksCV += numericalize(row['Clicks']);
        totalCostCV += numericalize(row['Cost']);
        totalConversionsCV += numericalize(row['Conversions']);
 
        // Query (search term) is a close variant, so let's add it to array with all close variants and their metrics 
        addToArray(row, CLOSE_VARIANTS);
      }
    }
  }
 
  Logger.log("Total NR of Close Variants: "+totalNrOfCloseVariants+"\n");
  Logger.log("Total Real EM Impressions: "+totalImpressionsEM);
  Logger.log("Total Close Variant Impressions: "+totalImpressionsCV);
  Logger.log("Total Real EM Clicks: "+totalClicksEM);
  Logger.log("Total Close Variant Clicks: "+totalClicksCV);
  Logger.log("Total Real EM Cost: "+totalCostEM);
  Logger.log("Total Close Variant Cost: "+totalCostCV);
  Logger.log("Total Real EM Conversions: "+totalConversionsEM);
  Logger.log("Total Close Variant Conversions: "+totalConversionsCV);
   
   
  for (var keyword in map) {
    if (map.hasOwnProperty(keyword)) {
    KEYWORD_STATS.push([
        "["+map[keyword].keyword+"]",
        map[keyword].nrOfCloseVariants,
        (map[keyword].costEM/map[keyword].conversionsEM).toFixed(2),
        (map[keyword].costCV/map[keyword].conversionsCV).toFixed(2),
        (( (map[keyword].costCV/map[keyword].conversionsCV)/(map[keyword].costEM/map[keyword].conversionsEM) ) - 1).toFixed(2),
        map[keyword].impressionsCV+map[keyword].impressionsEM,
        map[keyword].impressionsEM,
        map[keyword].impressionsCV,
        (map[keyword].impressionsCV/(map[keyword].impressionsCV+map[keyword].impressionsEM)).toFixed(2),
        map[keyword].clicksCV+map[keyword].clicksEM,
        map[keyword].clicksEM,
        map[keyword].clicksCV,
        (map[keyword].clicksCV/(map[keyword].clicksCV+map[keyword].clicksEM)).toFixed(2),
        map[keyword].costCV+map[keyword].costEM,
        map[keyword].costEM,
        map[keyword].costCV,
        (map[keyword].costCV/(map[keyword].costCV+map[keyword].costEM)).toFixed(2),
        map[keyword].conversionsCV+map[keyword].conversionsEM,
        map[keyword].conversionsEM,
        map[keyword].conversionsCV,
        (map[keyword].conversionsCV/(map[keyword].conversionsCV+map[keyword].conversionsEM)).toFixed(2)
      ]);
    }
  }
  KEYWORD_STATS.unshift(["_TOTAL_", 
                         totalNrOfCloseVariants,
                         (totalCostEM/totalConversionsEM).toFixed(2),
                         (totalCostCV/totalConversionsCV).toFixed(2),   
                         (( (totalCostCV/totalConversionsCV)/(totalCostEM/totalConversionsEM) ) -1).toFixed(2),   
                         totalImpressionsEM+totalImpressionsCV, 
                         totalImpressionsEM, totalImpressionsCV,
                         (totalImpressionsCV/(totalImpressionsEM+totalImpressionsCV)).toFixed(2), 
                         totalClicksEM+totalClicksCV,
                         totalClicksEM, 
                         totalClicksCV, 
                         (totalClicksCV/(totalClicksEM+totalClicksCV)).toFixed(2), 
                         totalCostEM+totalCostCV, 
                         totalCostEM, 
                         totalCostCV, 
                         (totalCostCV/(totalCostEM+totalCostCV)).toFixed(2),
                         totalConversionsEM+totalConversionsCV, 
                         totalConversionsEM, 
                         totalConversionsCV, 
                         (totalConversionsCV/(totalConversionsEM+totalConversionsCV)).toFixed(2)
                        ]); 
 
}
 
 
function numericalize(string){
  return parseFloat(string.toString().replace(/\,/g, ''));
} 
 
 
function KeywordStatsObject(keyword) {
  this.keyword = keyword;
  this.nrOfCloseVariants = 0;
  this.impressionsEM = 0;
  this.impressionsCV = 0;
  this.clicksEM = 0;
  this.clicksCV = 0;
  this.costEM = 0;
  this.costCV = 0;  
  this.conversionsEM = 0;
  this.conversionsCV = 0;  
 
}
 
 
function addToArray(reportRow,array) {
  array.push([reportRow.Query, reportRow.KeywordTextMatchingQuery, reportRow.Impressions, reportRow.Clicks, reportRow.Cost, reportRow.Conversions]);
}
 
 
function reportKeywordStats(sheet) {
 
  // let's build the spreadsheet
  var header = [
    "Keyword", 
    "Nr of CV Search Terms",
    "EM Cost/Conv",
    "CV Cost/Conv",
    "Diff Cost/Conv",
    "Total Impressions",    
    "EM Impressions",
    "CV Impressions",
    "CV Impression Share",
    "Total Clicks",    
    "EM Clicks",
    "CV Clicks",
    "CV Click Share",    
    "Total Cost",    
    "EM Cost",
    "CV Cost",
    "CV Cost Share",
    "Total Conversions",    
    "EM Conversions",
    "CV Conversions",
    "CV Conversions Share"      
  ];
 
  // write issues to sheet
  var range = sheet.getRange(1, 1, KEYWORD_STATS.length, header.length);
  range.setValues(KEYWORD_STATS);
   
  // sort by Close Variant Impressions desc, add header when sorted
  sheet.sort(8, false); 
  sheet.insertRows(1);
  range = sheet.getRange(1, 1, 1, header.length);
  range.setValues([header]);
  var rangeHeader = sheet.getRange('1:1');
  rangeHeader.setFontWeight("bold");
   
  // highlight issues
  var range_DiffInCostPerConv = sheet.getRange('E:E');
  var range_CVImprShare = sheet.getRange('I:I');
  var range_CVClickShare = sheet.getRange('M:M');
  var range_CVCostShare = sheet.getRange('Q:Q');
  var range_CVConvShare = sheet.getRange('U:U'); 
  var rule1 = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(THRESHOLD)
    .setBackground("#FFCC99")
    .setRanges([range_DiffInCostPerConv, range_CVImprShare, range_CVClickShare, range_CVCostShare, range_CVConvShare])
    .build();
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule1);
  sheet.setConditionalFormatRules(rules);
 
}
 
 
function reportCloseVariants(closeVariantSheet){
 
  // let's build the Close Variant sheet
  var closeVariantSheetHeader = [
    "Search Term (Close Variant)", 
    "Keyword",
    "Impressions", 
    "Clicks",
    "Cost",
    "Conversions"  
  ];
 
  // write issues to Close Variant sheet
  var range = closeVariantSheet.getRange(1, 1, CLOSE_VARIANTS.length, closeVariantSheetHeader.length);
  range.setValues(CLOSE_VARIANTS);
   
  // sort by Close Variant Impressions desc, add header when sorted
  closeVariantSheet.sort(2, false); 
  closeVariantSheet.insertRows(1);
  range = closeVariantSheet.getRange(1, 1, 1, closeVariantSheetHeader.length);
  range.setValues([closeVariantSheetHeader]);  
   
  var rangeHeader = closeVariantSheet.getRange('1:1');
  rangeHeader.setFontWeight("bold");
}