Gemba AI Pipeline: LLM-Structured Store Feedback Across 60 Countries
Turning Scattered Store-Floor Feedback into Queryable Signals
A pipeline that reads what people report from the store floor — what's selling, what's missing, what customers are asking for — across 1,300+ stores in nearly 60 countries, and turns it into structured data merchandising and planning teams can query. Reports arrive as free text with photos attached, and the pipeline reads both, producing 2,500+ demand signals a day from material that used to be read by hand.
Problem
Employees working in or visiting stores report what's selling, what's missing, and what customers are asking for — through three channels: emails, an in-house mobile app, and video walkthroughs. Each channel produced feedback that merchandising and planning teams had to read and categorize by hand, with no shared structure across the three sources.
Goal: Consolidate all three channels into one structured, queryable table of demand signals — without a human opening every report.
Approach
We built a .NET batch orchestrator that drives a multi-stage BigQuery pipeline for each feedback channel, embedding every extraction and classification step as a BigQuery AI.GENERATE call directly inside the SQL — letting BigQuery itself reason over each report as part of the pipeline. Attached photos are read alongside the text rather than discarded, because the picture often carries the detail the sentence leaves out.
Structuring the input mattered as much as structuring the output. Reporters were given a four-question convention — where, when, what product, what happened — that maps onto the fields the pipeline needs, so most reports arrive already carrying what the extraction depends on.
System Architecture
Each report moves through persisted, resumable stages. Structure pulls one or more distinct pieces of feedback out of each raw report, filtering out irrelevant text — like email signatures and reply-chain clutter — that would otherwise get mistaken for real feedback. Hierarchy maps free-text product mentions onto the merchandising taxonomy. Context classifies the sentiment and topic of each item, such as high demand, a stock gap, or a quality complaint. Range validates product attributes like color and material against a bilingual reference list. A final consolidation step then joins everything against store and product master data and writes the finished record. Because every stage's output is persisted, a failed or interrupted run resumes exactly where it left off instead of reprocessing from scratch.
- Multichannel ingestion: Text and photos are pulled from three channels — emails, an in-house mobile app, and video walkthroughs.
- LLM-native structuring: Extraction and classification run as LLM calls inside BigQuery itself, turning free-text reports into 2,500+ structured demand signals daily.
- Validation safety net: A second pass cross-checks the LLM's taxonomy and hierarchy choices against canonical reference tables, silently reverting to the trusted value whenever the model's answer doesn't match.
- Correction loop: Reports missing a required field — location, date, product, or observation — trigger an automatic reply asking the sender for it, framed to staff as data-quality feedback rather than a rejection.
- Rollout and enablement: Training material shipped alongside the system taught employees the reporting convention across nearly 60 countries, so data quality was designed for at the source rather than patched downstream.
Downstream
The structured table this pipeline produces is what the Gemba AI Chatbot queries. This project makes the feedback answerable; the chatbot makes it askable in plain language.