Find negative keywords that block queries that have converted in the past

Conflicting Negative Keywords for Converting Queries

/* 
// 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);
    }
  }
}