Inside the Waste Detection Engine

Sourced from two tables combined in a single query. Total spend comes from google_ads_search_terms_historical. Wasted spend and recovery potential come from waste_management_intelligence_daily — a pre-calculated pipeline table that classifies every dollar of spend with a waste category, entity type, and recommended action. The leakage rate is the ratio of the two.

Leakage Rate = wasted_spend ÷ total_spend × 100

Where:
  total_spend  = SUM(cost) from google_ads_search_terms_historical
  wasted_spend = SUM(wasted_spend) from waste_management_intelligence_daily

Both calculated over the same 30-day window.
COALESCE(wasted_spend, 0) applied — accounts with
zero classified waste return 0.0% instead of NULL.

Sourced from google_ads_search_terms_historical joined against search_terms_intelligence_daily and waste_management_intelligence_daily. A term qualifies as a waste candidate when it has zero conversions AND at least 2 clicks in the 30-day window. The 2-click minimum filters out single accidental impressions. The verdict field (ADD_NEGATIVE, MONITOR, UNDER_REVIEW) is pre-classified by the search_terms_intelligence pipeline.

Waste term conditions (ALL must be true):
  1. conversions = 0
  2. clicks ≥ 2
  3. cost > 0

Verdict sourced from search_terms_intelligence_daily:
  is_negative_candidate = TRUE → ADD_NEGATIVE
  Otherwise → MONITOR or UNDER_REVIEW

Results ordered by wasted_spend DESC, capped at 25 terms.

Sourced from google_ads_search_terms_matched_keywords joined against waste_management_intelligence_daily. The query calculates each match type's proportional share of campaign spend, then applies that share to the campaign's total classified waste. This ensures the match type breakdown always sums exactly to your total wasted spend KPI — no double-counting or rounding gaps.

Match type waste = 
  SAFE_DIVIDE(match_type_spend, total_campaign_spend)
  × campaign_total_wasted_spend

Summed across all campaigns per match type.

Match types tracked:
  BROAD, PHRASE, EXACT, UNKNOWN

Sourced from google_ads_search_terms_historical (for device-level spend distribution) joined against waste_management_intelligence_daily (for rigorous classified waste). Device waste is NOT calculated directly from zero-conversion rows — instead, each campaign's total classified waste is distributed to devices proportionally based on device spend share. This ensures the device friction chart always equals the headline KPI exactly.

Device wasted_spend =
  SAFE_DIVIDE(device_spend, total_campaign_spend)
  × campaign_total_wasted_spend

waste_rate = device_wasted_spend ÷ device_total_spend × 100

Guardrail: HAVING total_spend > 0
(Excludes devices with no active spend)

Sourced from google_ads_account_pulse_hourly. Unlike a simple zero-conversion filter, this query calculates True Wasted Spend using a dynamic Target CPA derived from your account's own 30-day average. For each hour-of-day block, any spend above what would have been needed to buy the actual conversions at your average CPA is classified as waste. Statistical significance threshold: only hours with at least 50 clicks are surfaced in the top wasted hours table.

avg_cpa = SUM(cost) ÷ SUM(conversions)
             (from google_ads_search_terms_historical, 30-day window)

true_wasted_spend per hour =
  GREATEST(0, hourly_spend − (hourly_conversions × avg_cpa))

Top wasted hours: clicks ≥ 50 AND ordered by true_wasted_spend DESC
(Capped at 5 time blocks for the report)

Sourced from waste_management_intelligence_daily. Every entity in your account is pre-classified by the pipeline into one of four waste_category values. The savings_forecast query groups these and sums wasted_spend and recovery_potential per category. This is the structural diagnosis — a single dominant category tells you exactly which lever to pull first.

Per category:
  total_waste = SUM(wasted_spend)
  recovery_potential = SUM(recovery_potential)
  entity_count = COUNT(DISTINCT entity_id)

Ordered by total_waste DESC.
waste_category IS NOT NULL filter applied.

Actual waste_category values from the pipeline:
  EXPLORATION   — algorithmic learning spend, campaign over-learning
  CREATIVE      — weak RSA combinations, low-performing assets
  QUERY         — zero-conversion search term intent mismatch
  AUCTION_BID   — bid inefficiency, overpaying at auction

Appears in the PDF appendix as a composite score from 0–100. Calculated as the ratio of converting spend to total spend within the reporting window — the inverse of your leakage rate. A score of 98/100 means 98% of spend produced at least one conversion. This metric is a high-level health signal, not an action trigger on its own — the category breakdown tells you what to fix, the efficiency score tells you how urgently.

Account Efficiency Score =
  (1 − leakage_rate) × 100

Where leakage_rate = wasted_spend ÷ total_spend

Example: 1.6% leakage → Score = 98.4 → rounds to 98/100

Severity bands:
  90–100 → Strong efficiency, targeted fixes only
  70–89  → Moderate — structural improvements needed
  < 70   → Critical — immediate intervention required

Sourced from search_terms_intelligence_daily joined against google_ads_search_terms_historical. A term surfaces as a negative candidate only when the pipeline has set is_negative_candidate = TRUE. This flag is set by the intelligence pipeline based on multi-signal analysis — not just zero conversions. Results are ordered by wasted_spend and capped at 15 terms for the report.

Negative candidate conditions:
  1. is_negative_candidate = TRUE (pipeline-classified)
  2. Joined to historical table to confirm spend in window
  3. Ordered by SUM(wasted_spend) DESC
  4. Limit 15

The recommendation field (e.g. ADD_EXACT_NEGATIVE,
ADD_PHRASE_NEGATIVE) is sourced directly from the
intelligence pipeline — not computed at report time.

The financial recovery metric applies a conservative 70% capture rate against total identified waste. This multiplier accounts for standard market volatility and the reality that not all paused spend will perfectly remap to incremental conversions. The recovery_potential field is pre-calculated per entity in the pipeline and summed at report time. Reallocating this capital to high-efficiency campaigns requires no increase in total baseline spend.

Conservative Recovery = confirmed_waste × 0.70

Where confirmed_waste = SUM(wasted_spend)
from waste_management_intelligence_daily

The 30% discount accounts for:
  • Standard market volatility
  • Imperfect spend remapping to efficient campaigns
  • Reality that not all paused spend converts immediately

action_priority field determines fix order:
  Priority 1 → highest recovery impact, fix first
  Priority 2–3 → secondary, address after P1 fixes