Mission/open goal
Mission: Find Zava's Hidden Product-Quality Crisis
Harness and model
GH copilot on AUTO
Turn-by-turn journey
- The first step was to try and understand the data. This involved mapping the schema for the tables.
Prompt: Map the schema for these tables: SalesOrders, SalesOrderLines, SupportTickets, SupportChats, Docs, Products, Customers, Employees. For each, list columns with types, primary keys, and foreign keys. Then show me: how SalesOrderLines links to Products and to a product category; how SupportTickets links to a Product/Order and where SatisfactionScore, Priority, Status live; and how Docs rows are typed (review vs chat) and linked to a product, category, or order. Finally, return one sample row of SupportChats.MessagesJson so I can see the JSON structure.
- The second step involved trying to understand the categories carrying most of the revenue. The idea here is that the hidden crisis almost certainly lives in one of the categories carrying most of the revenue.
Prompt: run these two queries:
Query 1 — Category-level: From SalesOrderLines, group by ProductCategory. Return ProductCategory, SUM(Quantity) as TotalUnits, SUM(LineTotal) as TotalRevenue, and each category's percentage of the grand total revenue. Order by TotalRevenue DESC.
Query 2 — Product-level within top categories: From SalesOrderLines, for the top 3 categories by revenue, group by ProductCategory, ProductName, SKU. Return ProductCategory, ProductName, SKU, SUM(Quantity) as TotalUnits, SUM(LineTotal) as TotalRevenue. Order by ProductCategory, TotalRevenue DESC. Limit to top 10 products per category.
The results indicate that B2B carries most of the revenue.
- The next phase determined the key number of tickets per 100 units by category. We are looking for a category where the rate is notaby higher than the others and satisfaction is notably lower.
The takeaways here were that Premium Short Sleeve Men's Top, SKU ZCPTM-SS-M-BW is the leading suspect. Reason; It is the number 1 revenue product in premium(meaningful sales impact), owns 9 of 25 premium tickets, the average satisfaction of 1.67 is lower than other products in both categories and has 3 high priority tickets which is the highest of any SKU.
- This phase involves confirming whether ZCPTM-SS-M-BW is the only product with high revenue and low satisafction.
Prompt: For all products with at least 1 support ticket, build a single summary row per SKU. Join SalesOrderLines (grouped by SKU for TotalUnits and TotalRevenue) with SupportTickets (grouped by RelatedSKU for TicketCount, AVG SatisfactionScore, count of Priority = 'High' or 'Critical', count of Status NOT IN ('Resolved','Closed')). Return: SKU, ProductName, ProductCategory, TotalUnits, TotalRevenue, TicketCount, AvgSatisfaction, TicketsPerOrder (TicketCount / number of distinct orders for that SKU), HighCriticalCount, OpenUnresolvedCount. Order by AvgSatisfaction ASC, TicketCount DESC.
The results here confirmed that ZCPTM-SS-M-BW is the only product with high revenue, high ticket volume and low satisfaction.
- The next phase is to determine the root cause.
Prompt: Find all SupportChats where RelatedSKU = 'ZCPTM-SS-M-BW'. Return TranscriptId, TicketId, Scenario, Resolution, CustomerName, Language, and MessagesJson. I need to see the full chat transcripts so we can identify recurring complaint themes. Also check for any SupportChats where RelatedSKU is NULL but RelatedOrderId matches an order containing this SKU (join through SalesOrderLines where SKU = 'ZCPTM-SS-M-BW').
Once this run, this followed up;
Prompt: From those chat transcripts, extract only the customer messages (sender = 'customer'). Group the complaints into themes — for example: defective/damaged, sizing/fit, color mismatch, shipping delay, return difficulty, quality below expectation, fabric issues, etc. List each theme with a count and 2-3 short verbatim excerpts from the customer messages. I need the strongest 3-5 themes for the executive brief.
The results indicated that the complaints revolved around surprise charges, connectivity failures, confusion about what the product does.
- The next phase cross-checks against reviews and support-chat documents.
Prompt: run these queries:
Query 1 — Doc counts for the suspect SKU: From Docs, where RelatedSKU = 'ZCPTM-SS-M-BW', return: total doc count, count by SourceType (Review vs SupportChat), count by Category, and list each DocId with its Title, SourceType, and Body (first 200 characters). Order by SourceType, DocId.
Query 2 — Negative signal in TagsJson: For the same docs, extract the 'rating' value from TagsJson (use JSON_VALUE(TagsJson, '$.rating') or similar). Return DocId, SourceType, Title, rating, and Body (first 300 characters). Order by rating ASC. I need to identify the most negative documents for vector search in the next phase.
Query 3 — Category-level comparison: Group all Docs by the product category derived from RelatedSKU (join Docs.RelatedSKU to Products.SKU to get Products.Category). Return category, total doc count, count of Review docs, count of SupportChat docs, and average rating from TagsJson.
- The next phase establishes whether the problem from step 6 is only isolated to one SKU or has affected other products.
This confirmed that the problem is product-specific and not platform-wide.
- The next phase determines whether our product's dissatisfaction rate is disproportionate relative to its share of sales
Completion
Bonus work
The agent went ahead to give the business implications and recommendations
Mission/open goal
Mission: Find Zava's Hidden Product-Quality Crisis
Harness and model
GH copilot on AUTO
Turn-by-turn journey
Prompt: Map the schema for these tables: SalesOrders, SalesOrderLines, SupportTickets, SupportChats, Docs, Products, Customers, Employees. For each, list columns with types, primary keys, and foreign keys. Then show me: how SalesOrderLines links to Products and to a product category; how SupportTickets links to a Product/Order and where SatisfactionScore, Priority, Status live; and how Docs rows are typed (review vs chat) and linked to a product, category, or order. Finally, return one sample row of SupportChats.MessagesJson so I can see the JSON structure.
Prompt: run these two queries:
Query 1 — Category-level: From SalesOrderLines, group by ProductCategory. Return ProductCategory, SUM(Quantity) as TotalUnits, SUM(LineTotal) as TotalRevenue, and each category's percentage of the grand total revenue. Order by TotalRevenue DESC.
Query 2 — Product-level within top categories: From SalesOrderLines, for the top 3 categories by revenue, group by ProductCategory, ProductName, SKU. Return ProductCategory, ProductName, SKU, SUM(Quantity) as TotalUnits, SUM(LineTotal) as TotalRevenue. Order by ProductCategory, TotalRevenue DESC. Limit to top 10 products per category.
The results indicate that B2B carries most of the revenue.
The takeaways here were that Premium Short Sleeve Men's Top, SKU ZCPTM-SS-M-BW is the leading suspect. Reason; It is the number 1 revenue product in premium(meaningful sales impact), owns 9 of 25 premium tickets, the average satisfaction of 1.67 is lower than other products in both categories and has 3 high priority tickets which is the highest of any SKU.
Prompt: For all products with at least 1 support ticket, build a single summary row per SKU. Join SalesOrderLines (grouped by SKU for TotalUnits and TotalRevenue) with SupportTickets (grouped by RelatedSKU for TicketCount, AVG SatisfactionScore, count of Priority = 'High' or 'Critical', count of Status NOT IN ('Resolved','Closed')). Return: SKU, ProductName, ProductCategory, TotalUnits, TotalRevenue, TicketCount, AvgSatisfaction, TicketsPerOrder (TicketCount / number of distinct orders for that SKU), HighCriticalCount, OpenUnresolvedCount. Order by AvgSatisfaction ASC, TicketCount DESC.
The results here confirmed that ZCPTM-SS-M-BW is the only product with high revenue, high ticket volume and low satisfaction.
Prompt: Find all SupportChats where RelatedSKU = 'ZCPTM-SS-M-BW'. Return TranscriptId, TicketId, Scenario, Resolution, CustomerName, Language, and MessagesJson. I need to see the full chat transcripts so we can identify recurring complaint themes. Also check for any SupportChats where RelatedSKU is NULL but RelatedOrderId matches an order containing this SKU (join through SalesOrderLines where SKU = 'ZCPTM-SS-M-BW').
Once this run, this followed up;
Prompt: From those chat transcripts, extract only the customer messages (sender = 'customer'). Group the complaints into themes — for example: defective/damaged, sizing/fit, color mismatch, shipping delay, return difficulty, quality below expectation, fabric issues, etc. List each theme with a count and 2-3 short verbatim excerpts from the customer messages. I need the strongest 3-5 themes for the executive brief.
The results indicated that the complaints revolved around surprise charges, connectivity failures, confusion about what the product does.
Prompt: run these queries:
Query 1 — Doc counts for the suspect SKU: From Docs, where RelatedSKU = 'ZCPTM-SS-M-BW', return: total doc count, count by SourceType (Review vs SupportChat), count by Category, and list each DocId with its Title, SourceType, and Body (first 200 characters). Order by SourceType, DocId.
Query 2 — Negative signal in TagsJson: For the same docs, extract the 'rating' value from TagsJson (use JSON_VALUE(TagsJson, '$.rating') or similar). Return DocId, SourceType, Title, rating, and Body (first 300 characters). Order by rating ASC. I need to identify the most negative documents for vector search in the next phase.
Query 3 — Category-level comparison: Group all Docs by the product category derived from RelatedSKU (join Docs.RelatedSKU to Products.SKU to get Products.Category). Return category, total doc count, count of Review docs, count of SupportChat docs, and average rating from TagsJson.
This confirmed that the problem is product-specific and not platform-wide.
Completion
Bonus work
The agent went ahead to give the business implications and recommendations