Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save siliconvallaeys/6f1eb4a0a39aea11f5ae4645ab0731e5 to your computer and use it in GitHub Desktop.
Save siliconvallaeys/6f1eb4a0a39aea11f5ae4645ab0731e5 to your computer and use it in GitHub Desktop.
Find negative keywords that block queries that have converted in the past
/*
// AdWords Script: Negatives Blocking Converting Queries
// -----------------------------------------------------------------------------
// Copyright 2017 Optmyzr Inc., All Rights Reserved
//
// This script identifies negative keywords that are now blocking ads from
// appearing for previously converting queries
//
// For more PPC management tools, visit www.optmyzr.com
//
*/
var DEBUG = 1;
var currentSetting = {};
function main(){
// options allowed: blank
currentSetting.spreadsheetUrl = '';
// Number of days to look back for converting queries
currentSetting.LAST_N_DAYS = 90; // use a smaller number if the script times out
// Email where report will be sent
// the generated report will be shared with these people so they can edit the Google Sheet
currentSetting.EMAIL = 'example@example.com';
// Do not edit anything below this line
debug("Script Execution Begins");
var negativeListName = {};
var conflicts = findAllConflicts(negativeListName);
var output = [['Level', 'Campaign', 'AdGroup', 'List Name', 'Negative Keyword', 'Blocked Query', 'Conversions']];
for (var i = 0; i < conflicts.length; i++) {
var conflict = conflicts[i];
output.push([
conflict.level,
conflict.campaignName,
conflict.adGroupName,
negativeListName[conflict.negative] ? Object.keys(negativeListName[conflict.negative]).join('; ') : '',
conflict.negative,
conflict.query.text,
conflict.query.conversions
]);
}
var count = output.length - 1;
Logger.log('Number of Negatives blocking Converting Queries: ' + count);
if(count < 1) {
Logger.log('No Negatives found which block Converting Queries during Last ' + currentSetting.LAST_N_DAYS + ' Days.');
return;
}
debug('Populating Results in Google Sheets')
var ss = parseGoogleSheet();
var sheet = ss.getSheets()[0];
sheet.setFrozenRows(1);
sheet.clearContents();
sheet.getRange(1,1,1,output[0].length).setBackground('#efefef').setFontWeight('bold').setHorizontalAlignment('center').setWrap(true);
sheet.getRange(1,1,output.length,output[0].length).setValues(output).setFontFamily('Calibri');
deleteExtraRowsCols(sheet, 1, 10, 1, 1);
if(currentSetting.EMAIL) {
debug('Sending Email');
var SUB = AdWordsApp.currentAccount().getName() + ' -> Negatives Blocking Converting Queries';
var MSG = 'Hello,\n\n' + count + ' Negatives were found in your AdWords Account which are blocking Queries which have converted in Last ' + currentSetting.LAST_N_DAYS + ' days.';
MSG += 'Details can be found below:\n' + ss.getUrl();
MSG += '\n\nThanks';
MailApp.sendEmail(currentSetting.EMAIL, SUB, MSG);
ss.addEditors(currentSetting.EMAIL.split(','));
}
debug("Script Execution Ends");
};
function findAllConflicts(negativeListName) {
debug('Finding Converting Queries');
var DATE_RANGE = getAdWordsFormattedDate(currentSetting.LAST_N_DAYS, 'yyyyMMdd') + ',' + getAdWordsFormattedDate(1, 'yyyyMMdd');
var query = [
'SELECT CampaignId, CampaignName, AdGroupId, AdGroupName, Query, Conversions',
'FROM SEARCH_QUERY_PERFORMANCE_REPORT',
'WHERE CampaignStatus = "ENABLED" AND AdGroupStatus = "ENABLED"',
'and Conversions > 0',
'DURING', DATE_RANGE
].join(' ');
var report = AdWordsApp.report(query);
var cache = {};
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignId = row['CampaignId'];
var campaignName = row['CampaignName'];
var adGroupId = row['AdGroupId'];
var adGroupName = row['AdGroupName'];
var keywordText = row['Query'];
if (!cache[campaignId]) {
cache[campaignId] = {
campaignName: campaignName,
adGroups: {},
negatives: [],
negativesFromLists: []
};
}
if (!cache[campaignId].adGroups[adGroupId]) {
cache[campaignId].adGroups[adGroupId] = {
adGroupName: adGroupName,
queries: [],
negatives: []
};
}
cache[campaignId].adGroups[adGroupId].queries.push({ 'text': keywordText, 'conversions': row.Conversions });
}
debug('Reading AdGroup Negatives');
var query = [
'SELECT CampaignId, CampaignName, AdGroupId, AdGroupName,',
'Criteria, KeywordMatchType, IsNegative',
'FROM KEYWORDS_PERFORMANCE_REPORT',
'WHERE CampaignStatus = "ENABLED" AND AdGroupStatus = "ENABLED" AND',
'Status = "ENABLED" AND IsNegative = true',
'DURING YESTERDAY'
].join(' ');
var report = AdWordsApp.report(query, { 'includeZeroImpressions': true });
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignId = row['CampaignId'];
var campaignName = row['CampaignName'];
var adGroupId = row['AdGroupId'];
var adGroupName = row['AdGroupName'];
var keywordText = row['Criteria'];
var keywordMatchType = row['KeywordMatchType'];
var isNegative = row['IsNegative'];
if (!cache[campaignId]) {
cache[campaignId] = {
campaignName: campaignName,
adGroups: {},
negatives: [],
negativesFromLists: []
};
}
if (!cache[campaignId].adGroups[adGroupId]) {
cache[campaignId].adGroups[adGroupId] = {
adGroupName: adGroupName,
queries: [],
negatives: []
};
}
cache[campaignId].adGroups[adGroupId].negatives.push(normalizeKeyword(keywordText, keywordMatchType));
}
debug('Reading Campaign Negatives');
var query = [
'SELECT CampaignId, Criteria, KeywordMatchType',
'FROM CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT',
'WHERE CampaignStatus = "ENABLED"'
].join(' ');
var report = AdWordsApp.report(query);
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaignId = row['CampaignId'];
var keywordText = row['Criteria'];
var keywordMatchType = row['KeywordMatchType'];
if (cache[campaignId]) {
cache[campaignId].negatives
.push(normalizeKeyword(keywordText, keywordMatchType));
}
}
debug('Reading List Negatives');
var negativeKeywordLists =
AdWordsApp.negativeKeywordLists().withCondition('Status = ACTIVE').get();
while (negativeKeywordLists.hasNext()) {
var negativeKeywordList = negativeKeywordLists.next();
var negatives = [];
var negativeKeywords = negativeKeywordList.negativeKeywords().get();
while (negativeKeywords.hasNext()) {
var negative = negativeKeywords.next();
var text = normalizeKeyword(negative.getText(), negative.getMatchType());
negatives.push(text);
if(!negativeListName[text.display]) {
negativeListName[text.display] = {};
}
negativeListName[text.display][negativeKeywordList.getName()] = 1;
}
var campaigns = negativeKeywordList.campaigns()
.withCondition('Status = ENABLED').get();
while (campaigns.hasNext()) {
var campaign = campaigns.next();
var campaignId = campaign.getId();
if (cache[campaignId]) {
cache[campaignId].negativesFromLists = cache[campaignId].negativesFromLists.concat(negatives);
}
}
}
debug('Finding negative conflicts');
var conflicts = [];
// Adds context about the conflict.
var enrichConflict = function(conflict, campaignId, adGroupId, level) {
conflict.campaignId = campaignId;
conflict.adGroupId = adGroupId;
conflict.campaignName = cache[campaignId].campaignName;
conflict.adGroupName = cache[campaignId].adGroups[adGroupId].adGroupName;
conflict.level = level;
};
for (var campaignId in cache) {
for (var adGroupId in cache[campaignId].adGroups) {
var queries = cache[campaignId].adGroups[adGroupId].queries;
var negativeLevels = {
'Campaign': cache[campaignId].negatives,
'Ad Group': cache[campaignId].adGroups[adGroupId].negatives,
'Negative list': cache[campaignId].negativesFromLists
};
for (var level in negativeLevels) {
var newConflicts = checkForConflicts(negativeLevels[level], queries);
for (var i = 0; i < newConflicts.length; i++) {
enrichConflict(newConflicts[i], campaignId, adGroupId, level);
}
conflicts = conflicts.concat(newConflicts);
}
}
}
return conflicts;
}
function checkForConflicts(negatives, queries) {
var conflicts = [];
for (var i = 0; i < negatives.length; i++) {
var negative = negatives[i];
var anyBlock = false;
var blockedPositives = [];
for (var j = 0; j < queries.length; j++) {
var query = queries[j];
if(negativeBlocksPositive(negative, query.text)) {
conflicts.push({
'negative': negative.display,
'query': query
});
}
}
}
return conflicts;
}
function trimKeyword(text, open, close) {
if (text.substring(0, 1) == open &&
text.substring(text.length - 1) == close) {
return text.substring(1, text.length - 1);
}
return text;
}
function normalizeKeyword(text, matchType) {
var display;
var raw = text;
matchType = matchType.toUpperCase();
// Replace leading and trailing "" for phrase match keywords and [] for
// exact match keywords, if it is there.
if (matchType == 'PHRASE') {
raw = trimKeyword(raw, '"', '"');
} else if (matchType == 'EXACT') {
raw = trimKeyword(raw, '[', ']');
}
// Collapse any runs of whitespace into single spaces.
raw = raw.replace(new RegExp('\\s+', 'g'), ' ');
// Keywords are not case sensitive.
raw = raw.toLowerCase();
// Set display version.
display = raw;
if (matchType == 'PHRASE') {
display = '"' + display + '"';
} else if (matchType == 'EXACT') {
display = '[' + display + ']';
}
// Remove broad match modifier '+' sign.
raw = raw.replace(new RegExp('\\s\\+', 'g'), ' ');
return {display: display, raw: raw, matchType: matchType};
}
function hasAllTokens(keywordText1, keywordText2) {
var keywordTokens1 = keywordText1.split(' ');
var keywordTokens2 = keywordText2.split(' ');
for (var i = 0; i < keywordTokens1.length; i++) {
if (keywordTokens2.indexOf(keywordTokens1[i]) == -1) {
return false;
}
}
return true;
}
function isSubsequence(keywordText1, keywordText2) {
return (' ' + keywordText2 + ' ').indexOf(' ' + keywordText1 + ' ') >= 0;
}
function negativeBlocksPositive(negative, positive) {
switch (negative.matchType) {
case 'BROAD':
return hasAllTokens(negative.raw, positive);
break;
case 'PHRASE':
return isSubsequence(negative.raw, positive);
break;
case 'EXACT':
return positive === negative.raw;
break;
}
}
// Common functions
/* function debug(log)
// -------------------
// Used to write debug data to the user log as well as to a log that is sent to Optmyzr for help with troubleshooting when the
// support team gets questions.
*/
OPTMYZRLOG = "";
debugLogToUpload = "";
function debug(log) {
try {
if (typeof DEBUG != "undefined" && DEBUG == true) {
Logger.log(log);
}
OPTMYZRLOG += "\n " + log;
debugLogToUpload += "\n " + log;
} catch (e) {
Logger.log("error with debug: " + e);
}
}
/**
* Get AdWords Formatted date for n days back
* @param {int} d - Numer of days to go back for start/end date
* @return {String} - Formatted date yyyyMMdd
**/
function getAdWordsFormattedDate(d, format){
var date = new Date();
date.setDate(date.getDate() - d);
return Utilities.formatDate(date,AdWordsApp.currentAccount().getTimeZone(),format);
}
function parseDateRange() {
debug("Parsing Date Range");
currentSetting.date_range = "LAST_N_DAYS"; // LAST_N_DAYS, LAST_MONTH, CUSTOM_DATE
// Required only when date_range is CUSTOM_DATE
currentSetting.custom_date = {
'start': '', // Put Start Date in format: yyyy-MM-dd
'end': '' // Put End Date in format: yyyy-MM-dd
}
// Required only when date_range is LAST_N_DAYS
currentSetting.LAST_N_DAYS = 90;
if(currentSetting.date_range == 'LAST_N_DAYS') {
currentSetting.FROM = getAdWordsFormattedDate(currentSetting.LAST_N_DAYS, 'yyyyMMdd');
currentSetting.TO = getAdWordsFormattedDate(1, 'yyyyMMdd');
return;
}
if(currentSetting.date_range == 'LAST_MONTH') {
var now = new Date(getAdWordsFormattedDate(0, 'MMM d, yyyy'));
now.setHours(12);
now.setDate(1);
now.setDate(0);
currentSetting.TO = Utilities.formatDate(now, 'PST', 'yyyyMMdd');
now.setDate(1);
currentSetting.FROM = Utilities.formatDate(now, 'PST', 'yyyyMMdd');
return;
}
if(currentSetting.date_range == 'CUSTOM_DATE') {
currentSetting.TO = currentSetting.custom_date.start.replace(/-/g, '');
currentSetting.FROM = currentSetting.custom_date.end.replace(/-/g, '');
return;
}
}
function parseGoogleSheet() {
var today = getAdWordsFormattedDate(0 , 'MMM d, yyyy');
if(!currentSetting.spreadsheetUrl || currentSetting.spreadsheetUrl == 'new') {
var ss = SpreadsheetApp.create(AdWordsApp.currentAccount().getName() + ' -> Negatives Blocking Converting Queries (' + today + ')');
Logger.log('New Sheet: ' + ss.getUrl());
ss.getSheets()[0].setName('Report');
return ss;
}
return SpreadsheetApp.openByUrl(currentSetting.spreadsheetUrl);
}
function deleteExtraRowsCols(sheet, rowFlag, rowOffSet, colFlag, colOffset) {
if(colFlag) {
if((sheet.getMaxColumns() - sheet.getLastColumn()) > colOffset) {
sheet.deleteColumns(sheet.getLastColumn()+(colOffset+1), sheet.getMaxColumns() - sheet.getLastColumn() - colOffset);
}
}
if(rowFlag) {
if((sheet.getMaxRows() - sheet.getLastRow()) > rowOffSet) {
sheet.deleteRows(sheet.getLastRow()+(rowOffSet+1), sheet.getMaxRows() - sheet.getLastRow() - rowOffSet);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment