Search Term N-Gram Negative Miner

// Search Term N-Gram Negative Miner — Google Ads script
// Mines the search-term report into 1- and 2-word n-grams, aggregates
// cost / conversions per n-gram, and writes negative-keyword candidates
// (high spend, zero conversions) to a Google Sheet for review.
var SPREADSHEET_URL = 'PASTE_YOUR_SHEET_URL';
var LOOKBACK_DAYS   = 90;
var MIN_COST        = 50;   // flag n-grams that spent at least this (account currency)
var MAX_NGRAM       = 2;    // 1 = unigrams only, 2 = unigrams + bigrams

function main() {
  var tz    = AdsApp.currentAccount().getTimeZone();
  var to    = new Date();
  var from  = new Date(to.getTime() - LOOKBACK_DAYS * 864e5);
  var range = Utilities.formatDate(from, tz, 'yyyyMMdd') + ',' +
              Utilities.formatDate(to, tz, 'yyyyMMdd');

  var report = AdsApp.report(
    'SELECT Query, Cost, Conversions, Clicks ' +
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'WHERE CampaignStatus = ENABLED ' +
    'DURING ' + range
  );

  var grams = {};
  var rows  = report.rows();
  while (rows.hasNext()) {
    var row   = rows.next();
    var cost  = parseFloat(row['Cost']) || 0;
    var conv  = parseFloat(row['Conversions']) || 0;
    var clk   = parseInt(row['Clicks'], 10) || 0;
    var words = String(row['Query']).toLowerCase().split(/s+/);

    for (var n = 1; n <= MAX_NGRAM; n++) {
      for (var i = 0; i + n <= words.length; i++) {
        var g = words.slice(i, i + n).join(' ');
        if (!grams[g]) { grams[g] = { cost: 0, conv: 0, clicks: 0 }; }
        grams[g].cost   += cost;
        grams[g].conv   += conv;
        grams[g].clicks += clk;
      }
    }
  }

  var out = [['N-gram', 'Cost', 'Conversions', 'Clicks']];
  Object.keys(grams).forEach(function (g) {
    var s = grams[g];
    if (s.cost >= MIN_COST && s.conv === 0) {
      out.push([g, s.cost.toFixed(2), s.conv, s.clicks]);
    }
  });
  out.sort(function (a, b) { return (b[1] - a[1]) || 0; });

  var sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getActiveSheet();
  sheet.clearContents();
  sheet.getRange(1, 1, out.length, 4).setValues(out);
  Logger.log((out.length - 1) + ' negative candidates written to the sheet.');
}