- plain English to charts
- schema probed, not assumed
- ratios computed as ratios
- deltas only with coverage
The problem
I worked on an AI analytics assistant for online stores. A store owner asks a question in plain English ("how did sales do last month?") and gets a chart they can keep on a dashboard. The dashboard is the home, the assistant is a rail beside it, and the only thing that persists is a tile.
The hard part was not drawing charts. It was this:
A wrong guess about the store's data does not error. It returns a plausible number.
A model writing SQL has to assume a schema. Every wrong assumption produces a number that looks fine, sits in a tile and gets believed. When I checked the assistant against a real store, I found several silent misreports with one shared cause:
- Which orders count as paid. The model assumed the standard statuses. The store used custom fulfilment statuses, so part of its revenue simply vanished.
- What "gross sales" means. The column the model reached for included shipping, so the tile read well above the owner's own figure.
- Where order details live. Depending on the storage mode, order metadata sits in one of two tables. On the wrong one, joins quietly returned nothing.
- How subscriptions work. Status names and renewal shapes differ per store, so recurring revenue and churn were invented.
Probe the schema, don't prompt about it
You can't fix this with a longer prompt, because the right answer is different for every store. So the fix was architectural: before answering, the system probes the actual database and tells the model what it measured.
question ──▶ probe store ──▶ measured shape ──▶ model writes SQL ──▶ run ──▶ chart
(statuses, storage mode, subscriptions, coverage)The probe reads which order statuses exist instead of hardcoding paid ones. It finds the storage mode and names the right table for order metadata. A third step runs only once the storage mode is known and reports whether the store has subscriptions, their real status names and how to compute recurring revenue from them.
The probe also reports coverage. That lets the model say "I can only see 3 of the last 30 days" instead of answering anyway.
Later I applied the same idea across surfaces. Every place that totalled sales had its own idea of a sale, so the numbers disagreed with each other. Now there is one written rule for what counts as a sale, and every surface and every prompt uses it.
Ratios are not sums
Even with correct SQL, the numbers on screen could be wrong. Every panel headline summed its metric across time buckets. That is right for sales and order counts. It is badly wrong for an average, a rate or a margin, because it adds up each day's average.
With illustrative numbers: 30 days, 3,000 in sales over 100 orders. The real average order value is 30. Summing thirty daily averages of about 30 shows roughly 900.
The fix separates additive measures from ratios. A ratio is computed once over the whole window, numerator over denominator, never per bucket and then added.
// additive: sum the buckets. ratio: divide the totals.
const headline = metric.kind === 'ratio'
? sum(rows, metric.numerator) / sum(rows, metric.denominator)
: sum(rows, metric.value)Deltas need a comparison that can support them
A panel compared this month with the same month last year and showed a large drop. The current window had data on almost every day. The comparison window had data on three, because the store was new. The badge was comparing a month with a few days, and nothing on screen said so.
Now a delta is suppressed when its comparison window doesn't have enough coverage to support it. No number is better than a confident wrong one.
The same thinking fixed two smaller lies:
- Invented curves. An hourly average with mostly empty hours was drawn as a smooth line, implying data that didn't exist. Sparse average series are now drawn as points.
- Partial months. A month-bucketed tile that started mid-month showed a full month label over part of a month. A strict refusal rejected too many valid tiles, so it became a warning on save.
When the assistant saves a tile, the chat re-reads the saved result by id instead of showing numbers the model typed. A card built from model output would look the same whether or not anything was saved.
The assistant later grew live tiles fed by MCP tools. That part is covered in agents on MCP.
What I'd do differently
- Probe first, from day one. I added the probe after finding misreports. It should have been the starting point.
- Type every metric at definition. Marking each metric as additive or ratio, and its unit, up front would have prevented the summed averages entirely.
- Make coverage visible everywhere. Not just in suppressed deltas, but as a quiet note on every card.
Takeaway
An analytics AI fails silently, so it has to be built to be right, not just to sound right. Measure the store instead of describing it, compute ratios as ratios, and refuse to show a comparison the data cannot support.