Mission: Zava Trustworthy Voice-of-Customer Intelligence Mart

Mission/open goal Description

I set my own goal, in the "bring your own" spirit, and deliberately spanned all three data roles rather than one: data engineering (assemble reviews, support chats, tickets, and sales into one clean per-SKU view), data science (use vector similarity to find and audit complaint themes), and data analysis (turn it into a ranked, decision-ready risk scorecard).
The deliverable is a rerunnable notebook that connects live to PromptathonDb and, on each run, recomputes a per-SKU support-risk scorecard from the current reviews, tickets, and sales — combining revenue, complaint volume, satisfaction, and negative-document signals into one transparent (non–black-box) score. It also uses find_similar_docs_by_doc_id over the precomputed Docs.Embedding to surface recurring complaint themes and audit how far those semantic matches can be trusted before they inform the ranking.
Business question: Which product is quietly accumulating the most customer dissatisfaction across reviews, chats, and support tickets — and how much confidence should leadership place in that signal?
Why this shape. The hard part isn't ranking products; the feedback is messy, unstructured, and multilingual (language and rating are packed inside Docs.TagsJson as "key:value" strings), so keyword grouping fails and semantic similarity is the point — with the emphasis on measuring where the matches are wrong. It's deliberately descriptive and concurrent: no predictive "future revenue" claim, because the data is a cross-sectional snapshot with no churn outcome.

Harness and model

GitHub Copilot Chat (agent mode) with automatic model selection (mostly MAI-Code-1-Flash, only step 4 Raptor mini) — executed all live SQL MCP calls and built the notebook. I used Claude (Opus 4.8 Max) separately to plan the approach, write the prompts, and review results; Copilot did the execution.

Turn-by-turn journey

1. Prompt (Step 1): Use the SQL MCP tools to query the live PromptathonDb database directly. Do not answer from SQL_DATABASE.md, dab-config.json, or any repository file — if you find yourself reading a repo file, stop and use the database instead.
   Do these steps in order, and after each one show me the exact tool call you made and the raw result:
   Call describe_entities and list every entity and its fields as returned by the server.
   Run aggregate_records on SupportTickets grouped by Category and Priority, returning the counts.
   Run read_records on Docs for 3 rows where SourceType = 'Review', returning DocId, SourceType, TagsJson, and the first part of Body.
   Call find_similar_docs_by_doc_id on one of those DocIds with TopN = 5, and return the neighbour DocIds with their cosine distances.
   At the end, tell me plainly: for each step, did the data come from a live SQL MCP tool call or from a file? If any connection failed, say exactly what failed instead of falling back to documentation.

Result (Step 1): ✅ Worked. All four calls ran live against the database (confirmed, no repo files) — discovered the 9 entities, aggregated tickets by category/priority, read sample reviews, ran a vector search. It self-corrected two live quirks: the server rejects singular entity names (SupportTicket failed → SupportTickets worked) and aggregate_records requires a groupby. Limitation surfaced immediately: the data is tiny (~47 tickets, biggest group only 5), so everything here is directional, not a ranking.

2. Prompt (Step 2): Use the SQL MCP tools against the live PromptathonDb only — no repo files. Remember the server uses plural entity names (Docs, not Doc) and aggregate_records needs a groupby. After each step, show the exact tool call and the raw result.
   I'm seeding a "premium quality complaint" theme from two negative reviews, DocId 2 and DocId 3 read_records on Docs for DocId 2 and 3 — return DocId, SourceType, TagsJson, RelatedSKU, and Body. These are my seeds; confirm both are negative/complaint reviews.
   find_similar_docs_by_doc_id on DocId 2, TopN 5. Return each neighbour's DocId and cosine Distance.
   find_similar_docs_by_doc_id on DocId 3, TopN 5. Same fields.
   Collect every neighbour DocId from steps 2–3 (excluding the seeds themselves), and read_records on Docs for those ids — return DocId, SourceType, TagsJson, RelatedSKU, and Body for each, so I can read them.
   Then present one table with a row per (seed, neighbour) pair: seed DocId, neighbour DocId, cosine Distance, neighbour's rating and language (from TagsJson), and a one-line gist of the neighbour's Body. Add an empty final column titled "Same theme? (true/partial/false)" for me to fill in by hand — do not fill it yourself or judge similarity for me.
   Confirm every step came from a live SQL MCP call, not a file.

Result (Step 2): ✅ Worked — and the "messy" output was the point. Seeded a premium-quality-complaint theme from two 1-star reviews (DocId 2 & 3), ran two vector searches, deduped to 4 neighbours across 6 pairs, and built the hand-labelling table exactly as asked (without judging similarity itself). The useful finding: the neighbourhood was mixed — one true match, two milder 2-star reviews, and a 5-star delighted review of the same SKU as a false positive (distance 0.32), live proof that similarity tracks topic, not sentiment. No failure; the small neighbourhood just reflects the tiny corpus.

3. Prompt (Step 3): Use the SQL MCP tools against the live PromptathonDb only — no repo files. Plural entity names.
   read_records on Docs, returning DocId, SourceType, TagsJson, RelatedSKU, and Body. Set first to 100 to pull the whole corpus in one call; if the result paginates (an endCursor/after is returned), make follow-up calls with that cursor until every row is retrieved, and tell me the total row count.
   Then output the whole set as one CSV code block with columns: DocId, SourceType, rating, language, RelatedSKU, Body. Extract rating and language from TagsJson (e.g. "rating:1" → 1, "language:en" → en). Replace any line breaks inside Body with spaces so each document is a single CSV row. Add one empty trailing column header, theme_member, and do not fill it.
   Confirm the pull was a live SQL MCP call and give the total row count.

Result (Step 3): ✅ Worked — and corrected an assumption. Pulled the entire Docs corpus in one live call (no pagination needed) as CSV, with rating/language parsed from TagsJson and theme_member left empty for hand-labelling. Two things it revealed: the true corpus size is 89 docs, not the ~83 assumed, and reading everything exposed a second complaint theme the seeds never surfaced (a smart-fabric top losing app connectivity after washing, 6 reviews in EN/ES/FR). Hand-labelling the full set then gave the recall reference — seeding from one theme recovered 3/7 of its own docs and 0/6 of the second, concrete proof of single-seed blindness.

4. Prompt (Step 4): Use SQL MCP against the live PromptathonDb only — plural entity names; aggregate_records needs a groupby. After each step, show the tool call and the raw result.
   Context: I've audited a complaint theme — a smart-fabric top, SKU ZCPTM-SS-M-BW, that loses app connectivity after washing (6 reviews in EN/ES/FR). I want to (a) confirm retrieval recovers it when seeded from inside the cluster, and (b) quantify this SKU's business impact.
   Seed check: find_similar_docs_by_doc_id on DocId 39, TopN 5. Return neighbour DocIds + cosine Distance. (Expect the other connectivity reviews.)
   Sales footprint: read_records on SalesOrderLines where SKU eq 'ZCPTM-SS-M-BW' — return OrderId, Quantity, UnitPrice, LineTotal. Then report: number of order lines, total units (sum of Quantity), total revenue (sum of LineTotal).
   Support burden: read_records on SupportTickets where RelatedSKU eq 'ZCPTM-SS-M-BW' — return TicketId, Category, Priority, Status, SatisfactionScore. Then report: ticket count, priority/status mix, average SatisfactionScore.
   Is this SKU the worst? aggregate_records on SupportTickets, groupby [RelatedSKU], count — show the SKUs ranked by ticket count so I can see where ZCPTM-SS-M-BW sits.
   Complaint footprint: read_records on Docs where RelatedSKU eq 'ZCPTM-SS-M-BW' — return DocId, SourceType, TagsJson. Then report: how many are Reviews vs SupportChats, and the rating/language spread.
   Confirm every step was a live SQL MCP call. (This was done by Raptor mini model)

Result (Step 4): ✅ Delivered the business answer — one data caveat. Confirmed retrieval recovers the connectivity cluster when seeded from inside it (DocId 39 → 44, 42, 40, 41), then quantified the SKU: it's the 1 most-complained-about identifiable product (9 tickets vs 1–3 for others; the only bigger bucket is 14 null-SKU general cases), with uniformly low satisfaction and 14 negative docs across three languages. The problem: read_records paginates at 100 rows, so the sales totals it reported here (210 units / $18,265) were slightly undercounted — the Step-5 notebook, using a direct connection with no row cap, corrected these to the full figures (228 units / $19,825).

5. Prompt (Step 5): Create a rerunnable Jupyter notebook in this Codespace called voc_risk_scorecard.ipynb.
   Its purpose: whenever new reviews and support tickets land in PromptathonDb, re-running it
   regenerates a per-SKU risk scorecard. Build and RUN it end-to-end, showing outputs.
   STEP 0 — working data access (do this first, show a smoke test):
   Determine the reliable way to query PromptathonDb from a Python cell in THIS Codespace.
   Try the Data API Builder REST endpoints (entities are exposed over HTTP at the DAB base URL;
   use the PLURAL names e.g. /api/Docs, /api/SupportTickets, /api/SalesOrderLines; the stored proc
   FindSimilarDocsByDocId is a POST), and/or a direct SQL connection using the MSSQL_CONNECTION_STRING
   env var. Use whichever actually works, wrap it in one helper function at the top, and prove it by
   printing a Docs row count before building anything else.
   PARAMETERS cell (so it reruns on new data): negative rating threshold (default 2), risk weights,
   top-N SKUs to report.
   PIPELINE (deterministic — must rerun cleanly on new rows):
   Pull Docs (DocId, SourceType, RelatedSKU, TagsJson, Body). Parse TagsJson — it is a JSON ARRAY
   of "key:value" strings like ["review","rating:1","language:en","sku:..."] — into rating,
   language, sku. Flag a doc negative if a review with rating <= threshold, or a support chat whose
   Resolution/Scenario indicates a complaint/unresolved issue.
   Pull SupportTickets (RelatedSKU, Priority, Status, SatisfactionScore).
   Pull SalesOrderLines (SKU, Quantity, LineTotal). Revenue = sum(LineTotal); do NOT sum
   SalesOrders.TotalAmount (double-counts).
   Aggregate PER SKU: revenue, units, ticket count, avg SatisfactionScore, negative-review count,
   negative-doc count.
   Transparent risk score: min-max normalise those columns and combine with the documented weights
   (no black box). Output a table ranked by risk score, plus a short markdown exec summary naming
   the top-N risk SKUs with their numbers.
   OPTIONAL analysis layer (note: only works for docs that already have embeddings):
   For the top risk SKU, take one of its negative reviews and call FindSimilarDocsByDocId (TopN 5),
   showing neighbour DocIds and cosine Distance (LOWER = closer) as supporting evidence.
   GOTCHAS to respect (learned live): plural entity names; TagsJson is an array of "key:value" strings;
   cosine Distance lower=closer; if using aggregate_records it REQUIRES a groupby; RelatedSKU and
   CustomerId are nullable (guests = null CustomerId).
   Do not hardcode any results — the notebook must recompute everything from the live data on each run.
   Confirm all data came from live calls and show the final ranked scorecard.

Result (Step 5): ✅ Strongest step — a genuinely rerunnable notebook. After some ODBC-driver troubleshooting it found a working live path (direct pymssql to the DB container), proved it with a smoke test (89 docs), and built + ran the per-SKU risk scorecard end-to-end with no hardcoded results. ZCPTM-SS-M-BW topped it at 0.97 vs 0.47 for the next SKU — independently converging on the same product the semantic audit flagged. Known problems in the output: revenue is weighted as risk, so a high-selling but complaint-free product ranks 3 on sales alone (revenue is really an impact multiplier, not a risk signal); support-chat complaint detection is conservative (chats contributed 0 to the negative-doc count); and roughly a third of tickets (14 of ~47) have no SKU and can't be attributed.

Completion

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

Bonus work

The core deliverable was the per-SKU risk scorecard. Beyond producing it, I added several layers of rigor and reusability that weren't strictly required to answer the question:
Trust auditing of the AI, not just using it. Rather than trusting vector similarity, I measured it: hand-labelled a sample of neighbours true/partial/false and computed precision, then read the full 89-doc corpus to compute recall. zava_docs_labelled.xlsx This exposed a false positive (a 5-star review sitting close to a 1-star complaint on the same product — similarity tracks topic, not sentiment) and demonstrated seed-dependence (seeding from one complaint type recovered none of a second, distinct defect — recall 0.00), which is exactly the failure mode a naive embedding search would hide.
Two independent methods, cross-checked. The risk scorecard (aggregation-based) and the semantic theme audit (embedding-based) were built separately and converged on the same SKU — a stronger result than either alone.
Genuinely rerunnable. The notebook voc_risk_scorecard.ipynb connects live to the database and recomputes everything from current data on each run — no hardcoded results — so it works on future reviews and tickets, not just today's snapshot.
Transparent, not black-box. The risk score is a documented min-max weighted combination anyone can inspect and re-weight, not an opaque model.
Honest about its limits. I state plainly where the result is directional rather than definitive: revenue is weighted as an impact multiplier (not that high sales = risk); support-chat complaint detection is conservative; roughly a third of tickets (14 of ~47) carry no SKU and can't be attributed; and the counts are small on an 89-doc corpus.

Create and run voc_risk_scorecard.ipynb in the Codespace. Start with the Python-cell smoke test using the DAB REST endpoints or MSSQL_CONNECTION_STRING, confirming a Docs row count before building the rerunnable pipeline. Done means the notebook regenerates the per-SKU scorecard from live Docs, SupportTickets, and SalesOrderLines data, ranks risk with documented weights, and shows the requested executive summary and outputs.