Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Manage close variants to automatically exclude as negative keywords
// 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 = 'example@example.com'; // 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 = 'example@example.com'; // 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, "notifications@optmyzr.com", "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];
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.