Geo Waste Finder: the locations quietly spending your budget with nothing to show - The Vault

Most accounts target a country or a handful of regions and never look inside that targeting again. Some of those locations convert well, some barely convert at all, and the account keeps spending on both at the same rate because nobody ranked them side by side. This script does the ranking: it pulls every location's spend and conversions, works out which ones are taking more of the budget than they earn in results, and writes a sorted list to a Sheet. It changes nothing in the account. No location gets a bid adjustment, no location gets excluded. It reports, you decide.

Before you start

You need access to the account and permission to authorise scripts.
Create a Google Sheet for the output and copy its URL into the CONFIG block.
Decide your two thresholds: a minimum spend below which a location is not worth acting on, and a waste threshold (the point gap between spend share and conversion share) that counts as a genuine problem rather than noise.
This script reads geographic_view, which reports against the location a click was matched to. If you specifically need to separate a user's physical location from the location they searched about, the user_location_view resource carries that split; it is a straightforward swap of the FROM clause with the same shape of report.

The script

JavaScript

/**
 * Geo Waste Finder
 * The SEM Dispatch Vault, semdispatch.com/vault
 *
 * Ranks locations by how far their spend share outruns their conversion
 * share, writes the worst offenders to a Sheet, and optionally emails a
 * summary. Read-only: this script never changes bids, budgets or targeting.
 */

var CONFIG = {
  // How many days back to pull the geographic report.
  LOOKBACK_DAYS: 30,

  // Ignore locations that have not spent at least this much in the account
  // currency. Keeps small, noisy locations off the flagged list.
  MIN_SPEND: 50,

  // Flag a location when its spend share exceeds its conversion share by
  // this many points. 0.05 means five percentage points.
  WASTE_THRESHOLD: 0.05,

  // Paste the URL of a Google Sheet the script can write to.
  SHEET_URL: "PASTE_YOUR_SHEET_URL_HERE",

  // Optional. An email address for the summary, e.g. "you@example.com".
  // Leave empty to log and write the Sheet only.
  EMAIL: ""
};

function main() {
  var account = AdsApp.currentAccount();
  var currency = account.getCurrencyCode();

  var end = new Date();
  var start = new Date(end.getTime() - CONFIG.LOOKBACK_DAYS * 24 * 60 * 60 * 1000);
  var tz = account.getTimeZone();
  var startStr = Utilities.formatDate(start, tz, "yyyy-MM-dd");
  var endStr = Utilities.formatDate(end, tz, "yyyy-MM-dd");

  var query =
    "SELECT geographic_view.country_criterion_id, " +
    "geographic_view.location_type, " +
    "segments.geo_target_region, " +
    "metrics.cost_micros, " +
    "metrics.conversions, " +
    "metrics.conversions_value, " +
    "metrics.clicks " +
    "FROM geographic_view " +
    "WHERE segments.date BETWEEN '" + startStr + "' AND '" + endStr + "'";

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

  var byLocation = {};
  var totalCost = 0;
  var totalConversions = 0;

  while (rows.hasNext()) {
    var row = rows.next();
    var region = row["segments.geo_target_region"] || "unspecified";
    var country = row["geographic_view.country_criterion_id"];
    var key = country + ":" + region;

    var cost = Number(row["metrics.cost_micros"]) / 1e6;
    var conversions = Number(row["metrics.conversions"]);
    var conversionsValue = Number(row["metrics.conversions_value"]);
    var clicks = Number(row["metrics.clicks"]);

    if (!byLocation[key]) {
      byLocation[key] = {
        key: key,
        region: region,
        country: country,
        locationType: row["geographic_view.location_type"],
        cost: 0,
        conversions: 0,
        conversionsValue: 0,
        clicks: 0
      };
    }

    byLocation[key].cost += cost;
    byLocation[key].conversions += conversions;
    byLocation[key].conversionsValue += conversionsValue;
    byLocation[key].clicks += clicks;

    totalCost += cost;
    totalConversions += conversions;
  }

  var locations = [];
  for (var k in byLocation) {
    locations.push(byLocation[k]);
  }

  locations.forEach(function (loc) {
    loc.spendShare = totalCost > 0 ? loc.cost / totalCost : 0;
    loc.conversionShare = totalConversions > 0 ? loc.conversions / totalConversions : 0;
    loc.gap = loc.spendShare - loc.conversionShare;
    loc.costPerConversion = loc.conversions > 0 ? loc.cost / loc.conversions : null;
    loc.flagged = loc.cost >= CONFIG.MIN_SPEND && loc.gap > CONFIG.WASTE_THRESHOLD;
    loc.wastedEstimate = loc.gap > 0 ? loc.gap * totalCost : 0;
  });

  var flagged = locations.filter(function (loc) { return loc.flagged; });
  flagged.sort(function (a, b) { return b.wastedEstimate - a.wastedEstimate; });

  Logger.log("Geo Waste Finder, " + account.getName());
  Logger.log("Locations examined: " + locations.length + ", flagged: " + flagged.length);
  flagged.forEach(function (loc) {
    Logger.log(
      loc.region + " (country " + loc.country + "): spend " + currency + " " +
      loc.cost.toFixed(2) + ", " + (loc.spendShare * 100).toFixed(1) +
      "% of spend vs " + (loc.conversionShare * 100).toFixed(1) +
      "% of conversions, est. wasted " + currency + " " + loc.wastedEstimate.toFixed(2)
    );
  });

  if (CONFIG.SHEET_URL && CONFIG.SHEET_URL.indexOf("http") === 0) {
    var sheet = SpreadsheetApp.openByUrl(CONFIG.SHEET_URL).getSheets()[0];
    sheet.clearContents();
    sheet.appendRow([
      "Region", "Country criterion", "Location type", "Spend", "Spend share",
      "Conversions", "Conversion share", "Cost per conversion",
      "Estimated wasted spend"
    ]);
    flagged.forEach(function (loc) {
      sheet.appendRow([
        loc.region, loc.country, loc.locationType, loc.cost.toFixed(2),
        (loc.spendShare * 100).toFixed(1) + "%", loc.conversions.toFixed(1),
        (loc.conversionShare * 100).toFixed(1) + "%",
        loc.costPerConversion === null ? "no conversions" : loc.costPerConversion.toFixed(2),
        loc.wastedEstimate.toFixed(2)
      ]);
    });
  }

  if (CONFIG.EMAIL !== "" && flagged.length > 0) {
    var lines = ["Geo Waste Finder, " + account.getName(), ""];
    flagged.slice(0, 10).forEach(function (loc) {
      lines.push(
        loc.region + ": " + currency + " " + loc.cost.toFixed(2) + " spent, est. wasted " +
        currency + " " + loc.wastedEstimate.toFixed(2)
      );
    });
    MailApp.sendEmail(
      CONFIG.EMAIL,
      "[Geo waste] " + account.getName() + ": " + flagged.length + " locations flagged",
      lines.join("\n")
    );
  }
}

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.
Set SHEET_URL to a Sheet you own, and adjust MIN_SPEND and WASTE_THRESHOLD for the account's scale.
Authorise the script, then use Preview to check the log output before scheduling.
Schedule it weekly. Geo waste moves slowly; a daily run mostly reproduces the same list.

How to read the output

Spend share and conversion share are each a location's slice of the account total. A location taking twelve percent of spend and delivering three percent of conversions has a nine-point gap: that is the number the script sorts on, turned into a rough currency estimate of what that gap is costing across the account's total spend. Cost per conversion is worth a second look before you act; a location with a genuinely small number of conversions can still be efficient, it is the spend-versus-share pattern combined with a real spend floor that separates a true problem from statistical noise.

One honest limitation: this reads the location a click was matched to, not confirmed intent, and a short lookback window can flag a location that is simply having a slow month. Run it over 30 days, cross-check anything it flags against a longer trend before you touch targeting, and remember it is a report, not a verdict.