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

Mission/open goal Description

We investigated the Data Analyst mission in the Zava Promptathon dataset to identify the strongest evidence-backed product-quality risk cluster. The goal was to combine sales, support, chat transcript, review, and vector-search evidence to determine which product or product category should be prioritized for immediate intervention.

Process and investigation journey:
The investigation started with a revenue analysis over SalesOrderLines to identify the highest-value products and categories. After the first pass surfaced several strong sellers, I compared those products against support-ticket data to see whether high-revenue items also had elevated support burden and poor customer satisfaction. The next step focused on the most suspicious SKU and examined related support chat messages to extract recurring complaint themes. Finally, I used vector similarity on a representative negative document to verify whether similar complaints appeared across other reviews and support documents.

SQL queries and evidence:

Revenue analysis query

Purpose: identify products and categories with the highest revenue and quantity sold.
Query: revenue_analysis.sql
Evidence: the highest-revenue items were ZavaCore Systems Professional Athletic Jersey (B2B, $521,612.54, 1,332 units) and ZavaCore Smart Cleat (B2B, $448,179.78, 657 units). Premium and Elite apparel products also appeared as notable revenue contributors.
Support comparison query

Purpose: compare top-revenue products to support-ticket volume, satisfaction, priority, and open status.
Query: support_comparison.sql
Evidence: the Premium Short Sleeve Men’s Top (SKU ZCPTM-SS-M-BW) had 228 units sold, $19,825.05 revenue, 9 support tickets, average satisfaction of 1.67, and 2 high-priority tickets.
Chat theme extraction query

Purpose: inspect customer messages from SupportChats.MessagesJson for recurring complaint themes.
Query: queries/chat_themes.sql
Evidence: customer messages repeatedly mentioned app connectivity issues, smart-fabric disconnection after washing, and trouble locating the product in the app.
Similarity search

Purpose: verify whether negative documents cluster around the same product issue.
Query: executed dbo.FindSimilarDocsByDocId for DocId 39.
Evidence: the similarity search returned multiple highly similar negative review documents tied to the same SKU, including documents about connectivity failure after washing and broken smart features.
Order context query

Purpose: understand order type, channel, and customer context for the risky SKU.
Query: queries/order_context.sql
Evidence: the SKU was sold in B2C retail channels (San Francisco, Dallas, London) and online channels, which suggests the issue is visible across multiple customer paths.
Risk cluster identified:
The strongest evidence-backed risk cluster is the Premium Short Sleeve Men’s Top, SKU ZCPTM-SS-M-BW, within the Premium category.

Why this was selected:

Strong commercial relevance: 228 units sold and $19,825.05 revenue.
Elevated support burden: 9 tickets linked to the SKU.
Poor customer satisfaction: average satisfaction score of 1.67.
High-priority issues: 2 high-priority tickets.
Repeated qualitative complaints: support chats and reviews repeatedly mention connectivity failure and smart-feature breakdown.
Vector evidence: similar documents clustered around the same issue.
Complaint themes:
The dominant complaint themes were:

smart-fabric connectivity failure
app pairing and sensor disconnection
product disappearing from the companion app
issues after washing or laundering
unexpected charges or billing confusion
return requests because the product did not meet expectations
Recommendations:
The first action should be to investigate and remediate the smart-fabric connectivity issue affecting this SKU, particularly failures that occur after washing. The evidence suggests a product-quality and durability problem rather than a one-off support issue. Recommended next steps:

Prioritize the SKU for engineering review and QA investigation.
Review the product’s washing/care and sensor durability assumptions.
Check whether the app integration or firmware update process is contributing to the failures.
Create a targeted support playbook for affected customers and monitor follow-up complaints.
Architecture/data flow:
The workflow followed a simple evidence chain:

Sales data from SalesOrderLines was aggregated to identify high-revenue products.
Support data from SupportTickets was joined to those products to measure ticket burden and satisfaction.
SupportChats.MessagesJson was parsed to extract customer language and recurring complaint themes.
Docs rows and the vector search tool were used to validate whether similar complaints appeared in other support and review documents.
Order and customer context from SalesOrders and Customers was added to frame the risk cluster by channel and customer path.
Where the agent struggled and how it was corrected:
The agent initially over-indexed on revenue-only evidence and did not yet connect the top-selling products to support risk. The correction came by shifting the analysis to a combined sales-and-support comparison and then adding qualitative evidence from chat messages and vector similarity. This was important because the mission requires a multi-dimensional conclusion rather than a single metric.

Confidence and limitations:
Confidence is high for the identified SKU-based risk cluster because it is supported by sales impact, support burden, low satisfaction, repeated complaint language, and similar negative documents. The main limitation is that the first revenue query also surfaced large B2B revenue leaders that had NULL SKUs, so there may be additional risk clusters outside the SKU-based analysis. The current conclusion is strongest for the B2C premium apparel SKU identified here.

Harness and model

GitHub Copilot in VS Code, using the MAI-Code-1-Flash model.

Turn-by-turn journey

Prompt:
"Inspect the mission requirements and explain the schema before I run the first query." Agent response or action: Reviewed the mission brief and schema documentation, then explained the key entities and their relationships.
Result:
The investigation began with a clear understanding of where sales and support evidence lived in the database.
Prompt:
"Run the first revenue analysis query and show me the results." Agent response or action: Created and executed a SQL script against dbo.SalesOrderLines.
Result:
The first revenue analysis identified the top-selling products and categories.
Prompt:
"Compare the highest-revenue products with support tickets, satisfaction scores, and complaint themes." Agent response or action: Built a support comparison query and then examined support-ticket metrics for the leading SKUs.
Result:
The Premium Short Sleeve Men’s Top emerged as the strongest candidate for a product-quality risk cluster.
Prompt:
"Analyze the support chat messages tied to that product and identify recurring complaint themes." Agent response or action: Parsed SupportChats.MessagesJson and extracted customer-facing messages.
Result:
The recurring themes were connectivity failure, app pairing issues, post-wash problems, billing confusion, and return requests.
Prompt:
"Run vector similarity search on a representative negative document and verify whether similar complaints cluster around the same product issue." Agent response or action: Ran the similarity search over a negative review document.
Result:
The search confirmed that multiple similar documents clustered around the same smart-fabric issue.
Prompt:
"Prepare a final executive brief in the mission’s required format." Agent response or action: Consolidated the evidence into a structured brief covering risk cluster, evidence, complaint themes, recommendation, confidence, and limitations.
Result:
The investigation was completed with a concise, evidence-based submission ready for the issue.

Completion

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

Bonus work

I created reusable SQL scripts in the workspace for the revenue analysis, support comparison, chat-theme extraction, document similarity candidate search, and order-context analysis. These scripts make the investigation repeatable and easier to audit.

The issue is a completed mission report rather than a change request. It names revenue_analysis.sql, support_comparison.sql, queries/chat_themes.sql, queries/order_context.sql, and dbo.FindSimilarDocsByDocId; review those artifacts and the cited evidence. No additional acceptance criteria or follow-up change is specified.