Paid Performance Audit (v6.1)

Comprehensive paid-channel diagnostic for a CE. Python renders all BQ tables (including landing pages, keyword IS, campaign targeting). Claude writes narrative only.

Audience: Growth analyst + GM + Perf team
Scope: All paid channels. Non-paid issues detected and routed to /ce-audit.

When to Use

• CE shows paid revenue decline or stagnation
• Weekly review flags a "Losing Ground" CE
• Before adjusting bids/budgets/targeting for a CE
• GM asks "what's going on with paid for X?"

Usage

/perf-audit-v6 "Louvre Museum"
/perf-audit-v6 "Edge NYC"
/perf-audit-v6 "London Eye"

---

Report Structure

1. Executive Summary (status + causal story + $ impact — written LAST, appears first)
2. CE Overview (Table 1 — rendered by code)
3. Channel Breakdown (revenue metrics — rendered by code)
4. Paid Deep Dive (Table 2 Search+PMax+Bing + Landing Pages — rendered by code)
5. Coverage + Matchmaking (cohort table, budget table, geo table — rendered by code)
6. External Dynamics (demand, competition, money on table)
7. Funnel (via /cvr-rca — Shapley, device, experience, LY gap)
8. Search Intelligence (CSV-based if uploaded, cluster-first)
9. Red Flags Summary
10. Recommended Actions ($ sized, defensible)
A. Evidence Appendix (A1-A3 monthly trends, A6 budget detail, A7 targeting — rendered by code)
B. Data Sources

---

Execution Steps

Step 0 — Prompt for optional uploads (non-blocking)

Send this prompt before Step 1. Don't wait for response.

While I start pulling data, these optional inputs strengthen the analysis:

Auction Insights CSVs (adds competitor names + outranking share to Section 6b):

1. Google Ads → current account → select all <CE Name> campaigns → Auction insights tab → segment by Week → date range: last 8 weeks → Download → CSV
2. Switch to the pre-consolidation account → same CE campaigns → Auction insights → segment by Week → same 8 weeks last year → Download → CSV

Finding the LY account: Query BQ:
SELECT DISTINCT customer_id, account_name FROM google_ads_campaign_stats WHERE campaign_target_combined_entity_id = '<CE_ID>' AND report_date < '2026-02-01' LIMIT 1

Search Terms CSV (adds cluster analysis to Section 8):
Google Ads → Search terms report → filter to <CE Name> campaigns → last 4 weeks → Download CSV.
If not available, Section 8 will use keyword IS data only.

Identifying CSVs: File names are auto-numbered ("Auction insights report (12).csv") and meaningless. Read line 2 of each CSV — it contains the date range (e.g., "March 23, 2026 - April 19, 2026"). Match against L4W/P4W/LY date windows to identify CY vs LY.

Share file paths anytime during the audit.

BQ Quick Reference

When writing ad-hoc BQ queries for this audit, use these column names (NOT guessed names):

Table	Key columns
fct_orders	DATE(created_at) for date filter, combined_entity_id (STRING — use quotes: = '252'), channel_name, channel_grouping, campaign_name, amount_revenue_usd (net revenue, NOT order_value_completed_usd). No order_status filter needed.
google_ads_campaign_stats	report_date, sum_spend, count_clicks, count_impressions, campaign_target_combined_entity_id (STRING), campaign_name, campaign_status, current_campaign_budget, current_campaign_target_roas
google_ads_campaign_budget_stats	report_date, campaign_id, daily_budget
mixpanel_user_page_funnel_progression	event_date, combined_entity_id (STRING), page_url, user_id, has_select_page_viewed, has_checkout_started, has_order_completed

Step 1 — Resolve CE + compute dates

Resolve the CE name to an ID:

cd ~/analytics && python3 -c "
from google.cloud import bigquery
client = bigquery.Client(project='headout-analytics', location='EU')
rows = list(client.query(\"\"\"
    SELECT combined_entity_id, combined_entity_name, market
    FROM analytics_reporting.dim_combined_entities
    WHERE LOWER(combined_entity_name) LIKE LOWER('%<CE_NAME>%')
    LIMIT 5
\"\"\").result())
for r in rows: print(f'{r.combined_entity_id}: {r.combined_entity_name} ({r.market})')
"

Compute date windows (L4W = last 4 complete Mon-Sun weeks):

cd ~/analytics && python3 -c "
from datetime import date, timedelta
today = date.today()
end = today - timedelta(days=today.weekday() + 1)  # last Sunday
start = end - timedelta(days=27)  # 4 weeks back
ly_end = end - timedelta(days=364)
ly_start = start - timedelta(days=364)
p4w_end = start - timedelta(days=1)
p4w_start = p4w_end - timedelta(days=27)
print(f'L4W: {start} to {end}')
print(f'P4W: {p4w_start} to {p4w_end}')
print(f'LY:  {ly_start} to {ly_end}')
"

Step 2 — Run renderer (produces skeleton with all BQ tables)

cd ~/analytics && python3 scripts/perf_audit_v6.py render \
  --ce-id <CE_ID> --ce-name "<CE Name>" --market "<Market>" \
  --l4w-start <L4W_START> --l4w-end <L4W_END> \
  --p4w-start <P4W_START> --p4w-end <P4W_END> \
  --ly-start <LY_START> --ly-end <LY_END> \
  --output thoughts/shared/perf-audits/perf-audit-<slug>-<date>-v6.md

LP URL: Omit --lp-url on first run. After the skeleton renders, the Landing Pages table (Section 4) shows the top LP by clicks — use that URL. Backfill the header manually if needed. The LP URL is NOT in dim_combined_entities — it varies by CE (e.g., pompeii-tickets.com/ not headout.com/pompeii-tickets/).

This fetches all BQ data and outputs a markdown skeleton with pre-formatted tables and <!-- ... --> markers. All tables (CE overview, channels, Google Search, cohorts, budget with portfolio type, geo, landing pages, money-on-table, campaign targeting, appendix) are rendered by code.

Step 3 — Read skeleton + DIAGNOSTICS.md, fill narratives

Read the skeleton file. All tables are pre-formatted — do NOT reformat them. Read DIAGNOSTICS.md for hypothesis trees. Walk the trees against the data.

Replace every <!-- ... --> marker with analysis following the narrative rules below.

Section ordering with reading instructions:

1. Section 2 (CE Overview) — read Table 1:

• Read Revenue row: L4W vs LY (direction + magnitude). MoM direction (recovering or declining?).
• Read ROI(1): improved or compressed? If ROI improved but revenue dropped → volume problem, not efficiency.
• Read Orders: if orders dropped more than revenue → AOV rose (fewer, more valuable customers).
• Connect: "The question for the next section is whether this is demand, paid, or non-paid driven."

2. Section 3 (Channel Breakdown) — read Channel table:

• Sort by Δ LY column. Which channels lost the most revenue in absolute $?
• Sum the top 3 losers — do they explain the total CE decline from Section 2?
• Check paid vs non-paid split: if non-paid channels (Organic, Direct) lost more than paid → the issue is broader than ads.
• Note any channel with >50% YoY growth (emerging channel signal).

3. Section 4 (Paid Deep Dive) — read Table 2 + LP tables:

• Read CPC column: L4W vs LY. But do NOT conclude on blended CPC alone — note it and defer to Section 5 per-language analysis.
• Read CVR: if CVR declined alongside CPC flat/up → not quality traffic improvement.
• Read Clicks: magnitude of YoY loss. If >30% → significant volume problem.
• Check LY CM1 vs LY Paid Rev: if CM1 > Rev, flag as data anomaly with footnote.
• LP Ad Performance table: compare CTR across language LPs. Language LPs with 2x+ CTR vs generic → validates dedicated LP strategy.
• LP On-Site Funnel table: read LP2S and S2C columns. Which stage has the largest YoY delta? If S2C is dominant leak (>5pp) across multiple pages → product-level issue, not page-specific.

4. Section 5 (Coverage) — read Cohort table:

• First: Language CPC × Scale scan. For each cohort with >$1K spend, read CPC (L4W vs LY) and Clicks (L4W vs LY). Classify each: CPC↑+Scale↓ = competition, CPC↑+Scale↑ = healthy, CPC flat+Scale↓ = SIS compression. Write the scan table.
• Then: Top 3 by CM1 share. For each, what drove RPC change: CVR vs AOV vs TR?
• Read SIS and Rank Lost columns: if rank-lost > 60% with budget-lost < 5% → tROAS is the constraint. Compute recommendation using formula (actual ROI × 0.9, floor 130%).
• Read Budget Summary: Individual vs Portfolio aggregates. Are portfolio campaigns underperforming or expected long-tail?
• Read Geo table: largest gap between Click Share and Customer Share → under-served feeder markets.

5. Section 6 (External Dynamics):

• 6a: Run Ahrefs for 3-5 CE keywords across top markets. If demand growing but clicks falling → capture problem (confirms Section 5 competition scan).
• 6b: Connect Section 5 language scan to competition analysis. The per-language CPC × scale patterns ARE the competition evidence. Then apply 3-lens tree (see DIAGNOSTICS.md §4). If auction insights CSV available, parse and build Δ of Δ table.
• 6c: Read Money on Table (pre-rendered). Top 3 cohorts by opportunity — note which are rank-constrained vs budget-constrained.

6. Section 7 (Funnel) — run /cvr-rca:

• See Step 4 below.

7. Section 8 (Search Intelligence):

• Read Ad Group Coverage table (pre-rendered). Which AG types have most clicks? Language gaps?
• If CSV uploaded: cluster, build cross-reference, flag wasted spend.

8. Section 9 (Red Flags) — consolidate from all sections:

• Re-read your narratives from Sections 2-8. Extract every issue flagged. Rank by severity (HIGH = revenue impact + worsening, MEDIUM = notable but stable, LOW = monitor).

9. Section 10 (Actions):

• One action per red flag minimum. Use tROAS formula for bid recommendations. Size every action in $/L4W. State the math.

10. Section 1 (Executive Summary) — write LAST:

• Re-read Sections 2-10. Lead with the language-level story (which languages are competing, which are growing). State tROAS recommendations with formula. Connect funnel findings. Reference competition if auction data available.

Step 4 — Funnel (via /cvr-rca)

Note: /cvr-rca is an external skill installed at ~/.cvr-rca/, NOT in .claude/skills/. Invoke via Skill("cvr-rca", args="<CE_ID> <P4W_START> <P4W_END> <L4W_START> <L4W_END>"). Do NOT search for it in the local skills directory.

1. Run CVR-RCA:

/cvr-rca <CE_ID> <P4W_START> <P4W_END> <L4W_START> <L4W_END>

2. Read summary.json from the output directory:

~/Documents/CVR RCA Runs/ce<ID>_<P4W_START>_<L4W_END>/summary.json

3. Validate: Compare CVR-RCA's Google Ads CVR delta against cohort table TOTAL CVR delta (Section 5). Direction must agree. Note any magnitude gap — expected ~0.1-0.2pp from different data sources (clicks vs LP users).

4. Write Section 7 narrative using CVR-RCA findings (see Section 7 rules below).

5. If CVR-RCA cannot run (e.g., BQ auth issues), fall back to abbreviated narrative using cohort table CVR + SIS data from Section 5. Note "CVR-RCA unavailable — abbreviated funnel analysis."

Step 5 — Create Google Sheet (6 tabs)

Sheet creation — try in order, stop at first success:

1. gws CLI (preferred): gws sheets spreadsheets create ... then gws sheets spreadsheets values update ... for each tab.
2. Sheets API via gcloud token: Use gcloud auth print-access-token + urllib to call Sheets API v4 directly. Requires Sheets API scope on the project.
3. MCP Google Drive: mcp__claude_ai_Google_Drive__create_file with contentMimeType: text/csv and base64Content — creates one Google Sheet per CSV (auto-converts). Upload all 6 tabs as separate sheets.
4. Local fallback: Save CSVs to .cache/perf-audits/<slug>-<date>/ and note paths in Section B for manual import.

Always save CSVs to .cache/ regardless of which upload method succeeds — they're the durable backup.

Tab definitions:

• Tab 1 (Search Term Clusters): Cluster aggregates — Clicks, Spend, Conv, CVR, CPC, RPC, Spend Share. Empty with note if no CSV.
• Tab 2 (Keywords): Top 50 keywords by spend — cluster tag, campaign, match type, clicks, spend, CVR, ROI.
• Tab 3 (Keyword Universe): Ahrefs volume data — keyword, monthly volume, trend, competition. Note gap if Ahrefs unavailable.
• Tab 4 (Auction Insights): CY L4W + P4W weekly competitor data from CSV. Empty if no CSV.
• Tab 5 (Campaign Detail): Primary source is fct_orders grouped by channel × campaign for ALL rows — includes non-paid channels, cross-CE attributed campaigns, and LY-only campaigns ($0 L4W). For the Channel column, use the custom CASE statement from scripts/perf_audit_engine_v6/sources/bq.py:462-500 — it maps channel_name + campaign_name patterns into detailed channels (Google Search, Google PMax, Bing, Google Cross-sell, etc.). Do NOT use raw channel_grouping (gives only broad 'Paid'/'Organic'). Base columns: Channel, Campaign (or "(no campaign)" for non-paid), L4W Rev, L4W Ord, L4W AOV, P4W Rev, P4W Ord, LY Rev, LY Ord, Δ LY ($), Δ LY (%), Δ P4W ($), Δ P4W (%), L4W Share. Then LEFT JOIN google_ads_campaign_stats on campaign name to enrich paid rows with: L4W Spend, L4W ROI, Status (Active/Paused), Type (Individual/Portfolio). Non-paid and unmatched rows show "—" for enriched columns. Do NOT filter or drop any fct_orders rows — cross-CE campaigns (e.g., Venice cid1022 attributed to Pompeii) are expected. Sorted by L4W Rev DESC.
• Tab 6 (Landing Page Funnel): Full LP breakdown from mixpanel_user_page_funnel_progression using COUNT(DISTINCT user_id) (matches Omni Page URL Analysis). Columns: Page URL, L4W Users, L4W CVR, L4W LP2S, L4W S2C, L4W C2O, LY Users, LY LP2S, LY S2C, LY C2O, Δ LP2S, Δ S2C, Δ C2O. Top 7-8 by users in report (Section 4), full dump here. Note: Mixpanel collapses language variants (/it/, /de/, /fr/) into root URL — language-specific ad LPs only appear in the Ad Performance table (Google Ads source). Do NOT use mixpanel_user_funnel_progression (session-level, inflated rates) or COUNT(*) (sessions, not users).

After creating, add to Section B:

**Google Sheet:** [Perf Audit v6.1 — <CE Name> (<date>)](<sheet_url>)
- Tab 1: Search Term Clusters
- Tab 2: Top 50 Keywords
- Tab 3: Keyword Universe
- Tab 4: Auction Insights
- Tab 5: Campaign Detail
- Tab 6: Landing Page Funnel

Step 6 — Self-eval

Read EVAL.md. Run quick checks, score the report.

---

Narrative Rules

Reasoning Flow — Natural Language

Every section follows: frame → test → conclude. No labeled templates (**Hypothesis:**, **Verdict:**). Natural analyst prose.

1. Opening framing (1-2 sentences setting expectations from prior sections)
2. Narrative + data (walk DIAGNOSTICS.md trees, testing branches)
3. Closing conclusion (clear "so what")

Each section's opening connects to the prior section's finding. Section 4 closes with a causal chain linking all conclusions.

Executive Summary — Write LAST (Section 1)

• Status: CRITICAL / WARNING / HEALTHY

• Actions table (immediately after status):

  Action	Expected Impact	Confidence	Why

  Confidence: Certain > High > Medium > Directional. Keep "Why" crisp — max 1-2 sentences with key numbers.

• Causal story: 2-3 sentences connecting root cause → symptom → $ impact

• Channel attribution: which channel(s) drove the delta (from Section 3)

• CVR-RCA findings: If CVR-RCA identified P1 or P2 funnel issues, they MUST appear in the actions table. Cite Shapley step, device/experience locus, and sized impact from CVR-RCA. Example: "Fix S2C 9.7pp LY gap (~670 lost checkouts/L4W) — structural, all experiences affected (CVR-RCA §3)"

• Every metric labeled with source: "(BQ, paid)" or "(CVR-RCA)" or "(GAds)"

Conditional Reporting

If CE diagnosis is HEALTHY across all signals:

• Output Sections 1-4 + condensed Section 10 (top 3 actions only)
• Skip deep dive sections (5-9)
• Total report: <200 lines

If WARNING or CRITICAL → full 10-section report.

Portfolio Campaign Awareness

• Type = Individual → individual target ROAS campaign (bid_strategy_name is null)
• Type = Portfolio → shared budget + shared target ROAS across many campaigns
• Do NOT flag portfolio campaigns as "dormant" — they share budget dynamically
• Monthly cadence: high-performers graduate from portfolios to individual campaigns

tROAS / ROI Relationship

tROAS is a bidding lever, not a metric to match:

• Higher tROAS → Google bids conservatively → less volume, higher efficiency
• Lower tROAS → Google bids aggressively → more volume, lower efficiency

Google's Smart Bidding doesn't hit targets exactly. A 160% target delivering 147% actual ROI is normal algorithm variance (~15% buffer). Rules:

• Do NOT recommend reducing tROAS when actual ROI is within 15% of target — that's expected behavior
• Lowering tROAS tells Google to bid MORE aggressively (more spend, lower efficiency) — only recommend when volume is the bottleneck, not efficiency
• If actual ROI is significantly below target (>15% gap), investigate quality score, landing page, or competition — the problem is rarely the tROAS setting itself
• For portfolio campaigns: the target applies to the portfolio as a whole, not individual campaigns. One campaign at 147% and another at 175% averaging to 160% is working as designed

tROAS Recommendation Methodology

When recommending a tROAS change (only when rank-lost > budget-lost AND volume is the bottleneck):

1. Target = current actual ROI × 0.90 (10% headroom for algorithm variance). Example: English actual ROI 157% → recommend tROAS ≤ 141%, round to 140%.
2. Floor = 130% — never recommend below this (breakeven risk).
3. Ceiling = current tROAS — if actual ROI is within 15% of target, don't change.
4. Floor-hitting edge case: If all cohorts compute to ≤130% (actual ROI 131-144% × 0.9 = 118-130%), don't just recommend "130% for everything." Instead: (a) flag that TR or RPC decline is making current tROAS targets structurally unachievable, (b) recommend 130% as a short-term bridge, (c) identify the upstream fix (TR recovery, product mix, S2C). The insight is that tROAS is a symptom — the root cause is why RPC can't support higher targets.
5. State the math in the action table: "Reduce EN tROAS 155% → 140% (actual ROI 157% × 0.9 = 141%)"
6. Per-language, not blanket. Each cohort has different ROI and competition. English at 157% gets a different target than German at 163%.
7. Never recommend below LY ROI floor unless there's a specific volume recovery rationale.

Section 5 Narrative Rules (Coverage + Matchmaking)

After the pre-rendered cohort table, always do the language competition scan first:

Language-level CPC × Scale scan (required before cohort breakdown):

For each language cohort with >$1K spend, state CPC direction and scale direction YoY:

Language	CPC Δ LY	Clicks Δ LY	Signal
English	+14%	-62%	Competition — CPC up, scale halved
German	+9%	+6%	Healthy — moderate CPC rise, scale stable
Italian	+70%	+48%	Scaling aggressively
Spanish	+17%	-76%	Competition + pullback

This tells the reader immediately: which languages are losing to competition, which are growth engines. The executive summary should lead with this framing — not "reduce tROAS across the board."

Then:

1. Top 3 cohort driver breakdown — for top 3 by revenue share, explain what drove RPC: CVR vs AOV vs TR. Lead with ROI.
2. L12M trajectory per cohort — reference appendix A3 when it changes interpretation
3. Coverage gaps — present as lowest-hanging fruits first:
   • PMax not existing (check child accounts)
   • Language/geo targeting gaps (campaign targeting table in appendix A7)
   • Dormant campaigns (benchmark tROAS vs active cohorts)
   • Geographic gaps (geo table in skeleton)
4. Portfolio campaigns — use Type column in budget table. Don't flag portfolio campaigns as underperforming if ROI is within 15% of target.
5. Tourist mix context (1-2 sentences from LLM knowledge, labeled as "est.")

CPC 3-Lens Tree (Section 6b)

Apply in order:

1. Quality traffic? — CVR improved alongside CPC → Google buying better clicks. GOOD.
2. AOV structural? — ticket price rose → everyone bids more. Structural.
3. Competition? — ONLY state if: auction insights show new competitors OR SIS declining MoM. Never say "competition" without evidence.

Separate CPC story from SIS story. CPC can be justified (quality clicks) while SIS decline is concerning (showing in fewer auctions).

Competitor Tables (CSV-dependent)

If auction insights CSVs provided, parse and build two tables:

Step-by-step CSV processing:

CSV format: Row 1 = title ("Auction insights report"), Row 2 = date range, Row 3 = headers. Data starts Row 4. Columns: Week, Display URL domain, Impression share, Overlap rate, Position above rate, Top of page rate, Abs. Top of page rate, Outranking share. Percentage values have "%" suffix — strip before computing. "--" means no data for that week.

1. Read CY CSV. Skip rows 1-2. Parse headers from row 3. Group rows by "Week" column date.
2. Split into L4W (last 4 weeks by date) and P4W (first 4 weeks). If not exactly 8 weeks, split into equal halves.
3. Read LY CSV. Same parsing. Split into L4W and P4W.
4. For each competitor ("Display URL domain"), compute average SIS per half-window. Skip "--" values. Average = mean of non-null weekly SIS values.
5. Compute Δ P4W = L4W avg SIS − P4W avg SIS (MoM pace) for CY and LY separately.
6. Compute Δ of Δ = CY Δ P4W − LY Δ P4W. Positive = competitor accelerating. Negative = decelerating.
7. Compute same for all other metrics (Overlap rate, Position above, Top of page, Abs top, Outranking share) for the Competitive Position table.

Table 1 — SIS Trajectory (delta of deltas):

| Competitor | LY P4W | LY L4W | LY Δ P4W | P4W | L4W | Δ P4W | Δ of Δ |
|-----------|--------|--------|----------|-----|-----|-------|--------|
| viator.com | 46.4% | 44.7% | -1.7pp | 51.2% | 54.5% | +3.3pp | **+5.0pp ↑** |
| Headout (You) | 27.3% | 35.3% | +8.0pp | 24.9% | 22.0% | -2.8pp | **-10.8pp ↓** |

Required footnote: * LY vs CY SIS not directly comparable (account consolidation changed denominator). Δ of Δ compares MoM pace within each year — the reliable signal.

Fallbacks:

• LY CSV unavailable: show CY-only table (P4W, L4W, Δ P4W) without Δ of Δ. Note "LY auction insights unavailable — MoM only."
• LY CSV from different account (pre-consolidation): expected — Step 0 instructs downloading from the old account. Competitor names should still match across accounts. If a competitor appears in CY but not LY, show "— (new)" for LY columns.
• CSV covers different weeks (not 8): split into equal halves regardless. Note the actual date range.
• Partial LY data (fewer competitors or fewer weeks): use what's available. Note gaps inline (e.g., "LY data: 4 weeks only").

Table 2 — Competitive Position (CY L4W snapshot):

| Competitor | SIS | Overlap Rate | Position Above | Top of Page % | Abs Top % | Outranking Share |

Full period averages from CY CSV. No deltas — this is a snapshot.

Without CSVs: Fall back to cohort SIS/rank-lost data from Section 5. Note "Competitor names unavailable — auction insights CSV not provided."

Section 6a (Demand) Rules — via Ahrefs

Keyword selection: Pick 3-5 keywords representing the CE's core search intent across top markets:

• CE name in the primary market language (e.g., "pompei biglietti" for IT)
• CE name in English (e.g., "pompeii tickets" for GB/US)
• CE name in top non-English markets (e.g., "pompeji tickets" for DE, "billets pompei" for FR)
• Use the search term cluster data (Section 8) to identify the highest-volume terms per language.

Ahrefs calls:

mcp__claude_ai_Ahrefs__keywords-explorer-volume-history(
  keywords=["pompei biglietti", "pompeii tickets", "pompeji tickets"],
  country="IT"  // 2-letter ISO code for primary market
)

Run for each relevant country (primary market + top 2-3 feeder markets from geo table).

Table format:

| Keyword | Country | Jan | Mar | May | Trend |
|---------|---------|-----|-----|-----|-------|
| pompei biglietti | IT | 6,761 | 8,465 | 8,532 | +26% from Jan |

Show 3 months minimum (Jan → current) to capture seasonal ramp. If Ahrefs unavailable, note "Ahrefs demand data unavailable" and skip to Section 6b.

Narrative: Connect demand trend to paid click trend. If demand is growing but clicks are falling → capture problem (SIS). If demand is declining → market contraction, not a paid issue.

Section 7 (Funnel) Rules — via /cvr-rca

Run /cvr-rca with the same CE and date windows. Read summary.json.

Validation: Compare CVR-RCA Google Ads CVR Δ vs cohort table TOTAL CVR Δ (Section 5). Direction must agree. Note magnitude gap (~0.1-0.2pp expected — different data sources: clicks vs LP users).

Narrative structure:

1. Shapley headline: "C2O carries X% of the CVR change" — tells the reader which step matters before showing details
2. Direction + magnitude: CVR improved/declined by Xpp, driven by [step] — consistent with cohort table CVR trend
3. C2O sub-stages if C2O is significant: separate checkout submission (C2A) from payment success (A2O) — different owners
4. Device concentration: if mobile vs desktop diverge, flag it (e.g., "Android +7pp, Desktop flat" → mobile-specific change)
5. Experience-level: top 2-3 experiences by |Δrate| for the significant step — names the products, not just the metric
6. LY structural gap: if S2C or C2O is Xpp below LY, is it narrowing or persisting? Seasonal or structural?
7. Link: "Full investigation: CVR-RCA report"

Do NOT show the basic mixpanel_user_funnel_progression table. CVR-RCA uses mixpanel_user_page_funnel_progression — a different, richer table. One source of truth for funnel.

Section 8 (Search Intelligence) Rules

The skeleton renders an Ad Group Coverage table from BQ (aggregated by type: Tickets, Generic, Tour, DSA with languages and metrics). This is always available.

If search terms CSV uploaded, build three analyses:

Important: Before clustering, filter the CSV to Campaign type = 'Search' rows only. PMax terms appear in the CSV but inflate GENERIC/OTHER to ~98% of spend, making the cluster analysis useless. Filter first, then classify.

8a. Search Term Clusters — classify each search term (first match wins):

1. COMPETITOR (klook, viator, getyourguide, tiqets, musement, civitatis...)
2. INFORMATIONAL (timings, hours, orari, horaire, directions, parking, free, gratis, map, history...)
3. TICKETS (ticket, biglietti, billet, entradas, eintrittskarten, price, buy, book, admission, prenota, réserv...)
4. TOURS (tour, guided, walking, day trip, excursion, escursione, ausflug, visite guidée, gita...)
5. ATTRACTION (CE name variations, sub-attractions, archaeological, ruins, scavi...)
6. GENERIC/OTHER (everything else — often non-English terms)

Render cluster table: | Cluster | Terms | Clicks | Spend | CVR | CPC | Conv | Spend Share |

8b. Ad Group × Cluster Cross-Reference — this is the key coverage check. Use the CSV's ad group column (column 4) to build:

Search Cluster	Matched AG Type	AG Clicks	Cluster Clicks	On-Target %	Action
TICKETS	Tickets	X	Y	Y/X%	—
ATTRACTION	Generic	X	Y	Y/X%	—
TOURS	Tour	X	Y	Y/X%	Weak if <30%
INFORMATIONAL	(none)	—	Y	—	Flag if >$500 spend
COMPETITOR	(none)	—	Y	—	Flag if >$200 spend

On-Target % = what fraction of the AG type's clicks come from matching search intent. Low on-target means the AG is catching overflow from other intents via broad match.

Actionable findings from this table:

• AG type with <30% on-target → broad match overflow, consider tightening match types
• Search cluster with no matched AG → potential new ad group if volume justifies
• High-CVR cluster landing in wrong AG type → dedicated ad group could improve ad relevance + CTR
• Language present in one AG type but missing in another → language coverage gap

8c. Wasted Spend Flags — only flag terms with ≥100 clicks and 0 conversions. Require 6+ months poor performance for negative keyword recs. Show LY comparison if available.

8d. Keyword Volumes — per cluster from keyword planner if available. Sizes the opportunity.

If no CSV: Use the rendered Ad Group Coverage table + cohort SIS data. Note "Search terms CSV not provided — abbreviated analysis. Ad group coverage from BQ only."

Cluster-first, not search-term-first. Ad group coverage against experience clusters is the actionable layer.

Action Rules (Section 10)

| # | Action | Owner | Est. Impact | Timeline | Evidence |

• SPECIFIC, $ SIZED, OWNED, TIMED, linked to evidence
• Lowest-hanging fruits first: reactivation → bid optimization → keyword opportunities → negatives → structural
• Minimum 5 actions, target 7-10
• Competitive response required when competitor IS > ours
• Verify: largest campaign ROI addressed, bid actions match actual strategy

Bing Depth Rule

Default: summary row in Channel Breakdown only. Deep dive only if Bing revenue moved >50% YoY OR >$5K absolute change. Markets for Bing deep dive: France, UK, US (high browser adoption + revenue contribution). APAC: skip Bing entirely.

Mixed Product Lines Within One CE

Some CEs contain campaigns for different product types (e.g., Eiffel Tower has tower ticket campaigns AND Madame Brasserie restaurant campaigns). These have different economics (different TR, AOV, keyword intent). When you see campaign names suggesting mixed products:

• Note it in Section 5 coverage narrative: "This CE contains mixed product lines (tower tickets + dining). Blended metrics are influenced by mix shift."
• Do NOT break out separate analysis per product — treat them as portfolio campaigns.
• If one product dominates (>80% of spend), analyze as a single-product CE. Note the minority product as context.

---

Perf Infra Context (read before interpreting campaigns)

• Geo consolidation (Feb 2026): City-specific → region-specific accounts. Paused geo campaigns (EN-RoW, EN-RoA) are INTENTIONAL.
• tCPA → tROAS transition: Dec 2025 (Phase 1), Jan (Phase 2), Feb (Phase 3). PMax first, Bing March 2026.
• PMax structure: City/country-level, NOT CE-specific. Use fct_orders attribution for per-CE PMax metrics.
• 145% tROAS standard for Pro+ CEs. CM1-based bidding.
• Portfolio campaigns: Long tail CEs grouped under shared-budget portfolios (160% target). See Portfolio Campaign Awareness section.
• SIS YoY is NOT like-for-like: Account consolidation + broad match + tROAS expanded eligible-impression denominator. Use MoM as primary SIS signal. YoY directional only.
• Language/geo config checks: Automated daily by perf scripts. Zero manual fixes needed. Don't flag config issues in the audit.

---

Date Range Calculations

L4W (default): Last 4 complete weeks (Monday–Sunday), aligned to ISO weeks.
P4W: The 4 weeks immediately before L4W.
LY: Same ISO weeks last year (364 days back).

---

Global Rules

1. Insights-first: narrative leads every section
2. Source labeling: every metric labeled "(BQ, paid)" or "(GAds)"
3. Revenue distinction: "Revenue (BQ)" vs "Conv Value (GAds)"
4. Competitive claims require evidence
5. L12M for recommendations: any pause/merge must cite 12-month performance
6. Report length proportional to health: SHORT (<200 lines) for healthy, STANDARD (200-400) for warning, FULL (400-700) for critical
7. Question-answer framework: every section answers explicit questions or acknowledges gaps
8. Money-on-table sizing: SIS gap × RPC, tourist % × CVR × AOV, or cluster volume × CVR
9. ROI = CM1 ÷ Spend. One metric, from BQ. Don't define it in reports.
10. BQ is the YoY baseline, not GAds conv_value. GAds switched from revenue to CM1 in Oct 2025.
11. Budget commentary only when budget IS the constraint. Only surface when budget-lost IS >10%.
12. Succinct footers. One line max. State scope, not methodology.

---

Anti-Patterns — NEVER DO THESE

1. NEVER reference budget utilization as self-suppression (44% of budget is fine)
2. NEVER equate unused budget with lost clicks
3. NEVER say "portfolio setting bids low" if tROAS is 160%+
4. NEVER recommend reducing tROAS to "match" actual ROI — lowering tROAS increases spend, not efficiency. Only recommend lowering when volume is the bottleneck.
5. NEVER flag portfolio campaigns as "dormant" — they share budget dynamically
6. NEVER show blended funnel — filter to largest cohort
7. NEVER recommend reactivating paused geo campaigns without checking consolidation
8. NEVER flag PMax as missing without checking child account migration
9. NEVER say "competition" without citing auction insights or SIS MoM data
10. NEVER recommend pausing a language campaign based on L4W only — show L12M
11. NEVER start a section with a table — narrative first
12. NEVER invent "GAds ROI vs BQ ROI" — there is ONE ROI
13. NEVER compare campaign count YoY (consolidation merged 2-3 per language into one)
14. NEVER flag YoY SIS drops as CRITICAL (denominator expansion, use MoM)
15. Don't confuse GMV (order_value_completed_usd) with revenue (amount_revenue_usd)
16. NEVER flag position segments or search partners (dropped — not actionable)
17. NEVER flag language/geo config issues (automated daily by scripts)
18. If CM1 > Paid Revenue for any window after rendering, flag as a data issue — the renderer already corrects PMax sum_conversion_value (GMV) via revenue_percentage, so this should be rare. If it still occurs, the gap should be <5%. If larger, investigate PMax attribution.

---

Quality Checklist

• Skeleton tables untouched (not reformatted)
• Every <!-- ... --> marker replaced with narrative
• Section 2-3-4 narratives flow: CE Overview → Channel mix → Paid drill-down
• Top 3 cohort driver breakdown present (CVR vs AOV vs TR)
• L12M trajectory referenced from appendix A1/A2
• CPC explanation uses 3-lens tree
• Competitor tables present if CSVs provided (SIS Trajectory + Competitive Position)
• Money on table sized in $/L4W
• Funnel: CVR-RCA run, Shapley cited, direction validated against cohort CVR
• Portfolio campaigns correctly identified (Individual vs Portfolio in budget table)
• No budget flags unless >10% budget-lost
• No "competition" without evidence
• Every action: specific, $ sized, owned, timed, evidence link
• Minimum 5 actions, target 7-10
• Executive Summary (Section 1) written LAST with causal chain
• CM1 ≤ Paid Revenue in all windows (footnote if not)
• Language CPC × Scale scan in Section 5 (not just blended CPC)
• tROAS recommendations state the formula (actual ROI × 0.9, floor 130%). If all hit floor, flag TR/RPC as root cause.
• A2 vs A3 consistency: if any month shows CVR 0% or ROI <10% in A2 but normal ROI in A3, flag as possible tracking outage
• Report length matches status (SHORT/STANDARD/FULL)

---

File Naming

thoughts/shared/perf-audits/perf-audit-<slug>-<YYYY-MM-DD>-v6.md

Requirements

BigQuery Access

• Project: headout-analytics
• Script: scripts/perf_audit_v6.py (render subcommand)

Ahrefs (optional)

• keywords-explorer-volume-history + keywords-explorer-overview

Slack (optional)

• slack_search_public_and_private for SP context

Related Skills

• /ce-audit — Full CE health check (supply, CX, organic, competitive)
• /market-weekly-review — Market-level weekly review
• /availability-diagnostics — Inventory and availability analysis