Find wasted spend in a Google Ads search terms export

Why one ad group at a time hides waste

The report splits each search term by campaign, ad group and match type. A broad-match query that cost a few dollars in each of five ad groups, with no conversions, never stands out in any one of them. Totalled across the account, it is one of your most expensive non-converting queries. The FIXED expression does that totalling before the zero-conversion check, so a term that converted anywhere is never flagged.

Getting the CSV out of Google Ads

1. In Google Ads, open the Search terms report for the campaigns you want to check (it sits under Insights and reports).
2. Pick a date range, and leave out the last week or so: recent clicks may still convert.
3. Make sure the columns include the search term, match type, campaign, ad group, clicks, cost and conversions.
4. Download it as .csv and drop it in the box above.

The sample uses column names as they typically appear in that download (Search term, Match type, Added/Excluded, Campaign, Ad group, Clicks, Impr., Cost, Conversions, Conv. value). Your account's language and column choices can change them; the box lets you match each one.

The Tableau calculation

Search terms, totalled across the account

On Rows: [Search term]. Each calculation below is one column.

// Term conversions, whole account
{ FIXED [Search term] : SUM([Conversions]) }

// Campaigns the term matched in
COUNTD([Campaign])

// Clicks
SUM([Clicks])

// Cost
SUM([Cost])

// Wasted spend: cost of terms with no conversions anywhere, not yet excluded
SUM(IF [Term conversions, whole account] = 0 AND [Added/Excluded] <> 'Excluded' THEN [Cost] ELSE 0 END)

// Conversions
SUM([Conversions])

By match type

On Rows: [Match type]. Each calculation below is one column.

// Term conversions, whole account
{ FIXED [Search term] : SUM([Conversions]) }

// Cost
SUM([Cost])

// Conversions
SUM([Conversions])

// Cost per conversion
SUM([Cost]) / SUM([Conversions])

// Conversion value per unit of cost (ROAS)
SUM([Conv. value]) / SUM([Cost])

// Share of cost on terms with no conversions anywhere
ZN(SUM(IF [Term conversions, whole account] = 0 THEN [Cost] END)) / SUM([Cost])

Reading the match type table

Close variants appear as their own match type labels, such as Exact match (close variant), so you can see whether they convert as well as true exact matches. In Tableau, dividing by a SUM of zero returns NULL, so a match type with no conversions shows a blank cost per conversion; the SQL gets the same with NULLIF.

The equivalent SQL

Search terms, totalled across the account

WITH terms AS (
  SELECT * FROM read_csv('search-terms-report.csv', skip = 2, header = true)
  WHERE "Search term" NOT LIKE 'Total:%'
),
flagged AS (
  SELECT *, SUM("Conversions") OVER (PARTITION BY "Search term") AS term_conversions
  FROM terms
)
SELECT "Search term",
       COUNT(DISTINCT "Campaign") AS campaigns,
       SUM("Clicks") AS clicks,
       SUM("Cost") AS cost,
       SUM(CASE WHEN term_conversions = 0 AND "Added/Excluded" <> 'Excluded' THEN "Cost" ELSE 0 END) AS wasted_spend,
       SUM("Conversions") AS conversions
FROM flagged
GROUP BY "Search term"
ORDER BY wasted_spend DESC, cost DESC;

By match type

WITH terms AS (
  SELECT * FROM read_csv('search-terms-report.csv', skip = 2, header = true)
  WHERE "Search term" NOT LIKE 'Total:%'
),
flagged AS (
  SELECT *, SUM("Conversions") OVER (PARTITION BY "Search term") AS term_conversions
  FROM terms
)
SELECT "Match type",
       SUM("Cost") AS cost,
       SUM("Conversions") AS conversions,
       SUM("Cost") / NULLIF(SUM("Conversions"), 0) AS cost_per_conversion,
       SUM("Conv. value") / SUM("Cost") AS roas,
       COALESCE(SUM("Cost") FILTER (WHERE term_conversions = 0), 0) / SUM("Cost") AS share_on_non_converting
FROM flagged
GROUP BY "Match type"
ORDER BY "Match type";

Before you add negatives

• Not every query is listed. Google Ads omits search terms that don't have enough query activity to meet its privacy standards. Compare the cost in this file with your campaign totals to see how much spend the rows don't cover.
• Conversion lag. Buyers who take a week or two to convert make recent terms look like waste. Use a date range that ends at least a week ago.
• Single words across many queries. Words like free, jobs or pdf cost money across hundreds of long-tail queries. Flag them with CONTAINS(LOWER([Search term]), 'jobs'), or split terms into words in SQL with UNNEST(string_split(lower("Search term"), ' ')) and total the wasted spend per word.