Manage close variants to automatically exclude as negative keywords

Exclude Close Variants

// Report on how close variants relate to your keywords in Google Ads
// Automatically exclude queries with poor performance or a big Levenshtein distance
// Free AdWords Script courtesy of Optmyzr.com
// October 22, 2018

function main() {

  // -----------------
  // Edit this section with your preferences
  // ----------------
  var time = 'LAST_30_DAYS';          // the date range for the report with performance data
  var reportVersion = 'v201809';      // the API version to use
  var emailAddresses = '[email protected]';  // the email address to send an email with the generated report
  var atLeastThisManyImpressionsForTheQuery = 15;    // only report on queries with at least this many impressions. Useful to be able to process larger accounts by limiting the number of queries to include in the data
  var accountManagers = '[email protected]';   // give Google spreadsheet access to this username
  var spreadsheetUrl = 'new';
  var includeLevinsthein = 0; // set value to 0 if your script times out. by not adding this score, the script will run faster
  // use 1 or none of the following two settings to limit the campaigns analyzed
  var campaignNameContains = ""; // this is NOT case sensitive
  var campaignNameDoesNotInclude = ""; // this is NOT case sensitive
  //-------
  var addNegativesBasedOnPerformance = true;  // Set to false or true. Set to 'true' if you want to add negative keywords for queries with cost but no conversions
  var maxAllowedNonConvertingCost = 5;   // this setting is used if the previous setting is 'true'. It specifies the maximum allowed cost without conversions before a negative keyword is added
  //-------
  var addNegativesBasedOnLev = true;  // Set to false or true. Set to 'true' to add negative keywords for queries with a certain Levenshtein distance score
  var maxAllowedLevDiff = 4; // this setting is used if the previous setting is 'true'. It determines the biggest allowed Levenshtein distance score before a negative keyword will be added.
  
  // -------------------
  // Don't make edits after this unless you know how to write scripts
  // -------------------
  
  var map = new Array();
  
  // Get Campaign IDs
  var campaignsToCheck = new Array();
  if(campaignNameDoesNotInclude) {
  	if(campaignNameDoesNotInclude.indexOf("'") != -1)
    	{
      	var doesNotContainString = '"' + campaignNameDoesNotInclude + '"';
    	} else {
    	  var doesNotContainString = "'" + campaignNameDoesNotInclude + "'";
   	 }
  	var campaigns = AdWordsApp.campaigns()
    .withCondition('Name DOES_NOT_CONTAIN_IGNORE_CASE ' + doesNotContainString)
    .withCondition('Status != REMOVED')
    .get();
  } else if(campaignNameContains) {
        if(campaignNameContains.indexOf("'") != -1)    {
      	var containsString = '"' + campaignNameContains + '"';
    	} else {
    	  var containsString = "'" + campaignNameContains + "'";
   	 }
  	var campaigns = AdWordsApp.campaigns()
    .withCondition('Name CONTAINS_IGNORE_CASE ' + containsString)
    .withCondition('Status != REMOVED')
    .get();
            } else {
    var campaigns = AdWordsApp.campaigns()
    .withCondition('Status != REMOVED')
    .get();
  }
  
  while(campaigns.hasNext()) {
  	var campaign = campaigns.next();
  	var campaignId = campaign.getId();
    Logger.log(campaignId);
  	campaignsToCheck.push(campaignId);
 
  }
    
  
  
  // Keywords
  var query =
    'SELECT Id, KeywordMatchType, Criteria, Clicks, Impressions, Cost, ConversionValue, Conversions, AveragePosition, Ctr, AverageCpc, AdGroupName, CampaignName, CampaignId, AdGroupId ' +
    'FROM KEYWORDS_PERFORMANCE_REPORT ' +
    'WHERE Impressions > 0 ' +
    'AND CampaignId IN ' + JSON.stringify(campaignsToCheck) + " " +
    'DURING ' + time;
  
  var report = AdWordsApp.report(query,{apiVersion: reportVersion});
  var rows = report.rows();
  
  while(rows.hasNext()) {
    var row = rows.next();
    var adGroupId = row['AdGroupId'];
    var id = row['Id'];
    var criteria = row['Criteria'];
    //Logger.log(row['Criteria']);
    
    if(criteria.indexOf('+') != -1) {
      	var subMatchType = "BMM";
    } else {
    	var subMatchType = row['KeywordMatchType'];
    }
    
    var key = adGroupId + "-" + id;
    
    if(!map[key]) {
    	map[key] = new Object();
    	map[key].keyword = new Object();
    	map[key].searchTerms = new Array();
    	map[key].keyword.criteria = row['Criteria'];
      	map[key].keyword.clicks = parseInt(row['Clicks'],10);
      	map[key].keyword.impressions = parseInt(row['Impressions'],10);
      	map[key].keyword.cost = getFloat(row['Cost']);
      	map[key].keyword.conversions = getFloat(row['Conversions']);
      	map[key].keyword.ctr = getFloat(row['Ctr']);
      	map[key].keyword.averagePosition = getFloat(row['AveragePosition']);
      	map[key].keyword.averageCpc = getFloat(row['AverageCpc']);
      	map[key].keyword.conversionValue = getFloat(row['ConversionValue']);
      	map[key].keyword.matchType = row['KeywordMatchType'];
      	map[key].keyword.subMatchType = subMatchType;
      	map[key].keyword.campaignName = row['CampaignName'];
      	map[key].keyword.adGroupName = row['AdGroupName'];
      	map[key].keyword.adGroupId = row['AdGroupId'];
    }
    
  }
  
  Logger.log("");
  // Search Terms
  var query =
    'SELECT KeywordId, KeywordTextMatchingQuery, Query, QueryMatchTypeWithVariant, Clicks, Impressions, Cost, ConversionValue, Conversions, AveragePosition, Ctr, AverageCpc, AdGroupName, CampaignName, CampaignId, AdGroupId ' +
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'WHERE Impressions > ' + atLeastThisManyImpressionsForTheQuery + " " + 
      'AND CampaignId IN ' + JSON.stringify(campaignsToCheck) + " " +
    'DURING ' + time;
  
  var report = AdWordsApp.report(query,{apiVersion: reportVersion});
  var rows = report.rows();
  
  while(rows.hasNext()) {
    var row = rows.next();
    var campaignId = row['CampaignId'];
    var adGroupId = row['AdGroupId'];
    var id = row['KeywordId'];
    var query = row['Query'];
    
    //Logger.log(query);
    var key = adGroupId + "-" + id;
    var adGroupIdInArray = new Array(adGroupId);
  
    if(!map[key]) {
      // most likely a shopping campaign
      // Logger.log("query not associated with kw in campaign: " + row['CampaignName']);
    } else {
  		if(!map[key].searchTerms[query]) {
    		map[key].searchTerms[query] = new Object();
      		map[key].searchTerms[query].matchTypeVariant = row['QueryMatchTypeWithVariant'];
    		map[key].searchTerms[query].clicks = parseInt(row['Clicks'],10); 
          	map[key].searchTerms[query].impressions = parseInt(row['Impressions'],10);
      		map[key].searchTerms[query].cost = getFloat(row['Cost']);
      		map[key].searchTerms[query].conversions = getFloat(row['Conversions']);
      		map[key].searchTerms[query].ctr = getFloat(row['Ctr']);
      		map[key].searchTerms[query].averagePosition = getFloat(row['AveragePosition']);
      		map[key].searchTerms[query].averageCpc = getFloat(row['AverageCpc']);
      		map[key].searchTerms[query].conversionValue = getFloat(row['ConversionValue']);
      		map[key].searchTerms[query].impressions = parseInt(row['Impressions'],10);
      		map[key].searchTerms[query].campaignName = row['CampaignName']; 
  		}
      
      	if(addNegativesBasedOnPerformance) {
    		if(map[key].searchTerms[query].cost > maxAllowedNonConvertingCost && !map[key].searchTerms[query].conversions) {
      	
              var negativeExactKeyword = '[' + query + ']';
          		var adGroupIterator = AdWordsApp.adGroups()
            						.withIds(adGroupIdInArray)
      								.get();
  				if (adGroupIterator.hasNext()) {
    				var adGroup = adGroupIterator.next();
    				adGroup.createNegativeKeyword(negativeExactKeyword);
  				}
          
    		}
   		}
    }
  }
  
  
  
  
  // Spreadsheet
  var reportDate = new Date();
  var dateForFilename = reportDate.yyyymmdd();
  if(spreadsheetUrl.toLowerCase().indexOf("new") != -1)
  {
    var spreadsheet = SpreadsheetApp.create("Keyword Analysis - " + AdWordsApp.currentAccount().getName() + " - " + 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(["Campaign Name", "Ad Group Name", "Match Type", "Sub Match Type", "Keyword", "Search Term", "Query Match Type with Variant", "Levenshtein distance", 
                     "KW Clicks", "KW Impressions", "KW Cost", "KW CTR", "KW Avg. CPC", "KW Conversions", "KW Conv. Value", "KW Avg Pos.",
                     "Query Clicks", "Query Impressions", "Query Cost", "Query CTR", "Query Avg. CPC", "Query Conversions", "Query Conv. Value", "Query Avg Pos."]);
  kwSheet.setFrozenRows(1);
  
  
  // Render
  for(var key in map) {
    //Logger.log(key);
    var criteria = map[key].keyword.criteria;
    var matchType = map[key].keyword.matchType;
    var subMatchType = map[key].keyword.subMatchType;
    var kwClicks = map[key].keyword.clicks;
    var kwImpressions = map[key].keyword.impressions;
    var kwConversions = map[key].keyword.conversions;
    var kwCost = map[key].keyword.cost;
    var kwCtr = map[key].keyword.ctr;
    var kwAverageCpc = map[key].keyword.averageCpc;
    var kwConversionValue = map[key].keyword.conversionValue;
    var kwAveragePosition = map[key].keyword.averagePosition;
    var campaignName = map[key].keyword.campaignName;
    var adGroupName = map[key].keyword.adGroupName;
    for(var query in map[key].searchTerms) {
      var queryClicks = map[key].searchTerms[query].clicks;
      var matchTypeVariant = map[key].searchTerms[query].matchTypeVariant;
      //Logger.log(criteria + " | " + query + " | " + matchType + "    " + matchTypeVariant);
      if(matchType.toLowerCase() != matchTypeVariant.toLowerCase()) {
      	//var difference = getDifference(criteria, query);
        //var diffLen = difference.length;
        var rawCriteria = criteria.replace(/\+/g,"");
        if(includeLevinsthein) {
          	var diffLen = levDist(rawCriteria, query);
          	if(addNegativesBasedOnLev) {
    			if(diffLen > maxAllowedLevDiff) {
      	
              		var negativeExactKeyword = '[' + query + ']';
                  	var adGroupIdInArray = new Array(adGroupId);
          			var adGroupIterator = AdWordsApp.adGroups()
            							.withIds(adGroupIdInArray)
      									.get();
  					if (adGroupIterator.hasNext()) {
    					var adGroup = adGroupIterator.next();
    					adGroup.createNegativeKeyword(negativeExactKeyword);
  					}
          
    			}
   			}
        } else {
          var diffLen = "";
        }
        var queryClicks = map[key].searchTerms[query].clicks;
    	var queryImpressions = map[key].searchTerms[query].impressions;
    	var queryConversions = map[key].searchTerms[query].conversions;
    	var queryCost = map[key].searchTerms[query].cost;
    	var queryCtr = map[key].searchTerms[query].ctr;
    	var queryAverageCpc = map[key].searchTerms[query].averageCpc;
    	var queryConversionValue = map[key].searchTerms[query].conversionValue;
    	var queryAveragePosition = map[key].searchTerms[query].averagePosition;
        //Logger.log(criteria + "     " + matchType + "      " + query + "      " + matchTypeVariant + "        " + kwClicks + "     " + "      " + diffLen);
        kwSheet.appendRow([campaignName, adGroupName, matchType, subMatchType, "'"+criteria, query, matchTypeVariant, diffLen, 
                           kwClicks, kwImpressions, kwCost, kwCtr, kwAverageCpc, kwConversions, kwConversionValue, kwAveragePosition,
                          queryClicks, queryImpressions, queryCost, queryCtr, queryAverageCpc, queryConversions, queryConversionValue, queryAveragePosition]);
      }
    }
  }
  
  
  // Notify
  Logger.log(spreadsheetUrl);
  var body = "your report is ready at: " + spreadsheetUrl;
  MailApp.sendEmail(emailAddresses, "[email protected]", "Your match type analysis is ready", body);
  
}

// date functions
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 getFloat (input) {
    if(!input || input == "" || typeof(input) === 'undefined') var input = "0.0";
    input = input.toString();
    var output = parseFloat(input.replace(/,/g, ""));
    return output;
  }

// Function: levDist
// Author James Westgate (https://stackoverflow.com/users/305319/james-westgate)
// Source: https://stackoverflow.com/questions/11919065/sort-an-array-by-the-levenshtein-distance-with-best-performance-in-javascript/11958496#11958496
// License: CC-BY-SA (https://creativecommons.org/licenses/by-sa/4.0/)
function levDist(s, t) {
    var d = []; //2d matrix

    // Step 1
    var n = s.length;
    var m = t.length;

    if (n == 0) return m;
    if (m == 0) return n;

    //Create an array of arrays in javascript (a descending loop is quicker)
    for (var i = n; i >= 0; i--) d[i] = [];

    // Step 2
    for (var i = n; i >= 0; i--) d[i][0] = i;
    for (var j = m; j >= 0; j--) d[0][j] = j;

    // Step 3
    for (var i = 1; i <= n; i++) {
        var s_i = s.charAt(i - 1);

        // Step 4
        for (var j = 1; j <= m; j++) {

            //Check the jagged ld total so far
            if (i == j && d[i][j] > 4) return n;

            var t_j = t.charAt(j - 1);
            var cost = (s_i == t_j) ? 0 : 1; // Step 5

            //Calculate the minimum
            var mi = d[i - 1][j] + 1;
            var b = d[i][j - 1] + 1;
            var c = d[i - 1][j - 1] + cost;

            if (b < mi) mi = b;
            if (c < mi) mi = c;

            d[i][j] = mi; // Step 6

            //Damerau transposition
            if (i > 1 && j > 1 && s_i == t.charAt(j - 2) && s.charAt(i - 2) == t_j) {
                d[i][j] = Math.min(d[i][j], d[i - 2][j - 2] + cost);
            }
        }
    }
    // Step 7
    return d[n][m];
}