Free PPC

Toolkit

var CONFIG = {
  // ═══════════════════════════════════════════════════════════════════════════
  // THRESHOLDS — Adjust for your account
  // ═══════════════════════════════════════════════════════════════════════════
  MAX_CPA: 50.00,              // Flag terms above this CPA
  MIN_CONVERSIONS: 0,          // Terms with 0 conversions and spend > MIN_SPEND
  MIN_SPEND_NO_CONV: 25.00,    // Minimum spend with 0 conversions to flag
  MIN_CLICKS: 5,               // Minimum clicks before evaluating
  DATE_RANGE: 'LAST_30_DAYS',

  // ═══════════════════════════════════════════════════════════════════════════
  // OUTPUT SETTINGS
  // ═══════════════════════════════════════════════════════════════════════════
  SPREADSHEET_URL: 'CREATE_NEW',
  AUTO_APPLY: false,           // Set to true to auto-add negatives
  MATCH_TYPE: 'EXACT',         // EXACT, PHRASE, or BROAD
  NEGATIVE_LIST_NAME: 'Auto-Negatives',

  // ═══════════════════════════════════════════════════════════════════════════
  // PROTECTION
  // ═══════════════════════════════════════════════════════════════════════════
  BRAND_TERMS: ['your brand', 'your company'],  // Never add as negative
  MAX_NEGATIVES_PER_RUN: 50,   // Safety limit
  EMAIL_RECIPIENTS: [],
};

function main() {
  var report = getSearchTermReport();
  var flagged = analyzeTerms(report);
  var filtered = applyProtections(flagged);

  logResults(filtered);

  if (CONFIG.AUTO_APPLY && filtered.length > 0) {
    addNegativeKeywords(filtered);
  }

  if (CONFIG.EMAIL_RECIPIENTS.length > 0) {
    sendAlert(filtered);
  }
}

function getSearchTermReport() {
  var query = 'SELECT SearchTerm, CampaignName, AdGroupName, ' +
    'Impressions, Clicks, Cost, Conversions, ConversionValue ' +
    'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +
    'WHERE Impressions > 0 ' +
    'DURING ' + CONFIG.DATE_RANGE;

  var report = AdsApp.report(query);
  var rows = report.rows();
  var terms = [];

  while (rows.hasNext()) {
    var row = rows.next();
    terms.push({
      term: row['SearchTerm'],
      campaign: row['CampaignName'],
      adGroup: row['AdGroupName'],
      impressions: parseInt(row['Impressions']),
      clicks: parseInt(row['Clicks']),
      cost: parseFloat(row['Cost']),
      conversions: parseFloat(row['Conversions']),
      value: parseFloat(row['ConversionValue']),
    });
  }

  Logger.log('Found ' + terms.length + ' search terms to analyze');
  return terms;
}

function analyzeTerms(terms) {
  var flagged = [];

  terms.forEach(function(t) {
    if (t.clicks < CONFIG.MIN_CLICKS) return;

    var reason = '';

    if (t.conversions === 0 && t.cost >= CONFIG.MIN_SPEND_NO_CONV) {
      reason = 'Zero conversions, spent $' + t.cost.toFixed(2);
    } else if (t.conversions > 0) {
      var cpa = t.cost / t.conversions;
      if (cpa > CONFIG.MAX_CPA) {
        reason = 'CPA $' + cpa.toFixed(2) + ' exceeds $' + CONFIG.MAX_CPA;
      }
    }

    if (reason) {
      flagged.push({
        term: t.term,
        campaign: t.campaign,
        adGroup: t.adGroup,
        clicks: t.clicks,
        cost: t.cost,
        conversions: t.conversions,
        reason: reason,
      });
    }
  });

  Logger.log('Flagged ' + flagged.length + ' terms');
  return flagged;
}

function applyProtections(flagged) {
  var brandRegex = new RegExp(CONFIG.BRAND_TERMS.join('|'), 'i');

  var filtered = flagged.filter(function(t) {
    return !brandRegex.test(t.term);
  });

  if (filtered.length > CONFIG.MAX_NEGATIVES_PER_RUN) {
    filtered.sort(function(a, b) { return b.cost - a.cost; });
    filtered = filtered.slice(0, CONFIG.MAX_NEGATIVES_PER_RUN);
    Logger.log('Limited to ' + CONFIG.MAX_NEGATIVES_PER_RUN + ' (safety cap)');
  }

  return filtered;
}

function addNegativeKeywords(terms) {
  var lists = AdsApp.negativeKeywordLists()
    .withCondition('Name = "' + CONFIG.NEGATIVE_LIST_NAME + '"')
    .get();

  var list;
  if (lists.hasNext()) {
    list = lists.next();
  } else {
    list = AdsApp.newNegativeKeywordListBuilder()
      .withName(CONFIG.NEGATIVE_LIST_NAME)
      .build()
      .getResult();
  }

  terms.forEach(function(t) {
    if (CONFIG.MATCH_TYPE === 'EXACT') {
      list.addNegativeKeyword('[' + t.term + ']');
    } else if (CONFIG.MATCH_TYPE === 'PHRASE') {
      list.addNegativeKeyword('"' + t.term + '"');
    } else {
      list.addNegativeKeyword(t.term);
    }
  });

  Logger.log('Added ' + terms.length + ' negative keywords to "' + CONFIG.NEGATIVE_LIST_NAME + '"');
}

function logResults(terms) {
  var ss;
  if (CONFIG.SPREADSHEET_URL === 'CREATE_NEW') {
    ss = SpreadsheetApp.create('Negative Keyword Report — ' + new Date().toISOString().split('T')[0]);
    Logger.log('Created spreadsheet: ' + ss.getUrl());
  } else {
    ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  }

  var sheet = ss.getActiveSheet();
  sheet.clear();
  sheet.appendRow(['Search Term', 'Campaign', 'Ad Group', 'Clicks', 'Cost', 'Conversions', 'Reason']);

  terms.forEach(function(t) {
    sheet.appendRow([t.term, t.campaign, t.adGroup, t.clicks, t.cost, t.conversions, t.reason]);
  });
}

function sendAlert(terms) {
  var subject = 'Negative Keyword Automator — ' + terms.length + ' terms flagged';
  var body = 'The following search terms were flagged:\n\n';

  terms.slice(0, 20).forEach(function(t) {
    body += '• ' + t.term + ' ($' + t.cost.toFixed(2) + ') — ' + t.reason + '\n';
  });

  if (terms.length > 20) {
    body += '\n... and ' + (terms.length - 20) + ' more. See spreadsheet for full list.';
  }

  CONFIG.EMAIL_RECIPIENTS.forEach(function(email) {
    MailApp.sendEmail(email, subject, body);
  });
}

Configuration Guide

How It Works

1. Pulls search term report for the configured date range
2. Flags terms exceeding CPA threshold or spending without conversions
3. Filters out brand terms (never added as negatives)
4. Caps additions per run for safety
5. Optionally auto-adds to a shared negative keyword list
6. Logs everything to a spreadsheet and sends email alerts