Zava's sales, support tickets, customer chats, reviews, and vector-search results are distributed across multiple tables in PromptathonDb, making it difficult for analysts to identify relationships between product quality, customer support, and business performance.
My objective was to use GitHub Copilot Agent Mode with SQL MCP tools to build a reusable Support Intelligence Data Mart that automatically discovers the database schema, validates data quality, engineers sales and support datasets, parses chat transcripts, enriches customer documents, and incorporates SQL Server vector search to identify recurring complaint patterns.
The dataset consisted of the PromptathonDb relational database, including SalesOrders, SalesOrderLines, SupportTickets, SupportChats, Docs, Products, Customers, and Employees. The agent was required to handle complex joins, guest and retail transactions, B2B/B2C segmentation, JSON transcript parsing, document enrichment, and vector similarity results before producing analyst-ready datasets.
The business question I asked the agent to solve was:
Which products, categories, customers, clients, or sales channels show the strongest signals of revenue risk caused by support issues, and what evidence supports those findings?
The expected outcome was a notebook-driven pipeline that produces a sales-support analytical mart, data quality report, parsed chat features, vector similarity features, an explainable product risk scorecard, and an executive summary for business stakeholders.
Harness and model
GitHub Copilot Chat (Agent Mode) in VS Code with SQL MCP tools (default model)
Turn-by-turn journey
-
Prompt: Discover the PromptathonDb schema and validate relationships between SalesOrders, SalesOrderLines, Products, Customers, Employees, SupportTickets, SupportChats, and Docs.
Agent response or action: Used SQL MCP to inspect the database, infer primary/foreign keys, validate joins, and identify key entities for analytics.
Result: Confirmed the schema and created the foundation for downstream feature engineering.
-
Prompt: Build a clean sales fact dataset at the order-line level.
Agent response or action: Generated SQL joins across SalesOrders, SalesOrderLines, Products, and Customers. Added business logic using CASE expressions to classify B2B, B2C, Guest, and Retail Walk-in orders while calculating line totals and order totals.
Result: Created a reusable fact_sales_order_line dataset with normalized customer segments and product hierarchy.
-
Prompt: Build a clean support fact dataset at the ticket level.
Agent response or action: Joined SupportTickets with Employees, Customers, and SupportChats. Engineered ticket priority, status, satisfaction, transcript metadata, resolution time, escalation, and SLA indicators.
Result: Produced a reusable fact_support_ticket table for support analytics.
-
Prompt: Perform comprehensive data quality validation.
Agent response or action: Executed SQL checks for duplicate IDs, orphaned records, null foreign keys, malformed JSON, missing satisfaction scores, and guest transactions with null CustomerIds.
Result: Generated a data quality report highlighting integrity issues and validation metrics.
-
Prompt: Parse support chat transcripts.
Agent response or action: Parsed SupportChats.MessagesJson, flattened conversations into transcript-level and message-level datasets, and extracted message counts, complaint phrases, keywords, customer/agent interactions, and resolution indicators.
Result: Created structured transcript feature tables for downstream analysis.
-
Prompt: Enrich customer documents.
Agent response or action: Parsed document tags and classified Docs by SourceType, rating, scenario, SKU, language, and resolution. Linked documents back to customers, orders, and support tickets where possible.
Result: Produced a normalized document enrichment table.
-
Prompt: Generate vector similarity features.
Agent response or action: Selected negative and high-risk documents, executed find_similar_docs_by_doc_id, and engineered similarity-based features including complaint density, repeated SKU count, repeated scenarios, and similarity cluster strength.
Result: Created a reusable vector similarity feature table for semantic complaint analysis.
-
Prompt: Create an explainable product support risk score.
Agent response or action: Combined revenue, quantity sold, ticket volume, priority severity, unresolved tickets, customer satisfaction, negative documents, and vector similarity metrics into a transparent weighted scoring model.
Result: Generated a ranked risk scorecard with explainable contributing factors for each product, category, and customer segment.
-
Prompt: Generate an executive summary.
Agent response or action: Summarized the highest-risk products, categories, and complaint clusters, highlighting the primary business drivers and recommended actions.
Result: Produced a concise Markdown executive summary ready for business stakeholder
Completion
Bonus work
Built reusable SQL and notebook workflows instead of one-off queries.
Added validation checks after each engineering step (row counts, aggregates, and data integrity).
Engineered transcript-level and message-level features by parsing SupportChats.MessagesJson.
Integrated SQL Server vector search (find_similar_docs_by_doc_id) to identify recurring complaint patterns and generate similarity-based features.
Designed an explainable risk scoring model that exposes the business metrics contributing to each score rather than using a black-box approach.
Generated reusable analytical artifacts, including sales facts, support facts, transcript features, vector similarity features, a product support risk scorecard, and an executive summary.
Developed and tested the solution in a Dockerized GitHub Codespaces/VS Code environment using GitHub Copilot Chat (Agent Mode) with SQL MCP, making the workflow reproducible.
Zava's sales, support tickets, customer chats, reviews, and vector-search results are distributed across multiple tables in PromptathonDb, making it difficult for analysts to identify relationships between product quality, customer support, and business performance.
My objective was to use GitHub Copilot Agent Mode with SQL MCP tools to build a reusable Support Intelligence Data Mart that automatically discovers the database schema, validates data quality, engineers sales and support datasets, parses chat transcripts, enriches customer documents, and incorporates SQL Server vector search to identify recurring complaint patterns.
The dataset consisted of the PromptathonDb relational database, including SalesOrders, SalesOrderLines, SupportTickets, SupportChats, Docs, Products, Customers, and Employees. The agent was required to handle complex joins, guest and retail transactions, B2B/B2C segmentation, JSON transcript parsing, document enrichment, and vector similarity results before producing analyst-ready datasets.
The business question I asked the agent to solve was:
Which products, categories, customers, clients, or sales channels show the strongest signals of revenue risk caused by support issues, and what evidence supports those findings?
The expected outcome was a notebook-driven pipeline that produces a sales-support analytical mart, data quality report, parsed chat features, vector similarity features, an explainable product risk scorecard, and an executive summary for business stakeholders.
Harness and model
GitHub Copilot Chat (Agent Mode) in VS Code with SQL MCP tools (default model)
Turn-by-turn journey
Prompt: Discover the PromptathonDb schema and validate relationships between SalesOrders, SalesOrderLines, Products, Customers, Employees, SupportTickets, SupportChats, and Docs.
Agent response or action: Used SQL MCP to inspect the database, infer primary/foreign keys, validate joins, and identify key entities for analytics.
Result: Confirmed the schema and created the foundation for downstream feature engineering.
Prompt: Build a clean sales fact dataset at the order-line level.
Agent response or action: Generated SQL joins across SalesOrders, SalesOrderLines, Products, and Customers. Added business logic using CASE expressions to classify B2B, B2C, Guest, and Retail Walk-in orders while calculating line totals and order totals.
Result: Created a reusable fact_sales_order_line dataset with normalized customer segments and product hierarchy.
Prompt: Build a clean support fact dataset at the ticket level.
Agent response or action: Joined SupportTickets with Employees, Customers, and SupportChats. Engineered ticket priority, status, satisfaction, transcript metadata, resolution time, escalation, and SLA indicators.
Result: Produced a reusable fact_support_ticket table for support analytics.
Prompt: Perform comprehensive data quality validation.
Agent response or action: Executed SQL checks for duplicate IDs, orphaned records, null foreign keys, malformed JSON, missing satisfaction scores, and guest transactions with null CustomerIds.
Result: Generated a data quality report highlighting integrity issues and validation metrics.
Prompt: Parse support chat transcripts.
Agent response or action: Parsed SupportChats.MessagesJson, flattened conversations into transcript-level and message-level datasets, and extracted message counts, complaint phrases, keywords, customer/agent interactions, and resolution indicators.
Result: Created structured transcript feature tables for downstream analysis.
Prompt: Enrich customer documents.
Agent response or action: Parsed document tags and classified Docs by SourceType, rating, scenario, SKU, language, and resolution. Linked documents back to customers, orders, and support tickets where possible.
Result: Produced a normalized document enrichment table.
Prompt: Generate vector similarity features.
Agent response or action: Selected negative and high-risk documents, executed find_similar_docs_by_doc_id, and engineered similarity-based features including complaint density, repeated SKU count, repeated scenarios, and similarity cluster strength.
Result: Created a reusable vector similarity feature table for semantic complaint analysis.
Prompt: Create an explainable product support risk score.
Agent response or action: Combined revenue, quantity sold, ticket volume, priority severity, unresolved tickets, customer satisfaction, negative documents, and vector similarity metrics into a transparent weighted scoring model.
Result: Generated a ranked risk scorecard with explainable contributing factors for each product, category, and customer segment.
Prompt: Generate an executive summary.
Agent response or action: Summarized the highest-risk products, categories, and complaint clusters, highlighting the primary business drivers and recommended actions.
Result: Produced a concise Markdown executive summary ready for business stakeholder
Completion
Bonus work
Built reusable SQL and notebook workflows instead of one-off queries.
Added validation checks after each engineering step (row counts, aggregates, and data integrity).
Engineered transcript-level and message-level features by parsing SupportChats.MessagesJson.
Integrated SQL Server vector search (find_similar_docs_by_doc_id) to identify recurring complaint patterns and generate similarity-based features.
Designed an explainable risk scoring model that exposes the business metrics contributing to each score rather than using a black-box approach.
Generated reusable analytical artifacts, including sales facts, support facts, transcript features, vector similarity features, a product support risk scorecard, and an executive summary.
Developed and tested the solution in a Dockerized GitHub Codespaces/VS Code environment using GitHub Copilot Chat (Agent Mode) with SQL MCP, making the workflow reproducible.