Script Download: Performance By Match Type And Keyword Length

Instructions specific to this script:
Important: Optmyzr’s Enhanced scripts are not yet compatible with Google’s new scripts experience. So, it is recommended to not move scripts to the new experience in Google Ads, as it may result in scripts not working as expected. Our team is actively working on upgrading to the new version and will keep you updated about the progress of the same.

New to scripts ?
See step by step instructions for running AdWords Scripts in general.
Need Assistance? We are here to help! Contact Us

Script Code

Copy script code into your Clipboard.

function main(){
  
// currentSetting: Edit these
  var accountName = AdWordsApp.currentAccount().getName();
    var spreadsheetUrl = "new";
  var accountManagers = ""; //currentSetting["Share_Results_Spreadsheet_With_These_Google_Accounts"];
    var emailAddress = ""; //currentSetting["Email_Addresses_To_Notify"];//"example@example.com";
    var time = "LAST_30_DAYS"; //currentSetting["Range"];
  
  
  
  var reportDate = new Date();
  var dateForFilename = reportDate.yyyymmdd();
  if(spreadsheetUrl.toLowerCase().indexOf("new") != -1)
  {
    var spreadsheet = SpreadsheetApp.create("Keyword Type Stats - " + accountName + " - " + dateForFilename + " (" + time + ")");
    var spreadsheetUrl = spreadsheet.getUrl();
  } 
  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  if(accountManagers && accountManagers!=""){
	var accountManagersArray = accountManagers.replace(/\s/g, "").split(",");
	spreadsheet.addEditors(accountManagersArray);
  }
  
  // Sheet for Keyword Report
  //get all sheets except first and delete them and insert new sheets every time to avoid name error
  var allSheets = spreadsheet.getSheets();
  
  for(var i=1,len=allSheets.length;i<len;i++){
	spreadsheet.deleteSheet(allSheets[i]);
  }
  
  allSheets[0].setName("Keywords");
  var kwSheet = allSheets[0];
  kwSheet.appendRow(["Type", "Clicks", "Impressions", "Cost", "CTR", "Avg. CPC", "Conversions", "Conv. Value", "ROAS", "Avg Pos."]);
  kwSheet.setFrozenRows(1);
  
  // Sheet for Query report
  var querySheet = spreadsheet.insertSheet("Queries");
  querySheet.appendRow(["Type", "Clicks", "Impressions", "Cost", "CTR", "Avg. CPC", "Conversions", "Conv. Value", "ROAS", "Avg Pos."]);
  querySheet.setFrozenRows(1);
  
  //var sheetNames = spreadsheet.getSheets();
  
  //var sheet1 = spreadsheet.getSheetByName("Sheet1");
  //spreadsheet.deleteSheet(sheet1);
  
  
  
  var report = AdWordsApp.report(
    'SELECT KeywordMatchType, KeywordText, Clicks, Impressions, Cost, ConversionValue, ConvertedClicks, AveragePosition ' +
    'FROM KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE Cost > 0 ' +
    'DURING ' + time
  );
  var rows = report.rows();
  processReport(kwSheet, rows, "KeywordText", "KeywordMatchType");
  
  var report = AdWordsApp.report(
    'SELECT MatchTypeWithVariant, KeywordTextMatchingQuery, Clicks, Impressions, Cost, ConversionValue, ConvertedClicks, AveragePosition ' +
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'WHERE Cost > 0 ' +
    'DURING ' + time
  );
  var rows = report.rows();
  processReport(querySheet, rows, "KeywordTextMatchingQuery", "MatchTypeWithVariant")
  
  var numRows = querySheet.getDataRange().getNumRows();
  var numColumns = querySheet.getDataRange().getNumColumns();
  querySheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");
  querySheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");
  querySheet.getRange(2, 5, numRows, 1).setNumberFormat("0.00%");
  querySheet.getRange(2, 4, numRows, 1).setNumberFormat("$#,##0.00");
  querySheet.getRange(2, 6, numRows, 1).setNumberFormat("$#,##0.00");
  querySheet.getRange(2, 8, numRows, 1).setNumberFormat("$#,##0.00");
  querySheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");
  querySheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");
  querySheet.getRange(2, 1, numRows, numColumns).sort(1);
  
  var numRows = kwSheet.getDataRange().getNumRows();
  var numColumns = kwSheet.getDataRange().getNumColumns();
  kwSheet.getRange(2, 2, numRows, 1).setNumberFormat("#,##0");
  kwSheet.getRange(2, 3, numRows, 1).setNumberFormat("#,##0");
  kwSheet.getRange(2, 5, numRows, 1).setNumberFormat("0.00%");
  kwSheet.getRange(2, 4, numRows, 1).setNumberFormat("$#,##0.00");
  kwSheet.getRange(2, 6, numRows, 1).setNumberFormat("$#,##0.00");
  kwSheet.getRange(2, 8, numRows, 1).setNumberFormat("$#,##0.00");
  kwSheet.getRange(2, 9, numRows, 1).setNumberFormat("0.00%");
  kwSheet.getRange(2, 10, numRows, 1).setNumberFormat("#,##0.00");
  kwSheet.getRange(2, 1, numRows, numColumns).sort(1);
  
    var notes = "Full report: " + spreadsheetUrl;
  
  if(emailAddress) {
    MailApp.sendEmail(emailAddress, "[NOTIFICATION] Keyword and Query Report for " + AdWordsApp.currentAccount().getName(), "Your report for the performance of different types of keywords and queries is ready: "+spreadsheet.getUrl() + "\n\n" + "Date Range for the report: " + time);
    }
    return(notes);
  
}
 
function processReport(sheet, rows, kwString, matchTypeString) {
  
  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 = parseFloat(row['Cost']);
    var value = parseFloat(row['ConversionValue']);
    var conversions = parseInt(row['ConvertedClicks']);
    var avgPos = parseFloat(row['AveragePosition']);
    var matchType = row[matchTypeString];
    var keywordText = row[kwString];
    var kwNumWords = keywordText.split(" ").length;
    
    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 = value / cost;
    var ctr = clicks / impressions;
    var avgCpc = cost/ clicks;
    var pos = pos / impressions;
    //Logger.log(adKey + " " + clicks + " " + impressions + " " + cost);
    Logger.log(kwKey + " clicks: " + clicks);
    sheet.appendRow([kwKey, clicks, impressions, cost, ctr, avgCpc, conversions, value, roas, pos]);
  } 
}

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
};

Questions? Feel Free to Contact Us .