Script Download: Performance By Match Type And Keyword Length

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.