Advanced Keyword Analyser (Match Type, string Length and N-Gram)

Analysing keywords by match type, string length and n-grams is not particularly easy within Google Ads, as it requires the export of several reports, the creation of custom code to determine the match type, and then the use of pivot tables for the analysis.

However, this report often highlights areas for improvement within your account. You can see which match types are performing the best, which specific words are performing the best, and whether long or shorter tailed keywords perform best within your account.

So, we built a script that creates these three reports for you automatically.

It creates two tables in the first sheet. The top table looks at the keyword performance based on the number of words within the keyword. For example, if the keyword is “men’s blue socks”, this is three words long, and the data is aggregated in the third row.

The bottom table looks at the performance of keywords based on their match type. This allows you to spot poor-performing match types and excessive use of broad match keywords.

In the sheet, several tabs are created along the bottom showing n-gram performance.

For example, 2-Gram looks at the best-performing two-word phrases within your keywords. This helps you identify high and low-performance n-grams that you can either bid up or add as negative keywords.

As with most of our scripts, we have created a control panel for you to customise the reports.

• Date range: You need to set the date range that you want to analyse in the format ‘year-month-day’. This can be done on lines 12 and 13.
• Include only enabled keywords: You need to decide if you only want to include enabled keywords or if you want to include paused ones, too. This can be set on line 16 to either true or false.
• Only include keywords with conversions: This setting allows you to select if you want to only include keywords that have at least one conversion. This can be set on line 19 to either true or false.
• Include or exclude campaigns: You can choose if you want to include or exclude certain campaigns from your reports. This can be done by defining them on lines 22 and 25. Remember to define campaign names within quotation marks.
• Your currency: You need to define the currency that your account runs in on line 28. You can do this by entering the currency symbol within the quotation marks, eg “£” or “$”.

Below is the script, which you can paste into your account in the Bulk Operations section.

Enjoyed this script? Here are another 153 Google Ads scripts that you should check out!

/*
Script to produce a spreadsheet giving key stats for keywords split by match type and the number of words and then for the different length ngrams.
*/
///////////////////////// BEGIN USER EDIT AREA /////////////////////////////
//Enter start and end dates in this format: dd-mm-yyyy
var startDate = "07-01-2018";
var endDate = "14-04-2018"
//Only include enabled keywords?
var enabledOnly = false;
//Only include keywords with conversions?
var withConversionsOnly = false;
//Only include campaigns with this text in the name (leave blank for all)
var campaignNameContains = '';                // e.g. "Brand";
//Exclude campaigns with this text in the name (leave blank if you don't want to exclude any)
var campaignNameDoesNotContain = '';          // e.g. "France"
//Enter here the currency used for the cost
var currency = "£";
///////////////////////// END USER EDIT AREA ///////////////////////////////
function main() {
  var ids = getIds()
  
  var fields = 'Impressions,Clicks,Conversions,Cost'
  var calculatedFields = 'CTR, Conversion Rate, Cost Per Conversion'
  var ngramMax = 8.0
  
  var campaignIds = getIds()
  
  var temp = getData(fields,startDate,endDate,ngramMax,ids,enabledOnly,withConversionsOnly)
  var matchTypeDict = temp[0]
  var numberWordsDict = temp[1]
  var ngramDict = temp[2]
  
  var accountName = AdWordsApp.currentAccount().getName()
  var ss = SpreadsheetApp.create("Keyword Analysis | "+ accountName);
   
  var ngramHeaders = ['Ngram'].concat(fields.split(',')).concat(calculatedFields.split(','))
  for (var dict in ngramDict) {  
    writeOutDict(ngramDict[dict],ngramHeaders,ss,dict+'-Gram')   
  }
   
  var numberWordsHeader = ['Number Words'].concat(fields.split(',').concat(calculatedFields.split(',')))
  writeOutDict(numberWordsDict,numberWordsHeader,ss,'Number Words - Match Type')
  var matchTypeHeader = ['Match Type'].concat(fields.split(',')).concat(calculatedFields.split(','))
  writeOutDict(matchTypeDict,matchTypeHeader,ss,'Number Words - Match Type',10)
  var spareSheet = ss.getSheetByName('Sheet1')
  if (spareSheet) {
    ss.deleteSheet(spareSheet)
  } 
  Logger.log('Spreadsheet has been created at the following url - '+ ss.getUrl())
}
function getData(fields, startDate,endDate,ngramMax,ids,enabledOnly,withConversionsOnly) {
  var ngramsDict = {}
  var numberWordsDict = {}
  var matchTypeDict = {}
  
  var startDate = convertDate(startDate)
  var endDate = convertDate(endDate)
  
  var headers = ['Campaign','AdGroup','Keyword','Match Type','Number Words'].concat(fields.split(',')) 
  var res = [headers]
  var query = 'SELECT CampaignName, AdGroupName, Criteria, KeywordMatchType, ' + fields + ' FROM KEYWORDS_PERFORMANCE_REPORT '+
                                 'WHERE Cost > 0 '+
                                 'AND AdNetworkType1 = "SEARCH" '+
                                 'AND CampaignId IN [' + ids + '] ' 
  
  if (enabledOnly) {
    query += 'AND Status = ENABLED '
    query += 'AND CampaignStatus = ENABLED '
    query += 'AND AdGroupStatus = ENABLED ' 
  }
  
  if (withConversionsOnly) {
    query += 'AND Conversions > 0 '
  }
  query += 'DURING ' + startDate + ',' + endDate
  
  var report = AdWordsApp.report(query)  
  var rows = report.rows()  
  while (rows.hasNext()) {
   var row = rows.next()
   var campaignName = row['CampaignName']
   var adgroupName = row['AdGroupName']
   var keyword = row['Criteria']
   
   var clicks = toInt(row['Clicks'])
   var impressions = toInt(row['Impressions'])
   var cost = toFloat(row['Cost'])
   var conversions = toInt(row['Conversions'])
   var numberWords = getLength(row['Criteria'])
   var matchType = getMatchType(row['KeywordMatchType'],row['Criteria'])
   var stats = [impressions,clicks,conversions,cost]
   //Logger.log([campaignName,adgroupName,keyword].concat(stats))
   
   addToNgrams(ngramsDict,keyword,stats,ngramMax)
   addToDict(numberWordsDict,numberWords,stats)
   addToDict(matchTypeDict,matchType,stats)
   }
  
  for (var dict in ngramsDict) {   
    addCalculatedMetrics(ngramsDict[dict])   
  }
   
  addCalculatedMetrics(numberWordsDict)
  addCalculatedMetrics(matchTypeDict)
  return [matchTypeDict,numberWordsDict,ngramsDict]
}
function addCalculatedMetrics(dict) {
  for (var key in dict) {
   var stats = dict[key] 
   var ctr = stats[1] > 0.0 ? stats[1] / stats[0] : 0.0
   var convRate = stats[2] > 0.0 ? stats[2] / stats[1] : 0.0
   var costPerConv = stats[2] > 0.0 ? stats[3] / stats[2] : 0.0
   dict[key] = stats.concat([ctr,convRate,costPerConv])
  } 
}
function getLength(criteria) {
  return criteria.length - criteria.replace(/ /g, '').length + 1
}
   
function getMatchType(matchType,criteria) {
  if ((matchType == 'Exact') || (matchType == 'Phrase')) {
   return matchType.toUpperCase()
  } else {
    if (criteria.indexOf('+') > -1) {
      return 'BMM'
    } else {
     return 'BROAD' 
    }
  }
}
function addToNgrams(ngramsDict,keyword,stats,ngramMax) {
 var ngrams = getNgrams(keyword,ngramMax)
 for (var i = 0; i < ngrams.length; i++) {
  var ngram = ngrams[i]
  var length = getLength(ngram)
  if (!ngramsDict[length]) {
    ngramsDict[length] = {}
  }
  var relDict = ngramsDict[length]
  addToDict(relDict,ngram,stats)
 }
}
function addToDict(dict,key,stats) {
  if (!dict[key]) {
   dict[key] = [0,0,0,0] 
  }
  var currentStats = dict[key]
  for (var j = 0; j < stats.length; j++) {
   currentStats[j] = currentStats[j] + stats[j] 
  }
  dict[key] = currentStats
}
  
function getNgrams(string,maxNGrams) {
  var string = string.toLowerCase()
  var split = replaceAllPlus(string).split(' ')
  var ngrams = []
  for (var i = 1; i <= maxNGrams; i++) {
    var temp = getSpecificNgrams(split,i)
    ngrams = ngrams.concat(temp)
  }
  return ngrams
}
function getSpecificNgrams(split,len) {
  var res = []
  for (var i = 0; (i + len) <= split.length; i++) {
    var temp = split.slice(i,i+len).join(' ')
    res.push(temp)
  }
  return res
}
function writeOut(data,ss,sheetName,skipRows) {
  if (data.length <= 1) {return}
 var skipRows = skipRows || 0
 var sheet = ss.getSheetByName(sheetName)
 if (!sheet) {
   ss.insertSheet(sheetName,1)
   var sheet = ss.getSheetByName(sheetName)
 }
 if (skipRows == 0) {sheet.clear()}
  
 sheet.getRange(skipRows+1,1,data.length,data[0].length).setValues(data).setBorder(true,true,true,true,false,false)
 sheet.getRange(skipRows+2,1,data.length-1,data[0].length).sort(1)
 sheet.getRange(skipRows+1,1,1,data[0].length).setBackground('#00008B').setFontColor('white').setFontWeight('bold')
 sheet.getRange(skipRows+2,5,data.length-1,1).setNumberFormat(currency+"###,###.#")
 sheet.getRange(skipRows+2,6,data.length-1,1).setNumberFormat("##.#%")
 sheet.getRange(skipRows+2,7,data.length-1,1).setNumberFormat("##.#%")
 sheet.getRange(skipRows+2,8,data.length-1,1).setNumberFormat(currency+"###,###.#")
}
function writeOutDict(dict,headers,ss,sheetName,skipRows) {
  var res = [headers]                                                                                          
  for (var key in dict) {
    var newRow = [key].concat(dict[key])
    res.push(newRow)
  }
 writeOut(res,ss,sheetName,skipRows)
}
function toFloat(value) {
  value = replaceAllCommas(value.toString())
  return parseFloat(value);
}
function toInt(value) {
  value = replaceAllCommas(value.toString())
  return parseInt(value);
}
function replaceAllCommas(string) {
 return string.replace(/,/g, '')
}
function replaceAllPlus(string) {
 return string.replace(/\+/g, '') 
}
function getIds() {
  if (campaignNameDoesNotContain == '') {campaignNameDoesNotContain = 'THIS_IS_A_DUMMY_VAR_USED_IF_DOES_NOT_CONTAIN_IS_NOT_BEING_USED'}
 var campaigns = AdWordsApp.campaigns()
  .withCondition('CampaignName CONTAINS "'+campaignNameContains +'"')
  .withCondition('CampaignName DOES_NOT_CONTAIN "'+campaignNameDoesNotContain +'"')
  .get()
 var ids = []
 //Logger.log('There are '+campaigns.totalNumEntities() + ' campaigns which meet the filters')
 while(campaigns.hasNext()) {
  ids.push(campaigns.next().getId()) 
 }
 return ids.join(',')
}
function convertDate(prettyDate) {
 var sp = prettyDate.split('-')
 return [sp[2],sp[1],sp[0]].join('')
}