Get aggregate performance data by keyword and search term match type

Match Type Performance Report

// Create a report in a Google spreadsheet with performance data by keyword match type and search term match type in Google Ads
// Free AdWords Script courtesy of Optmyzr.com
// October 22, 2018

var DEBUG = 0;

function main(){
  
  var currentSetting = new Object();

  
// -------- User settings: Edit these to customize the functionality
  var spreadsheetUrl = '';     // leave this blank or enter the URL to a Google spreadsheet to use for the report
  var accountManagers = '[email protected]'; // who should be given permission to edit the generated Google spreadsheet
  var emailAddress = '[email protected]';  // who should be notified when a report is ready
  var time = 'LAST_30_DAYS';  // what date range to use for the report
  var reportVersion = 'v201802';  // what API reporting version to use
  // -------- End of User Settings. Do not edit anything after this unless you know scripting.----------
  
  var sheetNameForKeywordData = 'Keyword Match Type';
  var sheetNameForQueryData = 'Search Term Match Type';
  var spreadsheetName = 'Match Type Performance Report';
  var overWriteOldData = 1;  // if set to '1', old data in the spreadsheet will be overwritten
  var includeAccountNameInReportName = 1;
  var includeDateGeneratedInReportName = 1;
  var includeDateRangeInReportName = 1;
  
  if(typeof sheetNameForKeywordData == "undefined") sheetNameForKeywordData = "Keywords";
  if(typeof sheetNameForQueryData == "undefined") sheetNameForQueryData = "Queries";
  if(typeof spreadsheetName == "undefined") spreadsheetName = "Keyword and Query Type Stats";
  
  
  if(typeof overWriteOldData == "undefined") overWriteOldData = 1;
  if(typeof includeAccountNameInReportName == "undefined") includeAccountNameInReportName = 1;
  if(typeof includeDateGeneratedInReportName == "undefined") includeDateGeneratedInReportName = 1;
  if(typeof includeDateRangeInReportName == "undefined") includeDateRangeInReportName = 1;
  
  var campaignNameIncludes = currentSetting['Campaign_Name_Contains'];
  var campaignLabel = currentSetting['Campaign_Label'];
  if(typeof campaignNameIncludes == "undefined") var campaignNameIncludes = "";
  if(typeof campaignLabel == "undefined") var campaignLabel = "";
  
  
  if(campaignLabel) var matchedCampaigns = getCampaignsByLabel(campaignLabel);
  
  
  var campaignNameCaseSensitive = 0;
  var campaignNameExactMatch = 0;
  if(campaignNameIncludes) var campaignNameSelectorString = getCampaignsByName(campaignNameIncludes, campaignNameCaseSensitive, campaignNameExactMatch);
  
  
  var accountName = AdWordsApp.currentAccount().getName();
  var sheetNames = new Array(sheetNameForKeywordData, sheetNameForQueryData);
  var currencyCode = AdWordsApp.currentAccount().getCurrencyCode();
  
  
  
  
  // Construct report name
  var reportDate = new Date();
  var dateForFilename = reportDate.yyyymmdd();
  if(includeAccountNameInReportName) {
    spreadsheetName = spreadsheetName + " - " + accountName;
  }
  if(includeDateGeneratedInReportName) {
    spreadsheetName = spreadsheetName + " - " + dateForFilename; 
  }
  if(includeDateRangeInReportName) {
    spreadsheetName = spreadsheetName + " (" + time + ")";
  }
  
  var destinationSpreadsheet = setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames);
  var spreadsheet = destinationSpreadsheet.spreadsheet;
  var isSpreadsheetNew = destinationSpreadsheet.isNew;
  var spreadsheetUrl = destinationSpreadsheet.url;
  
  
  
  
  var kwSheet = spreadsheet.getSheetByName(sheetNameForKeywordData);
  if(destinationSpreadsheet.overWrite) {
    kwSheet.appendRow(["Start Date", "End Date", "Type", "Clicks", "Impressions", "Cost (" + currencyCode + ")", "CTR", "Avg. CPC (" + currencyCode + ")", "Conversions", "Cost / Conversion", "Conv. Value (" + currencyCode + ")","Conv. Value / Cost", "Avg Pos."]);
    kwSheet.setFrozenRows(1);
  }
  
  // Sheet for Query report
  var querySheet = spreadsheet.getSheetByName(sheetNameForQueryData);
  if(destinationSpreadsheet.overWrite) {
    querySheet.appendRow(["Start Date", "End Date", "Type", "Clicks", "Impressions", "Cost (" + currencyCode + ")", "CTR", "Avg. CPC (" + currencyCode + ")", "Conversions", "Cost / Conversion", "Conv. Value (" + currencyCode + ")", "Conv. Value / Cost", "Avg Pos."]);
    querySheet.setFrozenRows(1);
  }
  
  var reportDates = getReportDates(time);
  
  var cols = ['KeywordMatchType', 
              'Criteria', 
              'Clicks', 
              'Impressions', 
              'Cost', 
              'ConversionValue', 
              'AveragePosition', 
              'Conversions', 
              'CampaignId',
              'CampaignName'];
  var query = ['SELECT ', 
               cols.join(','),
               'FROM KEYWORDS_PERFORMANCE_REPORT',
               'WHERE Impressions > 0'];
  if(campaignLabel) query.push('AND', matchedCampaigns.selectorStringForReports);
  if(campaignNameIncludes) query.push('AND', campaignNameSelectorString.forReports);
  query.push('DURING', time);
  
  var query = query.join(' ');
  if(DEBUG == 1) Logger.log("query: " + query);
  var report = AdWordsApp.report(query,{apiVersion:reportVersion});
  var rows = report.rows();
  processReport(kwSheet, rows, "Criteria", "KeywordMatchType", reportDates);
  addStyleToSheet(kwSheet);
  
  var cols = ['QueryMatchTypeWithVariant', 
              'Query', 
              'Clicks', 
              'Impressions', 
              'Cost', 
              'ConversionValue',
              'AveragePosition', 
              'Conversions', 
              'CampaignId',
              'CampaignName'];
  var query = ['SELECT ', 
               cols.join(','),
               'FROM SEARCH_QUERY_PERFORMANCE_REPORT',
               'WHERE Impressions > 0'];
  if(campaignLabel) query.push('AND', matchedCampaigns.selectorStringForReports);
  if(campaignNameIncludes) query.push('AND', campaignNameSelectorString.forReports);
  query.push('DURING', time);
  
  var query = query.join(' ');
  if(DEBUG == 1) Logger.log("query: " + query);
    
  var report = AdWordsApp.report(query,{apiVersion:reportVersion});
  var rows = report.rows();
  processReport(querySheet, rows, "Query", "QueryMatchTypeWithVariant", reportDates)
  addStyleToSheet(querySheet);
  
  //var spreadsheetUrl = spreadsheet.getUrl();
    var notes = "Full report: <a href=\""+spreadsheetUrl+"\" target=\"_blank\">link</a>";
  
  if(emailAddress) {
    var subject = "Keyword and Query Report";
    var body = "Your report for the performance of different types of keywords and queries is ready: <a href=\""+spreadsheet.getUrl()+"\" target=\"_blank\">link</a><br/><br/>" + "Manage the Optmyzr Enhanced Script that created this report: https://tools.optmyzr.com/enhancedscript_settings/scriptSettings/41";
    var emailType = "notification";
    sendEmailNotifications(emailAddress, subject, body, emailType);
  }
    
  return(notes);
  
}
 
function processReport(sheet, rows, kwString, matchTypeString, reportDates) {
  
  var kwList = new Array();
  var kwKeys = new Array();
  
  while(rows.hasNext()) {
    var row = rows.next();
    var clicks = parseInt(row['Clicks']);
    var impressions = parseInt(row['Impressions']);
    var cost = getFloat(row['Cost']);
    var value = getFloat(row['ConversionValue']);
    var conversions = parseInt(row['Conversions']);
    var avgPos = getFloat(row['AveragePosition']);
    var matchType = row[matchTypeString];
    var keywordText = row[kwString];
    var kwNumWords = keywordText.split(" ").length;
    
    if(kwString.toLowerCase().indexOf("criteria") != -1) {
    	debug("keyword text: " + keywordText + " (" + kwNumWords + ")");
    	kwNumWords = kwNumWords + " word keywords";
    } else if(kwString.toLowerCase().indexOf("query") != -1) {
    	debug("query text: " + keywordText + " (" + kwNumWords + ")");
    	kwNumWords = kwNumWords + " word queries";
    }
    
    if(!kwList[matchType]) {
      //Logger.log("new ad");
      kwList[matchType] = new Object();
      kwList[matchType].clicks = clicks;
      kwList[matchType].impressions = impressions;
      kwList[matchType].cost = cost;
      kwList[matchType].value = value;
      kwList[matchType].conversions = conversions;
      kwList[matchType].pos = avgPos * impressions;
      kwKeys.push(matchType);
    } else {
      //Logger.log("existing...");
      kwList[matchType].clicks += clicks;
      kwList[matchType].impressions += impressions;
      kwList[matchType].cost += cost;
      kwList[matchType].value += value;
      kwList[matchType].conversions +=conversions;
      kwList[matchType].pos += (avgPos * impressions);
    }
    if(!kwList[kwNumWords]) {
      //Logger.log("new ad");
      kwList[kwNumWords] = new Object();
      kwList[kwNumWords].clicks = clicks;
      kwList[kwNumWords].impressions = impressions;
      kwList[kwNumWords].cost = cost;
      kwList[kwNumWords].value = value;
      kwList[kwNumWords].conversions = conversions;
      kwList[kwNumWords].pos = avgPos * impressions;
      kwKeys.push(kwNumWords);
    } else {
      //Logger.log("existing...");
      kwList[kwNumWords].clicks += clicks;
      kwList[kwNumWords].impressions += impressions;
      kwList[kwNumWords].cost += cost;
      kwList[kwNumWords].value += value;
      kwList[kwNumWords].conversions += conversions;
      kwList[kwNumWords].pos += avgPos * impressions;
    }
  }
  
  Logger.log(kwKeys.length + " Unique Keyword Types Evaluated");
  
  for(var i=0; i < kwKeys.length; i++) {
   
    var kwKey = kwKeys[i];
    var kw = kwList[kwKey];
    var clicks = kw.clicks;
    var impressions = kw.impressions;
    var cost = kw.cost;
    var value = kw.value;
    var pos = kw.pos;
    var conversions = kw.conversions;
    var roas = (cost==0)? 0 : value / cost;
    var ctr = (impressions==0)? 0 : clicks / impressions;
    var avgCpc = (clicks==0)? 0 : cost/ clicks;
    var pos = (impressions==0)? 0 : pos / impressions;
    var costPerConversion = (conversions==0)? 0 : cost / conversions;
    //Logger.log(adKey + " " + clicks + " " + impressions + " " + cost);
    if(DEBUG == 1) Logger.log(kwKey + " clicks: " + clicks);
    sheet.appendRow([reportDates.niceStartDate, reportDates.niceEndDate, kwKey, clicks, impressions, cost, ctr, avgCpc, conversions, costPerConversion, value, roas, pos]);
  }
}

function addStyleToSheet(dataSheet) {
  var numRows = dataSheet.getDataRange().getNumRows();
  var numColumns = dataSheet.getDataRange().getNumColumns();
  dataSheet.getRange(2, 4, numRows, 1).setNumberFormat("#,##0"); //clicks
  dataSheet.getRange(2, 5, numRows, 1).setNumberFormat("#,##0"); // imp
  dataSheet.getRange(2, 6, numRows, 1).setNumberFormat("#,##0.00"); // cost
  dataSheet.getRange(2, 7, numRows, 1).setNumberFormat("0.00%"); // ctr
  dataSheet.getRange(2, 8, numRows, 1).setNumberFormat("#,##0.00"); // avg cpc
  dataSheet.getRange(2, 9, numRows, 1).setNumberFormat("#,##0.00"); //conversions
  dataSheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00"); // cost / conv
  dataSheet.getRange(2, 11, numRows, 1).setNumberFormat("#,##0.00"); // conv value
  dataSheet.getRange(2, 12, numRows, 1).setNumberFormat("0.00"); // conv value / cost
  dataSheet.getRange(2, 13, numRows, 1).setNumberFormat("#,##0.00"); // avg pos
  // sort on type column
  dataSheet.getRange(2, 1, numRows, numColumns).sort(3);
}

Date.prototype.yyyymmdd = function() {
  var yyyy = this.getFullYear().toString();
  var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based
  var dd  = this.getDate().toString();
  return yyyy + (mm[1]?mm:"0"+mm[0]) + (dd[1]?dd:"0"+dd[0]); // padding
};

function setUpReportInGoogleSheets(spreadsheetUrl, spreadsheetName, accountManagers, overWriteOldData, sheetNames, folderNames) {
    
    var destinationSpreadsheet = new Object();
    if(folderNames) {
      var folderStructure = folderNames.split(",");
    } else {
      var folderStructure = new Array();
    }
    
    
    var targetFolder = DriveApp.getRootFolder();
    for(var i = 0; i < folderStructure.length; i++) {
      var folderName = folderStructure[i];
      if(folderName.toLowerCase().indexOf("[account id]") != -1) {
        folderName = AdWordsApp.currentAccount().getCustomerId();
      } else if(folderName.toLowerCase().indexOf("[account name]") != -1) {
        folderName = AdWordsApp.currentAccount().getName();
      }
      Logger.log("folderName: " + folderName);
      var foldersIterator = targetFolder.getFoldersByName(folderName);
      if (foldersIterator.hasNext()) {
        
        targetFolder = foldersIterator.next();
        Logger.log("Selected target folder: " + folderName);
      } else {
        if(DEBUG==1) Logger.log("Creating a new folder: " + folderName);
        targetFolder = targetFolder.createFolder(folderName);
      }
    }
    
    
    destinationSpreadsheet.overWrite = overWriteOldData;
    
    if(!spreadsheetUrl || spreadsheetUrl == "" || spreadsheetUrl == " " || spreadsheetUrl.toLowerCase().indexOf("new") != -1) var isNew = 1;
    destinationSpreadsheet.isNew = isNew;
    
    if(!sheetNames || !sheetNames[0]) {
      var sheetNames = new Array();
      sheetNames[0] = "Sheet 1";
    }
    
    if(isNew)
    {
      var spreadsheet = SpreadsheetApp.create(spreadsheetName);
      var id = spreadsheet.getId();
      var spreadsheetUrl = spreadsheet.getUrl();
      var file = DriveApp.getFileById(id);
      targetFolder.addFile(file);
      if(folderName) DriveApp.getRootFolder().removeFile(file);
    } 
    var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
    destinationSpreadsheet.spreadsheet = spreadsheet;
    destinationSpreadsheet.url = spreadsheet.getUrl();
    
    // IF NEW -> REMOVE ALL SHEETS, THEN CREATE ALL SHEETS
    if(isNew){
      var allSheets = spreadsheet.getSheets(); 
      
      // remove
      for(var i=1,len=allSheets.length;i<len;i++){
        spreadsheet.deleteSheet(allSheets[i]);
      }
      
      // create
      allSheets[0].setName(sheetNames[0]);
      for(var sheetCounter = 1; sheetCounter < sheetNames.length; sheetCounter++) {
        var sheetName = sheetNames[sheetCounter];
        if(DEBUG == 1) Logger.log("sheet name: " + sheetName);
        spreadsheet.insertSheet(sheetName);
      }
    } else {
      // IF NOT NEW, MAKE SURE RIGHT SHEETS EXIST
      for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) {
        var sheetName = sheetNames[sheetCounter];
        if(DEBUG == 1) Logger.log("checking if sheet with name exists: " + sheetName);
        try {
          var thisSheet = spreadsheet.getSheetByName(sheetName);
          if(!thisSheet) spreadsheet.insertSheet(sheetName);
        } catch (e) {
          Logger.log(e);
        }
      }
    }
    
    
    // ADD ACCOUNT MANAGERS
    if(accountManagers && accountManagers!=""){
      var accountManagersArray = accountManagers.replace(/\s/g, "").split(",");
      for(var i=0; i < accountManagersArray.length; i++) {
        var accountManager = accountManagersArray[i];
        try {
          spreadsheet.addEditor(accountManager);
        } catch(e){
          debug("could not grant spreadsheet permissions to " + accountManager + ". " + e);
        }
      }
    }
    
    // IF OVERWRITE, CLEAR SHEETS
    if(overWriteOldData) {
      for(var sheetCounter = 0; sheetCounter < sheetNames.length; sheetCounter++) {
        var sheetName = sheetNames[sheetCounter];
        if(DEBUG == 1) Logger.log("sheet name: " + sheetName);
        try {
          var thisSheet = spreadsheet.getSheetByName(sheetName);
          if(thisSheet) thisSheet.clear();
        } catch (e) {
          Logger.log(e);
        }
      }
    }
    return(destinationSpreadsheet);
  } 
  
function getReportDates(time) {
    var timeZone = AdWordsApp.currentAccount().getTimeZone();
	var date = new Date();
    var dt =Utilities.formatDate(date, timeZone, 'MMMM dd, yyyy HH:mm:ss');
	today = new Date(dt);
	
    var reportDates = new Object();
    
    switch(time) {
      case "LAST_30_DAYS":
        var startDate = new Date().setDate(today.getDate()-30);
        var endDate = new Date().setDate(today.getDate()-1);
        break;
      case "LAST_14_DAYS":
        var startDate = new Date().setDate(today.getDate()-14);
        var endDate = new Date().setDate(today.getDate()-1);
        break;
      case "LAST_7_DAYS":
        var startDate = new Date().setDate(today.getDate()-7);
        var endDate = new Date().setDate(today.getDate()-1);
        break;
      case "TODAY":
        var startDate = new Date().setDate(today);
        var endDate = new Date().setDate(today);
        break;
      case "YESTERDAY":
        var startDate = new Date().setDate(today.getDate()-1);
        var endDate = new Date().setDate(today.getDate()-1);
        break;
      case "THIS_MONTH":
        var startDate = new Date(today.getFullYear(), today.getMonth(), 1);
        var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());
        var lastPossibleDate = new Date(today.getFullYear(), today.getMonth()+1, 0);
        var reportLastPossibleDate = Utilities.formatDate(lastPossibleDate, "America/Los_Angeles", "yyyyMMdd");
        reportDates.reportLastPossibleDate = reportLastPossibleDate;
        reportDates.lastPossibleDate = lastPossibleDate;
        break;
      case "LAST_MONTH":
        var startDate = new Date(today.getFullYear(), today.getMonth()-1, 1);
        var endDate = new Date(today.getFullYear(), today.getMonth(), 0);
        var niceStartDate = Utilities.formatDate(new Date(startDate), "America/Los_Angeles", "yyyy-MM-dd");
        var niceEndDate = Utilities.formatDate(new Date(endDate),  "America/Los_Angeles", "yyyy-MM-dd");
        break;
      case "THIS_WEEK_SUN_TODAY":
        var tempDate = today;
        var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay()));
        var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());
        break;
      case "THIS_WEEK_MON_TODAY":
        var tempDate = today;
        var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() + 1));
        var endDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());
        break;
      case "LAST_WEEK": // mon-sun
        var tempDate = today;
        var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 6));
        var tempDate = new Date();
        var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay()));
        break;
      case "LAST_WEEK_SUN_SAT": // mon-sun
        var tempDate = today;
        var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 7));
        var tempDate = new Date();
        var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay() -1));
        break;
      case "LAST_BUSINESS_WEEK": // mon-fri
        var tempDate = today;
        var startDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 6));
        var tempDate = new Date();
        var endDate = new Date(tempDate.setDate(today.getDate() - today.getDay() - 2));
        break;
    }
    
    var niceStartDate = Utilities.formatDate(new Date(startDate), "America/Los_Angeles", "yyyy-MM-dd");
    var niceEndDate = Utilities.formatDate(new Date(endDate),   "America/Los_Angeles", "yyyy-MM-dd");
   
    reportDates.niceStartDate = niceStartDate;
    reportDates.niceEndDate = niceEndDate;
	
    return(reportDates);
    
  }

function getFloat (input) {
    if(!input || input == "" || typeof(input) === 'undefined') var input = "0.0";
    input = input.toString();
    var output = parseFloat(input.replace(/,/g, ""));
    return output;
  }

function debug(log) {
  
  if(DEBUG) Logger.log(log);
}

function sendEmailNotifications(emailAddresses, subject, body, emailType ) {
	
    if(emailAddresses) {
      if(emailType.toLowerCase().indexOf("warning") != -1) {
        var finalSubject = "[Warning] " + subject + " - " + AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")"
      } else if(emailType.toLowerCase().indexOf("notification") != -1) {
        var finalSubject = "[Notification] " + subject + " - " + AdWordsApp.currentAccount().getName() + " (" + AdWordsApp.currentAccount().getCustomerId() + ")"
      }
      
      if(AdWordsApp.getExecutionInfo().isPreview()) {
        var finalBody = "<b>This script ran in preview mode. No changes were made to your account.</b><br/>" + body;
      } else {
        var finalBody = body;
      }
      
      var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
      debug("emailQuotaRemaining: " + emailQuotaRemaining);
      
      if(emailQuotaRemaining > 0) {
        MailApp.sendEmail({
          to:emailAddresses, 
          subject:  finalSubject,
          htmlBody: finalBody
        });
        return("email sent to " + emailAddresses + ": " + finalSubject)
      } else {
        var text = "You have run out of email quota for today. No more emails can be sent today.";
        debug(text);
        return(text);
      }
      
      //if(DEBUG == 1) Logger.log("email sent to " + emailAddresses + ": " + finalSubject);
    }	
  }