Negative Keyword Conflicts (Manager account / MCC level)

// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Negative Keyword Conflicts - Manager Accounts
 *
 * @overview The Negative Keyword Conflicts script generates a spreadsheet
 *     and email alert if a Google Ads account has positive keywords which are
 *     blocked by negative keywords. See
 *     ...
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.1
 *
 * @changelog
 * - version 2.1
 *   - Fix bug where negative keywords were not categorized correctly.
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.3.3
 *   - Added column for negative keyword list name.
 * - version 1.3.2
 *   - Added validation for external spreadsheet setup.
 * - version 1.3.1
 *   - Fix bug where campaigns with multiple shared negative keyword lists were
 *     not handled correctly.
 * - version 1.3.0
 *   - Fix bug where in certain cases phrase match negatives were incorrectly
 *     reported as blocking positive keywords.
 * - version 1.2.1
 *   - Improvements to time zone handling.
 * - version 1.2
 *   - Improved compatibility with Large Manager Hierarchy template.
 *   - Add option for reusing the spreadsheet or making a copy.
 * - version 1.1
 *   - Bug fixes.
 * - version 1.0
 *   - Released initial version.
 */

const CONFIG = {
  // URL of the spreadsheet template.
  // This should be a copy of https://goo.gl/M4HjaH.
  SPREADSHEET_URL: 'YOUR_SPREADSHEET_URL',

  // Whether to output results to a copy of the above spreadsheet (true) or to
  // the spreadsheet directly, overwriting previous results (false).
  COPY_SPREADSHEET: false,

  // Array of addresses to be alerted via email if conflicts are found.
  RECIPIENT_EMAILS: [
    'YOUR_EMAIL_HERE'
  ],
  
  // Client ID's of the accounts to be processed. Eg.: '123-456-7890','987-654-3210'
  // Leave blank to include all accounts.
  CLIENT_ID: [
    //'YOUR_CLIENT_ID_HERE'
  ],

  // Label on the campaigns to be processed.
  // Leave blank to include all campaigns.
  CAMPAIGN_LABEL: '',

  // Limits on the number of keywords in an account the script can process.
  MAX_POSITIVES: 250000,
  MAX_NEGATIVES: 50000
};

/**
 * Configuration to be used for running reports.
 */
const REPORTING_OPTIONS = {
  // Comment out the following line to default to the latest reporting version.
  apiVersion: 'v11'
};

function main() {
  
    validateEmailAddresses();
    let accountSelector = AdsManagerApp.accounts();
  
    if (CONFIG.CLIENT_ID.length > 0) {
        accountSelector = accountSelector
            .withIds(CONFIG.CLIENT_ID)
    }

    accountSelector.executeInParallel('processAccount', 'processResults');
}


/**
 * Finds conflicts and returns the results in a format suitable for
 * executeInParallel().
 *
 * @return {string} JSON stringified list of conflict objects.
 */
function processAccount() {
  return JSON.stringify(findAllConflicts());
}

/**
 * Outputs conflicts to a spreadsheet and sends an email alert if applicable.
 *
 * @param {Array.<Object>} results A list of ExecutionResult objects.
 */
function processResults(results) {
  let hasConflicts = false;
  let spreadsheet = validateAndGetSpreadsheet(CONFIG.SPREADSHEET_URL);  
  
  if (CONFIG.COPY_SPREADSHEET) {
    spreadsheet = spreadsheet.copy('Negative Keyword Conflicts');
  }
  initializeSpreadsheet(spreadsheet);

  for (var i = 0; i < results.length; i++) {
    if (!results[i].getError()) {
      hasConflicts = outputConflicts(spreadsheet, results[i].getCustomerId(),
          JSON.parse(results[i].getReturnValue())) || hasConflicts;
    } else {
      Logger.log('Processing for ' + results[i].getCustomerId() + ' failed.');
    }
  }

  if (hasConflicts && CONFIG.RECIPIENT_EMAILS) {
    sendEmail(spreadsheet);
  }
}

/**
 * Finds all negative keyword conflicts in an account.
 *
 * @return {Array.<Object>} An array of conflicts.
 */
function findAllConflicts() {
  let campaignIds;
  if (CONFIG.CAMPAIGN_LABEL) {
    campaignIds = getCampaignIdsWithLabel(CONFIG.CAMPAIGN_LABEL);
  } else {
    campaignIds = getAllCampaignIds();
  }

  let campaignCondition = '';
  if (campaignIds.length > 0) {
    campaignCondition = `AND campaign.id IN (${campaignIds.join(',')})`;
  }

  console.log('Downloading keywords performance report');
  let query =
      `SELECT campaign.id, campaign.name, ad_group.id, ad_group.name, ` +
      `ad_group_criterion.keyword.text, ` +
      `ad_group_criterion.keyword.match_type, ` +
      `ad_group_criterion.negative FROM keyword_view ` +
      `WHERE campaign.status = "ENABLED" AND ad_group.status = "ENABLED" `+
      `AND ad_group_criterion.status = "ENABLED" ` +
      `${campaignCondition} AND segments.date DURING YESTERDAY`;
  let report = AdsApp.report(query, REPORTING_OPTIONS);

  console.log('Building cache and populating with keywords');
  let cache = {};
  let numPositives = 0;
  let numNegatives = 0;

  let rows = report.rows();
  for (const row of rows) {

    const campaignId = row['campaign.id'];
    const campaignName = row['campaign.name'];
    const adGroupId = row['ad_group.id'];
    const adGroupName = row['ad_group.name'];
    const keywordText = row['ad_group_criterion.keyword.text'];
    const keywordMatchType = row['ad_group_criterion.keyword.match_type'];
    const isNegative = row['ad_group_criterion.negative'];

    if (!cache[campaignId]) {
      cache[campaignId] = {
        campaignName: campaignName,
        adGroups: {},
        negatives: [],
        negativesFromLists: [],
      };
    }

    if (!cache[campaignId].adGroups[adGroupId]) {
      cache[campaignId].adGroups[adGroupId] = {
        adGroupName: adGroupName,
        positives: [],
        negatives: [],
      };
    }

    if (isNegative) {
      cache[campaignId].adGroups[adGroupId].negatives
        .push(normalizeKeyword(keywordText, keywordMatchType));
      numNegatives++;
    } else {
      cache[campaignId].adGroups[adGroupId].positives
        .push(normalizeKeyword(keywordText, keywordMatchType));
      numPositives++;
    }

    if (numPositives > CONFIG.MAX_POSITIVES ||
        numNegatives > CONFIG.MAX_NEGATIVES) {
      throw 'Trying to process too many keywords. Please restrict the ' +
            'script to a smaller subset of campaigns.';
    }
  }

  console.log('Downloading campaign negatives report');
  query =
      `SELECT campaign.id, campaign_criterion.keyword.text, ` +
      `campaign_criterion.keyword.match_type FROM campaign_criterion ` +
      `WHERE campaign_criterion.negative = true AND ` +
      `campaign_criterion.type = "KEYWORD" AND ` +
      `campaign.status = "ENABLED" ${campaignCondition}`;
  report = AdsApp.report(query, REPORTING_OPTIONS);

  rows = report.rows();
  for (const row of rows) {

    const campaignId = row['campaign.id'];
    const keywordText = row['campaign_criterion.keyword.text'];
    const keywordMatchType = row['campaign_criterion.keyword.match_type'];

    if (cache[campaignId]) {
      cache[campaignId].negatives
        .push(normalizeKeyword(keywordText, keywordMatchType));
    }
  }

  console.log('Populating cache with negative keyword lists');
  const negativeKeywordLists =
    AdsApp.negativeKeywordLists().withCondition('Status = ACTIVE').get();

  for (const negativeKeywordList of negativeKeywordLists) {
    const negativeList = {name: negativeKeywordList.getName(), negatives: []};
    const negativeKeywords = negativeKeywordList.negativeKeywords().get();

    for (const negative of negativeKeywords) {
      negativeList.negatives.push(
          normalizeKeyword(negative.getText(), negative.getMatchType()));
    }

    const campaigns = negativeKeywordList.campaigns()
        .withCondition('Status = ENABLED').get();

    for (const campaign of campaigns) {
      const campaignId = campaign.getId();

      if (cache[campaignId]) {
        cache[campaignId].negativesFromLists =
            cache[campaignId].negativesFromLists.concat(negativeList);
      }
    }
  }

  console.log('Finding negative conflicts');
  let conflicts = [];

  // Adds context about the conflict.
  const enrichConflict = function(
      conflict, campaignId, adGroupId, level, opt_listName) {
    conflict.campaignId = campaignId;
    conflict.adGroupId = adGroupId;
    conflict.campaignName = cache[campaignId].campaignName;
    conflict.adGroupName = cache[campaignId].adGroups[adGroupId].adGroupName;
    conflict.level = level;
    conflict.listName = opt_listName || '-';
  };

  for (const campaignId in cache) {
    for (const adGroupId in cache[campaignId].adGroups) {
      const positives = cache[campaignId].adGroups[adGroupId].positives;

      const negativeLevels = {
        'Campaign': cache[campaignId].negatives,
        'Ad Group': cache[campaignId].adGroups[adGroupId].negatives
      };

      for (const level in negativeLevels) {
        const newConflicts =
          checkForConflicts(negativeLevels[level], positives);

        for (const newConflict of newConflicts) {
          enrichConflict(newConflict, campaignId, adGroupId, level);
        }
        conflicts = conflicts.concat(newConflicts);
      }

      const negativeLists = cache[campaignId].negativesFromLists;
      const level = 'Negative list';
      for (const negativeList of negativeLists) {
        const newConflicts = checkForConflicts(
            negativeList.negatives, positives);

        for (const newConflict of newConflicts) {
          enrichConflict(
              newConflict, campaignId, adGroupId, level, negativeList.name);
        }
        conflicts = conflicts.concat(newConflicts);
      }
    }
  }

  return conflicts;
}

/**
 * Saves conflicts to a spreadsheet if present.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 * @param {string} customerId The account the conflicts are for.
 * @param {Array.<Object>} conflicts A list of conflicts.
 * @return {boolean} True if there were conflicts and false otherwise.
 */
function outputConflicts(spreadsheet, customerId, conflicts) {
  if (conflicts.length > 0) {
    saveConflictsToSpreadsheet(spreadsheet, customerId, conflicts);
    console.log(`Conflicts were found for ${customerId}` +
               `. See ${spreadsheet.getUrl()}`);
    return true;
  } else {
    console.log(`No conflicts were found for ${customerId}.`);
    return false;
  }
}

/**
 * Sets up the spreadsheet to receive output.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 */
function initializeSpreadsheet(spreadsheet) {
  // Make sure the spreadsheet is using the account's timezone.
  spreadsheet.setSpreadsheetTimeZone(AdsApp.currentAccount().getTimeZone());

  // Clear the last run date on the spreadsheet.
  spreadsheet.getRangeByName('RunDate').clearContent();

  // Clear all rows in the spreadsheet below the header row.
  spreadsheet.getRangeByName('Headers')
    .offset(1, 0, spreadsheet.getSheetByName('Conflicts')
    .getDataRange().getLastRow())
    .clearContent();
}

/**
 * Saves conflicts for a particular account to the spreadsheet starting at the
 * first unused row.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 * @param {string} customerId The account that the conflicts are for.
 * @param {Array.<Object>} conflicts A list of conflicts.
 */
function saveConflictsToSpreadsheet(spreadsheet, customerId, conflicts) {
  // Find the first open row on the Report tab below the headers and create a
  // range large enough to hold all of the failures, one per row.
  const lastRow = spreadsheet.getSheetByName('Conflicts')
    .getDataRange().getLastRow();
  const headers = spreadsheet.getRangeByName('Headers');
  const outputRange = headers
    .offset(lastRow - headers.getRow() + 1, 0, conflicts.length);

  // Build each row of output values in the order of the columns.
  const outputValues = [];
  for (const conflict of conflicts) {
    outputValues.push([
      customerId,
      conflict.negative,
      conflict.level,
      conflict.positives.join(', '),
      conflict.campaignName,
      conflict.adGroupName,
      conflict.listName
    ]);
  }
  outputRange.setValues(outputValues);

  spreadsheet.getRangeByName('RunDate').setValue(new Date());

  for (const recipientEmail of CONFIG.RECIPIENT_EMAILS) {
    spreadsheet.addEditor(recipientEmail);
  }
}

/**
 * Sends an email to a list of email addresses with a link to the spreadsheet.
 *
 * @param {Object} spreadsheet The spreadsheet object.
 */
function sendEmail(spreadsheet) {
  MailApp.sendEmail(CONFIG.RECIPIENT_EMAILS.join(','),
      'Negative Keyword Conflicts Found',
      `Negative keyword conflicts were found in your ` +
      `Google Ads account(s). See ` +
      `${spreadsheet.getUrl()} for details. You may wish ` +
      `to delete the negative keywords causing the conflicts.`);
}

/**
 * Retrieves the campaign IDs of a campaign iterator.
 *
 * @param {Object} campaigns A CampaignIterator object.
 * @return {Array.<Integer>} An array of campaign IDs.
 */
function getCampaignIds(campaigns) {
  const campaignIds = [];
  for (const campaign of campaigns) {
    campaignIds.push(campaign.getId());
  }

  return campaignIds;
}

/**
 * Retrieves all campaign IDs in an account.
 *
 * @return {Array.<Integer>} An array of campaign IDs.
 */
function getAllCampaignIds() {
  return getCampaignIds(AdsApp.campaigns().get());
}

/**
 * Retrieves the campaign IDs with a given label.
 *
 * @param {string} labelText The text of the label.
 * @return {Array.<Integer>} An array of campaign IDs, or null if the
 *     label was not found.
 */
function getCampaignIdsWithLabel(labelText) {
  const labels = AdsApp.labels()
    .withCondition('Name = "' + labelText + '"')
    .get();

  if (!labels.hasNext()) {
    return null;
  }
  const label = labels.next();

  return getCampaignIds(label.campaigns().get());
}

/**
 * Compares a set of negative keywords and positive keywords to identify
 * conflicts where a negative keyword blocks a positive keyword.
 *
 * @param {Array.<Object>} negatives A list of objects with fields
 *     display, raw, and matchType.
 * @param {Array.<Object>} positives A list of objects with fields
 *     display, raw, and matchType.
 * @return {Array.<Object>} An array of conflicts, each an object with
 *     the negative keyword display text causing the conflict and an array
 *     of blocked positive keyword display texts.
 */
function checkForConflicts(negatives, positives) {
  const conflicts = [];

  for (const negative of negatives) {
    let anyBlock = false;
    const blockedPositives = [];

    for (const positive of positives) {

      if (negativeBlocksPositive(negative, positive)) {
        anyBlock = true;
        blockedPositives.push(positive.display);
      }
    }

    if (anyBlock) {
      conflicts.push({
        negative: negative.display,
        positives: blockedPositives
      });
    }
  }

  return conflicts;
}

/**
 * Removes leading and trailing match type punctuation from the first and
 * last character of a keyword's text, if any.
 *
 * @param {string} text A keyword's text to remove punctuation from.
 * @param {string} open The character that may be the first character.
 * @param {string} close The character that may be the last character.
 * @return {Object} The same text, trimmed of open and close if present.
 */
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;
}

/**
 * Normalizes a keyword by returning a raw and display version and consistent
 * match type. The raw version has no leading and trailing punctuation for
 * phrase and exact match keywords, no consecutive whitespace, is all
 * lowercase, and removes broad match qualifiers. The display version has no
 * consecutive whitespace and is all lowercase. The match type is uppercase.
 *
 * @param {string} text A keyword's text that should be normalized.
 * @param {string} matchType The keyword's match type.
 * @return {Object} An object with fields display, raw, and matchType.
 */
function normalizeKeyword(text, matchType) {
  let display;
  let 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};
}

/**
 * Tests whether all of the tokens in one keyword's raw text appear in
 * the tokens of a second keyword's text.
 *
 * @param {string} keywordText1 the raw keyword text whose tokens may
 *     appear in the other keyword text.
 * @param {string} keywordText2 the raw keyword text which may contain
 *     the tokens of the other keyword.
 * @return {boolean} Whether all tokens in keywordText1 appear among
 *     the tokens of keywordText2.
 */
function hasAllTokens(keywordText1, keywordText2) {
  const keywordTokens1 = keywordText1.split(' ');
  const keywordTokens2 = keywordText2.split(' ');

  for (const keywordToken of keywordTokens1) {
    if (keywordTokens2.indexOf(keywordToken) == -1) {
      return false;
    }
  }

  return true;
}

/**
 * Tests whether all of the tokens in one keyword's raw text appear in
 * order in the tokens of a second keyword's text.
 *
 * @param {string} keywordText1 the raw keyword text whose tokens may
 *     appear in the other keyword text.
 * @param {string} keywordText2 the raw keyword text which may contain
 *     the tokens of the other keyword in order.
 * @return {boolean} Whether all tokens in keywordText1 appear in order
 *     among the tokens of keywordText2.
 */
function isSubsequence(keywordText1, keywordText2) {
  return (' ' + keywordText2 + ' ').indexOf(' ' + keywordText1 + ' ') >= 0;
}

/**
 * Tests whether a negative keyword blocks a positive keyword, taking into
 * account their match types.
 *
 * @param {Object} negative An object with fields raw and matchType.
 * @param {Object} positive An object with fields raw and matchType.
 * @return {boolean} Whether the negative keyword blocks the positive keyword.
 */
function negativeBlocksPositive(negative, positive) {
  let isNegativeStricter;

  switch (positive.matchType) {
    case 'BROAD':
      isNegativeStricter = negative.matchType != 'BROAD';
      break;

    case 'PHRASE':
      isNegativeStricter = negative.matchType == 'EXACT';
      break;

    case 'EXACT':
      isNegativeStricter = false;
      break;
  }

  if (isNegativeStricter) {
    return false;
  }

  switch (negative.matchType) {
    case 'BROAD':
      return hasAllTokens(negative.raw, positive.raw);
      break;

    case 'PHRASE':
      return isSubsequence(negative.raw, positive.raw);
      break;

    case 'EXACT':
      return positive.raw === negative.raw;
      break;
  }
}

/**
 * Validates the provided spreadsheet URL to make sure that it's set up
 * properly. Throws a descriptive error message if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl) {
  if (spreadsheeturl == 'YOUR_SPREADSHEET_URL') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  return SpreadsheetApp.openByUrl(spreadsheeturl);
}

/**
 * Validates the provided email address to make sure it's not the default.
 * Throws a descriptive error message if validation fails.
 *
 * @throws {Error} If the list of email addresses is still the default
 */
function validateEmailAddresses() {
  if (CONFIG.RECIPIENT_EMAILS &&
      CONFIG.RECIPIENT_EMAILS[0] == 'YOUR_EMAIL_HERE') {
    throw new Error('Please either specify a valid email address or clear' +
        ' the RECIPIENT_EMAILS field.');
  }
}