Hector Brand Ads Audit

Hector Brand Advertising Audit (v1.1)

⚠️ CRITICAL OBLIGATIONS — READ BEFORE EVERY USE

1. ALWAYS run the precursor-questions block before any MCP call. Account+country, date range, comparison toggle, focus ASINs, currency-override. The audit cannot begin until REQUIRED precursors are answered.
2. NEVER guess the profile_id. Always resolve via find_profile_by_name. If multiple profiles match, present them and have the user pick. If zero match, surface that — do not proceed.
3. Push filters server-side wherever possible. Use master_search_filters on every analytics_* call. Client-side filtration is reserved only for what server-side cannot do (multi-token brand exclusion, ASIN-pattern matching, normalized joins).
4. Use temp files for any MCP response >30 rows. Write raw response to /home/claude/audit_data/<pillar>_raw.json immediately. Process via Python script that writes <pillar>_aggregated.json. Only the small aggregate enters chat context. This is mandatory — context overflow is the #1 risk to multi-pillar runs.
5. Bleeding is UNGROUPED. One row per search-term × campaign × ad-group combination. Negation happens at ad-group level — grouping hides which campaign to negate in. Reinstate Campaign column.
6. Headroom and Harvesting are GROUPED by SearchTerm. Impression share is a search-term-level metric. Same brand classifier applied per pillar (exclude in bleeding, partition in headroom, exclude in harvesting).
7. Headroom and Harvesting use the account's generic non-brand search-term ROAS as the benchmark. Never hardcode 1.1. Computed as: sum(Revenue across non-branded SP search-term rows) ÷ sum(Spend across non-branded SP search-term rows) for the period. No hard floor (terms beating the benchmark qualify even if benchmark < 1.0).
8. Placement uses non-brand campaigns only. Server-side filter CampaignName not contains "Brand".
9. Output is ONE HTML file to /mnt/user-data/outputs/<brand>_amazon_audit_<period>.html. 9 slides, 1280×720 fixed, arrow-key nav, then present_files.
10. Run the closing self-check before delivering.

Why this skill exists

Hector's agency runs the same shape of advertising audit for every brand it manages. Without a skill, each analyst rebuilds the methodology from memory — sometimes forgetting to exclude branded terms from bleeding, sometimes using the wrong ROAS guardrail, sometimes mislabeling placement rows. The skill enforces one definition of "what an audit is" across the team and outputs the deliverable in a single keystroke.

v1.1 corrects three fundamental issues in v1: bleeding was incorrectly grouped (should be ungrouped — negation is ad-group level), Headroom used a hardcoded ROAS 1.1 threshold (should be the account's generic non-brand search-term ROAS), and Harvesting was parked (v1.1 implements it client-side via analytics_targeting_list join). Pulls are now narrow and server-side-filtered; client-side aggregation runs through Python files instead of chat context.

When this skill activates

Fires on:

• "Run an ads audit for <brand>"
• "Run a brand audit for <brand>"
• "Do an advertising audit on <brand>"
• "Audit <brand>'s Amazon ads"
• "Run a Hector brand audit for <brand>"
• Close paraphrases where the words "audit" + ("ads" OR "advertising" OR "brand") co-occur AND a brand or account is named

Does NOT fire on:

• Conversational mentions of audits ("we ran an audit last week")
• Bid-lookup, date-resolution, or other Hector workflow requests
• Requests to edit, improve, or review THIS skill itself (those go to skill-architect)
• Audit-methodology questions where no account is named
• Single-pillar requests ("just show me the bleeders for X") — those are atomic queries, not full audits

Blocking precondition

You CANNOT proceed to any MCP call until the precursor block is complete. Hard gate. If the user supplied some but not all of the REQUIRED precursors in their opening message, ask only for the missing ones.

Step-by-Step Execution

Step 1 — Acknowledge and present the precursor block

Open with one short sentence confirming the audit will run, then present:

Before I start, I need a few things:

REQUIRED
• Account: brand name AND country (e.g. "<brand name>, India")
• Date range: e.g. "Jan 1 – May 26, 2026". Default if unspecified: last 60 days, ending yesterday.

OPTIONAL
• Comparison window: ON (default — Hector compares to prior period of same length) or OFF
• Focus ASINs: any ASINs to restrict the audit to (default: full account)
• Currency override: only if you want to convert from account-default (e.g. for a multi-country roll-up)

I'll auto-detect brand tokens from the name you give — spaced, joined, "&"/"and", initialism, and common typo forms — and echo them before running so you can add/remove.

If the user already supplied all required precursors, skip to Step 2.

Stopping condition: all REQUIRED precursors collected.

Step 2 — Resolve the profile

Call find_profile_by_name. Filter to the user's country.

• One match in country: confirm in one line, proceed.
• Multiple in country: list with profile_id, currency. Ask user to pick. Do NOT auto-pick.
• Zero in country: surface, ask if they meant a different country or stop.

Stopping condition: exactly one profile_id confirmed.

Step 3 — Echo brand classifier tokens

Auto-detect brand tokens from the brand name. Build variants:

• Original with-space form
• Joined no-space form
• With "&" and "and" alternatives
• Common typos (extend as audits accumulate)

Apply these rules to whatever brand the user named. This skill is not scoped to any particular set of accounts — it works on any profile the caller can access.

Extend the typo set as audits accumulate for that account.

Echo the generated tokens. Wait one turn for override. Default-accept if no response.

Stopping condition: brand tokens locked.

Step 4 — Pull Account Overview data (Pillar 1, Slide 2)

Two parallel calls:

4a. analytics_account_health

• Filters: date range, prior period if comparison=ON
• Metrics: ["Spend","Revenue","Orders","ROAS","AOV","CPC","CR_perc"]

4b. analytics_campaign_name_list — TWO sub-calls for brand vs non-brand split:

4b-i. Brand campaigns:

• master_search_filters: [{level: "CampaignName", type: "contains", keywords: ["Brand"], condition: "and"}]
• Metrics: ["Spend","Revenue","Orders","ROAS"]
• limit=1000

4b-ii. Non-brand campaigns:

• master_search_filters: [{level: "CampaignName", type: "not contains", keywords: ["Brand"], condition: "and"}]
• Same metrics, limit=1000

Write both responses to /home/claude/audit_data/campaigns_brand.json and campaigns_nonbrand.json. Aggregate by campaign type in a small Python script. Surface only the aggregated KPIs in chat.

Compute bleeding click floor: floor(1 / (CR_perc / 100)). Store as click_floor.

Step 5 — Compute generic non-brand search-term ROAS benchmark

One call: analytics_search_term_list

• master_search_filters: [
  • {level: "CampaignType", type: "is", keywords: ["sponsored products"], condition: "and"},
  • One not contains filter per brand token from Step 3, all AND'd together — e.g. {level: "SearchTerm", type: "not contains", keywords: ["<token 1>"], condition: "and"}, {level: "SearchTerm", type: "not contains", keywords: ["<token 2>"], condition: "and"}, one entry per confirmed token
• ]
• Metrics: ["Spend","Revenue"]
• limit=1000, sort_column=Spend, sort_type=DESC

Save to /home/claude/audit_data/search_terms_nonbrand.json. Python script:

1. Filter out ASIN-pattern rows (^b0[a-z0-9]{8}$).
2. Sum Spend, sum Revenue across remaining rows.
3. benchmark_roas = sum_Revenue / sum_Spend
4. Count non-brand rows. If count < 50, set heavily_branded_flag = true. The Headroom slide will display the diagnostic message.

Surface only the benchmark and the row count in chat.

Step 6 — Pull Bleeding data (Pillar 2, Slide 3)

One call: analytics_search_term_list

• master_search_filters: [
  • {level: "Clicks", type: "greater_than", keywords: click_floor - 1, condition: "and"},
  • {level: "Orders", type: "equal", keywords: 0, condition: "and"},
  • {level: "CampaignType", type: "is", keywords: ["sponsored products"], condition: "and"}
• ]
• Metrics: ["Spend","Clicks"]
• limit=1000, sort_column=Spend, sort_type=DESC

Save to /home/claude/audit_data/bleeders_raw.json. Python script:

1. Exclude branded SearchTerms via regex from Step 3.
2. Exclude ASIN-pattern SearchTerms (^b0[a-z0-9]{8}$).
3. Keep ungrouped — one row per search-term × campaign × ad-group.
4. Compute per-row CPC = Spend / Clicks.
5. Sort by Spend DESC. Top 10 for the slide.
6. Compute headline: count of unique SearchTerms, count of distinct campaigns appearing in the bleeding set, total bleed spend, % of account spend, total bleed clicks.
7. Flag outlier CPCs (>2× account CPC) for the commentary.

Surface aggregates + top 10 only.

Step 7 — Pull Targeting data (for Harvesting joins)

One call: analytics_targeting_list

• master_search_filters: [{level: "MatchType", type: "is", keywords: ["EXACT"], condition: "and"}]
• Metrics: ["Spend"] (we only need the existence check, not metrics)
• limit=1000

This pull returns currently-targeted EXACT keywords AND ASIN-exact targets. Save to /home/claude/audit_data/targeting_exact.json. Python script:

1. Split rows into two sets:
   • Keyword targets: normalized to lowercase, trimmed, single-spaced
   • ASIN-exact targets: normalized to uppercase (B0XX111 form)
2. Save normalized sets as exact_keywords.json and exact_asins.json for the harvesting joins.

If response returns exactly 1000 rows, raise limit and re-pull, or flag the truncation in the slide footnote.

Step 8 — Build Search Term Harvesting (Pillar 3a, Slide 4)

Read search_terms_nonbrand.json (already pulled in Step 5). Python script:

1. Filter rows: SearchTerm matches NOT ASIN pattern (we want word terms).
2. Group by SearchTerm: sum Spend, Revenue, Orders, recompute ROAS.
3. Normalize SearchTerm (lowercase, trim, single-space).
4. Cross-reference against exact_keywords.json:
   • Keep only SearchTerms that DO NOT appear in the exact-keyword set. These are untargeted.
5. Apply qualifying filters:
   • Orders > 2
   • ROAS > benchmark_roas (from Step 5)
6. Sort by Orders DESC. Top 10.
7. Headline: count of untargeted search terms meeting criteria, total orders, total revenue, total spend, combined ROAS.

If zero candidates qualify (rare), slide shows "No search-term harvest gap detected — all profitable non-brand search terms are already targeted as exact-match keywords."

Step 9 — Build ASIN Harvesting (Pillar 3b, Slide 5)

Read search_terms_nonbrand.json again. Python script:

1. Filter to rows where SearchTerm matches ASIN pattern (^b0[a-z0-9]{8}$).
2. Group by SearchTerm (uppercase normalized to B0XX111 form): sum Spend, Revenue, Orders, recompute ROAS.
3. Cross-reference against exact_asins.json:
   • Keep only ASIN SearchTerms that DO NOT appear in the ASIN-exact-target set.
4. Apply qualifying filters:
   • Orders > 2
   • ROAS > benchmark_roas
5. Sort by Orders DESC. Top 10.
6. Headline: count of untargeted ASINs meeting criteria, total orders, total revenue, total spend, combined ROAS.

If zero candidates, slide shows "No ASIN harvest gap detected — all profitable competitor-ASIN searches are already targeted."

Step 10 — Build Headroom (Pillar 4, Slide 6)

If heavily_branded_flag (from Step 5) is true, render the diagnostic slide: "Account is running predominantly on branded campaigns — non-brand activity insufficient to compute a meaningful benchmark." Skip to Step 11.

Otherwise, one call: analytics_search_term_list

• master_search_filters: [
  • {level: "searchTermImpressionShare", type: "less_than", keywords: 20, condition: "and"},
  • {level: "Orders", type: "greater_than", keywords: 2, condition: "and"},
  • {level: "ROAS", type: "greater_than", keywords: benchmark_roas, condition: "and"}
• ]
• Metrics: ["Spend","Revenue","Orders","ROAS"]
• limit=1000, sort_column=Orders, sort_type=DESC

Save to headroom_raw.json. Python script:

1. Group by SearchTerm: sum Orders, Spend, Revenue, recompute ROAS.
2. Classify each grouped SearchTerm via brand classifier (branded vs non-branded).
3. Split: Top 10 Branded by Orders, Top 10 Non-Branded by Orders.
4. Compute totals for each.

Step 11 — Pull Placement data (Pillar 5a, Slide 7)

One call: analytics_placement_type_list

• master_search_filters: [{level: "CampaignName", type: "not contains", keywords: ["Brand"], condition: "and"}]
• Metrics: ["Spend","Revenue","Orders","ROAS","CPC","CR_perc","Clicks"]

Python script:

1. Relabel placement names:
   • "Top of Search on-Amazon" → "Top of Search"
   • "Detail Page on-Amazon" → "Product Page"
   • "Other on-Amazon" → "Rest of Search"
2. Compute Spend% = row Spend / total non-brand Spend. Same for Revenue%.
3. Tag each row:
   • WIN: ROAS > non-brand avg ROAS AND Revenue% > Spend% by >5pp
   • BLEED: ROAS < 1.0 AND Spend% > Revenue% by >5pp
   • BALANCED: otherwise
   • DROP: optional fourth tag for ROAS < 0.5 (severe bleed)

Step 12 — Build Placement Strategy (Pillar 5b, Slide 8)

Static slide. Illustrative round-number example showing WIN/LOSE/DROP placement cards and the 2-step "lower base bid + add TOS modifier" flow. No new data pull. Use representative numbers (CPC ₹40, ₹30, ₹28; ROAS 1.0, 0.3, 0.7) adjusted to the account's actual placement pattern.

Step 13 — Render the HTML deck

Use the template structure. Nine slides:

1. Cover — brand, period, marketplace, account type, currency
2. Account Overview — 8 KPI cells, breakdowns, commentary
3. Bleeding — 3 KPIs (search terms bleeding, X across Y campaigns; wasted spend; wasted clicks), top 10 ungrouped table with Campaign column, side commentary
4. Search Term Harvesting — 3 KPIs, top 10 untargeted SearchTerms with Orders/Revenue/ROAS, commentary
5. ASIN Harvesting — 3 KPIs, top 10 untargeted ASINs with Orders/Revenue/ROAS, commentary
6. Headroom to Grow — 3 KPIs, split-grid Branded Top 10 vs Non-Branded Top 10, commentary echoing the benchmark
7. Placement Data — 4 non-brand KPIs, 3-row placement table with WIN/BLEED/BALANCED tags, commentary
8. Placement Strategy — 3 placement cards + 2-step flow, commentary about no-negative-modifier mechanic
9. Closing — Thank-you

Design tokens (locked):

• Background #faf8f3, card #ffffff, ink #1a1816
• Accent: brand-specific. Pick a colour complementing the brand being audited; default to rust #b8451f. A cool alternative is teal #2c5f7f.
• Green #4a6b3a, Red #a83232, Amber #c08527
• Fonts: Fraunces serif (display), Inter (body), JetBrains Mono (numbers)
• Stage 1280×720 fixed, auto-scaled to viewport via JS transform

Filename: <brand_slug>_amazon_audit_<YYYYMMDD>_to_<YYYYMMDD>.html to /mnt/user-data/outputs/

Step 14 — Validate, present, recap

1. Run HTML validator (python3 -c "import html.parser; ...") — verify no mismatched tags.
2. Copy to /mnt/user-data/outputs/.
3. Call present_files.
4. Post a 4-6 line recap: which pillars ran, key headline numbers, any caveats (truncation flags, heavily-branded diagnostic, etc.).
5. Run the closing self-check.

Closing Self-Check (RUN BEFORE EVERY RESPONSE)

Before delivering the audit to the user, verify ALL of the following:

• Precursor block presented and all REQUIRED fields collected (account+country, date range)
• Profile resolved via find_profile_by_name — never guessed
• Brand classifier tokens echoed before running Pillars 2 and 4
• Bleeding click floor = floor(1 / (CR_perc/100)) from account CR
• Generic ROAS benchmark computed from non-brand SP search-term rows, NOT hardcoded
• Bleeding is UNGROUPED — Campaign column present, one row per search-term × campaign × ad-group
• Bleeding excludes branded SearchTerms (verified by scanning top 10 against brand classifier)
• Bleeding excludes ASIN-format SearchTerms (no b0xxxxxxxx entries)
• Bleeding is SP-only (server-side filter)
• Harvesting joins use normalized keys — keywords lowercase/trimmed/single-spaced, ASINs uppercase
• Search Term Harvesting excludes ASIN-pattern rows (those go to ASIN Harvesting)
• ASIN Harvesting only includes ASIN-pattern rows (b0xxxxxxxx form)
• Headroom is GROUPED by SearchTerm, split branded vs non-branded
• Headroom ROAS threshold = computed benchmark, NOT 1.1 hardcode
• Placement uses non-brand campaigns only (server-side not contains "Brand" filter)
• Placement labels relabeled to Top of Search / Product Page / Rest of Search
• All 9 slides present in the HTML, in order
• HTML validates (no mismatched tags via parser check)
• File saved to /mnt/user-data/outputs/ and present_files called
• Temp files used for every MCP response >30 rows — raw rows never enter chat context
• Truncation footnote applied if any pull returned exactly 1000 rows
• Heavily-branded diagnostic shown on Headroom slide if non-brand row count < 50
• Date range echoed in cover slide and matches precursor input
• Benchmark ROAS displayed on Headroom slide lead text

If any check fails, fix it before delivering.

What NOT to Do

• Never group the Bleeding table. Negation is per-ad-group. Grouping hides which campaign to negate in.
• Never hardcode 1.1 as the ROAS threshold. The benchmark is computed per audit from the account's actual non-brand search-term performance.
• Never include branded SearchTerms in Bleeding or Harvesting. Apply the brand classifier consistently.
• Never include SB or SD rows in Bleeding. Sponsored Products only — different conversion dynamics distort the bleed signal.
• Never include brand campaigns in Placement. Branded SB units distort the placement-share read.
• Never auto-pick a profile when multiple match. Surface the options and let the user choose.
• Never paste raw rows or full slide content inline in chat. Use temp files. The deliverable IS the HTML.
• Never proceed without the precursor block.
• Never use placeholder data if any MCP call fails. Surface the failure and stop.
• Never pull metrics the slide doesn't show. Each pillar has a defined column set; pull only those.
• Never re-pull the same data across pillars when a temp file already exists. Read from disk.

Edge Cases

Worked Examples

Example 1 — Happy path

User: "Run a brand ads audit for , India, Jan 1 to May 26 2026"

1. Acknowledge in one sentence. Skip precursor block — all REQUIRED present.
2. find_profile_by_name("<brand>") → one match in India: profile <profile_id>, INR.
3. Echo the generated brand tokens. One turn for override.
4. Step 4: account_health + 2× campaign_name_list (brand/non-brand). Compute click_floor.
5. Step 5: search_terms_nonbrand pull → benchmark = ~1.05 (computed, not 1.1).
6. Step 6: bleeders pull → temp file → top 10 ungrouped with Campaign column.
7. Step 7: targeting_exact pull → split into keyword and ASIN sets.
8. Step 8: Search Term Harvesting join → top 10 untargeted terms.
9. Step 9: ASIN Harvesting join → top 10 untargeted ASINs.
10. Step 10: Headroom pull (with benchmark as threshold) → split branded/non-branded.
11. Step 11: Placement pull → 3 rows tagged.
12. Step 12: Strategy slide static.
13. Step 13: Render 9-slide HTML.
14. Step 14: Validate, present, recap. "Pillars 1-5 complete. Headline: ROAS 1.41, 192 bleeding rows across 24 campaigns, harvest gap of N terms, headroom on 180 terms vs benchmark 1.05, TOS underspent." Self-check ✓.

Example 2 — Heavily branded account

User: "Audit India, last 60 days"

1. Precursors collected.
2. Profile resolved.
3. Brand tokens echoed.
4. Step 5 benchmark computation returns only 12 non-brand search-term rows.
5. heavily_branded_flag = true.
6. Bleeding and Harvesting still run (using whatever non-brand rows exist).
7. Headroom slide renders the diagnostic message instead of the data table.
8. Placement still runs normally.
9. Recap notes the diagnostic. Self-check ✓.

Example 3 — Truncation case

User: "Run an ads audit on , US, full year 2025"

1. Precursors collected. Date range >12 months — confirm before proceeding.
2. Profile resolved (US, currency=USD).
3. Step 6 bleeders pull returns exactly 1000 rows.
4. Truncation flagged. Slide footnote: "Bleeding pull capped at 1000 rows — actual bleed may be higher."
5. Recap mentions the truncation in the closing summary. Self-check ✓.

Example 4 — Zero-spend window

User: "Audit IN for Jan 1 to Feb 1 2026"

1. Precursors collected. Profile resolved.
2. Account Overview returns Spend = 0.
3. Stop. Reply: " had no ad spend in this window. The account looks to have launched after the window ends. Want to widen it?"
4. No slides generated.

Versioning

v1.1 — May 27 2026. Major correction release.

• Bleeding ungrouped (was incorrectly grouped in v1). Campaign column reinstated. Headline restated as "X search terms bleeding across Y campaigns."
• Headroom + Harvesting benchmark = account's generic non-brand search-term ROAS (was hardcoded 1.1). Computed from non-brand SP search-term rows; no hard floor.
• Harvesting unparked — Search Term Harvesting (Slide 4) and ASIN Harvesting (Slide 5) implemented via client-side join against analytics_targeting_list. Bugs filed in ClickUp remain open but no longer block the audit. Tech-team migration marker placed for future composite endpoint.
• Metrics narrowed per pillar. Account Overview: Spend/Revenue/Orders/ROAS/AOV/CPC/CR. Bleeding: Spend/Clicks. Headroom: Spend/Revenue/Orders/ROAS. Placement: Spend/Revenue/Orders/ROAS/CPC/CR/Clicks. All others dropped.
• Row cap raised to 1000 (was 500). Truncation footnote applied if exactly 1000 returned.
• Server-side filtering pushed wherever possible. Brand vs non-brand campaign splits done via separate filtered calls. Bleeding/Headroom thresholds applied server-side. Branded SearchTerm exclusion partially server-side (multi-token not contains AND'd).
• Temp-file pattern enforced. Every MCP response >30 rows written to /home/claude/audit_data/. Raw rows never enter chat context. Mitigates context exhaustion on long audits.
• Heavily-branded diagnostic. If non-brand row count < 50, Headroom slide displays diagnostic instead of data table.
• Slide count: 9 (was 7 in v1). Harvesting added as Slides 4-5.

v1 — May 27 2026. Initial release. Encoded 4-pillar framework. Bleeding grouped (later corrected). ROAS threshold hardcoded at 1.1 (later corrected). Harvesting parked.

Planned v1.2+: Once tech team builds composite endpoints (audit_get_account_overview, audit_get_bleeders, audit_get_harvesting, audit_get_headroom, audit_get_placement), migrate each pillar's MCP calls to single composite calls. Once searchTermImpressionShare and searchTermImpressionRank become returnable metrics, add per-row IS%/IR display to Headroom. Consider optional pillars: SP/SB/SD deep-dives, budget pacing, dayparting, SQP brand-share, monthly trend chart.