Hector sqp analysis

Hector SQP Analysis (v1.2)

⚠️ CRITICAL OBLIGATIONS — READ BEFORE EVERY USE

1. ALWAYS ask the precursor questions before any MCP call. Account name + country, week window, AND brand token confirmation are REQUIRED. Do not begin pulls until all three are answered.
2. NEVER guess the profile_id. Always resolve via find_profile_by_name. If multiple profiles match, list them and wait for the user to pick. If zero match, surface that and stop.
3. SQP is weekly and Saturday-aligned. NEVER pass today or yesterday as end_date. Compute the most recently completed Saturday (≥3 days before today in Asia/Kolkata for IN accounts) locally. Use week_range enum when it fits; use explicit start_date/end_date otherwise. NEVER pass both in the same call. SQP date resolution is LOCAL — do NOT call analytics_budget_pacing.
4. Request ONLY the 10 output metrics (list in Step 4A). Never request the full 18-metric set — 8 of those fields are never displayed anywhere and roughly double the payload.
5. Always apply the server-side metric_filters min-volume filter (T_Query > min_search_volume, default 100) on every analytics_sqp_search_terms call. Long-tail rows must be dropped on the server, never in context.
6. NEVER re-transcribe raw tool data by hand. If the tool result was file-offloaded to /mnt/user-data/tool_results/*.json, merge and process it FROM DISK with Python. If it arrived inline, write it ONCE as a compact TSV (header row + data rows, values rounded to 2dp) — never as pretty-printed JSON with repeated keys, and never twice.
7. Build BOTH output files with ONE Python generator script that reads processed data from disk and injects it into the XLSX and the HTML template. NEVER hand-type data rows inside a create_file call for the HTML or a per-row openpyxl transcript.
8. NEVER echo raw data rows, sample rows, or merged datasets in chat. Step narration is one line per step, maximum. The deliverables are the two files plus the structured executive summary — nothing else.
9. Paginate up to 5 pages (200 rows each, explicit_limit: true). Stop early the moment a page returns fewer than 200 rows. With the min-volume filter applied, most accounts finish in 1–3 pages.
10. Scale-Up threshold is DYNAMIC — computed from the data, not hardcoded. 50% of the brand's average IS% across non-branded terms. Brand tokens MUST be user-confirmed (Step 1b) before filtering.
11. Incomplete SQP data: if response_meta.data_status indicates incomplete data for the latest week, PROCEED and show a red warning banner in the HTML and a note in the Excel cover cell. AOV values are raw numbers — no currency symbol; state the currency once in the executive summary.
12. Apply SQP_LABELS to every HTML <th> — never raw API field names. Run the closing self-check before delivering any output.

---

Why this skill exists

The Search Query Performance (SQP) report from Amazon Brand Analytics is the richest source of market-level demand signals available to sellers — but it requires consistent methodology to extract actionable insights, and the raw dataset is large enough to exhaust a chat's context if handled carelessly. This skill enforces a single, repeatable six-tab analysis AND a strict token budget: server-side filtering, minimal metric sets, disk-first data handling, and script-generated outputs, so the analysis completes reliably in a single chat every week for any account.

---

When this skill activates

Fires on:

• "Run SQP analysis for <brand>"
• "Do a search query performance report for <brand>"
• "SQP analysis / SQP report for <brand>"
• "Analyse SQP for <brand>"
• Close paraphrases where ("SQP" OR "search query performance") co-occur with a brand or account name AND the user clearly wants a full structured analysis / report

Does NOT fire on:

• Single-metric SQP lookups ("what's our impression share on <term> this week?") — answer directly
• Conversational SQP mentions without an analysis request
• Bid change or keyword harvesting requests (separate skills)
• Date-resolution requests
• Requests to edit, improve, or review this skill (those go to skill-architect)

---

Blocking preconditions

You CANNOT proceed to any MCP call until ALL of the following are confirmed:

1. Account name + country (Step 1a)
2. Brand tokens confirmed by the user (Step 1b)
3. profile_id resolved (Step 2)

Missing any one of these means the skill cannot run. Ask only for what is still missing.

---

Token & Context Budget (governs every step)

The same row of data must pass through the chat context at most once. The four rules below enforce that:

T1 — Server does the filtering. Every analytics_sqp_search_terms call carries metric_filters: [{field: "T_Query", operator: "greater_than", value: <min_search_volume>}]. Rows below the volume floor never enter the chat. (Default floor: 100; the user can raise or lower it in precursors. If the user explicitly asks for "full universe / include long-tail", set the floor to 0 and warn that the run will be heavier.)

T2 — Minimal metric sets. 4A requests exactly 10 metrics; 4B requests exactly 2. Requesting a metric that no tab, sheet, or computation uses is a violation.

T3 — Disk-first data handling. Check where the tool result landed:

• Offloaded (result stored at /mnt/user-data/tool_results/*.json — the response envelope says so): merge pages, dedupe, and process entirely with a bash/Python script reading those files. Print only row counts and summary aggregates to stdout — never full rows.
• Inline (rows visible in the tool response): write the merged rows ONCE to /home/claude/sqp_data/<name>.tsv as tab-separated values — header row, then data rows, numbers rounded to 2 decimals. TSV, not JSON: repeated JSON keys on 600+ rows waste ~60% of the tokens. Never write the same dataset to disk twice, and never "verify" a written file by printing its contents.

T4 — Script-generated outputs. One Python generator script (Step 6/7) reads the processed files and writes BOTH the XLSX and the HTML. The script's size is fixed regardless of data volume: it contains the template, SQP_LABELS, styling, and logic — the data flows disk → script → output file without re-entering chat. Hand-typing table rows into an HTML create_file is a violation.

Chat hygiene: one line of narration per step ("Page 2: 200 rows — continuing", "Merged 612 terms after dedupe"). No inline tables. No sample rows. No restating the plan between steps.

---

Step-by-Step Execution

Step 1a — Collect precursors

Present the following block immediately after the user's request:

Before I start the SQP analysis, I need a couple of things:

REQUIRED
• Account: brand name + country (e.g. "<brand name>, India")
• Week window: how many completed weeks? Default: last 4 weeks.
  (Options: last_week / last_2_weeks / last_4_weeks / last_8_weeks / last_12_weeks)

OPTIONAL
• Min search volume filter: exclude low-volume long-tail terms (default: 100 queries/week — applied server-side)
• Focus ASINs: restrict analysis to specific ASINs (default: full account)
• AOV gap threshold: % gap between category and brand AOV to flag (default: 15%)

Stopping condition: account name + country confirmed. Week window defaults to last_4_weeks if not provided.

---

Step 1b — Confirm brand tokens (BLOCKING — must complete before any data pull)

After the account is confirmed, present the inferred brand tokens for user approval:

To correctly separate branded vs non-branded keywords, I need to confirm the brand tokens
I'll use to filter. Based on "<brand name>", I'll exclude terms containing:

  • "<brand_name_lowercase>"
  • "<common variant if obvious>"

Are these correct? Please add, remove, or confirm — e.g.:
  ✓ Looks good
  ✗ Remove "<x>", add "<y>"

Rules:

• Infer tokens from the brand name only: a single-word name yields that word; a multi-word name yields the full phrase, the "&" and "and" variants, and the initialism
• Do NOT auto-detect further variants — only what is unambiguous from the name
• Wait for explicit user confirmation before proceeding
• Store confirmed tokens as brand_tokens[] — used in Scale-Up processing

Stopping condition: user confirms or corrects the brand token list.

---

Step 2 — Resolve profile

Call find_profile_by_name with the brand name and country.

• Single match → use it; echo: "Profile resolved: <name> (<profile_id>)."
• Multiple matches → list them (name + profile_id + marketplace); ask user to pick. Stop until confirmed.
• Zero matches → surface: "No profile found for '<input>'. Please check the brand name and country." Stop.

Stopping condition: one confirmed profile_id.

---

Step 3 — Compute the SQP date window (local — no Marketing Stream call)

SQP is weekly, Saturday-aligned, with a ~3-day lag. Compute locally for Asia/Kolkata (IN accounts):

1. Identify today's date in Asia/Kolkata.
2. Find the most recently completed Saturday that is at least 3 days before today:

Today (IST)	end_date
Sunday	today − 8 days
Monday	today − 9 days
Tuesday	today − 10 days
Wednesday	today − 4 days
Thursday	today − 5 days
Friday	today − 6 days
Saturday	today − 7 days

3. Use week_range enum when it maps exactly (last_week / last_2_weeks / last_4_weeks / last_8_weeks / last_12_weeks). For any other window, compute explicit start_date + end_date (YYYY-MM-DD, Sunday-to-Saturday aligned). Never pass both.
4. Echo the resolved window: "Analysing SQP for <start> – <end> (<N> completed weeks, Saturday-aligned, Asia/Kolkata)."

Do NOT call analytics_budget_pacing.

---

Step 4 — Pull SQP data (token-budgeted)

4A — Search-term universe (paginated, filtered, 10 metrics ONLY)

Tool: analytics_sqp_search_terms — page 1 first; pages 2–5 only while the prior page returned exactly 200 rows.

{
  "profile_id": "<resolved>",
  "week_range": "<resolved>",
  "metrics": [
    "T_Query", "T_IS_perc", "B_CS_perc", "O_S_Perc",
    "B_CR_perc", "CAT_CR_perc",
    "T_AOV", "B_AOV",
    "Ad_ROAS", "Ad_ACOS"
  ],
  "metric_filters": [
    {"field": "T_Query", "operator": "greater_than", "value": <min_search_volume>}
  ],
  "sort_column": "T_Query",
  "sort_type": "DESC",
  "explicit_limit": true,
  "limit": 200,
  "page": <1..5>
}

These 10 metrics are the complete display set — every column in every tab and sheet is either one of these or derived from them (aov_gap_pct, Scale_Signal, Trend, deltas). Do NOT add T_Imp, B_Imp, T_Clicks, B_Clicks, T_Orders, B_Orders, Ad_Spend, or Ad_CPC — none appear in any output.

Pagination rules:

• Stop the moment a page returns < 200 rows (end of filtered data).
• Handle each page per T3: offloaded → leave on disk; inline → append to /home/claude/sqp_data/full_universe.tsv (write once, compact).
• Dedupe on SearchTerm during the merge (keep the row with higher T_Query); note only the deduped COUNT in chat.

Incomplete data handling: after page 1, check response_meta.data_status. If incomplete → continue normally, set data_incomplete = True (drives the warning banner in Step 6/7).

If focus ASINs were provided, add to every 4A/4B call:

"user_filters": [{"aggregate": "Asin", "filters": ["<asin1>", "<asin2>"]}]

4B — WoW delta data (Movers & Shakers only — 2 metrics ONLY)

Tool: analytics_sqp_search_terms for week_range: "last_2_weeks", same pagination and same metric_filters, but:

"metrics": ["T_Query", "T_IS_perc"]

The server automatically attaches previous_value and delta_pct per metric for multi-week pulls — that is the entire WoW dataset Tab 1 needs. Handle per T3 → /home/claude/sqp_data/wow_delta.tsv (or offloaded files on disk).

4C — Funnel Drop-Off classification

Tool: sqp_funnel_analysis

{
  "profile_id": "<resolved>",
  "week_range": "<resolved>",
  "drop_threshold_pct": 40,
  "min_search_volume": <from precursors, default 100>,
  "limit": 50
}

Handle per T3 → /home/claude/sqp_data/funnel_raw.json (small; already classified).

4D — Weekly account-level trend (Cover tab sparkline)

Tool: analytics_sqp_week_list

{
  "profile_id": "<resolved>",
  "week_range": "<resolved>",
  "metrics": ["T_Query", "T_IS_perc", "B_CS_perc", "O_S_Perc"]
}

One row per week — tiny; keep inline or on disk, either is fine.

---

Step 5 — Process data (Python, on disk)

All processing runs in Python against the files from Step 4 (offloaded JSONs or written TSVs). Outputs go to /home/claude/sqp_data/<tab>_processed.json. Print only counts and the computed threshold to stdout — never rows.

5A — Movers & Shakers

Source: WoW delta data

# Classify each term by T_Query WoW delta_pct
# Rising:  delta_pct >= +10%  → "📈 Rising"
# Falling: delta_pct <= -10%  → "📉 Falling"
# Stable:  otherwise          → "➡️ Stable"
# Sort by abs(delta_pct) DESC within each class
# Keep top 100 Rising + top 100 Falling for the tab

5B — Scale-Up (DYNAMIC THRESHOLD)

Source: full universe, brand_tokens from Step 1b

# 1. non_branded = rows where SearchTerm contains NO confirmed brand token (case-insensitive)
# 2. avg_is = mean(T_IS_perc) across non_branded (skip nulls)
# 3. threshold = avg_is * 0.50
# 4. Candidates: T_IS_perc < threshold
#      AND (B_CR_perc > CAT_CR_perc OR T_IS_perc > threshold * 0.5)
#    Signal: B_CR_perc > CAT_CR_perc AND T_IS_perc < threshold → "High Brand CVR"
#            T_IS_perc < threshold * 0.5                        → "Very Low IS — Untapped"
#            both                                               → "Strong Signal"
#    Sort by T_Query DESC
# Echo (one line): "Scale-Up threshold: X% (50% of brand avg IS% Y% across N non-branded terms)"

5C — AOV Gap

Source: full universe, threshold from precursors (default 0.15)

# Filter: T_AOV > 0 AND B_AOV > 0
# aov_gap_pct = (B_AOV - T_AOV) / T_AOV * 100
# Flag: abs(aov_gap_pct)/100 >= threshold
# Segment: < 0 → "Under-priced"; > 0 → "Over-priced"
# Sort by abs(aov_gap_pct) DESC within segment
# Count excluded terms (T_AOV=0 or B_AOV=0) for the summary

5D — Top Keywords

Source: full universe — no extra filtering, sorted T_Query DESC, cap 500 rows for the tab (full data in Excel).

5E — Funnel Drop-Off

Source: funnel_raw.json — use as-is. Bucket display order: VISIBILITY → LISTING → PAGE_CONTENT → CHECKOUT_GAP → STRONG_PERFORMER → NO_CLEAR_GAP.

---

Steps 6 & 7 — Build BOTH outputs with ONE generator script

Write a single Python script, /home/claude/build_outputs.py, that reads the five processed JSONs plus the 4D trend data and writes both deliverables. The data never re-enters chat — the script contains only the template, labels, styling, and logic.

Step 6 — XLSX spec (inside the generator, via openpyxl)

Five data sheets in this order:

Sheet	Source	Sort
Movers & Shakers	5A	Abs delta_pct DESC
Top Keywords	5D	T_Query DESC
Scale-Up Keywords	5B	T_Query DESC
AOV Gap Analysis	5C	Abs aov_gap_pct DESC
Funnel Drop-Off	5E	Bucket order → T_Query DESC within bucket

Formatting:

• Header row: bold, white text, dark navy fill 1F3864; freeze top row on every sheet; auto-fit column widths (cap 45 chars)
• Movers & Shakers: Rising rows → light green C6EFCE; Falling → light red FFC7CE
• Scale-Up: "High Brand CVR" → green C6EFCE; "Very Low IS" → light blue DDEBF7; "Strong Signal" → purple E2CFFF
• AOV Gap: Under-priced → orange FCE4D6; Over-priced → purple E2EFDA
• Funnel Drop-Off bucket fills: VISIBILITY=FFC7CE, LISTING=FCE4D6, PAGE_CONTENT=FFEB9C, CHECKOUT_GAP=DDEBF7, STRONG_PERFORMER=C6EFCE, NO_CLEAR_GAP=D9D9D9
• IS% / share columns: 0.0% format, stored as fractions (0.253 → 25.3%)
• AOV columns: #,##0.00 (no currency symbol); T_Query/counts: #,##0; Arial 10 throughout
• If data_incomplete = True: red bold note in A1 of every sheet: "⚠️ Latest week data may be incomplete — results reflect available data only."

Save to /mnt/user-data/outputs/<brand>_SQP_Analysis_<end_date>.xlsx.

Step 7 — HTML spec (inside the same generator)

One self-contained HTML file, zero external dependencies, inline CSS + vanilla JS. The generator injects the processed data as a single compact const DATA = {...} JSON literal into the template.

SQP_LABELS map (mandatory — the generator writes it at the top of the <script> block):

const SQP_LABELS = {
  SearchTerm: 'Search Term', T_Query: 'Search Volume', T_Imp: 'Total Impressions',
  B_Imp: 'Brand Impressions', T_IS_perc: 'Brand IS%', T_Clicks: 'Total Clicks',
  B_Clicks: 'Brand Clicks', B_CS_perc: 'Brand Click Share', T_ATC: 'Total ATCs',
  B_ATC: 'Brand ATCs', B_ATCS_perc: 'Brand ATC Share', T_Orders: 'Total Orders',
  B_Orders: 'Brand Orders', O_S_Perc: 'Brand Order Share', CAT_CTR_perc: 'Category CTR',
  B_CTR_perc: 'Brand CTR', CAT_CR_perc: 'Category CVR', B_CR_perc: 'Brand CVR',
  T_AOV: 'Category AOV', B_AOV: 'Brand AOV', aov_gap_pct: 'AOV Gap %',
  Ad_Spend: 'Ad Spends', Ad_Revenue: '(Ad ROAS × Ad Spends)', Ad_Orders: 'Ad Orders',
  Ad_Impressions: 'Ad-Imp', Ad_Clicks: 'Ad-Clicks', Ad_CPC: 'Ad-CPC',
  Ad_CTR_perc: 'Ad-CTR %', Ad_CR_perc: 'Ad-CR %', Ad_ACOS: 'Ad ACOS', Ad_ROAS: 'Ad ROAS',
  Ad_AOV: 'Ad-AOV', Scale_Signal: 'Scale Signal', Trend: 'Trend', Bucket: 'Funnel Bucket',
  Action: 'Recommended Action', delta_pct: 'WoW Δ%', prev_T_Query: 'Prior Week Volume'
};
const col = k => SQP_LABELS[k] || k;
// RULE: Every <th> MUST be: <th>${col('field_name')}</th> — never a raw API key literal.

Layout: fixed header bar (Brand | SQP Analysis | date range | ⚠️ if incomplete) + 6 tab buttons (T0 Summary, T1 Movers & Shakers, T2 Top Keywords, T3 Scale-Up, T4 AOV Gap, T5 Funnel Drop-Off); active tab content below with KPI cards → sortable/searchable table.

• Tab 0 — Cover/Summary: brand name (large) + date range + currency note ("AOV figures are in INR"); red full-width banner if data_incomplete; four KPI cards (Total Terms Analysed | Total Market Search Volume | Avg Brand IS% (non-branded) | Scale-Up Threshold Used); inline SVG sparkline of weekly T_Query from 4D; one-line auto-insight per tab (Movers: "X rising, Y falling — top gainer <term> (+Z%)"; Top Keywords: "Top term <term> (<N>/week), brand IS% X%"; Scale-Up: "<N> opportunities (threshold X% IS)"; AOV Gap: "<N> under-priced + <M> over-priced — largest gap <term> (X%)"; Funnel: "Primary bottleneck: <bucket> (<N> terms)").
• Tab 1 — Movers & Shakers: KPI cards (# Rising | # Falling | Biggest Gainer | Biggest Faller); toggle 📈/📉/All; columns Search Term, Search Volume, Prior Week Volume, Δ Volume, WoW Δ%, Brand IS%, Trend badge; Rising rows green left border 4px solid #70AD47, Falling red #FF0000.
• Tab 2 — Top Keywords: KPI cards (Total Terms | Total Market Search Volume | Avg Brand IS% | Avg Brand CVR); live keyword search; columns #, Search Term, Search Volume, Brand IS%, Brand Click Share, Brand Order Share, Brand CVR, Category CVR, Ad ROAS, Ad ACOS; up to 500 rows, client-side pagination 50/page.
• Tab 3 — Scale-Up: KPI cards (# Scale-Up Terms | Combined Market Volume | Avg Brand IS% (set) | Threshold Used); threshold note ("Scale-Up IS% threshold: X% — 50% of brand average IS% (Y%) across N non-branded terms"); columns Search Term, Search Volume, Brand IS%, Brand Order Share, Brand CVR, Category CVR, Brand AOV, Category AOV, Scale Signal badge (green/blue/purple).
• Tab 4 — AOV Gap: KPI cards (# Under-priced | # Over-priced | Avg Gap % each); currency note; sub-sections "🔻 Under-priced vs Market" (orange) / "🔺 Over-priced vs Market" (purple); columns Search Term, Search Volume, Brand AOV, Category AOV, AOV Gap %, Brand IS%, Brand Order Share; inline SVG bar chart of top 20 terms (Brand vs Category AOV); orange/purple left borders.
• Tab 5 — Funnel Drop-Off: KPI cards (# Visibility | # Listing | # Page Content | # Checkout gaps); columns Funnel Bucket, Search Term, Search Volume, Brand IS%, Brand CVR, Category CVR, Recommended Action; bucket badge colours (VISIBILITY=red, LISTING=orange, PAGE_CONTENT=yellow/dark text, CHECKOUT_GAP=blue, STRONG_PERFORMER=green, NO_CLEAR_GAP=grey); rows grouped by bucket with counts in group headers.

General HTML rules: tab switching via pure JS with URL-hash state; tables with alternating row colours, sticky first column, click-to-sort headers; client-side search on every tab; "Download this tab as CSV" button per tab (JS Blob); @media print hides nav and decoration; palette header #1F3864, accent #2E75B6, background #F5F7FA, cards #FFFFFF; min-width 1100px; percentages displayed as "25.3%" (fraction × 100); AOV displayed #,##0.00, no currency symbol.

Save to /mnt/user-data/outputs/<brand>_SQP_Dashboard_<end_date>.html.

Run the generator once with bash. If it errors, fix the script and re-run — do NOT fall back to hand-writing either file.

---

Step 8 — Present outputs and executive summary

1. Call present_files with both file paths (XLSX first, then HTML).
2. Print a structured executive summary (no inline tables):

SQP Analysis — <Brand> | <date range> | AOV figures in <currency>

Terms analysed:        <N> (min volume filter: <floor> queries/week, applied server-side)
⚠️ Data note:          [Only if incomplete] Latest week data may be partial.

📈 Movers & Shakers:  <X> rising, <Y> falling. Top gainer: "<term>" (+Z%). Top faller: "<term>" (-Z%).
🔑 Top Keywords:       Top term: "<term>" (<N> searches/week). Avg Brand IS%: X%.
🚀 Scale-Up:          <N> opportunities. Threshold: X% IS (50% of avg Y%). Top term: "<term>".
💰 AOV Gap:           <N> under-priced, <M> over-priced. Largest gap: "<term>" (X%).
                      <Z> terms excluded (missing T_AOV or B_AOV).
🔻 Funnel Drop-Off:   Primary bottleneck: <bucket> (<N> terms). Top action: "<recommended action>".

---

Closing Self-Check (RUN BEFORE EVERY RESPONSE)

Before delivering any output, verify ALL of the following:

• Precursors collected: Account + country, week window, AND brand tokens confirmed before any MCP call?
• Profile resolved: find_profile_by_name called and one profile_id confirmed?
• No analytics_budget_pacing called: date computed locally; end_date is a completed Saturday ≥3 days before today?
• Only 10 metrics in 4A, only 2 in 4B: no unused metrics requested?
• Server-side metric_filters applied on every search-terms call with the min-volume floor?
• Single-transcription rule held: offloaded results processed from disk; inline results written once as compact TSV; no dataset written or printed twice?
• No raw rows echoed in chat: narration one line per step; counts and thresholds only?
• Both files built by the generator script: no hand-typed data rows in any create_file call?
• Pagination: stopped early only when a page returned < 200 rows?
• Scale-Up threshold dynamic: 50% of brand avg IS% across non-branded terms, using user-confirmed tokens?
• Incomplete data flag handled: warning banner in HTML and Excel if data_incomplete = True?
• AOV values have no currency symbol: currency mentioned once in summary and Tab 4 note only?
• SQP_LABELS applied: every HTML <th> uses ${col('field_name')}?
• Two files in /mnt/user-data/outputs/, present_files called (XLSX first), executive summary printed?

If any check fails, fix it before delivering.

---

What NOT to Do

• Never call analytics_budget_pacing for SQP work. Date is always computed locally.
• Never pass week_range AND start_date/end_date in the same call. The tool rejects it.
• Never request the old 18-metric set. The 10-metric list in 4A is exhaustive for every output.
• Never omit metric_filters on a search-terms call (unless the user explicitly asked for full long-tail — then floor = 0 with a warning).
• Never re-transcribe an offloaded tool result into a new file by hand — process it from /mnt/user-data/tool_results/ with Python.
• Never write raw data as pretty-printed JSON when a compact TSV will do, and never write the same dataset twice.
• Never print rows, sample rows, or file contents to chat or stdout for "verification" — counts and aggregates only.
• Never hand-type table rows into the HTML or XLSX — the generator script builds both.
• Never auto-apply brand tokens without user confirmation. Step 1b is a hard gate.
• Never hardcode the Scale-Up IS% threshold. Computed from the account's own data.
• Never display AOV with a currency symbol. State the currency once in the summary only.
• Never fall back to prior week silently when data is incomplete — proceed and warn.
• Never answer "just show me the numbers" with inline tables when this skill is triggered — the deliverable is the two files.
• Never include external scripts or CDN links in the HTML.
• Never stop pagination early unless a page returned < 200 rows.

---

Edge Cases

Situation	Behaviour
Page 1 returns < 200 rows	No further pages. Dataset = that page.
Tool result is file-offloaded to /mnt/user-data/tool_results/	Process from disk (T3). Do not re-transcribe. Use read_sqp_file() per the tool's own offload rule when reading.
Tool result arrives inline	Write once as compact TSV to /home/claude/sqp_data/; never echo rows.
response_meta.data_status = incomplete	Set data_incomplete = True, proceed, warning banner in HTML and Excel.
User asks for "full universe / include long-tail"	Set metric_filters floor to 0; warn once that the run is heavier and may take longer.
Scale-Up filter returns 0 terms	Echo threshold used, note "No scale-up opportunities at this threshold." Empty tab with note; no retry.
AOV gap filter returns 0 terms	Note "No terms meet the AOV gap threshold of X%." Empty tab with note.
T_AOV or B_AOV = 0 for a term	Exclude from AOV Gap tab; count exclusions in summary.
sqp_funnel_analysis returns empty buckets	"No terms classified in this bucket" row per empty bucket.
User provides focus ASINs	Add user_filters: [{aggregate:"Asin", filters:[...]}] to ALL 4A and 4B calls.
Week window not in enum (e.g. "last 3 weeks")	Compute explicit Sunday–Saturday start_date/end_date; echo what was used and why.
Multiple accounts match profile search	Numbered list (name + profile_id + marketplace); user picks by number.
Brand has multiple tokens	Present all inferred variants for confirmation in Step 1b.
Duplicate SearchTerms across pages	Dedupe on SearchTerm, keep higher T_Query; report deduped count only.
Non-branded set empty (all terms branded)	Warn: "All terms appear to be branded — Scale-Up threshold cannot be computed. Please review brand tokens." Show warning in place of Tab 3 content.
Generator script errors	Fix and re-run the script. Never fall back to hand-writing output files.

---

Worked Example — <brand>, India, last 4 weeks

User: "Run SQP analysis for <brand>, India."

Step 1a: Week window not specified → default last_4_weeks. Min volume default 100. Account confirmed.

Step 1b: Inferred token presented: the brand name, lowercased. User adds one category variant → brand_tokens now holds both.

Step 2: find_profile_by_name("<brand>", "India") → one profile_id resolved. Echoed.

Step 3: Today = Thursday Jul 17, 2026 (IST) → most recent Saturday ≥3 days back = Jul 12, 2026. week_range: "last_4_weeks" fits. Echo: "Analysing SQP for Jun 15 – Jul 12, 2026 (4 completed weeks, Saturday-aligned)."

Step 4A: 10 metrics + metric_filters: [{field:"T_Query", operator:"greater_than", value:100}].

• Page 1: 200 rows → continue. Page 2: 200 → continue. Page 3: 187 → stop.
• Results offloaded to /mnt/user-data/tool_results/ → merged on disk with Python. Chat line: "Merged 583 terms (4 duplicates removed) — filter cut the long tail server-side."
• (v1.1 would have pulled ~943 unfiltered terms × 18 metrics across 5 pages and transcribed them twice — roughly 4× the context cost for identical output tables.)

Step 4B: last_2_weeks, metrics ["T_Query","T_IS_perc"], same filter → 3 pages → merged on disk.

Step 4C: sqp_funnel_analysis → funnel_raw.json. Step 4D: analytics_sqp_week_list → 4 weekly rows.

Step 5B: Non-branded = 512 terms; avg IS% = 6.2%; threshold = 3.1%. Chat line: "Scale-Up threshold: 3.1% (50% of brand avg IS% 6.2% across 512 non-branded terms)."

Steps 6–7: build_outputs.py written once; run once; produces <brand>_SQP_Analysis_2026-07-12.xlsx (5 sheets) and <brand>_SQP_Dashboard_2026-07-12.html (6 tabs, SQP_LABELS applied, self-contained). No data rows appear in chat.

Step 8: present_files([...xlsx, ...html]) + structured executive summary.

Self-check: all boxes checked. ✓

---

Dependencies

Tool / Library	Purpose
find_profile_by_name (Hector MCP)	Profile resolution
analytics_sqp_search_terms (Hector MCP)	Per-term data — paginated, server-filtered (Tabs 1–4)
analytics_sqp_week_list (Hector MCP)	Weekly trend for Cover sparkline
sqp_funnel_analysis (Hector MCP)	Funnel classification for Tab 5
openpyxl, pandas (preinstalled Python)	Merge/process on disk + XLSX generation
Vanilla JS (inline, script-injected)	HTML dashboard interactivity

---

Versioning

v1.2 — Jul 16, 2026. Token/context-efficiency revision — identical deliverables, materially lower context consumption:

• Metric set cut 18 → 10 on the main pull (removed T_Imp, B_Imp, T_Clicks, B_Clicks, T_Orders, B_Orders, Ad_Spend, Ad_CPC — none appear in any tab or sheet). WoW pull cut 4 → 2 metrics.
• Server-side metric_filters added (T_Query > min-volume floor, default 100) so long-tail rows never enter chat context; pagination typically finishes in 1–3 pages instead of 5.
• Single-transcription rule (T3): file-offloaded tool results (/mnt/user-data/tool_results/) are merged and processed from disk with Python; inline results are written once as compact TSV — the v1.1 instruction to re-transcribe every merged dataset into JSON (which doubled token cost) is removed.
• Generator-script rule (T4): one Python script builds both the XLSX and HTML from disk — v1.1's hand-typed HTML with embedded data rows (a third pass of the data through context) is prohibited.
• Chat hygiene rules added: one-line step narration, no row echoes, no verification prints.
• New "Token & Context Budget" section, updated banner, self-check, What-NOT-to-Do, edge cases, and worked example.

v1.1 — Jul 16, 2026. Dynamic Scale-Up threshold (50% of brand avg IS%), blocking brand-token confirmation, pagination to 1000 rows, proceed+warn on incomplete data, Cover tab, raw AOV numbers, structured summary.

v1 — Jul 16, 2026. Initial release.