Unifying a Decade of Siloed Data with a Natural Language Query Engine
Explore detailed project journeys, design strategies, and measurable outcomes. Each case study offers a comprehensive look at process, challenges, and solutions—demonstrating thoughtful, user-centered product design in action.
![modern tech setup for a travel tech [background image]](https://cdn.prod.website-files.com/68312696c5081fe7d23d7f08/69a7bb06e9d70066893e41ea_NLQ-MOCK.png)
10 years of siloed business data
WeckMethod is a 4-person fitness equipment and education company. Over 10+ years of operation, business-critical data accumulated most recently across six completely separate platforms: Shopify (e-commerce sales, orders, customers), Cin7 (inventory management, BOMs, purchase orders), ShipStation (fulfillment, shipping costs, tracking), Meta/Facebook Ads (advertising spend and attribution), Mailchimp (email marketing, subscriber data), and Insense (influencer/UGC campaign management).
There was no way to ask a cross-platform question. Something as fundamental as "which products have the highest return rate relative to shipping cost" required manually exporting CSVs from multiple systems, combining them in spreadsheets, and hoping the data lined up. Most of these questions simply never got asked.
On top of the platform silos, there was a structural split: services and training courses were sold through the company's main Rails-based site, while physical products were sold through Shopify. A customer who bought a BOSU ball on Shopify and later purchased a training certification on the main site existed as two unrelated records in two unrelated systems.
All business questions lacked immediate answers
Every cross-platform question was a multi-day research project. The team needed answers about product performance, customer behavior, and marketing ROI, but getting them meant CSV exports, spreadsheet merging, and days of lead time.
The truth was masked by time and resource constraints
- 6+ separate core platforms, zero cross-referencing possible between them
- Answering cross-channel questions required CSV exports from each system + permissions
- Customer records fragmented across Shopify and the main site with no shared identifier
- iOS tracking changes prevented meaningful ad-attribution from site to Shopify
- Requesting reports from other team members added days of lead time
- No budget for a full-time data analyst
LLM-Assisted data analysis and pipeline construction with lightning efficiency enables action
- A unified data model that links orders, inventory, fulfillment, and marketing
- Cross-platform customer identity resolution
- A query interface anyone on the team can use — no SQL, no exports
- Real-time answers, not batch reports
- Built using the existing Rails stack
The company was making critical pricing, inventory, and marketing decisions based on (reactive!) gut feel due to fragmented spreadsheets and access dependencies proving so painful that most questions weren't worth the effort to answer.
Building a unified data layer
Before the query engine could exist, the data had to be consolidated. I designed and built a data integration layer that connects all six external platforms into a single PostgreSQL schema, making cross-platform relationships queryable for the first time.
- 6 API integrations — REST connections to Shopify, Cin7, ShipStation, Meta Ads, Mailchimp, and Insense, each with their own authentication, rate limits, pagination schemes, and data formats. Normalized into a consistent internal schema.
- Fuzzy customer matching — Built a cross-channel transaction matching system that identifies the same customer across the main site and Shopify using email, name, and address normalization. Recovered cross-channel visibility lost when iOS privacy changes broke traditional tracking.
- Cross-platform entity resolution — A Shopify order can now be traced to its Cin7 inventory source and ShipStation fulfillment record. A customer's entire journey from ad impression to purchase to shipment is linked in a single queryable path.
- Polymorphic entity graph — Flexible data schema that models relationships between entities across platforms. Products, orders, customers, shipments, campaigns, and inventory records are all nodes in a graph that can be traversed in any direction.
- 12+ service objects — Each API integration has dedicated sync services handling incremental updates, conflict resolution, and error recovery.
Natural Language Query Interface
With the data unified, I built a natural language query interface that lets anyone on the team ask business questions in plain English. The system translates questions into structured SQL, executes them against the unified data layer, and returns visual answers — no technical knowledge required.
LLM-Generated SQL translated from user queries referencing a comprehensive business ontology
- Claude isn't making assumptive leaps, I created a comprehensive ontology covering the full business and its logic across the database schema. Each terminology is defined as per the schema, making SQL generation seamless and not dependent entirely upon LLM interpretation.
Multi-model engine query routing with confidence scoring
- Simple lookups route through a fast path. Complex cross-platform questions route through a more thorough query planning pipeline. Every response includes a confidence score so the user knows how much to trust the answer, and the raw SQL is always one click away for verification.
Visual graphic-based outputs ensure usability, with raw data optionality
- The default response format is charts, tables, and plain-English summaries designed for the organization broadly, not highly technical staff. But every query also exposes the generated SQL, the confidence score, and the raw result set. This dual-layer approach delivers usability to the entire organization without foregoing transparency.
Outcomes
- Self-serve answers with no wait — All team members could ask a business question in plain English and get a visual answer in seconds. Questions that previously required CSV exports and days of cross-team lead time could now be resolved in moments.
- Cross-Platform customer attribution — For the first time, the company gained full customer journey visibility from the ad that brought them in, to the order they placed, to the shipment that fulfilled it..something that was completely invisible and impossible before.
- Reduced subscription costs + enabled optionality — The purpose-built analytics dashboards alone enabled replacement of multiple third-party subscriptions and bought back an average 5-10 hours a week for higher-leverage tasks. A reliable foundational layer in place also opened opportunities for further automation tailoring and bespoke solutions.