Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create negatives where search queries are too dissimilar to the keywords they trigger. Based on containing the same words and/or edit distance.
/**
*
* Make Exact Match Exact
*
* Adds negatives for any search query that doesn't actually exactly match an exact
* match keyword.
*
* Version: 2.0
* Google AdWords Script maintained on brainlabsdigital.com
*
**/
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Options
// Use this if you want to exclude some campaigns. Case insensitive.
// For example ["Brand"] would ignore any campaigns with 'brand' in the name,
// while ["Brand","Competitor"] would ignore any campaigns with 'brand' or
// 'competitor' in the name.
// Leave as [] to not exclude any campaigns.
var campaignNameDoesNotContain = [];
// Use this if you only want to look at some campaigns. Case insensitive.
// For example ["Brand"] would only look at campaigns with 'brand' in the name,
// while ["Brand","Generic"] would only look at campaigns with 'brand' or 'generic'
// in the name.
// Leave as [] to include all campaigns.
var campaignNameContains = [];
//Choose whether the negatives are created, or if you just get an email to review
var makeChanges = false;
// These addresses will be emailed when the tool is run, eg "daniel@example.com"
// If there are multiple addresses then separate them with commas, eg "a@a.com, b@b.com"
// Leave as "" to not send any emails
var emailAddresses = "";
// Set to true to have the script check if keywords and search queries contain
// any of the same words, making a negative if not.
// Either this or useEditDistance must be set to true.
var useWordInCommon = true;
// Edit distance is a way of determining whether two words are similar or not
// based on how many letters would need to be changed to move from one to the other
// See here: https://en.wikipedia.org/wiki/Levenshtein_distance
// Please note that this is computationally expensive and may time out on larger accounts
var useEditDistance = false;
// If using edit distance, how many changes need to be made for words to be
// considered sufficiently distinct.
var maxEditDistance = 3;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
function main() {
var campaigns = {};
var adGroups = {};
var exactKeywords = [];
var exactGroupIds = {};
if (!useWordInCommon && !useEditDistance) {
throw("Must check for either word in common or edit distance");
}
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Pull a list of all exact match keywords in the account
var campaignIds = getCampaignIds();
var report = AdWordsApp.report(
"SELECT AdGroupId, Id, Criteria " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE Impressions > 0 AND KeywordMatchType = EXACT " +
"AND CampaignId IN [" + campaignIds.join(",") + "] " +
"AND AdGroupStatus IN [ENABLED, PAUSED] " +
"AND Status IN [ENABLED, PAUSED] " +
"DURING LAST_30_DAYS");
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var keywordId = row['Id'];
var adGroupId = row['AdGroupId'];
exactKeywords.push(adGroupId + "#" + keywordId);
exactGroupIds[adGroupId] = true;
if(!adGroups.hasOwnProperty(adGroupId)){
adGroups[adGroupId] = [[], [], []];
}
adGroups[adGroupId][2].push(row['Criteria'].toLowerCase().trim());
}
exactGroupIds = Object.keys(exactGroupIds);
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Remove ad groups with non-exact keywords
var nonExactGroupIds = {};
for (var i=0; i<exactGroupIds.length; i+=10000) {
var exactGroupIdsChunk = exactGroupIds.slice(i, i+10000);
var report = AdWordsApp.report(
"SELECT AdGroupId, Id " +
"FROM KEYWORDS_PERFORMANCE_REPORT " +
"WHERE KeywordMatchType != EXACT AND IsNegative = FALSE " +
"AND AdGroupId IN [" + exactGroupIdsChunk.join(",") + "] " +
"AND Status IN [ENABLED, PAUSED] " +
"DURING LAST_30_DAYS");
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var adGroupId = row['AdGroupId'];
nonExactGroupIds[adGroupId] = true;
}
}
var onlyExactGroupIds = [];
for (var i=0; i<exactGroupIds.length; i++) {
if (nonExactGroupIds[exactGroupIds[i]] == undefined) {
onlyExactGroupIds.push(exactGroupIds[i]);
}
}
Logger.log(onlyExactGroupIds.length + " ad groups (with only exact keywords) were found.");
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Pull a list of all exact (close variant) search queries
for (var i=0; i<onlyExactGroupIds.length; i+=10000) {
var onlyExactGroupIdsChunk = onlyExactGroupIds.slice(i, i+10000);
var report = AdWordsApp.report(
"SELECT Query, AdGroupId, CampaignId, KeywordId, KeywordTextMatchingQuery, Impressions, QueryMatchTypeWithVariant, AdGroupName " +
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE AdGroupId IN [" + onlyExactGroupIdsChunk.join(",") + "] " +
"DURING LAST_30_DAYS");
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var adGroupId = parseInt(row['AdGroupId']);
var campaignId = parseInt(row['CampaignId']);
var keywordId = parseInt(row['KeywordId']);
var searchQuery = row['Query'].toLowerCase().trim();
var keyword = row['KeywordTextMatchingQuery'].toLowerCase().trim();
var matchType = row['QueryMatchTypeWithVariant'].toLowerCase().trim();
if(keyword !== searchQuery && matchType.indexOf("exact (close variant)") !== -1){
if (adGroups[adGroupId][2].indexOf(searchQuery) > -1) {
// This query is a positive keyword in the ad group
// so we don't want to add is as a negative
continue;
}
if (
(useWordInCommon && hasWordInCommon(searchQuery, keyword))
|| (useEditDistance && editDistance(searchQuery, keyword) <= maxEditDistance)
) {
continue;
}
if(!campaigns.hasOwnProperty(campaignId)){
campaigns[campaignId] = [[], []];
}
campaigns[campaignId][0].push(searchQuery);
campaigns[campaignId][1].push(adGroupId + "#" + keywordId);
if(!adGroups.hasOwnProperty(adGroupId)){
adGroups[adGroupId] = [[], []];
}
adGroups[adGroupId][0].push(searchQuery);
adGroups[adGroupId][1].push(adGroupId + "#" + keywordId);
}
}
}
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Parse data correctly
var adGroupIds = [];
var adGroupNegatives = [];
for(var x in adGroups){
adGroupIds.push(parseInt(x));
adGroupNegatives.push([]);
for(var y = 0; y < adGroups[x][0].length; y++){
var keywordId = adGroups[x][1][y];
var keywordText = adGroups[x][0][y];
if(exactKeywords.indexOf(keywordId) !== -1){
adGroupNegatives[adGroupIds.indexOf(parseInt(x))].push(keywordText);
}
}
}
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Create the new negative exact keywords
var results = [];
for (var i=0; i<adGroupIds.length; i+=10000) {
var adGroupIdsChunk = adGroupIds.slice(i, i+10000);
var adGroupIterator = AdWordsApp.adGroups()
.withIds(adGroupIdsChunk)
.get();
while(adGroupIterator.hasNext()){
var adGroup = adGroupIterator.next();
var adGroupId = adGroup.getId();
var adGroupName = adGroup.getName();
var adGroupIndex = adGroupIds.indexOf(adGroupId);
var campaignName = adGroup.getCampaign().getName();
for(var j=0; j < adGroupNegatives[adGroupIndex].length; j++){
if (makeChanges) {
adGroup.createNegativeKeyword("[" + adGroupNegatives[adGroupIndex][j] + "]");
}
results.push([campaignName, adGroupName, adGroupNegatives[adGroupIndex][j]]);
}
}
}
if (!makeChanges || AdWordsApp.getExecutionInfo().isPreview()) {
Logger.log(results.length + " new negatives were found.");
} else {
Logger.log(results.length + " new negatives were created.");
}
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
//Email the results
if (emailAddresses == "") {
Logger.log("No email addresses given - not sending email.");
} else if(results.length == 0) {
Logger.log("No changes to email.");
} else {
var attachments = [];
var headers = ["Campaign", "Ad Group", "Negative"];
attachments.push(createEscapedCsv([headers].concat(results), "Ad-Group-Negatives.csv"));
if (!makeChanges || AdWordsApp.getExecutionInfo().isPreview()) {
var verb = "would be";
} else {
var verb = "were";
}
var subject = AdWordsApp.currentAccount().getName() + " - Making Exact Match Exact - " + Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
var body = "Please find attached a list of the " + results.length + " negative keywords that " + verb + " added to your account.";
var options = {attachments: attachments};
MailApp.sendEmail(emailAddresses, subject, body, options);
}
}
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Prepare an array to be made into a CSV
function createEscapedCsv(array, csvName) {
var cells = [];
for (var i=0; i<array.length; i++) {
var row = [];
for (var j=0; j<array[i].length; j++) {
row.push(array[i][j].replace(/"/g,'""'));
}
cells.push('"' + row.join('","') + '"');
}
return Utilities.newBlob("\ufeff" + cells.join("\n"), 'text/csv', csvName);
}
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~//
// Get the IDs of campaigns which match the given options
function getCampaignIds() {
var whereStatement = "";
var whereStatementsArray = [];
var campaignIds = [];
for (var i=0; i<campaignNameDoesNotContain.length; i++) {
whereStatement += "AND CampaignName DOES_NOT_CONTAIN_IGNORE_CASE '" + campaignNameDoesNotContain[i].replace(/"/g,'\\\"') + "' ";
}
if (campaignNameContains.length == 0) {
whereStatementsArray = [whereStatement];
} else {
for (var i=0; i<campaignNameContains.length; i++) {
whereStatementsArray.push(whereStatement + 'AND CampaignName CONTAINS_IGNORE_CASE "' + campaignNameContains[i].replace(/"/g,'\\\"') + '" ');
}
}
for (var i=0; i<whereStatementsArray.length; i++) {
var campaignReport = AdWordsApp.report(
"SELECT CampaignId " +
"FROM CAMPAIGN_PERFORMANCE_REPORT " +
"WHERE CampaignStatus = ENABLED " +
whereStatementsArray[i] +
"DURING LAST_30_DAYS");
var rows = campaignReport.rows();
while (rows.hasNext()) {
var row = rows.next();
campaignIds.push(row['CampaignId']);
}
}
if (campaignIds.length == 0) {
throw("No campaigns found with the given settings.");
}
Logger.log(campaignIds.length + " campaigns were found.");
return campaignIds;
}
function hasWordInCommon(searchQuery, keyword) {
var searchQueryWords = searchQuery.split(" ");
var keywordWords = keyword.split(" ");
var wordsInCommon = searchQueryWords.filter(function(word) {
return (keywordWords.indexOf(word) > -1);
});
return (wordsInCommon.length > 0);
}
function editDistance(searchQuery, keyword) {
var matrix = [[0]];
for (var i=1; i<=searchQuery.length; i++) {
matrix[i] = [];
matrix[i][0] = i;
}
for (var j=1; j<=keyword.length; j++) {
matrix[0][j] = j;
}
for (var j=1; j<=keyword.length; j++) {
for (var i=1; i<=searchQuery.length; i++) {
var substitutionCost = ((searchQuery[i] === keyword[j]) ? 0 : 1);
matrix[i][j] = Math.min(
matrix[i-1][j] + 1,
matrix[i][j-1] + 1,
matrix[i-1][j-1] + substitutionCost
);
}
}
return matrix[searchQuery.length][keyword.length];
}
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.