Mission: Find Zava's Hidden Product-Quality Crisis — Why the #1 Revenue SKU Is the Crisis, and Why Every Dashboard Missed It

Mission/open goal Description

Mission: Principal Data Analyst — Find Zava's Hidden Product-Quality Crisis.

Goal: identify the product/category Zava must prioritize for immediate intervention by proving convergence of five independent signals — sales impact, support burden, low satisfaction, recurring complaint themes, and vector-similar negative documents — and, critically, answer the part of the mission most submissions skip: explain the mechanism by which the crisis stayed hidden from every top-level dashboard.

Verdict: ZCPTM-SS-M-BW — Premium Short Sleeve Men's Top (M, Black/White). It is simultaneously Zava's no. 1 revenue SKU ($19,825.05 / 228 units / 105 orders — rank verified by query, not assumed) and its worst support outlier: 9 tickets (2.6x the next SKU), average satisfaction 1.67 vs 3.38 company-wide, all 9 tickets scored 2 or below, and 6 of the 12 negative reviews in the entire Docs corpus target this one SKU in three languages (EN/ES/FR), all describing the same defect: smart-fabric connectivity dies after washing.

The crisis hides through four verified fragmentation mechanisms:

1. Category fragmentation — 9 tickets split across ProductQuestion(4)/TechnicalSupport(3)/Returns(2); no single queue alarms.
2. Scenario fragmentation — 8 linked chats split across five scenarios (OrderStatus 3, Return 2, Billing 1, ProductQuestion 1, TechSupport 1); the defect hides inside routine conversations.
3. Language fragmentation — complaints in EN, ES, FR; per-language keyword monitors each see only a fraction. Only semantic vector search reunites them.
4. 2-star reviews — every crisis review is rating 2, not 1; any "1-star alert" threshold misses all six. Meanwhile sales dashboards actively celebrate this SKU because it's the revenue leader.

Environment note (honest): I ran locally against SQL Server 2022 Developer (the 2025 container wasn't available on this machine), so I rebuilt the seed pipeline from the official setup.zip and wrote an exact cosine-distance polyfill for dbo.FindSimilarDocsByDocId (exploded-component dot product / norms — mathematically identical to VECTOR_DISTANCE('cosine')). The SQL MCP server itself is the real Data API Builder 2.0.8 MCP server from this repo's dab-config.json, launched with --mcp-stdio exactly as .vscode/mcp.json does; all tool calls are genuine JSON-RPC exchanges captured to mcp_evidence.jsonl.

Harness and model

Claude Code (agent mode) with Claude Fable 5, driving the repo's own SQL MCP server (Data API Builder 2.0.8, --mcp-stdio) against PromptathonDb

Turn-by-turn journey

1. Prompt: Stand up the Promptathon environment locally and discover the schema.
   Agent response or action: Seeded PromptathonDb from the official setup.zip using the official ZavaSeedLoader (patched for SQL Server 2022: VECTOR(1536) to NVARCHAR(MAX) + exploded-component table + exact cosine polyfill for dbo.FindSimilarDocsByDocId). Started the repo's DAB SQL MCP server over stdio; MCP initialize handshake returned {"name":"SQL MCP Server","version":"2.0.8"}; called describe_entities.
   Result: Canonical row counts confirmed — Products 578, Customers 1,228, Employees 281, SalesOrders 1,800, SalesOrderLines 2,747, SupportTickets 47, SupportChats 45, Docs 89 (all embedded).

2. Prompt: Which products and categories carry meaningful revenue and quantity?
   Agent response or action: Aggregated SalesOrderLines by SKU (B2C), plus a dedicated revenue-rank query.
   Result: ZCPTM-SS-M-BW (Premium Short Sleeve Men's Top, M, Black/White) is the no. 1 revenue SKU company-wide — $19,825.05, 228 units, 105 orders.

3. Prompt: Compare top sellers against support ticket volume, priority, status, and satisfaction.
   Agent response or action: Grouped SupportTickets by RelatedSKU: ticket count, avg satisfaction, High/Critical count, unresolved count, resolution hours.
   Result: The same SKU is the worst support outlier — 9 tickets (next-worst SKU: 3), avg satisfaction 1.67 vs 3.38 company baseline, 3 High/Critical, and all 9 tickets scored 2 or below. The top seller and the top problem are the same product.

4. Prompt: Aggregate satisfaction by SKU through the MCP aggregate_records tool.
   Agent response or action: DEAD END — my first call passed a nested aggregations array and the tool rejected it: {"status":"error","type":"InvalidArguments","message":"Missing required argument 'function'"}. The tool takes a single function/field pair.
   Result: Corrected into two calls (AVG SatisfactionScore, COUNT TicketId). Both exchanges, including the failure, are preserved in mcp_evidence.jsonl.

5. Prompt: Parse SupportChats.MessagesJson and find recurring complaint themes.
   Agent response or action: Parsed all 45 transcripts, isolated customer-only text, ran regex theme families (connectivity/pairing, washing, refund, billing, sizing) across EN/ES/FR.
   Result: DEAD END that became a finding — English keyword mining surfaced only 1 connectivity mention among the crisis chats, because complaints are split across Spanish and French and across scenarios (a Billing chat, three OrderStatus chats, Returns). Keyword grouping structurally cannot see this cluster; that failure is itself the hiding mechanism, and it motivated the vector-search step.

6. Prompt: Analyze Docs for negative patterns by SKU.
   Agent response or action: Tag-mined TagsJson ratings; read the crisis SKU's review bodies via MCP read_records.
   Result: 6 of the 12 negative reviews in the entire corpus target this one SKU (50% concentration), in EN/ES/FR, all rating:2, all describing the same defect — smart-fabric connectivity dies after washing ("Support says it is a known issue with this top").

7. Prompt: Run vector similarity from a representative negative document.
   Agent response or action: Called the custom MCP tool find_similar_docs_by_doc_id with DocId 39 ("Smart fabric stopped connecting after one wash"), TopN 8.
   Result: The five nearest neighbors (cosine distance 0.211-0.270) are exactly the other five crisis-SKU negative reviews, across three languages. Nearest non-crisis doc: 0.443 — a clean ~0.17 separation gap.

8. Prompt: Don't trust one lucky seed — audit retrieval quality.
   Agent response or action: Re-seeded the vector search from each of the six crisis reviews; measured precision@5 against the SKU label; labeled neighbor languages.
   Result: P@5 = 30/30 (100%) from every seed, mixed languages every time. Honest caveat found: chat-transcript docs cluster more loosely than reviews (one crisis chat doc sits at 0.511, outside the tight review cluster), and 89 docs is a small corpus — encouraging, not proof.

9. Prompt: Could 9 low-satisfaction tickets on the top-revenue SKU be random chance?
   Agent response or action: Permutation test — 100,000 random 9-ticket subsets of the 45 scored tickets (numpy, seed 42, reproducible).
   Result: Zero subsets reached a mean of 1.67 or lower. p < 0.00001. The cluster is not noise.

10. Prompt: Quantify the blast radius and produce the executive brief.
    Agent response or action: Joined orders/lines/products/customers for margin, channel, and segment analysis; cross-validated against raw seed returns.csv; verified all 9 crisis tickets' RelatedOrderId actually contain the SKU (9/9); compared sibling SKUs; assembled the final executed notebook.
    Result: $19,380.05 delivered revenue ($14,474 gross margin) at risk; ~96% of buyers are unreachable retail walk-ins (Dallas/San Francisco/London, CustomerId NULL); filers are overwhelmingly the "New" segment. Sibling ZCPTM-SS-L-BW (154 units) has 1 ticket and 0 negative docs — the defect is likely lot/size-specific, sharpening the recommended CAPA. Only 1 return in returns.csv (Denied, $0) proves the return wave postdates the data window — refund exposure framed as projection, not booked cost.

Artifact attached below: zava-crisis-artifacts.zip — executed notebook (zava_product_quality_crisis.ipynb, 14 code cells, 0 errors), MCP JSON-RPC evidence log (mcp_evidence.jsonl), SQL investigation + results, and analysis scripts.

Architecture / data flow:
setup.zip -> ZavaSeedLoader (SQL 2022 polyfill) -> PromptathonDb -> DAB SQL MCP server (stdio JSON-RPC) + pyodbc -> sales / support / docs / chat-theme analyses -> FindSimilarDocsByDocId vector clustering -> retrieval-precision audit + permutation test -> fragmentation (why-hidden) analysis -> executive brief.

zava-crisis-artifacts.zip

Completion

• Yes, the agent completed the mission or goal.
• No, the agent did not complete the mission or goal.

Bonus work

• Real MCP evidence, not screenshots: drove the repo's actual Data API Builder SQL MCP server over raw JSON-RPC stdio and captured the full request/response log — initialize handshake, tools/list, describe_entities, read_records, aggregate_records ×2 (including the failed-schema dead end), and find_similar_docs_by_doc_id ×2 — in mcp_evidence.jsonl.

• Statistical rigor: permutation test (100,000 draws, seeded, reproducible) instead of eyeballing "1.67 looks low" — p < 0.00001 that the crisis SKU's satisfaction cluster is random chance.

• Retrieval-quality audit: precision@5 measured from all six crisis-review seeds (30/30 = 100%), with language labels per neighbor and an honest negative finding — chat-transcript docs cluster more loosely than reviews, and an 89-doc corpus limits how far the precision claim generalizes. Vector results were audited, not just trusted.

• Source-data cross-validation: checked returns.csv from the raw seed zip — only 1 return for the crisis SKU (Denied, $0 refund), proving the return wave postdates the January 2024 data window; refund exposure is therefore framed as a projection, not booked cost.

• Linkage QA: verified that all 9 crisis tickets' RelatedOrderId values actually contain the crisis SKU in their order lines (9/9 pass) — the ticket-to-product linkage is real, not coincidental.

• Lot-isolation lead: sibling-SKU comparison (same shirt, size L/same color: 154 units, 1 ticket, 0 negative docs) converts the finding from an alarm into a testable manufacturing hypothesis — the defect is likely specific to the M/Black-White production lot — which sharpens the recommended CAPA.

• Engineering under constraint: implemented an exact cosine-distance polyfill (exploded embedding components + precomputed norms) so SQL Server 2022 reproduces SQL Server 2025's VECTOR_DISTANCE('cosine') semantics against the same dbo.FindSimilarDocsByDocId stored-procedure contract — verified against the canonical distances other participants report.

• Monitoring fix so it can't happen again: proposed a weekly SKU-level ticket-volume × satisfaction monitor that cuts across ticket categories, chat scenarios, and languages — closing the exact four fragmentation gaps that let this crisis hide.

Start with the attached zava-crisis-artifacts.zip, especially zava_product_quality_crisis.ipynb and mcp_evidence.jsonl; the issue describes the completed SQL MCP investigation and its data sources. Review the SQL investigation and analysis scripts, then verify that the documented revenue, support, satisfaction, vector-search, and fragmentation findings are reproducible.