Mission: Principal Data Analyst Submission – Identifying Zava's Hidden Product Quality Crisis

Mission/open goal Description

Goal

Investigate Zava's sales, support, and customer feedback data to identify a hidden product-quality crisis before it becomes visible in traditional business dashboards.

The objective was to combine structured SQL analysis with semantic vector search to determine which product category should be prioritized for immediate business intervention.

---

Business Problem

Although revenue remained healthy, support interactions and customer feedback suggested that a product-quality issue was emerging.

The challenge was to identify the highest-risk product cluster using evidence from:

• SalesOrders
• SalesOrderLines
• Products
• SupportTickets
• SupportChats
• Docs
• Customers
• Employees
• Vector similarity search (FindSimilarDocsByDocId)

---

Investigation Journey

Step	Investigation	Outcome
1	Explored database schema	Understood relationships between sales, support, customer, and document tables
2	Ranked products by revenue and quantity sold	Identified high-impact products contributing significant revenue
3	Joined Products with SupportTickets	Found Premium Short Sleeve Men's Top had the highest ticket volume with very low satisfaction
4	Investigated support metrics	Compared ticket count, satisfaction, resolution time, and priorities
5	Reviewed customer documents and reviews	Identified recurring complaints around fit, durability, stitching, and smart-fabric reliability
6	Executed vector similarity search	Confirmed complaints formed a semantic cluster rather than isolated incidents
7	Initial hypothesis	Assumed a single SKU defect
8	Final conclusion	Determined the issue was a category-level premium apparel quality problem affecting multiple SKUs

---

SQL MCP Evidence Used

The investigation combined multiple SQL MCP operations including:

• describe_entities
• read_records
• aggregate_records
• execute_entity (FindSimilarDocsByDocId)

These tools were used to:

• analyze sales performance
• compare support burden
• evaluate satisfaction
• inspect customer reviews
• perform semantic similarity search

---

Key Findings

Risk Cluster

• Product Category: Premium Smart-Fabric Apparel
• Products: Premium Tops and Shorts
• Order Type: B2C
• Sales Channel: Online
• Customer Segment: Primarily New Customers

---

Quantitative Evidence

• Revenue: $28,382.95
• Quantity Sold: 327 units
• Support Tickets: 10
• Average Satisfaction: ~1/5
• Average Resolution Time: 52.7 hours
• Priority Distribution:
  • Critical: 2
  • High: 2
  • Medium: 3
  • Low: 3
• Negative Reviews & Support Documents: 21

---

Vector Search Evidence

Representative negative review:

"The stitching seems weak and the fabric already looks worn."

Using FindSimilarDocsByDocId:

• 10 similar documents returned
• 9 additional complaint documents
• Similarity distances ranged from 0.2377 to 0.4543

Common complaint themes:

• Poor fit
• Weak stitching
• Fabric quality
• Smart-fabric failure after washing
• Refund requests

This confirmed the issue was not an isolated complaint.

---

Business Recommendation

Product Team

• Investigate wash durability
• Improve smart-fabric reliability
• Review affected premium SKUs

Quality Assurance

• Introduce accelerated wash-cycle testing
• Expand durability validation
• Improve manufacturing quality checks

Customer Support

• Fast-track refunds
• Create dedicated resolution playbooks
• Proactively contact affected customers

Marketing

• Pause premium reliability claims until validation
• Update product messaging based on verified performance

---

Artifact Created

This submission includes:

• Data_Analyst_Investigation.ipynb
• SQL investigation queries
• Executive business report
• Mermaid architecture diagram
• Vector similarity analysis
• Business recommendations

---

Reflection

What worked well

• Combining SQL analytics with semantic vector search produced stronger evidence than structured metrics alone.
• SQL MCP tools enabled rapid exploration of relationships across multiple business domains.
• Vector search helped identify recurring customer issues that traditional aggregation would have missed.

Challenges Encountered

• Initially attempted to query incorrect entity names before identifying the correct schema.
• Some support records lacked SKU references.
• Small support sample size required validating findings across multiple datasets.
• The investigation evolved from assuming a single defective SKU to identifying a broader category-level issue.

---

Impact

The investigation identified an early-warning quality issue before it became visible through revenue metrics.

The findings provide actionable recommendations for Product, QA, Customer Support, and Marketing teams to reduce customer dissatisfaction, improve product reliability, and protect future revenue.

Harness and model

Primary AI: GitHub Copilot Agent Mode (Codespaces) for SQL MCP analysis and vector search. Supporting AI: ChatGPT (GPT-5) for organizing findings, improving the executive report, notebook documentation, and GitHub submission formatting.

Turn-by-turn journey

Turn-by-Turn Journey

Turn 1 – Understand the Mission

Prompt

Read the Principal Data Analyst mission and explain the objective, required entities, and expected deliverables.

Outcome

• Understood the business objective.
• Identified required SQL entities.
• Planned an investigation combining structured SQL analysis with semantic vector search.

---

Turn 2 – Explore the Database Schema

Prompt

Describe all entities, columns, primary keys, foreign keys, and relationships in PromptathonDb.

Outcome

• Explored the complete database schema.
• Understood relationships among Products, SalesOrders, SalesOrderLines, SupportTickets, SupportChats, Docs, Customers, and Employees.
• Created a mental ER diagram before querying.

---

Turn 3 – Initial Query Attempt (Dead End)

Prompt

Read records from Product entity.

Result

Entity 'Product' is not defined in the configuration.

Correction

• Inspected available entities using describe_entities.
• Identified the correct entity name as Products.

Learning
Always verify entity names before querying.

---

Turn 4 – Sales Analysis

Prompt

Rank products by revenue, quantity sold, and order count.

Actions

• Queried SalesOrders
• Joined SalesOrderLines
• Joined Products

Outcome

• Identified Premium and Elite apparel as the highest revenue contributors.
• Shortlisted products with significant business impact.

---

Turn 5 – Support Ticket Analysis

Prompt

Compare support ticket volume, customer satisfaction, priority, and resolution time by product.

Outcome

• Premium Short Sleeve Men's Top recorded:
  • Highest support-ticket volume
  • Lowest satisfaction score
  • Long resolution time

Decision
This became the primary investigation target.

---

Turn 6 – Customer Review Investigation

Prompt

Analyze customer reviews and support documents.

Outcome
Recurring complaints included:

• Poor fit
• Weak stitching
• Fabric quality issues
• Wash durability problems
• Refund requests

Decision
The issue extended beyond numerical metrics.

---

Turn 7 – Vector Similarity Search

Prompt

Select a representative negative review and execute FindSimilarDocsByDocId.

Outcome

• Returned 10 semantically similar documents.
• Similarity distances ranged from 0.2377 to 0.4543.

Learning
Negative feedback was highly clustered.

---

Turn 8 – Hypothesis Validation

Initial Assumption
The problem was caused by one defective SKU.

Prompt

Check whether similar complaints belong to the same SKU, category, customer segment, order type, and sales channel.

Outcome
Complaints appeared across multiple Premium apparel SKUs.

Correction
Rejected the single-SKU hypothesis.

---

Turn 9 – Cross-Validation

Prompt

Correlate SalesOrders, Customers, Products, SupportTickets, and Docs.

Outcome
Common business pattern:

• Online purchases
• B2C orders
• New customers
• Premium apparel

No meaningful B2B concentration was observed.

---

Turn 10 – Root Cause Analysis

Prompt

Combine structured SQL evidence with semantic document analysis to identify the underlying business risk.

Outcome
Evidence consistently pointed toward:

• Smart-fabric durability
• Stitching defects
• Fit inconsistency
• Wash resilience failures

---

Turn 11 – Business Recommendations

Prompt

Recommend actions for Product, QA, Customer Support, and Marketing teams.

Outcome

Recommended:

• Improve product quality
• Add accelerated wash-cycle testing
• Fast-track customer refunds
• Review marketing claims
• Monitor future complaint trends

---

Turn 12 – Final Executive Report

Prompt

Prepare an executive summary supported by SQL evidence and vector-search findings.

Final Conclusion

The investigation evolved from an initial assumption of a single defective SKU into identifying a broader category-level quality issue affecting Premium smart-fabric apparel.

By combining:

• SQL analytics
• Customer support data
• Product sales
• Customer reviews
• Semantic vector search

the investigation uncovered an emerging business risk before it became visible in traditional sales dashboards.

---

SQL MCP Tools Used

• describe_entities
• read_records
• aggregate_records
• execute_entity (FindSimilarDocsByDocId)

---

Evidence Sources

• SalesOrders
• SalesOrderLines
• Products
• Customers
• Employees
• SupportTickets
• SupportChats
• Docs
• Vector Similarity Search

---

Key Learning

The investigation demonstrates how an iterative AI-assisted workflow can combine structured SQL analysis with semantic retrieval to identify business risks. Each turn refined the previous hypothesis, ultimately revealing that the issue was a category-level Premium smart-fabric apparel quality problem rather than an isolated product defect.

Completion

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

Bonus work

Bonus Work

Additional Analysis Performed

• Performed product-level revenue ranking before beginning support analysis to prioritize high-impact products.
• Joined SalesOrders, SalesOrderLines, Products, and SupportTickets to correlate commercial performance with customer issues.
• Parsed support tickets and reviewed customer review documents to identify recurring complaint themes.
• Used SQL Server vector search (FindSimilarDocsByDocId) to validate whether complaints formed a semantic cluster rather than isolated incidents.
• Compared structured metrics (revenue, ticket volume, satisfaction, resolution time) with unstructured evidence (reviews and support documents).
• Investigated whether the issue was isolated to a single SKU or spread across multiple products before concluding it was a category-level quality issue.
• Documented dead ends and course corrections throughout the investigation.
• Created a Jupyter Notebook documenting the complete analytical workflow.
• Designed a Mermaid architecture diagram illustrating the end-to-end investigation pipeline.
• Produced an executive business report with evidence-backed recommendations for Product, QA, Customer Support, and Marketing teams.

Skills Demonstrated

• SQL querying and aggregation
• Relational data analysis
• Business intelligence investigation
• Semantic search using vector embeddings
• Root cause analysis
• Evidence-based decision making
• AI-assisted analytical workflow with GitHub Copilot

Data_Analyst_Investigation.ipynb

submission-package.md

Start with Data_Analyst_Investigation.ipynb and submission-package.md, then review the documented SQL MCP workflow across SalesOrders, Products, SupportTickets, and Docs. Check the reported findings against the included queries and vector-search evidence. Done means the completed investigation and recommendations are clearly documented and reproducible.