Google Ads Script to Report Wasted Spend

/**
 * Wasted Spend Report
 *
 * Reads the search terms report across all enabled campaigns for the last N days,
 * splits findings into two buckets, and emails a report. It never adds negatives.
 *
 * Bucket 1: wasted spend - terms that got clicks with zero conversions.
 * Bucket 2: high cost per conversion - terms that converted but cost more
 *           per conversion than your threshold.
 *
 * Terms matching IGNORE_TERMS (brand and product names) are never flagged.
 *
 * Configure the constants below, then schedule this to run daily.
 */

const CONFIG = {
  LOOKBACK_DAYS: 30,
  MIN_SPEND: 20, // flag terms that spent at least this, in your account's currency
  MIN_CPA: 100,  // flag terms with cost per conversion above this
  NOTIFICATION_EMAIL: "you@example.com",
  IGNORE_TERMS: [
    // Brand names, product names, and anything you never want flagged.
    // Case-insensitive substring match, so "dan marketing" also catches
    // "dan marketing reviews" and "dan marketing pricing".
    "dan marketing",
    "danmarketing",
  ],
};

function main() {
  const dateRange = getDateRange(CONFIG.LOOKBACK_DAYS);
  const report = findWastedSpend(dateRange);

  if (report.wastedSpend.length === 0 && report.highCpa.length === 0) {
    Logger.log("No wasted spend found in the lookback window.");
    return;
  }

  sendReportEmail(report);
}

function getDateRange(days) {
  const end = new Date();
  const start = new Date();
  start.setDate(end.getDate() - days);
  const fmt = (d) => Utilities.formatDate(d, AdsApp.currentAccount().getTimeZone(), "yyyyMMdd");
  return { start: fmt(start), end: fmt(end) };
}

function findWastedSpend(dateRange) {
  const query = `
    SELECT
      search_term_view.search_term,
      campaign.id,
      campaign.name,
      ad_group.id,
      ad_group.name,
      metrics.cost_micros,
      metrics.conversions
    FROM search_term_view
    WHERE segments.date BETWEEN '${dateRange.start}' AND '${dateRange.end}'
      AND campaign.status = 'ENABLED'
    ORDER BY metrics.cost_micros DESC
  `;

  const rows = AdsApp.search(query);
  const wastedSpend = [];
  const highCpa = [];
  let ignored = 0;

  const minSpendMicros = CONFIG.MIN_SPEND * 1000000;
  const minCpaMicros = CONFIG.MIN_CPA * 1000000;

  while (rows.hasNext()) {
    const row = rows.next();
    const term = row.searchTermView.searchTerm;
    const cost = Number(row.metrics.costMicros);
    const conversions = Number(row.metrics.conversions);

    if (isIgnored(term)) {
      ignored++;
      continue;
    }

    if (cost < minSpendMicros) continue;

    const entry = {
      term: term,
      campaignId: row.campaign.id,
      campaignName: row.campaign.name,
      adGroupId: row.adGroup.id,
      adGroupName: row.adGroup.name,
      costMicros: cost,
      conversions: conversions,
    };

    if (conversions === 0) {
      wastedSpend.push(entry);
    } else if (cost / conversions > minCpaMicros) {
      highCpa.push(entry);
    }
  }

  return { wastedSpend, highCpa, ignored };
}

function isIgnored(term) {
  const normalized = term.toLowerCase();
  return CONFIG.IGNORE_TERMS.some((t) => normalized.includes(t.toLowerCase()));
}

function sendReportEmail(report) {
  const currency = AdsApp.currentAccount().getCurrencyCode();
  const totalWasted = sumMicros(report.wastedSpend);
  const totalHighCpa = sumMicros(report.highCpa);
  const total = totalWasted + totalHighCpa;
  const totalTerms = report.wastedSpend.length + report.highCpa.length;

  const subject = `[Google Ads] Wasted spend report: ${money(total, currency)} across ${totalTerms} terms`;

  const wastedLines = report.wastedSpend
    .map((r) => `- "${r.term}" (${r.campaignName} / ${r.adGroupName}) ${money(r.costMicros, currency)}`)
    .join("\n");

  const cpaLines = report.highCpa
    .map(
      (r) =>
        `- "${r.term}" (${r.campaignName} / ${r.adGroupName}) ${money(r.costMicros, currency)}, ${r.conversions} conversions, ${money(r.costMicros / r.conversions, currency)} each`
    )
    .join("\n");

  const body = [
    `Wasted spend (zero conversions): ${report.wastedSpend.length} terms, ${money(totalWasted, currency)}`,
    wastedLines,
    "",
    `High cost per conversion: ${report.highCpa.length} terms, ${money(totalHighCpa, currency)}`,
    cpaLines,
    "",
    `Ignored (brand protection): ${report.ignored} terms`,
  ].join("\n");

  MailApp.sendEmail(CONFIG.NOTIFICATION_EMAIL, subject, body);
}

function sumMicros(rows) {
  return rows.reduce((sum, r) => sum + r.costMicros, 0);
}

// Google Ads returns money in micros: 1,000,000 micros = 1 unit of the
// account's currency (e.g. $4.48 = 4480000 micros). Divide by 1e6 to get
// units. parseFloat guards against a string value slipping through.
function money(micros, currency) {
  return `${parseFloat(micros / 1000000).toFixed(2)} ${currency}`;
}