Negative keyword conflict finder

// Negative keyword conflict finder
// Finds enabled keywords that are blocked by your own negatives, at any of the three
// levels: ad group, campaign, and shared negative lists attached to the campaign.
// Shared lists are where the oldest and most destructive negatives usually live, and
// a check that reads campaign negatives only will miss them.
// Read-only: it reports the pairs. It never removes a negative.

var EMAIL      = '';     // leave empty to log only
var MAX_REPORT = 200;    // cap the output so the email stays readable

function main() {
  var keywords = positiveKeywords();
  Logger.log(keywords.length + ' enabled keywords in enabled ad groups and campaigns.');
  if (!keywords.length) {
    Logger.log('No enabled keywords found. Check that the account has active search campaigns.');
    return;
  }

  var byAdGroup  = adGroupNegatives();
  var byCampaign = campaignNegatives();
  var byList     = sharedListNegatives();

  var hits = [];

  for (var i = 0; i < keywords.length; i++) {
    var k = keywords[i];
    var applicable = []
      .concat(byAdGroup[k.adGroupId] || [])
      .concat(byCampaign[k.campaignId] || [])
      .concat(byList[k.campaignId] || []);

    for (var j = 0; j < applicable.length; j++) {
      var n = applicable[j];
      if (blocks(n.text, n.matchType, k.text)) {
        hits.push({
          campaign: k.campaign,
          adGroup:  k.adGroup,
          keyword:  k.text,
          kwMatch:  k.matchType,
          negative: n.text,
          negMatch: n.matchType,
          level:    n.level
        });
      }
    }
  }

  if (!hits.length) {
    Logger.log('No conflicts found.');
    return;
  }

  var lines = ['campaign\tad group\tkeyword\tmatch\tblocked by\tnegative match\tlevel'];
  for (var h = 0; h < Math.min(hits.length, MAX_REPORT); h++) {
    var x = hits[h];
    lines.push([x.campaign, x.adGroup, x.keyword, x.kwMatch, x.negative, x.negMatch, x.level].join('\t'));
  }

  var body = AdsApp.currentAccount().getName() + '\n' +
             hits.length + ' blocked keyword and negative pairs found' +
             (hits.length > MAX_REPORT ? ', showing the first ' + MAX_REPORT : '') + '.\n\n' +
             lines.join('\n') + '\n\n' +
             'A conflict is not automatically a mistake. Check the level before removing anything: ' +
             'a negative on a shared list was probably added for a different campaign.';

  Logger.log(body);
  if (EMAIL) {
    MailApp.sendEmail(EMAIL, 'Negative conflicts (' + hits.length + ') - ' + AdsApp.currentAccount().getName(), body);
  }
}

/* ---------- matching ---------- */

function words(s) {
  return String(s).toLowerCase()
    .replace(/[^\p{L}\p{N}\s]/gu, ' ')
    .split(/\s+/)
    .filter(function (w) { return w.length > 0; });
}

function blocks(negText, negMatch, kwText) {
  var n = words(negText);
  var k = words(kwText);
  if (!n.length || !k.length) { return false; }

  if (negMatch === 'EXACT')  { return n.join(' ') === k.join(' '); }
  if (negMatch === 'PHRASE') { return containsSequence(k, n); }

  // BROAD: every word of the negative must appear in the keyword, order ignored
  for (var i = 0; i < n.length; i++) {
    if (k.indexOf(n[i]) === -1) { return false; }
  }
  return true;
}

function containsSequence(haystack, needle) {
  for (var i = 0; i + needle.length <= haystack.length; i++) {
    var ok = true;
    for (var j = 0; j < needle.length; j++) {
      if (haystack[i + j] !== needle[j]) { ok = false; break; }
    }
    if (ok) { return true; }
  }
  return false;
}

/* ---------- reads ---------- */

function positiveKeywords() {
  var list = [];
  var rows = AdsApp.search(
    '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 ad_group_criterion ' +
    'WHERE ad_group_criterion.type = "KEYWORD" ' +
    '  AND ad_group_criterion.status = "ENABLED" ' +
    '  AND ad_group.status = "ENABLED" ' +
    '  AND campaign.status = "ENABLED"'
  );
  while (rows.hasNext()) {
    var r = rows.next();
    if (r.adGroupCriterion.negative === true || r.adGroupCriterion.negative === 'true') { continue; }
    list.push({
      campaignId: String(r.campaign.id),
      campaign:   String(r.campaign.name),
      adGroupId:  String(r.adGroup.id),
      adGroup:    String(r.adGroup.name),
      text:       String(r.adGroupCriterion.keyword.text),
      matchType:  String(r.adGroupCriterion.keyword.matchType)
    });
  }
  return list;
}

function adGroupNegatives() {
  var out  = {};
  var rows = AdsApp.search(
    'SELECT ad_group.id, ad_group_criterion.keyword.text, ad_group_criterion.keyword.match_type ' +
    'FROM ad_group_criterion ' +
    'WHERE ad_group_criterion.type = "KEYWORD" ' +
    '  AND ad_group_criterion.negative = TRUE'
  );
  while (rows.hasNext()) {
    var r  = rows.next();
    var id = String(r.adGroup.id);
    if (!out[id]) { out[id] = []; }
    out[id].push({
      text:      String(r.adGroupCriterion.keyword.text),
      matchType: String(r.adGroupCriterion.keyword.matchType),
      level:     'ad group'
    });
  }
  return out;
}

function campaignNegatives() {
  var out  = {};
  var rows = AdsApp.search(
    'SELECT campaign.id, campaign_criterion.keyword.text, campaign_criterion.keyword.match_type ' +
    'FROM campaign_criterion ' +
    'WHERE campaign_criterion.type = "KEYWORD" ' +
    '  AND campaign_criterion.negative = TRUE'
  );
  while (rows.hasNext()) {
    var r  = rows.next();
    var id = String(r.campaign.id);
    if (!out[id]) { out[id] = []; }
    out[id].push({
      text:      String(r.campaignCriterion.keyword.text),
      matchType: String(r.campaignCriterion.keyword.matchType),
      level:     'campaign'
    });
  }
  return out;
}

// Shared lists: one query for the criteria, one for which campaigns each list is on.
function sharedListNegatives() {
  var criteria = {};
  var rows = AdsApp.search(
    'SELECT shared_set.id, shared_set.name, ' +
    '       shared_criterion.keyword.text, shared_criterion.keyword.match_type ' +
    'FROM shared_criterion ' +
    'WHERE shared_criterion.type = "KEYWORD"'
  );
  while (rows.hasNext()) {
    var r  = rows.next();
    var id = String(r.sharedSet.id);
    if (!criteria[id]) { criteria[id] = []; }
    criteria[id].push({
      text:      String(r.sharedCriterion.keyword.text),
      matchType: String(r.sharedCriterion.keyword.matchType),
      level:     'list: ' + String(r.sharedSet.name)
    });
  }

  var out  = {};
  var maps = AdsApp.search(
    'SELECT campaign.id, shared_set.id ' +
    'FROM campaign_shared_set ' +
    'WHERE campaign.status != "REMOVED"'
  );
  while (maps.hasNext()) {
    var m   = maps.next();
    var cid = String(m.campaign.id);
    var sid = String(m.sharedSet.id);
    if (!criteria[sid]) { continue; }
    out[cid] = (out[cid] || []).concat(criteria[sid]);
  }
  return out;
}