Get a weekly waste audit emailed straight from your Google Ads account.

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   PPC RED FLAGS: COMPREHENSIVE WASTE REPORT (v3)
//   ppcredflags.com
//
//   ─────────────────────────────────────────────────────────────────────────
//   This script does not just find this week's bad queries. It finds every
//   form of search-term waste an experienced PPC auditor would catch:
//
//     • CHRONIC OFFENDERS: queries that have been bleeding money for 30+
//       days. The single strongest signal of agency neglect.
//
//     • EXPENSIVE CONVERTERS: queries with conversions but CPL 3x+ above
//       your account average. Soft-success queries the agency reports as
//       "performing" but that are quietly inflating your real CPL.
//
//     • GEOGRAPHIC LEAKAGE: clicks from countries you do not serve, the
//       classic "Presence or interest" location-targeting mistake.
//
//     • CATEGORICAL WASTE: job intent, DIY/free intent, research intent,
//       B2C-on-B2B intent, comparison shopping, negative sentiment, and
//       custom product-category mismatches you configure.
//
//     • TREND DIRECTION: is your waste getting better or worse week over
//       week? Direction matters more than current state.
//
//   The output is a complete weekly audit email designed to be forwarded
//   directly to your agency with the question "explain this."
//
//   ─────────────────────────────────────────────────────────────────────────
//   HOW TO INSTALL:
//
//     1. In Google Ads: Tools & Settings → Bulk Actions → Scripts → + New
//     2. Delete all the starter code in the editor
//     3. Paste this entire file
//     4. Edit the CONFIG section below (your email, brand, products, geo)
//     5. Click Preview to confirm the script runs against your data
//     6. Authorize when prompted (Google needs permission to send email)
//     7. Save, then set frequency: Weekly on Monday at 7am
//
//   READ-ONLY: This script reads your account data. It does not change any
//   campaign settings, bids, keywords, or budgets. Ever.
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

// === START_USER_CONFIG ===
var CONFIG = {

  // ── Who gets the report ─────────────────────────────────────────────────
  // Comma-separate multiple addresses: 'you@company.com, boss@company.com'
  EMAIL_RECIPIENTS: 'you@yourcompany.com',

  // A short label for your account, shown in the email subject.
  ACCOUNT_LABEL: 'Your Company Name',

  // ── Industry preset ─────────────────────────────────────────────────────
  // Controls vertical-aware behavior. Suppresses the Expensive Converters
  // section for high-AOV verticals where high-CPL converters are usually
  // the best leads, sets a vertical-appropriate floor for the conversion-
  // tracking sanity check, and softens chronic-offender wording for queries
  // that could plausibly be a service line you offer. See INDUSTRY_BEHAVIOR
  // table below for the supported values. Use 'custom' to disable.
  INDUSTRY: 'custom',

  // ── Spend thresholds ────────────────────────────────────────────────────
  // Minimum spend (in your account currency) for a query to be flagged
  // as wasted in the WEEKLY (7-day) window. Queries below this are ignored
  // in the recent-week section, too small to act on individually.
  WEEKLY_SPEND_THRESHOLD: 15,

  // Minimum spend for a query to be flagged as a CHRONIC OFFENDER over
  // the 30-day window. Lower this to catch smaller chronic offenders.
  // Even a $30 query that has been wasting money for 30 days is $360/year.
  CHRONIC_SPEND_THRESHOLD: 30,

  // CPL severity multiplier for "expensive converters" detection.
  // Queries WITH conversions but CPL above (account avg × this) get flagged.
  // Default 3.0 = flag queries 3x more expensive than account average.
  CPL_ANOMALY_MULTIPLIER: 3.0,

  // ── Your brand terms ────────────────────────────────────────────────────
  // Search terms containing these get separated into a brand note rather
  // than counted as pure waste. Add every variant, misspelling, and
  // brand-unique product name. Lowercase; matching is case-insensitive.
  BRAND_TERMS: [
    'your brand name',
    'yourbrand',
    'your brand product'
  ],

  // ── Competitor brand names ──────────────────────────────────────────────
  // Queries matching these are tagged as competitor traffic. Often
  // intentional (conquest targeting) but worth knowing the volume.
  COMPETITOR_TERMS: [
    'competitor one',
    'competitor two'
  ],

  // ── Products you DO NOT sell ────────────────────────────────────────────
  // List product types, services, or use cases that are NOT in your
  // catalog. Queries matching these are pure waste: broad/phrase match
  // overreach into adjacent product categories. Examples for an industrial
  // label printer: ['food labels', 'wedding labels', 'address labels',
  //                  'kids stickers', 'avery template']
  PRODUCTS_NOT_SOLD: [
    // Add words specific to products/services you do not offer
  ],

  // ── Your low-value terms ────────────────────────────────────────────────
  // Generic intent words that signal a low-quality lead for your business:
  // "cheap", "free", "diy", "youtube", "reddit", "wikipedia", etc. Queries
  // containing any of these get bucketed into a "Low-value intent (your
  // terms)" waste category. Different from PRODUCTS_NOT_SOLD (which is about
  // adjacent categories you don't offer). These are intent signals across
  // your real categories. Industry presets fill this with sensible defaults.
  LOW_VALUE_TERMS: [
    'free',
    'diy',
    'youtube',
    'reddit',
    'tutorial'
  ],

  // ── Buyer type ──────────────────────────────────────────────────────────
  // 'B2B' enables B2C-intent detection (filters out consumer searches).
  // 'B2C' enables B2B-intent detection (filters out wholesale/bulk).
  // 'BOTH' or null disables this filter.
  BUYER_TYPE: 'B2B',

  // ── Target geographic markets ───────────────────────────────────────────
  // Two-letter ISO country codes you actually serve.
  // The script flags spend from any other country as geographic leakage.
  // Examples: ['US'] or ['US', 'CA'] or ['US', 'CA', 'GB', 'AU']
  // Leave as empty array [] to skip geo analysis entirely.
  TARGET_COUNTRIES: ['US'],

  // ── Display options ─────────────────────────────────────────────────────
  CURRENCY_SYMBOL: '$',

  // Max rows in each query table. Keeps email readable.
  MAX_ROWS_CHRONIC: 30,
  MAX_ROWS_WEEKLY: 30,
  MAX_ROWS_EXPENSIVE: 15,

  // Show links back to ppcredflags.com for each waste category.
  INCLUDE_RED_FLAG_LINKS: true

};
// === END_USER_CONFIG ===

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: WASTE CATEGORIES
//   ─────────────────────────────────────────────────────────────────────────
//   Each query gets assigned to the FIRST category whose triggers it matches.
//   Order matters: more specific categories should come before catch-alls.
//   The 'other' category at the end catches anything not matched.
//
//   Trigger words match if they appear anywhere in the query (case-insensitive).
//   Use spaces around words to avoid false positives, e.g. ' vs ' not 'vs'.
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

var WASTE_CATEGORIES = [

  // ── Job & Career Intent ─────────────────────────────────────────────────
  {
    id: 'job_intent',
    label: 'Job & Career Intent',
    description: 'People looking for employment, not your product.',
    fix: 'Add all job/career terms as exact-match negatives at the account level. One-time fix, ten minutes of work, blocks this category permanently.',
    triggers: [
      'job', 'jobs', 'career', 'careers', 'salary', 'salaries',
      'hiring', 'hire', 'resume', 'employment', 'employee',
      'apply now', 'apply for', 'job opening', 'job posting',
      'work from home', 'remote work', 'remote position',
      'internship', 'intern', 'glassdoor', 'indeed.com', 'linkedin.com',
      'benefits', 'part time', 'full time', 'pay scale', 'wage'
    ],
    redFlagUrl: 'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
    color: '#7C3AED'
  },

  // ── DIY & Free Intent ───────────────────────────────────────────────────
  {
    id: 'diy_intent',
    label: 'DIY & Free Intent',
    description: 'People looking to do it themselves or find a free version.',
    fix: 'Add DIY/free intent terms as negatives. Especially "free": almost no qualified B2B buyer searches with that word.',
    triggers: [
      'how to', 'free', 'tutorial', 'diy', 'template', 'templates',
      'example', 'examples', 'homemade', 'make your own', 'make at home',
      'open source', 'download', 'printable', 'step by step',
      'do it yourself', 'self made', 'build my own', 'craft'
    ],
    redFlagUrl: 'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
    color: '#D97706'
  },

  // ── Informational / Research ────────────────────────────────────────────
  {
    id: 'info_intent',
    label: 'Informational & Research',
    description: 'Research queries with no buying intent.',
    fix: 'Add to negatives unless you have a content strategy specifically targeting research stages. Most informational traffic does not convert on paid.',
    triggers: [
      'what is', 'what are', 'definition', 'meaning of', 'meaning',
      ' vs ', 'versus', 'difference between', 'compare', 'comparison',
      'history of', 'types of', 'overview', 'explained', 'explanation',
      'how does', 'how do', 'why is', 'why does', 'wikipedia', 'wiki',
      'pros and cons', 'pros & cons'
    ],
    redFlagUrl: 'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
    color: '#0F766E'
  },

  // ── Negative sentiment ──────────────────────────────────────────────────
  {
    id: 'negative_sentiment',
    label: 'Negative Sentiment',
    description: 'Searches expressing complaints, comparison-against-you, or distrust.',
    fix: 'Most negative sentiment queries should be negatives. Exception: if you have a strong reputation page or comparison-against-competitor content, those specific queries can be exceptions.',
    triggers: [
      'review', 'reviews', 'rating', 'ratings', 'complaint', 'complaints',
      'scam', 'fraud', 'lawsuit', 'sued', 'fired', 'lost',
      'alternative to', 'alternatives to', 'instead of',
      'better than', 'worse than', 'problem with', 'issues with',
      'recall', 'fail', 'failed', 'broken'
    ],
    redFlagUrl: 'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
    color: '#BE123C'
  },

  // ── Comparison shopping (low premium-buyer intent) ──────────────────────
  {
    id: 'comparison_shopping',
    label: 'Comparison Shopping & Discount Hunting',
    description: 'Searches with primarily price-sensitive intent.',
    fix: 'For premium B2B accounts, most of these are non-buyers. Add as negatives or move to a heavily filtered campaign with strict bid limits.',
    triggers: [
      'cheap', 'cheapest', 'discount', 'discounted', 'coupon', 'coupons',
      'promo code', 'voucher', 'deal', 'deals', 'sale', 'on sale',
      'lowest price', 'best price', 'affordable', 'budget',
      'wholesale', 'bulk discount', 'clearance'
    ],
    redFlagUrl: 'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
    color: '#92400E'
  },

  // ── B2C intent on B2B account ───────────────────────────────────────────
  // This category is conditionally enabled by CONFIG.BUYER_TYPE
  {
    id: 'b2c_intent',
    label: 'Consumer / B2C Intent',
    description: 'Personal/household searches, not business buyers.',
    fix: 'Add consumer intent terms as negatives at the account level. These rarely convert for B2B and pollute your audience signals.',
    triggers: [
      'for home use', 'personal use', 'home use', 'for kids',
      'for my house', 'household', 'residential', 'apartment',
      'near me', 'in my area'
    ],
    redFlagUrl: 'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
    color: '#86198F',
    onlyIfBuyerType: 'B2B'
  },

  // ── B2B intent on B2C account ───────────────────────────────────────────
  {
    id: 'b2b_intent',
    label: 'Business / B2B Intent',
    description: 'Bulk/wholesale/enterprise searches, not retail consumers.',
    fix: 'Add business intent terms as negatives if you only sell retail.',
    triggers: [
      'bulk', 'wholesale', 'distributor', 'enterprise', 'b2b',
      'corporate account', 'commercial', 'business account',
      'tax exempt', 'reseller', 'pallet'
    ],
    redFlagUrl: 'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
    color: '#86198F',
    onlyIfBuyerType: 'B2C'
  },

  // ── Catch-all ───────────────────────────────────────────────────────────
  {
    id: 'other',
    label: 'Uncategorized Zero-Conversion Spend',
    description: 'Does not match a specific waste pattern but still produced zero conversions at meaningful spend.',
    fix: 'Review each query individually. If there is no plausible path from that search to buying your product, add as a negative. Build a running list for your agency to review.',
    triggers: [],
    redFlagUrl: 'https://ppcredflags.com/self-audit/',
    color: '#6B7280'
  }

];

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: INDUSTRY BEHAVIOR
//   ─────────────────────────────────────────────────────────────────────────
//   Per-vertical settings that adjust how the report interprets data:
//
//     - aovTier (high / medium / low) controls whether the Expensive
//       Converters section runs. 'high' verticals (roofing, hvac, dental,
//       legal_pi, etc.) suppress it entirely; high-CPL converters in those
//       verticals are usually the BEST leads, not waste. 'medium' shows the
//       section with the standard "validate against job value" caveat.
//       'low' shows it as actual waste worth acting on.
//
//     - convRateFloor: below this account-wide conversion rate, the
//       tracking-sanity callout fires. Higher for verticals with naturally
//       high conv rates (home services 3%, dental/legal 4%); lower for B2B
//       SaaS and e-commerce where 1-1.5% is normal.
//
//     - serviceLineAmbiguous: chronic-offender queries containing any of
//       these substrings get rendered with a softer "review with the
//       business, could be a service you offer" note instead of being
//       treated as definite waste. A roofer who DOES do commercial work
//       shouldn't have "flat roof" auto-negative'd by their agency on the
//       script's advice.
//
//   To add a vertical: add an entry here, add an option to the form's
//   industry <select> on /audit-script/, and add a matching entry to the
//   form JS preset table in src/scripts/audit-script-builder.js.
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

var INDUSTRY_BEHAVIOR = {
  custom:      { aovTier: 'medium', convRateFloor: 0.020, serviceLineAmbiguous: [] },
  roofing:     { aovTier: 'high',   convRateFloor: 0.030, serviceLineAmbiguous: ['metal roof', 'tile roof', 'flat roof', 'commercial roofing', 'cedar shake', 'slate roof', 'rubber roof', 'tpo'] },
  lawncare:    { aovTier: 'medium', convRateFloor: 0.025, serviceLineAmbiguous: ['tree removal', 'landscape design', 'irrigation', 'hardscape', 'mosquito', 'snow removal', 'arborist'] },
  hvac:        { aovTier: 'high',   convRateFloor: 0.030, serviceLineAmbiguous: ['commercial hvac', 'industrial', 'geothermal', 'mini split', 'ductless', 'heat pump', 'boiler'] },
  plumbing:    { aovTier: 'high',   convRateFloor: 0.030, serviceLineAmbiguous: ['commercial plumbing', 'industrial', 'sewer line', 'water heater', 'gas line', 'septic'] },
  painting:    { aovTier: 'medium', convRateFloor: 0.025, serviceLineAmbiguous: ['commercial painting', 'industrial', 'cabinet refinishing', 'wallpaper'] },
  pestcontrol: { aovTier: 'medium', convRateFloor: 0.025, serviceLineAmbiguous: ['commercial pest', 'agricultural', 'termite', 'mosquito control', 'wildlife removal', 'bedbug'] },
  autorepair:  { aovTier: 'medium', convRateFloor: 0.025, serviceLineAmbiguous: ['transmission rebuild', 'body work', 'paint and body', 'diesel', 'fleet maintenance'] },
  dental:      { aovTier: 'high',   convRateFloor: 0.040, serviceLineAmbiguous: ['orthodontics', 'implants', 'oral surgery', 'pediatric', 'cosmetic dentistry', 'invisalign'] },
  cosmetic:    { aovTier: 'high',   convRateFloor: 0.020, serviceLineAmbiguous: ['rhinoplasty', 'liposuction', 'breast augmentation', 'facelift', 'tummy tuck'] },
  legal_pi:    { aovTier: 'high',   convRateFloor: 0.040, serviceLineAmbiguous: ['family law', 'criminal defense', 'estate planning', 'business law', 'immigration'] },
  realestate:  { aovTier: 'high',   convRateFloor: 0.025, serviceLineAmbiguous: ['commercial real estate', 'property management', 'investment property', 'rental property'] },
  b2b_saas:    { aovTier: 'medium', convRateFloor: 0.015, serviceLineAmbiguous: [] },
  b2b_leadgen: { aovTier: 'medium', convRateFloor: 0.020, serviceLineAmbiguous: [] },
  ecom:        { aovTier: 'low',    convRateFloor: 0.015, serviceLineAmbiguous: [] }
};

function getIndustryBehavior() {
  return INDUSTRY_BEHAVIOR[CONFIG.INDUSTRY] || INDUSTRY_BEHAVIOR.custom;
}

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   END: INDUSTRY BEHAVIOR
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════


// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: MAIN
//   Entry point. Google Ads runs this on your schedule.
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

function main() {
  try {
    Logger.log('PPC Red Flags Comprehensive Waste Report: starting');

    // ── Step 1: Pull account-level baseline stats (last 30 days) ──────────
    var accountStats = getAccountStats();
    Logger.log('Account 30d stats. Spend: ' + accountStats.spend +
               ', Conversions: ' + accountStats.conversions +
               ', Avg CPL: ' + (accountStats.avgCPL || 'N/A'));

    // ── Step 2: Pull search term data for both windows ────────────────────
    var terms7d  = getSearchTermData('LAST_7_DAYS');
    var terms30d = getSearchTermData('LAST_30_DAYS');
    Logger.log('Search terms. 7d: ' + terms7d.length + ', 30d: ' + terms30d.length);

    // ── Step 3: Bucket each window into brand / competitor / waste ────────
    var weekly  = bucketTerms(terms7d,  CONFIG.WEEKLY_SPEND_THRESHOLD);
    var monthly = bucketTerms(terms30d, CONFIG.CHRONIC_SPEND_THRESHOLD);
    Logger.log('Weekly waste candidates: ' + weekly.waste.length +
               ', Monthly waste candidates: ' + monthly.waste.length);

    // ── Step 4: Identify chronic offenders (in BOTH windows, zero conv) ──
    var chronicOffenders = identifyChronicOffenders(weekly.waste, monthly.waste);
    Logger.log('Chronic offenders identified: ' + chronicOffenders.length);

    // ── Step 5: Find expensive converters (CPL anomalies) ─────────────────
    // Compute the trimmed baseline first so the CPL anomaly check is not
    // circular against the outliers it flags.
    var baselineCPL = computeBaselineCPL(terms30d);
    Logger.log('Baseline CPL (trimmed): ' + baselineCPL + ', raw avg CPL: ' + accountStats.avgCPL);
    var expensiveConverters = identifyExpensiveConverters(terms30d, baselineCPL);
    Logger.log('Expensive converters identified: ' + expensiveConverters.length);

    // ── Step 6: Geographic leakage analysis ───────────────────────────────
    var geoLeakage = (CONFIG.TARGET_COUNTRIES && CONFIG.TARGET_COUNTRIES.length > 0)
      ? analyzeGeoLeakage()
      : null;
    if (geoLeakage) {
      Logger.log('Geo leakage: ' + formatCurrency(geoLeakage.totalLeakage) +
                 ' from ' + geoLeakage.outOfTarget.length + ' regions');
    }

    // ── Step 7: Categorize the weekly waste ───────────────────────────────
    var weeklyCategorized = categorizeWaste(weekly.waste);

    // ── Step 8: Trend analysis (this week vs. trailing avg) ───────────────
    var trend = calculateTrend(weekly.waste, monthly.waste);
    Logger.log('Trend direction: ' + trend.direction + ' (' + trend.changePercent + '% change)');

    // ── Step 9: Severity tier the findings ────────────────────────────────
    var severity = assignSeverity({
      chronicOffenders:    chronicOffenders,
      weeklyWaste:         weekly.waste,
      expensiveConverters: expensiveConverters,
      geoLeakage:          geoLeakage
    });
    Logger.log('Severity: ' + severity.level + ': ' + severity.summary);

    // ── Step 10: Calculate totals ─────────────────────────────────────────
    var totals = {
      weeklyWaste:        sumCost(weekly.waste),
      monthlyWaste:       sumCost(monthly.waste),
      chronicWaste:       sumCost(chronicOffenders),
      expensiveWaste:     sumExpensiveOverage(expensiveConverters, baselineCPL),
      geoWaste:           geoLeakage ? geoLeakage.totalLeakage : 0,
      brandWeekly:        sumCost(weekly.brand),
      brandMonthly:       sumCost(monthly.brand),
      competitorWeekly:   sumCost(weekly.competitor),
      totalIdentified:    0
    };
    // Exclude expensive-converter overage from the total for high-AOV verticals
    // (roofing, hvac, plumbing, dental, legal_pi, cosmetic, realestate). In those
    // verticals the flagged converters are usually the account's best leads, not
    // waste, so counting them toward "total flagged" would overstate the problem.
    var includeExpensiveInTotal = getIndustryBehavior().aovTier !== 'high';
    totals.totalIdentified = totals.weeklyWaste
      + (includeExpensiveInTotal ? totals.expensiveWaste : 0)
      + (totals.geoWaste || 0);

    // ── Step 11: Build and send email ─────────────────────────────────────
    var subject  = buildSubject(severity, totals);
    var htmlBody = buildEmailHTML({
      totals:              totals,
      severity:            severity,
      trend:               trend,
      accountStats:        accountStats,
      baselineCPL:         baselineCPL,
      chronicOffenders:    chronicOffenders,
      weeklyWaste:         weekly.waste,
      weeklyCategorized:   weeklyCategorized,
      expensiveConverters: expensiveConverters,
      geoLeakage:          geoLeakage,
      brandWeekly:         weekly.brand,
      competitorWeekly:    weekly.competitor
    });

    MailApp.sendEmail({
      to:       CONFIG.EMAIL_RECIPIENTS,
      subject:  subject,
      htmlBody: htmlBody
    });

    Logger.log('Report sent to: ' + CONFIG.EMAIL_RECIPIENTS);
    Logger.log('PPC Red Flags Comprehensive Waste Report: complete');

  } catch (e) {
    Logger.log('ERROR: ' + e.message);
    Logger.log('Stack: ' + e.stack);
    try {
      MailApp.sendEmail({
        to:      CONFIG.EMAIL_RECIPIENTS,
        subject: 'Weekly Waste Report ERROR: ' + CONFIG.ACCOUNT_LABEL,
        body:    'The PPC Red Flags weekly waste report encountered an error:\n\n' +
                 e.message + '\n\nStack:\n' + e.stack + '\n\n' +
                 'Check the script logs in Google Ads → Tools → Scripts for full details.'
      });
    } catch (mailErr) {
      Logger.log('Email error too: ' + mailErr.message);
    }
  }
}

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: DATA COLLECTION
//   Functions that pull raw data from the Google Ads reporting API.
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

// ── Account-level baseline stats from last 30 days ──────────────────────
// Uses the `customer` resource which aggregates all account metrics.
// Returned cost is in micros: divide by 1,000,000 for currency.
function getAccountStats() {
  var query =
    'SELECT metrics.cost_micros, ' +
    '       metrics.conversions, ' +
    '       metrics.clicks, ' +
    '       metrics.impressions ' +
    'FROM   customer ' +
    'WHERE  segments.date DURING LAST_30_DAYS';

  var iterator = AdsApp.search(query);

  var totalSpend = 0, totalConv = 0, totalClicks = 0, totalImpressions = 0;
  while (iterator.hasNext()) {
    var row = iterator.next();
    totalSpend       += (parseFloat(row.metrics.costMicros) || 0) / 1000000;
    totalConv        += parseFloat(row.metrics.conversions) || 0;
    totalClicks      += parseInt(row.metrics.clicks)        || 0;
    totalImpressions += parseInt(row.metrics.impressions)   || 0;
  }

  return {
    spend:        totalSpend,
    conversions:  totalConv,
    clicks:       totalClicks,
    impressions:  totalImpressions,
    avgCPL:       totalConv > 0       ? (totalSpend / totalConv)   : 0,
    avgCTR:       totalImpressions > 0 ? (totalClicks / totalImpressions) : 0,
    convRate:     totalClicks > 0     ? (totalConv / totalClicks)  : 0
  };
}

// ── Search terms for a given window ─────────────────────────────────────
// GAQL via AdsApp.search(). The older AWQL AdsApp.report() API was
// deprecated by Google for SEARCH_TERMS_PERFORMANCE_REPORT in 2024.
// In GAQL: fields use snake_case in the query, camelCase when reading
// off the result row. Cost is returned in micros (1/1,000,000 of currency).
//
// PMax granularity caveat:
// search_term_view returns per-query metrics for Search campaigns. For
// Performance Max campaigns, Google has historically returned bucketed
// "search category" data here rather than raw search terms with cost.
// Coverage has improved (PMax search-term insights launched in 2024-2025)
// but is still not at parity with Search. In practice, queries routing to
// a PMax campaign in this report may show partial data: zero clicks /
// cost on rows where Google declined to release the underlying detail.
// The script handles that gracefully (parseFloat fallbacks return 0). If
// you find PMax rows mostly blank in your real account, the script isn't
// broken. That's the API's current state. Run a manual check against
// the "Search categories" report inside the PMax campaign for the same
// window to fill in the gaps.
function getSearchTermData(dateRange) {
  var terms = [];

  var query =
    'SELECT search_term_view.search_term, ' +
    '       metrics.cost_micros, ' +
    '       metrics.conversions, ' +
    '       metrics.clicks, ' +
    '       metrics.impressions, ' +
    '       campaign.name, ' +
    '       ad_group.name, ' +
    '       segments.search_term_match_type ' +
    'FROM   search_term_view ' +
    'WHERE  segments.date DURING ' + dateRange;

  var iterator = AdsApp.search(query);

  while (iterator.hasNext()) {
    var row         = iterator.next();
    var cost        = (parseFloat(row.metrics.costMicros) || 0) / 1000000;
    var conversions = parseFloat(row.metrics.conversions) || 0;
    var clicks      = parseInt(row.metrics.clicks)        || 0;

    if (cost <= 0) continue;

    terms.push({
      query:        String(row.searchTermView.searchTerm).toLowerCase().trim(),
      queryDisplay: String(row.searchTermView.searchTerm),
      cost:         cost,
      conversions:  conversions,
      clicks:       clicks,
      impressions:  parseInt(row.metrics.impressions) || 0,
      campaign:     String(row.campaign.name),
      adGroup:      String(row.adGroup.name),
      matchType:    String(row.segments.searchTermMatchType || '')
    });
  }

  // Largest spenders first
  terms.sort(function(a, b) { return b.cost - a.cost; });
  return terms;
}

// ── ISO 2-letter code → Google Ads location criterion ID ───────────────
// Used by analyzeGeoLeakage() to translate the user's TARGET_COUNTRIES
// into the criterion IDs returned by the geographic_view report. Covers
// the ~50 most common markets. Add to this map if you target a country
// not listed here.
var ISO_TO_CRITERION = {
  'US': '2840', 'CA': '2124', 'GB': '2826', 'UK': '2826',
  'AU': '2036', 'NZ': '2554', 'DE': '2276', 'FR': '2250',
  'ES': '2724', 'IT': '2380', 'NL': '2528', 'SE': '2752',
  'JP': '2392', 'KR': '2410', 'SG': '2702', 'IN': '2356',
  'BR': '2076', 'MX': '2484', 'CN': '2156', 'PH': '2608',
  'PL': '2616', 'TR': '2792', 'NG': '2566', 'ZA': '2710',
  'EG': '2818', 'VN': '2704', 'ID': '2360', 'PK': '2586',
  'BD': '2050', 'RU': '2643', 'UA': '2804', 'IE': '2372',
  'DK': '2208', 'NO': '2578', 'FI': '2246', 'BE': '2056',
  'CH': '2756', 'AT': '2040', 'PT': '2620', 'GR': '2300',
  'IL': '2376', 'AE': '2784', 'SA': '2682', 'TH': '2764',
  'MY': '2458', 'HK': '2344', 'TW': '2158', 'CL': '2152',
  'AR': '2032', 'CO': '2170', 'PE': '2604'
};

// ── Geographic leakage: spend in countries you do not target ───────────
// GAQL `geographic_view` with `location_type = LOCATION_OF_PRESENCE`
// returns where the user PHYSICALLY was when they clicked, regardless
// of what location was targeted. This is the user-location data Google
// hides by default in the Locations UI tab.
function analyzeGeoLeakage() {
  // Translate user's 2-letter codes into the criterion IDs the API returns.
  var targetCriteria = {};
  var unmappedCodes = [];
  for (var i = 0; i < CONFIG.TARGET_COUNTRIES.length; i++) {
    var code2 = String(CONFIG.TARGET_COUNTRIES[i]).toUpperCase().trim();
    if (ISO_TO_CRITERION[code2]) {
      targetCriteria[ISO_TO_CRITERION[code2]] = true;
    } else {
      unmappedCodes.push(code2);
    }
  }
  if (unmappedCodes.length > 0) {
    Logger.log('Warning: country codes not in ISO_TO_CRITERION map (skipped): ' +
               unmappedCodes.join(', ') +
               '. Add them to the map at the top of the script if you target these markets.');
  }
  if (Object.keys(targetCriteria).length === 0) {
    Logger.log('No mappable target countries; skipping geo leakage analysis.');
    return null;
  }

  var query =
    'SELECT geographic_view.country_criterion_id, ' +
    '       metrics.cost_micros, ' +
    '       metrics.conversions, ' +
    '       metrics.clicks ' +
    'FROM   geographic_view ' +
    'WHERE  segments.date DURING LAST_30_DAYS ' +
    '  AND  geographic_view.location_type = "LOCATION_OF_PRESENCE"';

  var byCountry = {};
  try {
    var iterator = AdsApp.search(query);
    while (iterator.hasNext()) {
      var row         = iterator.next();
      var countryCode = String(row.geographicView.countryCriterionId);
      var cost        = (parseFloat(row.metrics.costMicros) || 0) / 1000000;
      var conv        = parseFloat(row.metrics.conversions) || 0;
      var clicks      = parseInt(row.metrics.clicks) || 0;

      if (cost <= 0) continue;

      if (!byCountry[countryCode]) {
        byCountry[countryCode] = {
          country: lookupCountryName(countryCode),
          code:    countryCode,
          cost:    0, conversions: 0, clicks: 0
        };
      }
      byCountry[countryCode].cost        += cost;
      byCountry[countryCode].conversions += conv;
      byCountry[countryCode].clicks      += clicks;
    }
  } catch (e) {
    Logger.log('Geo report failed (likely no geo data): ' + e.message);
    return null;
  }

  // Filter to OUT-OF-TARGET only: strict criterion-ID match, no substrings.
  var outOfTarget = [];
  var totalLeakage = 0;
  for (var code in byCountry) {
    if (!targetCriteria[code]) {
      outOfTarget.push(byCountry[code]);
      totalLeakage += byCountry[code].cost;
    }
  }

  outOfTarget.sort(function(a, b) { return b.cost - a.cost; });

  return {
    outOfTarget:  outOfTarget,
    totalLeakage: totalLeakage
  };
}

// Lightweight criterion-ID → country-name lookup for display only.
// Falls back to the code itself for anything unmapped.
function lookupCountryName(code) {
  var map = {
    '2840': 'United States',  '2124': 'Canada',         '2826': 'United Kingdom',
    '2484': 'Mexico',         '2076': 'Brazil',         '2356': 'India',
    '2608': 'Philippines',    '2392': 'Japan',          '2156': 'China',
    '2036': 'Australia',      '2276': 'Germany',        '2250': 'France',
    '2724': 'Spain',          '2380': 'Italy',          '2528': 'Netherlands',
    '2752': 'Sweden',         '2554': 'New Zealand',    '2410': 'South Korea',
    '2702': 'Singapore',      '2616': 'Poland',         '2792': 'Turkey',
    '2566': 'Nigeria',        '2710': 'South Africa',   '2818': 'Egypt',
    '2104': 'Myanmar',        '2704': 'Vietnam',        '2360': 'Indonesia',
    '2586': 'Pakistan',       '2050': 'Bangladesh',     '2643': 'Russia',
    '2804': 'Ukraine',        '2372': 'Ireland',        '2208': 'Denmark',
    '2578': 'Norway',         '2246': 'Finland',        '2056': 'Belgium',
    '2756': 'Switzerland',    '2040': 'Austria',        '2620': 'Portugal',
    '2300': 'Greece',         '2376': 'Israel',         '2784': 'United Arab Emirates',
    '2682': 'Saudi Arabia',   '2764': 'Thailand',       '2458': 'Malaysia',
    '2344': 'Hong Kong',      '2158': 'Taiwan',         '2152': 'Chile',
    '2032': 'Argentina',      '2170': 'Colombia',       '2604': 'Peru'
  };
  return map[code] || ('Country code ' + code);
}

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: ANALYSIS
//   Pure logic functions: categorize, score, identify patterns.
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

// ── Bucket terms into brand / competitor / waste ────────────────────────
function bucketTerms(terms, threshold) {
  var brand = [], competitor = [], waste = [];
  for (var i = 0; i < terms.length; i++) {
    var t = terms[i];
    if (isBrandQuery(t.query)) {
      brand.push(t);
    } else if (isCompetitorQuery(t.query)) {
      competitor.push(t);
    } else if (t.cost >= threshold && t.conversions === 0) {
      waste.push(t);
    }
  }
  return { brand: brand, competitor: competitor, waste: waste };
}

// ── Chronic offenders: queries appearing in BOTH 7d and 30d windows ─────
// These are queries that have been wasting money for 30+ days. The agency
// should have caught and added them as negatives. The fact that they are
// still showing up means nobody is doing the work.
function identifyChronicOffenders(weeklyWaste, monthlyWaste) {
  var weeklySet = {};
  for (var i = 0; i < weeklyWaste.length; i++) {
    weeklySet[weeklyWaste[i].query] = true;
  }

  var chronic = [];
  for (var j = 0; j < monthlyWaste.length; j++) {
    var t = monthlyWaste[j];
    if (weeklySet[t.query] && t.cost >= CONFIG.CHRONIC_SPEND_THRESHOLD) {
      // Use the 30-day cost: that is the cumulative damage
      chronic.push(t);
    }
  }

  chronic.sort(function(a, b) { return b.cost - a.cost; });
  return chronic;
}

// ── Expensive converters: queries WITH conversions but CPL way above baseline ─
// A different waste pattern. These queries "convert" so the agency reports
// them as performing. But their CPL is so far above the rest of the account
// that they are quietly dragging down overall efficiency.
//
// Compared against a TRIMMED baseline (top 20% of converters dropped before
// averaging), not the raw account-wide avg CPL. Using the raw average is
// circular because these same outliers inflate the number we are flagging
// against.
//
// Caveat for the reader: in high-AOV verticals (home services, enterprise
// B2B, capital equipment) a "high CPL" converting query is often the
// account's BEST lead, not waste. The renderer below makes this caveat
// explicit. A future industry-aware version of this script may suppress
// the section entirely for those verticals.
function identifyExpensiveConverters(terms, baselineCPL) {
  if (!baselineCPL || baselineCPL <= 0) return [];
  var threshold = baselineCPL * CONFIG.CPL_ANOMALY_MULTIPLIER;

  var expensive = [];
  for (var i = 0; i < terms.length; i++) {
    var t = terms[i];
    if (t.conversions > 0 && t.cost > 0) {
      // Skip brand/competitor (different signals)
      if (isBrandQuery(t.query) || isCompetitorQuery(t.query)) continue;

      var queryCPL = t.cost / t.conversions;
      if (queryCPL > threshold) {
        expensive.push({
          query:        t.query,
          queryDisplay: t.queryDisplay,
          cost:         t.cost,
          conversions:  t.conversions,
          clicks:       t.clicks,
          campaign:     t.campaign,
          cpl:          queryCPL,
          overage:      Math.max(0, t.cost - (t.conversions * baselineCPL)),
          multipleOfBaseline: queryCPL / baselineCPL
        });
      }
    }
  }

  // Largest CPL multiples first: biggest deviation from baseline
  expensive.sort(function(a, b) { return b.multipleOfBaseline - a.multipleOfBaseline; });
  return expensive;
}

// ── Trend direction: this week vs. trailing 4-week avg ───────────────────
function calculateTrend(weeklyWaste, monthlyWaste) {
  var weeklyTotal  = sumCost(weeklyWaste);
  var monthlyTotal = sumCost(monthlyWaste);
  var weeklyAvg    = monthlyTotal / 4.3;  // ~4.3 weeks per month

  if (weeklyAvg < 1) {
    return { direction: 'baseline', changePercent: 0, weeklyAvg: weeklyAvg, weeklyActual: weeklyTotal };
  }

  var changePercent = ((weeklyTotal - weeklyAvg) / weeklyAvg) * 100;
  var direction;
  if (changePercent > 15)       direction = 'worsening';
  else if (changePercent < -15) direction = 'improving';
  else                          direction = 'stable';

  return {
    direction:     direction,
    changePercent: changePercent.toFixed(1),
    weeklyAvg:     weeklyAvg,
    weeklyActual:  weeklyTotal
  };
}

// ── Categorize each wasted term into a waste category ───────────────────
function categorizeWaste(wastedTerms) {
  var result = {};

  // Initialize all categories as empty buckets
  for (var c = 0; c < WASTE_CATEGORIES.length; c++) {
    var cat = WASTE_CATEGORIES[c];
    if (!shouldUseCategory(cat)) continue;
    result[cat.id] = { category: cat, terms: [], total: 0 };
  }

  // Add custom "products not sold" category if user configured it
  if (CONFIG.PRODUCTS_NOT_SOLD && CONFIG.PRODUCTS_NOT_SOLD.length > 0) {
    result['products_not_sold'] = {
      category: {
        id:          'products_not_sold',
        label:       'Products You Do Not Sell',
        description: 'Search terms for product categories outside your catalog.',
        fix:         'These are pure broad/phrase match overreach. Add as exact-match negatives at the account level. Each adjacent product category you do not serve should have its own negative cluster.',
        triggers:    CONFIG.PRODUCTS_NOT_SOLD,
        redFlagUrl:  'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
        color:       '#A21CAF'
      },
      terms: [],
      total: 0
    };
  }

  // Add user-defined "low-value terms" category if configured. These are
  // generic intent signals (cheap, free, diy, youtube, reddit, etc.) the
  // user marked as low-value for their specific business. Checked AFTER
  // products_not_sold but BEFORE the standard categories. The user said
  // these are low-value for them specifically, so attribute them to the
  // user's bucket rather than the generic "diy_intent" / "comparison" ones.
  if (CONFIG.LOW_VALUE_TERMS && CONFIG.LOW_VALUE_TERMS.length > 0) {
    result['low_value_terms'] = {
      category: {
        id:          'low_value_terms',
        label:       'Low-Value Intent (your terms)',
        description: 'Search terms containing words you marked as low-value indicators for your business.',
        fix:         'Add as exact-match negatives at the account level. These are intent words you specifically flagged as signaling a low-quality lead for your specific business. That judgment should override the script\'s generic category guesses.',
        triggers:    CONFIG.LOW_VALUE_TERMS,
        redFlagUrl:  'https://ppcredflags.com/red-flags/broad-match-no-negatives/',
        color:       '#0F766E'
      },
      terms: [],
      total: 0
    };
  }

  // Assign each term to the FIRST matching category
  for (var i = 0; i < wastedTerms.length; i++) {
    var term = wastedTerms[i];
    var matched = false;

    // Check products_not_sold first (most specific to user)
    if (result['products_not_sold']) {
      var productTriggers = result['products_not_sold'].category.triggers;
      for (var p = 0; p < productTriggers.length; p++) {
        if (term.query.indexOf(String(productTriggers[p]).toLowerCase()) !== -1) {
          result['products_not_sold'].terms.push(term);
          result['products_not_sold'].total += term.cost;
          matched = true;
          break;
        }
      }
    }
    if (matched) continue;

    // Then user-defined low-value terms
    if (result['low_value_terms']) {
      var lvTriggers = result['low_value_terms'].category.triggers;
      for (var lv = 0; lv < lvTriggers.length; lv++) {
        if (term.query.indexOf(String(lvTriggers[lv]).toLowerCase()) !== -1) {
          result['low_value_terms'].terms.push(term);
          result['low_value_terms'].total += term.cost;
          matched = true;
          break;
        }
      }
    }
    if (matched) continue;

    // Then standard categories (in defined order)
    for (var k = 0; k < WASTE_CATEGORIES.length; k++) {
      var sCat = WASTE_CATEGORIES[k];
      if (!result[sCat.id])           continue;
      if (sCat.id === 'other')        continue;
      if (sCat.triggers.length === 0) continue;

      for (var t = 0; t < sCat.triggers.length; t++) {
        if (term.query.indexOf(String(sCat.triggers[t]).toLowerCase()) !== -1) {
          result[sCat.id].terms.push(term);
          result[sCat.id].total += term.cost;
          matched = true;
          break;
        }
      }
      if (matched) break;
    }

    // Fallback: 'other'
    if (!matched && result['other']) {
      result['other'].terms.push(term);
      result['other'].total += term.cost;
    }
  }

  return result;
}

// Should we use this category given current CONFIG?
function shouldUseCategory(category) {
  if (!category.onlyIfBuyerType) return true;
  return CONFIG.BUYER_TYPE === category.onlyIfBuyerType;
}

// ── Severity tiering ────────────────────────────────────────────────────
// CRITICAL: chronic offenders detected AND chronic spend > $100
// HIGH:     significant weekly waste (>$100) OR meaningful expensive converters OR geo leakage > $50
// MEDIUM:   any weekly waste flagged
// LOW:      no meaningful waste
function assignSeverity(findings) {
  var hasChronic     = findings.chronicOffenders.length > 0;
  var weeklyTotal    = sumCost(findings.weeklyWaste);
  var hasExpensive   = findings.expensiveConverters.length >= 3;
  var hasGeoLeakage  = findings.geoLeakage && findings.geoLeakage.totalLeakage > 50;

  if (hasChronic && sumCost(findings.chronicOffenders) > 100) {
    return {
      level:   'CRITICAL',
      color:   '#B91C1C',
      summary: findings.chronicOffenders.length + ' chronic offenders draining ' +
               formatCurrency(sumCost(findings.chronicOffenders)) + ' over the last 30 days'
    };
  }

  if (hasChronic || weeklyTotal > 100 || hasExpensive || hasGeoLeakage) {
    var details = [];
    if (hasChronic)        details.push(findings.chronicOffenders.length + ' chronic offenders');
    if (weeklyTotal > 100) details.push(formatCurrency(weeklyTotal) + ' weekly waste');
    if (hasExpensive)      details.push(findings.expensiveConverters.length + ' expensive converters');
    if (hasGeoLeakage)     details.push(formatCurrency(findings.geoLeakage.totalLeakage) + ' geo leakage');
    return {
      level:   'HIGH',
      color:   '#C2410C',
      summary: details.join(' · ')
    };
  }

  if (weeklyTotal > 0) {
    return {
      level:   'MEDIUM',
      color:   '#A16207',
      summary: formatCurrency(weeklyTotal) + ' in flagged waste this week'
    };
  }

  return {
    level:   'LOW',
    color:   '#15803D',
    summary: 'No significant waste detected this week'
  };
}

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: HELPERS: brand/competitor matching, sums
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

function isBrandQuery(query) {
  query = String(query).toLowerCase();
  for (var i = 0; i < CONFIG.BRAND_TERMS.length; i++) {
    var term = String(CONFIG.BRAND_TERMS[i]).toLowerCase().trim();
    if (term && query.indexOf(term) !== -1) return true;
  }
  return false;
}

function isCompetitorQuery(query) {
  query = String(query).toLowerCase();
  for (var i = 0; i < CONFIG.COMPETITOR_TERMS.length; i++) {
    var term = String(CONFIG.COMPETITOR_TERMS[i]).toLowerCase().trim();
    if (term && query.indexOf(term) !== -1) return true;
  }
  return false;
}

function sumCost(arr) {
  var total = 0;
  for (var i = 0; i < arr.length; i++) {
    total += (arr[i].cost || 0);
  }
  return total;
}

// ── Baseline CPL: trimmed mean, robust against the outliers we flag ──
// Among converting non-brand/non-competitor terms, drop the top 20% by
// per-query CPL, then compute the conversion-weighted average of the rest.
// This gives a defensible "expected CPL for a typical converting query"
// that does NOT include the outliers it will be compared against. Using
// the raw account-wide avg CPL is circular; the outliers inflate the
// number they are then flagged against.
function computeBaselineCPL(terms) {
  var converting = [];
  for (var i = 0; i < terms.length; i++) {
    var t = terms[i];
    if (t.conversions > 0 && t.cost > 0) {
      if (isBrandQuery(t.query) || isCompetitorQuery(t.query)) continue;
      converting.push({
        cpl:  t.cost / t.conversions,
        cost: t.cost,
        conv: t.conversions
      });
    }
  }
  if (converting.length === 0) return 0;

  function weightedMean(items) {
    var c = 0, n = 0;
    for (var j = 0; j < items.length; j++) {
      c += items[j].cost;
      n += items[j].conv;
    }
    return n > 0 ? c / n : 0;
  }

  // Too few converting terms to trim meaningfully; just weighted-average
  if (converting.length < 5) return weightedMean(converting);

  converting.sort(function(a, b) { return a.cpl - b.cpl; });
  var trimCount = Math.max(1, Math.floor(converting.length * 0.2));
  var trimmed   = converting.slice(0, converting.length - trimCount);
  return weightedMean(trimmed);
}

function sumExpensiveOverage(expensive, baselineCPL) {
  // Excess spend = actual cost minus what cost would have been at baseline
  if (!baselineCPL) return 0;
  var overage = 0;
  for (var i = 0; i < expensive.length; i++) {
    var expectedCost = expensive[i].conversions * baselineCPL;
    overage += Math.max(0, expensive[i].cost - expectedCost);
  }
  return overage;
}

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: EMAIL BUILDER
//   ─────────────────────────────────────────────────────────────────────────
//   Brand palette (ppcredflags.com):
//     Ink:        #1A1A1A      Border:     #E8E6DF
//     Cream:      #FAFAF7      Muted txt:  #6E6A60
//     Warm gray:  #F0EEE9      Faint txt:  #9D998E
//     Rust dark:  #C2410C      Rust light: #D6541E
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

function buildSubject(severity, totals) {
  var prefix = '';
  if (severity.level === 'CRITICAL')  prefix = '[CRITICAL] ';
  else if (severity.level === 'HIGH') prefix = '[HIGH] ';

  if (totals.totalIdentified < 1) {
    return prefix + 'Weekly Waste Report: clean week, ' + CONFIG.ACCOUNT_LABEL;
  }
  return prefix + 'Weekly Waste Report: ' + formatCurrency(totals.totalIdentified) +
         ' flagged, ' + CONFIG.ACCOUNT_LABEL;
}

function buildEmailHTML(d) {
  var html = '';

  html += '<!DOCTYPE html><html><head><meta charset="utf-8">';
  html += '<meta name="viewport" content="width=device-width,initial-scale=1"></head>';
  html += '<body style="margin:0;padding:0;background:#F0EEE9;font-family:Arial,sans-serif;">';
  html += '<table width="100%" cellpadding="0" cellspacing="0" style="background:#F0EEE9;padding:28px 0;">';
  html += '<tr><td align="center">';
  html += '<table width="640" cellpadding="0" cellspacing="0" style="max-width:640px;width:100%;background:#FAFAF7;border-radius:6px;overflow:hidden;">';

  html += renderHeader();
  html += renderAccountStrip();
  html += renderSeverityBanner(d.severity, d.totals, d.trend);

  // Where the $X total comes from: line-item reconciliation so the math
  // is transparent. Suppressed when nothing was flagged.
  html += renderTotalReconciliation(d.totals, d.chronicOffenders.length > 0);

  // Tracking sanity check: fires when the account-wide conv rate is low
  // enough to be suspect. Placed before everything else so the reader is
  // warned to verify tracking before treating the rest as findings.
  html += renderTrackingCallout(d.accountStats);

  // Show chronic offenders FIRST if they exist; most important finding
  if (d.chronicOffenders.length > 0) {
    html += renderChronicOffenders(d.chronicOffenders);
  }

  // Show clean-week message and skip everything else if truly nothing
  if (d.totals.totalIdentified < 1 && d.chronicOffenders.length === 0) {
    html += renderCleanWeek();
  } else {
    if (d.weeklyWaste.length > 0) {
      html += renderWeeklyWaste(d.weeklyWaste, d.weeklyCategorized, d.totals);
    }
    if (d.expensiveConverters.length > 0) {
      html += renderExpensiveConverters(d.expensiveConverters, d.baselineCPL, d.accountStats);
    }
    if (d.geoLeakage && d.geoLeakage.outOfTarget.length > 0) {
      html += renderGeoLeakage(d.geoLeakage);
    }
  }

  html += renderTrendSection(d.trend);
  html += renderAccountStats(d.accountStats, d.totals);

  if (d.brandWeekly.length > 0)      html += renderBrandNote(d.brandWeekly);
  if (d.competitorWeekly.length > 0) html += renderCompetitorNote(d.competitorWeekly);

  html += renderRecommendations(d.weeklyCategorized, d.chronicOffenders.length, d.geoLeakage);
  html += renderCTA();
  html += renderFooter();

  html += '</table></td></tr></table></body></html>';
  return html;
}

// ── Header bar ──────────────────────────────────────────────────────────
function renderHeader() {
  var h = '<tr><td style="background:#1A1A1A;padding:20px 28px;">';
  h += '<table width="100%" cellpadding="0" cellspacing="0"><tr>';
  h += '<td>';
  h += '<span style="font-family:Courier,monospace;font-size:11px;font-weight:bold;color:#C2410C;letter-spacing:3px;">PPC</span>';
  h += '&nbsp;';
  h += '<span style="font-family:Arial,sans-serif;font-size:22px;font-weight:bold;color:#D6541E;letter-spacing:-1px;">RED FLAGS</span>';
  h += '</td>';
  h += '<td align="right">';
  h += '<span style="font-family:Courier,monospace;font-size:10px;color:#6E6A60;letter-spacing:1.5px;">COMPREHENSIVE WASTE AUDIT</span>';
  h += '</td>';
  h += '</tr></table></td></tr>';
  return h;
}

// ── Account + date strip ────────────────────────────────────────────────
function renderAccountStrip() {
  var h = '<tr><td style="background:#2E2C26;padding:12px 28px;">';
  h += '<table width="100%" cellpadding="0" cellspacing="0"><tr>';
  h += '<td><span style="font-family:Arial,sans-serif;font-size:12px;font-weight:bold;color:#C9C5BA;">' + escapeHTML(CONFIG.ACCOUNT_LABEL) + '</span></td>';
  h += '<td align="right"><span style="font-family:Courier,monospace;font-size:11px;color:#6E6A60;">' + getReportDate() + '</span></td>';
  h += '</tr></table></td></tr>';
  return h;
}

// ── Severity banner ─────────────────────────────────────────────────────
function renderSeverityBanner(severity, totals, trend) {
  var trendArrow = '';
  if (trend.direction === 'worsening')      trendArrow = '&uarr;';
  else if (trend.direction === 'improving') trendArrow = '&darr;';
  else if (trend.direction === 'stable')    trendArrow = '&rarr;';

  var h = '<tr><td style="background:' + severity.color + ';padding:18px 28px;">';
  h += '<table width="100%" cellpadding="0" cellspacing="0"><tr>';
  h += '<td>';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#FFFFFF;letter-spacing:3px;text-transform:uppercase;opacity:0.85;">SEVERITY</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:24px;font-weight:bold;color:#FFFFFF;letter-spacing:-1px;margin-top:2px;">' + severity.level + '</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#FFFFFF;opacity:0.9;margin-top:6px;line-height:1.5;">' + escapeHTML(severity.summary) + '</div>';
  h += '</td>';
  h += '<td align="right" style="vertical-align:top;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#FFFFFF;letter-spacing:2px;text-transform:uppercase;opacity:0.85;">TOTAL FLAGGED</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:30px;font-weight:bold;color:#FFFFFF;letter-spacing:-1px;margin-top:2px;">' + formatCurrency(totals.totalIdentified) + '</div>';
  if (trendArrow) {
    h += '<div style="font-family:Courier,monospace;font-size:11px;color:#FFFFFF;opacity:0.85;margin-top:6px;">';
    h += trendArrow + ' ' + (trend.direction === 'baseline' ? 'establishing baseline' : trend.direction);
    h += '</div>';
  }
  h += '</td></tr></table>';
  h += '</td></tr>';
  return h;
}

// ── Total reconciliation panel ─────────────────────────────────────────
// Right under the severity banner, lists each line item that rolls up
// into "Total flagged" so the math is obvious. Without this, readers
// (especially anyone with finance instincts) bounce off the unsourced
// big number. Suppressed when nothing was flagged.
function renderTotalReconciliation(totals, hasChronic) {
  if (totals.totalIdentified < 1) return '';

  // Same rule as totals.totalIdentified in main(): high-AOV verticals exclude
  // the expensive-converter overage from the total (those rows are usually
  // the best leads, not waste), so we drop the line item too; otherwise the
  // reconciliation would not add up to the total.
  var isHighAov = getIndustryBehavior().aovTier === 'high';

  var lines = [];
  if (totals.weeklyWaste > 0) {
    lines.push({ label: '7-day zero-conversion waste',                                        amount: totals.weeklyWaste });
  }
  if (totals.expensiveWaste > 0 && !isHighAov) {
    lines.push({ label: '30-day expensive-converter overage (vs. baseline CPL)',              amount: totals.expensiveWaste });
  }
  if (totals.geoWaste > 0) {
    lines.push({ label: '30-day geographic leakage',                                          amount: totals.geoWaste });
  }
  if (lines.length === 0) return '';

  var h = '<tr><td style="padding:18px 28px;background:#F8F7F3;border-bottom:1px solid #E8E6DF;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#6E6A60;letter-spacing:2px;text-transform:uppercase;margin-bottom:10px;">WHAT IS IN THE TOTAL</div>';
  h += '<table width="100%" cellpadding="0" cellspacing="0" style="font-family:Courier,monospace;font-size:12px;color:#1A1A1A;">';
  for (var i = 0; i < lines.length; i++) {
    h += '<tr>';
    h += '<td style="padding:3px 0;">' + escapeHTML(lines[i].label) + '</td>';
    h += '<td style="padding:3px 0;text-align:right;font-weight:bold;">' + formatCurrency(lines[i].amount) + '</td>';
    h += '</tr>';
  }
  h += '<tr><td colspan="2" style="padding:6px 0 0;"><div style="border-top:1px solid #C9C5BA;height:1px;"></div></td></tr>';
  h += '<tr>';
  h += '<td style="padding:6px 0 0;font-weight:bold;">Total flagged</td>';
  h += '<td style="padding:6px 0 0;text-align:right;font-weight:bold;color:#C2410C;">' + formatCurrency(totals.totalIdentified) + '</td>';
  h += '</tr>';
  h += '</table>';

  if (hasChronic) {
    h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:11px;color:#6E6A60;margin-top:10px;line-height:1.5;">';
    h += 'Chronic-offender 30-day cumulative damage is shown separately below as a recurring-pattern signal. It is not added into this total, to avoid double-counting (chronic queries that also spent at threshold this week are already in the 7-day line above).';
    h += '</div>';
  }

  h += '</td></tr>';
  return h;
}

// ── Tracking sanity check ──────────────────────────────────────────────
// Fires when the account-wide conv rate is below the vertical's expected
// floor. A 1% rate is suspect for a home-services account; for B2B SaaS or
// e-commerce it can be normal. The threshold comes from the industry preset
// (INDUSTRY_BEHAVIOR.convRateFloor: 3% for home services, 4% for dental,
// 1.5% for SaaS / ecom, 2% custom default). Naming a tracking concern up
// front earns more trust than the waste list itself; if the tracking is
// broken, the "zero-conversion waste" findings rest on bad data.
function renderTrackingCallout(stats) {
  if (!stats || stats.clicks < 100) return '';
  var floor = getIndustryBehavior().convRateFloor;
  if (stats.convRate >= floor) return '';

  var convRatePct = (stats.convRate * 100).toFixed(2);
  var h = '<tr><td style="padding:24px 28px;background:#FFFBEB;border-bottom:2px solid #FCD34D;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#92400E;letter-spacing:2px;text-transform:uppercase;margin-bottom:6px;">CHECK YOUR TRACKING FIRST &middot; LOW CONVERSION RATE</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:18px;font-weight:bold;color:#92400E;letter-spacing:-0.5px;margin-bottom:10px;">Your account-wide conversion rate is ' + convRatePct + '%. That is low enough to be suspect on its own.</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#78350F;line-height:1.65;">';
  h += 'The most common cause of a number this low is not bad traffic. It is incomplete conversion tracking. Phone calls not tied to clicks. Form-fills firing the wrong event, or no event at all. Offline conversions not uploaded. GA goals not mirrored as Google Ads primary conversions. ';
  h += 'Every "zero-conversion waste" finding below assumes the conversions Google Ads sees are the conversions that actually happened. If half your real leads come through the phone and are not being tracked, half of what is flagged below may actually be working.';
  h += '</div>';
  h += '<div style="font-family:Georgia,serif;font-size:13px;color:#78350F;line-height:1.65;margin-top:12px;">';
  h += '<strong>Verify before acting on the rest of this report:</strong>';
  h += '<ul style="margin:6px 0 0;padding-left:22px;">';
  h += '<li>Which conversion actions are marked <em>Primary</em> in Google Ads?</li>';
  h += '<li>Are phone calls tracked (call extensions, call-from-website, GTM telephone-link events)?</li>';
  h += '<li>Are form submissions firing the right event, including any thank-you-page reloads?</li>';
  h += '<li>Are offline conversions being uploaded for sales / qualified-lead events?</li>';
  h += '<li>Do Google Ads conversion totals match GA4 totals over the same date range?</li>';
  h += '</ul>';
  h += '</div>';
  h += '</td></tr>';
  return h;
}

// ── Split chronic offenders by service-line ambiguity ──────────────────
// For verticals where some terms could legitimately be service lines the
// business offers (e.g. a roofer might do "commercial roofing", where adding
// it as a negative would be a mistake), separate them out from the clear
// waste. The industry preset's serviceLineAmbiguous list drives this.
function splitChronicByAmbiguity(chronic) {
  var ambiguous = (getIndustryBehavior().serviceLineAmbiguous || []);
  if (ambiguous.length === 0) {
    return { clear: chronic, review: [] };
  }
  var clear = [], review = [];
  for (var i = 0; i < chronic.length; i++) {
    var t = chronic[i];
    var hit = false;
    for (var j = 0; j < ambiguous.length; j++) {
      if (t.query.indexOf(String(ambiguous[j]).toLowerCase()) !== -1) {
        hit = true;
        break;
      }
    }
    if (hit) review.push(t); else clear.push(t);
  }
  return { clear: clear, review: review };
}

// ── Chronic offenders (the most important section) ──────────────────────
function renderChronicOffenders(chronic) {
  var split = splitChronicByAmbiguity(chronic);
  var totalClear  = sumCost(split.clear);
  var totalReview = sumCost(split.review);
  var totalAll    = totalClear + totalReview;

  var h = '<tr><td style="background:#FEF2F2;border-bottom:2px solid #FCA5A5;padding:24px 28px;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#7F1D1D;letter-spacing:2px;text-transform:uppercase;margin-bottom:6px;">CRITICAL &middot; CHRONIC OFFENDERS</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:20px;font-weight:bold;color:#7F1D1D;letter-spacing:-0.5px;margin-bottom:8px;">' + chronic.length + ' ' + (chronic.length === 1 ? 'query has' : 'queries have') + ' been wasting money for 30+ days.</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:14px;color:#7F1D1D;line-height:1.6;margin-bottom:18px;">';
  h += 'These queries showed up in your zero-conversion list this week AND in your zero-conversion list 30 days ago. ';
  h += 'Cumulative damage so far: <strong style="font-style:normal;">' + formatCurrency(totalAll) + '</strong>. ';
  h += 'Your agency has had a full month to add these as negatives or build campaigns around them. They have not.';
  h += '</div>';

  if (split.clear.length > 0) {
    if (split.review.length > 0) {
      // Sub-header only when there's also a review group below
      h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#7F1D1D;letter-spacing:2px;text-transform:uppercase;margin-bottom:6px;">CLEAR WASTE &middot; ' + formatCurrency(totalClear) + '</div>';
    }
    h += renderQueryTable(split.clear, CONFIG.MAX_ROWS_CHRONIC, '#7F1D1D', '30-DAY SPEND');
  }

  if (split.review.length > 0) {
    h += '<div style="margin-top:18px;font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#9A3412;letter-spacing:2px;text-transform:uppercase;margin-bottom:6px;">REVIEW WITH THE BUSINESS &middot; ' + formatCurrency(totalReview) + '</div>';
    h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#9A3412;line-height:1.6;margin-bottom:10px;padding:8px 12px;background:#FFEDD5;border-left:3px solid #9A3412;">';
    h += 'These chronic queries match a service line that businesses in your industry (<code style="font-family:Courier,monospace;font-style:normal;">' + escapeHTML(CONFIG.INDUSTRY) + '</code>) sometimes do offer. <strong style="font-style:normal;">Do not auto-negative these.</strong> Check first: if this is a service you offer, build a campaign around it instead of blocking it. If you don&rsquo;t offer it, then yes, negative away.';
    h += '</div>';
    h += renderQueryTable(split.review, CONFIG.MAX_ROWS_CHRONIC, '#9A3412', '30-DAY SPEND');
  }

  h += '</td></tr>';
  return h;
}

// ── Weekly waste section ────────────────────────────────────────────────
function renderWeeklyWaste(weekly, categorized, totals) {
  var h = '<tr><td style="padding:24px 28px;border-bottom:1px solid #E8E6DF;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#6E6A60;letter-spacing:2px;text-transform:uppercase;margin-bottom:6px;">THIS WEEK &middot; ZERO-CONVERSION WASTE</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:18px;font-weight:bold;color:#1A1A1A;letter-spacing:-0.5px;margin-bottom:14px;">' + weekly.length + ' queries &middot; ' + formatCurrency(totals.weeklyWaste) + ' spent &middot; zero conversions</div>';

  // Category breakdown
  h += renderCategoryBreakdown(categorized);

  // Full table
  h += '<div style="margin-top:18px;">';
  h += renderQueryTable(weekly, CONFIG.MAX_ROWS_WEEKLY, '#C2410C', 'SPEND');
  h += '</div>';

  h += '</td></tr>';
  return h;
}

// ── Category breakdown bars ─────────────────────────────────────────────
function renderCategoryBreakdown(categorized) {
  var h = '';
  var keys = Object.keys(categorized).sort(function(a, b) {
    return categorized[b].total - categorized[a].total;
  });

  for (var i = 0; i < keys.length; i++) {
    var bucket = categorized[keys[i]];
    if (bucket.terms.length === 0) continue;

    var cat = bucket.category;
    h += '<table width="100%" cellpadding="0" cellspacing="0" style="margin-bottom:8px;">';
    h += '<tr><td style="border-left:3px solid ' + cat.color + ';padding:9px 13px;background:#F8F7F3;border-radius:0 4px 4px 0;">';
    h += '<table width="100%" cellpadding="0" cellspacing="0"><tr>';
    h += '<td>';
    h += '<div style="font-family:Arial,sans-serif;font-size:13px;font-weight:bold;color:#1A1A1A;">' + escapeHTML(cat.label) + '</div>';
    h += '<div style="font-family:Georgia,serif;font-size:11px;font-style:italic;color:#6E6A60;margin-top:2px;">' + escapeHTML(cat.description) + '</div>';
    h += '</td>';
    h += '<td align="right" style="padding-left:12px;white-space:nowrap;">';
    h += '<div style="font-family:Courier,monospace;font-size:16px;font-weight:bold;color:' + cat.color + ';">' + formatCurrency(bucket.total) + '</div>';
    h += '<div style="font-family:Courier,monospace;font-size:9px;color:#9D998E;">' + bucket.terms.length + ' ' + (bucket.terms.length === 1 ? 'query' : 'queries') + '</div>';
    h += '</td></tr></table>';
    h += '</td></tr></table>';
  }
  return h;
}

// ── Generic query table renderer ────────────────────────────────────────
function renderQueryTable(terms, maxRows, accentColor, costLabel) {
  var h = '<table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid #E8E6DF;border-radius:4px;overflow:hidden;margin-top:10px;">';

  h += '<tr style="background:#1A1A1A;">';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#C9C5BA;letter-spacing:2px;">SEARCH TERM</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#C9C5BA;letter-spacing:2px;text-align:right;white-space:nowrap;">' + costLabel + '</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#C9C5BA;letter-spacing:2px;text-align:right;">CLICKS</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#C9C5BA;letter-spacing:2px;">CAMPAIGN</td>';
  h += '</tr>';

  var displayTerms = terms.slice(0, maxRows);
  for (var i = 0; i < displayTerms.length; i++) {
    var row = displayTerms[i];
    var bg = i % 2 === 0 ? '#FAFAF7' : '#F4F2EE';

    h += '<tr style="background:' + bg + ';">';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#1A1A1A;word-break:break-word;">' + escapeHTML(row.queryDisplay) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:' + accentColor + ';font-weight:bold;text-align:right;white-space:nowrap;">' + formatCurrency(row.cost) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#6E6A60;text-align:right;">' + (row.clicks || 0) + '</td>';
    var camp = String(row.campaign || '');
    var campDisplay = camp.length > 26 ? camp.substring(0, 24) + '...' : camp;
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:10px;color:#9D998E;max-width:140px;">' + escapeHTML(campDisplay) + '</td>';
    h += '</tr>';
  }

  if (terms.length > maxRows) {
    var remaining = terms.length - maxRows;
    h += '<tr style="background:#F0EEE9;"><td colspan="4" style="padding:8px 12px;font-family:Courier,monospace;font-size:10px;color:#9D998E;text-align:center;">';
    h += '+ ' + remaining + ' more ' + (remaining === 1 ? 'query' : 'queries') + ' not shown &middot; ' + terms.length + ' total flagged';
    h += '</td></tr>';
  }

  h += '</table>';
  return h;
}

// ── Expensive converters ────────────────────────────────────────────────
// Queries that DO convert, but at a CPL multiples above the rest of the
// account. Compared against a trimmed-mean baseline (top 20% of converters
// dropped) so the comparison is not circular against the outliers it flags.
//
// IMPORTANT CAVEAT shown to the reader: in high-AOV verticals (home
// services, capital equipment, enterprise B2B) a "high CPL" converting
// query is often the account's BEST lead, not waste. The lede below says
// so plainly. The OVERAGE column shows what the spend would have been at
// baseline CPL, so the user can do the LTV math themselves.
function renderExpensiveConverters(expensive, baselineCPL, accountStats) {
  var isHighAov = getIndustryBehavior().aovTier === 'high';
  var h = '<tr><td style="padding:24px 28px;border-bottom:1px solid #E8E6DF;background:#FFF7ED;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#9A3412;letter-spacing:2px;text-transform:uppercase;margin-bottom:6px;">' +
    (isHighAov ? 'NOT IN THE TOTAL &middot; HIGH-CPL CONVERTERS (LIKELY YOUR BEST LEADS)' : 'VALIDATE AGAINST JOB VALUE &middot; HIGH-CPL CONVERTERS') +
    '</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:18px;font-weight:bold;color:#9A3412;letter-spacing:-0.5px;margin-bottom:10px;">' + expensive.length + ' queries with conversions at CPL ' + CONFIG.CPL_ANOMALY_MULTIPLIER + 'x+ above the rest of your account.</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#9A3412;line-height:1.65;margin-bottom:12px;">';
  h += 'Baseline CPL (a trimmed average of your converting non-brand queries, with the highest-CPL outliers excluded so the comparison is not circular): <strong style="font-style:normal;">' + formatCurrency(baselineCPL) + '</strong>. ';
  h += 'Raw account-wide average for comparison: ' + formatCurrency(accountStats.avgCPL) + '.';
  h += '</div>';

  if (isHighAov) {
    h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#1F3D1B;line-height:1.65;margin-bottom:14px;border-left:3px solid #2F4A2C;padding:8px 14px;background:#EEF1EA;">';
    h += '<strong style="font-style:normal;">These are listed but excluded from your total flagged amount.</strong> Your industry preset (<code style="font-family:Courier,monospace;font-style:normal;">' + escapeHTML(CONFIG.INDUSTRY) + '</code>) is a high-AOV vertical: storm damage, emergency repair, insurance-claim, or financing-intent queries here are usually the account&rsquo;s <em>best</em> leads, not waste. A $3,000 CPL on a $25,000 job is a 0.12 cost-of-lead ratio, which is excellent. The OVERAGE column still shows what each row spent above a baseline-CPL hypothetical, useful for LTV math, not a number to act on directly.';
    h += '</div>';
  } else {
    h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#9A3412;line-height:1.65;margin-bottom:14px;border-left:3px solid #9A3412;padding:6px 12px;background:#FFEDD5;">';
    h += '<strong style="font-style:normal;">Validate against job value.</strong> The OVERAGE column shows what each row spent above what an at-baseline CPL would have produced. For high-AOV businesses (home services, capital equipment, enterprise B2B) the highest-CPL converters are often your best leads. Do the LTV math against job value before treating these as waste.';
    h += '</div>';
  }

  // Custom expensive-converters table: adds OVERAGE column, renames "vs AVG" to "vs BASELINE"
  var displayExp = expensive.slice(0, CONFIG.MAX_ROWS_EXPENSIVE);
  h += '<table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid #FED7AA;border-radius:4px;overflow:hidden;">';
  h += '<tr style="background:#9A3412;">';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#FED7AA;letter-spacing:2px;">SEARCH TERM</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#FED7AA;letter-spacing:2px;text-align:right;">CPL</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#FED7AA;letter-spacing:2px;text-align:right;">vs BASELINE</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#FED7AA;letter-spacing:2px;text-align:right;">CONV</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#FED7AA;letter-spacing:2px;text-align:right;">SPEND</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#FED7AA;letter-spacing:2px;text-align:right;">OVERAGE</td>';
  h += '</tr>';

  for (var i = 0; i < displayExp.length; i++) {
    var ex = displayExp[i];
    var bg = i % 2 === 0 ? '#FFFBEB' : '#FEF3C7';
    h += '<tr style="background:' + bg + ';">';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#1A1A1A;word-break:break-word;">' + escapeHTML(ex.queryDisplay) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#9A3412;font-weight:bold;text-align:right;white-space:nowrap;">' + formatCurrency(ex.cpl) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#9A3412;text-align:right;font-weight:bold;">' + ex.multipleOfBaseline.toFixed(1) + 'x</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#6E6A60;text-align:right;">' + ex.conversions.toFixed(1) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#6E6A60;text-align:right;">' + formatCurrency(ex.cost) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#9A3412;font-weight:bold;text-align:right;white-space:nowrap;">' + formatCurrency(ex.overage) + '</td>';
    h += '</tr>';
  }

  if (expensive.length > CONFIG.MAX_ROWS_EXPENSIVE) {
    h += '<tr style="background:#FEF3C7;"><td colspan="6" style="padding:8px 12px;font-family:Courier,monospace;font-size:10px;color:#92400E;text-align:center;">+ ' + (expensive.length - CONFIG.MAX_ROWS_EXPENSIVE) + ' more &middot; ' + expensive.length + ' total</td></tr>';
  }

  h += '</table>';
  h += '</td></tr>';
  return h;
}

// ── Geographic leakage ──────────────────────────────────────────────────
function renderGeoLeakage(geo) {
  var h = '<tr><td style="padding:24px 28px;border-bottom:1px solid #E8E6DF;background:#F0F9FF;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#0C4A6E;letter-spacing:2px;text-transform:uppercase;margin-bottom:6px;">GEOGRAPHIC LEAKAGE &middot; LAST 30 DAYS</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:18px;font-weight:bold;color:#0C4A6E;letter-spacing:-0.5px;margin-bottom:8px;">' + formatCurrency(geo.totalLeakage) + ' spent on users outside your target countries.</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#0C4A6E;line-height:1.6;margin-bottom:14px;">';
  h += 'Your account targets ' + escapeHTML(CONFIG.TARGET_COUNTRIES.join(', ')) + ', but spent money in ' + geo.outOfTarget.length + ' other ';
  h += (geo.outOfTarget.length === 1 ? 'country' : 'countries') + '. ';
  h += 'This usually means location targeting is set to "Presence or interest" instead of "Presence." ';
  h += 'One setting change in Campaign Settings &rarr; Locations.';
  h += '</div>';

  h += '<table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid #BAE6FD;border-radius:4px;overflow:hidden;">';
  h += '<tr style="background:#0C4A6E;">';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#BAE6FD;letter-spacing:2px;">COUNTRY (USER LOCATION)</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#BAE6FD;letter-spacing:2px;text-align:right;">SPEND</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#BAE6FD;letter-spacing:2px;text-align:right;">CLICKS</td>';
  h += '<td style="padding:8px 10px;font-family:Courier,monospace;font-size:9px;font-weight:bold;color:#BAE6FD;letter-spacing:2px;text-align:right;">CONV</td>';
  h += '</tr>';

  var topGeo = geo.outOfTarget.slice(0, 12);
  for (var i = 0; i < topGeo.length; i++) {
    var g = topGeo[i];
    var bg = i % 2 === 0 ? '#F0F9FF' : '#E0F2FE';
    h += '<tr style="background:' + bg + ';">';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#1A1A1A;">' + escapeHTML(g.country) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#0C4A6E;font-weight:bold;text-align:right;">' + formatCurrency(g.cost) + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#6E6A60;text-align:right;">' + g.clicks + '</td>';
    h += '<td style="padding:7px 10px;font-family:Courier,monospace;font-size:11px;color:#6E6A60;text-align:right;">' + g.conversions.toFixed(1) + '</td>';
    h += '</tr>';
  }

  h += '</table>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:11px;color:#0C4A6E;margin-top:10px;line-height:1.5;">';
  h += 'Fix: Campaign settings &rarr; Locations &rarr; click each campaign\'s targeted location &rarr; "Location options" &rarr; ';
  h += 'change to "Presence: People in or regularly in your targeted locations." Test in audit mode first.';
  h += '</div>';
  h += '</td></tr>';
  return h;
}

// ── Trend section ───────────────────────────────────────────────────────
function renderTrendSection(trend) {
  if (trend.direction === 'baseline') return '';

  var label, color, icon;
  switch (trend.direction) {
    case 'worsening': label = 'Waste is increasing'; color = '#B91C1C'; icon = '&uarr;'; break;
    case 'improving': label = 'Waste is decreasing'; color = '#15803D'; icon = '&darr;'; break;
    default:          label = 'Waste is stable';     color = '#6E6A60'; icon = '&rarr;'; break;
  }

  var h = '<tr><td style="padding:18px 28px;border-bottom:1px solid #E8E6DF;background:#F8F7F3;">';
  h += '<table width="100%" cellpadding="0" cellspacing="0"><tr>';
  h += '<td>';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#6E6A60;letter-spacing:2px;text-transform:uppercase;margin-bottom:4px;">TREND vs. TRAILING 30-DAY AVG</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:15px;font-weight:bold;color:' + color + ';">' + icon + ' ' + label + '</div>';
  h += '<div style="font-family:Georgia,serif;font-size:12px;font-style:italic;color:#6E6A60;margin-top:2px;">';
  h += 'This week: ' + formatCurrency(trend.weeklyActual) + ' &middot; trailing weekly avg: ' + formatCurrency(trend.weeklyAvg);
  h += '</div>';
  h += '</td>';
  h += '<td align="right" style="vertical-align:middle;">';
  h += '<div style="font-family:Arial,sans-serif;font-size:24px;font-weight:bold;color:' + color + ';letter-spacing:-1px;">';
  var pct = parseFloat(trend.changePercent);
  h += (pct > 0 ? '+' : '') + trend.changePercent + '%';
  h += '</div>';
  h += '</td></tr></table>';
  h += '</td></tr>';
  return h;
}

// ── Account stats panel ─────────────────────────────────────────────────
function renderAccountStats(stats, totals) {
  var wastePctAccount = stats.spend > 0
    ? ((totals.totalIdentified / stats.spend) * 100).toFixed(1)
    : '0';

  var h = '<tr><td style="padding:20px 28px;border-bottom:1px solid #E8E6DF;background:#F0EEE9;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#6E6A60;letter-spacing:2px;text-transform:uppercase;margin-bottom:12px;">ACCOUNT CONTEXT &middot; LAST 30 DAYS</div>';
  h += '<table width="100%" cellpadding="0" cellspacing="0">';
  h += '<tr>';
  h += renderStatCell('Total Spend',  formatCurrency(stats.spend));
  h += renderStatCell('Conversions',  stats.conversions.toFixed(1));
  h += renderStatCell('Avg CPL',      stats.avgCPL > 0 ? formatCurrency(stats.avgCPL) : 'N/A');
  h += renderStatCell('Conv. Rate',   (stats.convRate * 100).toFixed(2) + '%');
  h += '</tr></table>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:12px;color:#6E6A60;margin-top:14px;line-height:1.5;border-top:1px dashed #C9C5BA;padding-top:10px;">';
  h += 'Total flagged waste this week represents <strong style="font-style:normal;color:#C2410C;">' + wastePctAccount + '%</strong> of your trailing 30-day spend. ';
  h += 'There is no industry benchmark worth quoting for &ldquo;normal&rdquo; waste. It depends too much on vertical, account maturity, and tracking quality. Watch the trend section above (week-over-trailing-average direction) rather than the absolute percentage.';
  h += '</div>';
  h += '</td></tr>';
  return h;
}

function renderStatCell(label, value) {
  var h = '<td width="25%" style="padding:0 8px 0 0;">';
  h += '<div style="font-family:Courier,monospace;font-size:9px;color:#9D998E;letter-spacing:1px;text-transform:uppercase;margin-bottom:4px;">' + label + '</div>';
  h += '<div style="font-family:Arial,sans-serif;font-size:18px;font-weight:bold;color:#1A1A1A;letter-spacing:-0.5px;">' + value + '</div>';
  h += '</td>';
  return h;
}

// ── Brand queries note ──────────────────────────────────────────────────
function renderBrandNote(brandTerms) {
  var total = sumCost(brandTerms);
  var h = '<tr><td style="padding:14px 28px;background:#FEF3C7;border-bottom:1px solid #FCD34D;">';
  h += '<div style="font-family:Arial,sans-serif;font-size:12px;font-weight:bold;color:#92400E;margin-bottom:4px;">';
  h += 'Brand queries this week: ' + formatCurrency(total) + ' &middot; ' + brandTerms.length + ' ' + (brandTerms.length === 1 ? 'query' : 'queries');
  h += '</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:12px;color:#78350F;line-height:1.6;">';
  h += 'Excluded from waste totals above. These are your brand searches, people who already knew you. ';
  h += 'If your agency reports these conversions as paid campaign performance, that is its own red flag. ';
  if (CONFIG.INCLUDE_RED_FLAG_LINKS) {
    h += '<a href="https://ppcredflags.com/red-flags/reporting-clicks-not-leads/" style="color:#92400E;font-weight:bold;font-style:normal;">Red Flag #1 &rarr;</a>';
  }
  h += '</div></td></tr>';
  return h;
}

// ── Competitor queries note ─────────────────────────────────────────────
function renderCompetitorNote(competitorTerms) {
  var total = sumCost(competitorTerms);
  var h = '<tr><td style="padding:14px 28px;background:#F0F9FF;border-bottom:1px solid #BAE6FD;">';
  h += '<div style="font-family:Arial,sans-serif;font-size:12px;font-weight:bold;color:#0C4A6E;margin-bottom:4px;">';
  h += 'Competitor queries this week: ' + formatCurrency(total) + ' &middot; ' + competitorTerms.length + ' ' + (competitorTerms.length === 1 ? 'query' : 'queries');
  h += '</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:12px;color:#075985;line-height:1.6;">';
  h += 'Could be intentional conquest targeting or unintentional broad-match overreach. Confirm which campaigns are sourcing them.';
  h += '</div></td></tr>';
  return h;
}

// ── Recommendations ─────────────────────────────────────────────────────
function renderRecommendations(categorized, chronicCount, geoLeakage) {
  var h = '<tr><td style="padding:24px 28px;border-bottom:1px solid #E8E6DF;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;font-weight:bold;color:#6E6A60;letter-spacing:2px;text-transform:uppercase;margin-bottom:14px;">WHAT TO ASK YOUR AGENCY</div>';

  if (chronicCount > 0) {
    h += '<div style="margin-bottom:14px;padding-left:12px;border-left:3px solid #B91C1C;">';
    h += '<div style="font-family:Arial,sans-serif;font-size:13px;font-weight:bold;color:#1A1A1A;margin-bottom:4px;">On the chronic offenders</div>';
    h += '<div style="font-family:Georgia,serif;font-size:12px;font-style:italic;color:#6E6A60;line-height:1.6;">';
    h += '"Why are these ' + chronicCount + ' specific queries still in our search-terms report after 30 days of zero conversions? When was the negative-keyword list last reviewed end-to-end?" ';
    h += 'A vague answer is itself the answer.';
    if (CONFIG.INCLUDE_RED_FLAG_LINKS) {
      h += ' <a href="https://ppcredflags.com/red-flags/broad-match-no-negatives/" style="color:#C2410C;text-decoration:none;font-weight:bold;">Red Flag #2 &rarr;</a>';
    }
    h += '</div></div>';
  }

  if (geoLeakage && geoLeakage.totalLeakage > 50) {
    h += '<div style="margin-bottom:14px;padding-left:12px;border-left:3px solid #0C4A6E;">';
    h += '<div style="font-family:Arial,sans-serif;font-size:13px;font-weight:bold;color:#1A1A1A;margin-bottom:4px;">On the geographic leakage</div>';
    h += '<div style="font-family:Georgia,serif;font-size:12px;font-style:italic;color:#6E6A60;line-height:1.6;">';
    h += '"Are our campaigns set to \'Presence\' or \'Presence or interest\' for location targeting? If \'Presence or interest,\' why?" ';
    h += 'There is rarely a good reason to target by interest for a regional or country-specific business.';
    h += '</div></div>';
  }

  // Per-category recommendations
  var keys = Object.keys(categorized).sort(function(a, b) {
    return categorized[b].total - categorized[a].total;
  });
  for (var i = 0; i < keys.length; i++) {
    var bucket = categorized[keys[i]];
    if (bucket.terms.length === 0) continue;
    var cat = bucket.category;

    h += '<div style="margin-bottom:14px;padding-left:12px;border-left:3px solid ' + cat.color + ';">';
    h += '<div style="font-family:Arial,sans-serif;font-size:13px;font-weight:bold;color:#1A1A1A;margin-bottom:4px;">' + escapeHTML(cat.label) + ' (' + formatCurrency(bucket.total) + ')</div>';
    h += '<div style="font-family:Georgia,serif;font-size:12px;font-style:italic;color:#6E6A60;line-height:1.6;">' + escapeHTML(cat.fix);
    if (CONFIG.INCLUDE_RED_FLAG_LINKS && cat.redFlagUrl) {
      h += ' <a href="' + cat.redFlagUrl + '" style="color:#C2410C;text-decoration:none;font-weight:bold;font-style:normal;">Read more &rarr;</a>';
    }
    h += '</div></div>';
  }

  // PMax caveat: applies to any term routing to a PMax campaign above.
  // Standard account-level negatives don't apply to PMax through the
  // normal UI. Surfacing this once at the bottom is enough; surfacing
  // it per-row would clutter the recommendations.
  h += '<div style="margin-top:18px;padding:10px 14px;background:#FEF3C7;border-left:3px solid #92400E;font-family:Georgia,serif;font-style:italic;font-size:12px;color:#78350F;line-height:1.65;">';
  h += '<strong style="font-style:normal;">Note on PMax campaigns.</strong> Standard account-level negative keywords do not apply to PMax through the normal Google Ads UI. For terms routing to a campaign labelled &ldquo;PMax&hellip;&rdquo; above, use one of: a shared negative-keyword list applied to PMax via Google support, brand exclusions configured in the PMax campaign settings, or pushing the term into a Search campaign where you can negative it directly. Adding it as a regular account-level negative will look correct but will not actually block it from PMax.';
  h += '</div>';

  h += '</td></tr>';
  return h;
}

// ── Clean-week message ──────────────────────────────────────────────────
function renderCleanWeek() {
  var h = '<tr><td style="padding:48px 28px;text-align:center;">';
  h += '<div style="font-size:40px;margin-bottom:12px;color:#15803D;">&#10003;</div>';
  h += '<h2 style="font-family:Arial,sans-serif;font-size:22px;font-weight:bold;color:#1A1A1A;margin:0 0 10px;">No flagged waste detected.</h2>';
  h += '<p style="font-family:Georgia,serif;font-style:italic;font-size:14px;color:#6E6A60;margin:0 auto;max-width:480px;line-height:1.7;">';
  h += 'No queries hit the threshold this week. No chronic offenders. No geographic leakage above your minimum. ';
  h += 'This script will keep running. If anything changes, you will know on the next Monday.';
  h += '</p></td></tr>';
  return h;
}

// ── CTA block ───────────────────────────────────────────────────────────
function renderCTA() {
  var h = '<tr><td style="background:#1A1A1A;padding:30px 28px;text-align:center;">';
  h += '<div style="font-family:Arial,sans-serif;font-size:18px;font-weight:bold;color:#FAFAF7;margin-bottom:10px;">Want someone to go through this with you?</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:13px;color:#9D998E;margin-bottom:20px;line-height:1.7;max-width:500px;margin-left:auto;margin-right:auto;">';
  h += 'This script flags the patterns. A full audit explains the structural reasons your agency has not caught them, ';
  h += 'and what the same patterns mean about everything else they are doing.';
  h += '</div>';
  h += '<a href="https://ppcredflags.com/free-audit/" style="display:inline-block;background:#C2410C;color:#FAFAF7;font-family:Arial,sans-serif;font-size:14px;font-weight:bold;padding:13px 28px;border-radius:4px;text-decoration:none;letter-spacing:-0.5px;">Book a free audit &rarr;</a>';
  h += '</td></tr>';
  return h;
}

// ── Footer ──────────────────────────────────────────────────────────────
function renderFooter() {
  var h = '<tr><td style="padding:18px 28px;text-align:center;background:#F0EEE9;">';
  h += '<div style="font-family:Courier,monospace;font-size:10px;color:#9D998E;line-height:1.7;">';
  h += 'Generated by a Google Ads Script from <a href="https://ppcredflags.com" style="color:#C2410C;text-decoration:none;font-weight:bold;">ppcredflags.com</a><br>';
  h += 'Reads your account data only. Does not change settings, bids, or campaigns.<br>';
  h += 'Forward this email to your agency. Their response is its own diagnostic.';
  h += '</div>';
  h += '<div style="font-family:Georgia,serif;font-style:italic;font-size:10px;color:#9D998E;line-height:1.6;margin-top:10px;border-top:1px dashed #C9C5BA;padding-top:10px;">';
  h += 'PMax data caveat: Performance Max campaigns return less granular search-term data than Search campaigns. If a PMax row above shows partial metrics, that is the Google Ads API&rsquo;s current behavior, not the script. Cross-reference with the &ldquo;Search categories&rdquo; insights inside the PMax campaign for the same window to fill any gaps.';
  h += '</div></td></tr>';
  return h;
}

// ════════════════════════════════════════════════════════════════════════════
// ███████████████████████████████████████████████████████████████████████████
//
//   START: UTILITIES
//
// ███████████████████████████████████████████████████████████████████████████
// ════════════════════════════════════════════════════════════════════════════

function formatCurrency(amount) {
  if (typeof amount !== 'number' || !isFinite(amount)) return CONFIG.CURRENCY_SYMBOL + '0.00';
  return CONFIG.CURRENCY_SYMBOL + amount.toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ',');
}

function getReportDate() {
  var months = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
  var today  = new Date();
  return months[today.getMonth()] + ' ' + today.getDate() + ', ' + today.getFullYear();
}

function escapeHTML(str) {
  if (str === null || str === undefined) return '';
  return String(str)
    .replace(/&/g,  '&amp;')
    .replace(/</g,  '&lt;')
    .replace(/>/g,  '&gt;')
    .replace(/"/g,  '&quot;')
    .replace(/'/g,  '&#39;');
}