Search Terms N-gram Auditor: find the wasteful words hiding across your queries - The Vault

A single search term rarely tells you much: one odd query, a few pence, easy to ignore. The waste hides in the words those queries share. "Free", "jobs", "download", "cheap", a competitor's name: each one turns up across dozens of low-value terms, and only shows its true cost when you add the pieces back together. That is what an n-gram audit does, and it is tedious enough by hand that most people never do it.

This script reads the last 30 days of search terms, splits every query into its one, two and three word fragments, and totals the spend, clicks and conversions behind each fragment. It logs the fragments that cost money and converted nothing, and writes the whole table to a Google Sheet so you can sort and filter it yourself. It changes nothing: no negatives are added, no terms are excluded. It hands you the evidence and leaves the decision to you.

Before you start

You need access to the Google Ads account and permission to authorise scripts.
The Sheet is optional to pre-create. Leave SHEET_URL empty and the script makes a new spreadsheet on the first run and logs its URL; paste that URL back into the config to keep appending to the same sheet.
N-grams are evidence, not verdicts. A word that converts nothing over 30 days on thin volume may simply need more data, so read the clicks column alongside the cost before you act.

The script

JavaScript

/**
 * Search Terms N-gram Auditor
 * The SEM Dispatch Vault, semdispatch.com/vault
 *
 * Tokenises 30 days of search terms into 1, 2 and 3 word n-grams,
 * aggregates spend, clicks and conversions per n-gram, logs the most
 * wasteful (spend with zero conversions) and writes the full table to
 * a Google Sheet. Read-only: this script never changes the account.
 */

var CONFIG = {
  // URL of a Google Sheet to write to. Leave empty to create a new one
  // on the first run; its URL is logged so you can paste it back here.
  SHEET_URL: "",

  // How many wasteful n-grams to print to the log. The full table always
  // goes to the Sheet regardless of this number.
  TOP_N: 25,

  // Ignore n-grams seen on fewer clicks than this, so a single stray
  // click cannot flag a word as wasteful on no evidence.
  MIN_CLICKS: 3
};

function main() {
  var query =
    "SELECT search_term_view.search_term, " +
    "metrics.cost_micros, metrics.clicks, metrics.conversions " +
    "FROM search_term_view " +
    "WHERE segments.date DURING LAST_30_DAYS";

  var rows = AdsApp.report(query).rows();

  // key: n-gram text -> { grams, cost, clicks, conversions }
  var grams = {};

  while (rows.hasNext()) {
    var row = rows.next();
    var term = (row["search_term_view.search_term"] || "").toLowerCase();
    var cost = Number(row["metrics.cost_micros"] || 0) / 1000000;
    var clicks = Number(row["metrics.clicks"] || 0);
    var conversions = Number(row["metrics.conversions"] || 0);

    var words = term.split(/\s+/).filter(function (w) {
      return w.length > 0;
    });

    var seen = {};
    for (var n = 1; n <= 3; n++) {
      for (var i = 0; i + n <= words.length; i++) {
        var gram = words.slice(i, i + n).join(" ");
        // Count each distinct n-gram once per search term, so a query's
        // cost is not multiplied by a repeated word within it.
        if (seen[gram]) continue;
        seen[gram] = true;

        if (!grams[gram]) {
          grams[gram] = { grams: n, cost: 0, clicks: 0, conversions: 0 };
        }
        grams[gram].cost += cost;
        grams[gram].clicks += clicks;
        grams[gram].conversions += conversions;
      }
    }
  }

  var currency = AdsApp.currentAccount().getCurrencyCode();

  // Flatten to an array for sorting and writing.
  var table = [];
  for (var g in grams) {
    if (!grams.hasOwnProperty(g)) continue;
    var r = grams[g];
    table.push({
      ngram: g,
      words: r.grams,
      cost: r.cost,
      clicks: r.clicks,
      conversions: r.conversions
    });
  }

  // Wasteful = spend, no conversions, enough clicks to be worth reading.
  var wasteful = table
    .filter(function (r) {
      return r.conversions === 0 && r.cost > 0 && r.clicks >= CONFIG.MIN_CLICKS;
    })
    .sort(function (a, b) {
      return b.cost - a.cost;
    });

  Logger.log(
    "Scanned " + table.length + " distinct n-grams. " +
      wasteful.length + " spent money with zero conversions " +
      "(min " + CONFIG.MIN_CLICKS + " clicks)."
  );
  var top = wasteful.slice(0, CONFIG.TOP_N);
  for (var t = 0; t < top.length; t++) {
    var w = top[t];
    Logger.log(
      (t + 1) + ". \"" + w.ngram + "\" (" + w.words + "-gram): " +
        currency + " " + w.cost.toFixed(2) + ", " +
        w.clicks + " clicks, 0 conv"
    );
  }

  writeSheet(table, wasteful.length, currency);
}

function writeSheet(table, wastefulCount, currency) {
  var ss;
  if (CONFIG.SHEET_URL) {
    ss = SpreadsheetApp.openByUrl(CONFIG.SHEET_URL);
  } else {
    ss = SpreadsheetApp.create("SEM Dispatch: Search Term N-grams");
    Logger.log("Created a new sheet. Paste this URL into CONFIG.SHEET_URL:");
    Logger.log(ss.getUrl());
  }

  var stamp = Utilities.formatDate(
    new Date(),
    AdsApp.currentAccount().getTimeZone(),
    "yyyy-MM-dd HHmm"
  );
  var sheet = ss.insertSheet("n-grams " + stamp);

  // Sort the full table by cost so the sheet opens on the money.
  table.sort(function (a, b) {
    return b.cost - a.cost;
  });

  var header = [
    "N-gram",
    "Words",
    "Cost (" + currency + ")",
    "Clicks",
    "Conversions",
    "Zero-conv waste"
  ];
  var data = [header];
  for (var i = 0; i < table.length; i++) {
    var r = table[i];
    data.push([
      r.ngram,
      r.words,
      Number(r.cost.toFixed(2)),
      r.clicks,
      Number(r.conversions.toFixed(2)),
      r.conversions === 0 && r.cost > 0 ? "yes" : ""
    ]);
  }

  sheet.getRange(1, 1, data.length, header.length).setValues(data);
  sheet.getRange(1, 1, 1, header.length).setFontWeight("bold");
  sheet.setFrozenRows(1);

  Logger.log(
    "Wrote " + (data.length - 1) + " n-grams to the Sheet, " +
      wastefulCount + " flagged as zero-conversion waste."
  );
}

Set it up

In Google Ads, open Tools, then Bulk actions, then Scripts.
Create a new script, delete the placeholder, and paste the code above.
Leave SHEET_URL empty for the first run, or paste in the URL of a Sheet you have already made.
Authorise the script when prompted (it will ask for spreadsheet access), then use Preview and read the log.
If it created a Sheet, copy the logged URL into SHEET_URL so future runs append tabs to the same file. Schedule it weekly.

How to read the table

Each run adds a dated tab, so you can watch a word's cost climb week to week. Sort by the cost column and read from the top: a two or three word n-gram at the top of the zero-conversion list is usually a clean negative phrase candidate, because the longer fragment is specific enough to exclude without collateral damage. Single words need more care, since "free" as a broad negative can catch "free delivery" alongside "free download".

Two honest limitations. First, the script counts a distinct n-gram once per search term but the same fragment's cost is still summed across every term it appears in, which is the point, though it means the column totals will exceed your real account spend (a word is counted inside every query that contains it). Read the numbers as relative weight, not as a reconciled spend report. Second, 30 days on a low-traffic account can flag words that would convert given more data, which is why the clicks column and the MIN_CLICKS floor are there: trust a zero-conversion verdict more the more clicks sit behind it.