N-gram waste report

// N-gram waste report
// Splits every search term into 1- and 2-word n-grams, sums cost against them,
// and reports the n-grams that spent money and returned no conversions.
// Read-only: this script never adds a negative for you. It gives you the list.

var LOOKBACK_DAYS = 30;
var MIN_COST      = 25;   // account currency, ignore anything cheaper
var EMAIL         = '';   // leave empty to log only

function main() {
  var tz   = AdsApp.currentAccount().getTimeZone();
  var from = daysAgo(LOOKBACK_DAYS, tz);
  var to   = daysAgo(1, tz);

  var rows = AdsApp.search(
    'SELECT search_term_view.search_term, ' +
    '       metrics.cost_micros, ' +
    '       metrics.clicks, ' +
    '       metrics.conversions ' +
    'FROM search_term_view ' +
    'WHERE segments.date BETWEEN "' + from + '" AND "' + to + '"'
  );

  var grams = {};

  while (rows.hasNext()) {
    var r      = rows.next();
    var term   = String(r.searchTermView.searchTerm);
    var cost   = Number(r.metrics.costMicros) / 1000000;
    var clicks = Number(r.metrics.clicks);
    var conv   = Number(r.metrics.conversions);
    var words  = term.split(/\s+/);
    var seen   = {};

    for (var n = 1; n <= 2; n++) {
      for (var i = 0; i + n <= words.length; i++) {
        var g = words.slice(i, i + n).join(' ');
        if (seen[g]) { continue; }   // count each n-gram once per search term
        seen[g] = true;
        if (!grams[g]) { grams[g] = { cost: 0, clicks: 0, conv: 0, terms: 0 }; }
        grams[g].cost   += cost;
        grams[g].clicks += clicks;
        grams[g].conv   += conv;
        grams[g].terms  += 1;
      }
    }
  }

  var waste = [];
  for (var g in grams) {
    if (grams[g].conv === 0 && grams[g].cost >= MIN_COST) {
      waste.push([g, grams[g]]);
    }
  }
  waste.sort(function (a, b) { return b[1].cost - a[1].cost; });

  if (waste.length === 0) {
    Logger.log('No n-gram over ' + MIN_COST + ' without conversions.');
    return;
  }

  var total = 0;
  var lines = ['n-gram\tcost\tclicks\tterms'];
  for (var k = 0; k < Math.min(waste.length, 50); k++) {
    var w = waste[k];
    total += w[1].cost;
    lines.push(w[0] + '\t' + w[1].cost.toFixed(2) + '\t' + w[1].clicks + '\t' + w[1].terms);
  }

  var body = AdsApp.currentAccount().getName() + '\n' +
             from + ' to ' + to + '\n' +
             'Top rows account for ' + total.toFixed(2) + ' with no conversions.\n\n' +
             lines.join('\n') + '\n\n' +
             'N-grams overlap. Do not sum the column and call it total waste.';

  Logger.log(body);
  if (EMAIL) {
    MailApp.sendEmail(EMAIL, 'N-gram waste - ' + AdsApp.currentAccount().getName(), body);
  }
}

function daysAgo(n, tz) {
  var d = new Date();
  d.setDate(d.getDate() - n);
  return Utilities.formatDate(d, tz, 'yyyy-MM-dd');
}