Why an LLM lies confidently on correct numbers: metric semantics in AI analytics

"Rewrite your subject lines — the open rate is zero." The advice looked sensible and the number under it was real: opens 0. The model got neither the arithmetic nor the parsing wrong — it was handed a number that means something else. The zero did not mean "nobody opens the emails", it meant "nobody measures opens": the field is written once when a message is sent and never updated, because the product has no open tracking at all. The advice came out coherent, confident and invented — and nothing in the text shows it.

Why we wrote this

We could have written "we bolted AI onto our analytics". There are a hundred such texts and they are indistinguishable from one another. The thing worth writing about was a different one: when we connected product metrics to a language model, almost all of the pain turned out to be not in the model, not in the prompt and not in the choice of provider, but in numbers that were technically flawless and meant something other than what the code reading them assumed. That defect is caught by neither a crash nor a schema check: the model does not complain, it produces coherent, confident, wrong advice that is indistinguishable in the text from the right kind.

"Wire product analytics into an LLM" is a common task now, and a walkthrough of these particular rakes is almost nowhere to be found. So this article is not about architecture or model choice, but about six concrete cases, every one of which happened to us, and about how we closed each. Plus the uncomfortable part: two defects were caught by tests, one made it to production, and why that happened.

What the system is. Cross-channel AI recommendations inside a marketing platform: once a day the service collects figures from five sources into one JSON, puts it in a prompt, and gets back cards of advice. No ML knowledge required — there is none in this article; API and SQL knowledge is assumed.

Collect, normalise, analyse

Three steps. Collect — five sources write dated rows into the database. Normalise — turn those rows into numbers that mean exactly one thing. Analyse — one JSON goes into the prompt, the answer is parsed into cards. Collection and analysis are written once and then barely change; all six defects live in the middle.

The row shapes differ between sources, and that is not an integration detail — it is where the errors grow from.

TikTok, through the Display API, returns lifetime counters only: a row means "total since the beginning of time as of this date", not that day's result.

Instagram — only reach has a daily series, the rest of the metrics arrive as a single value for the whole period.

Google Play — a CSV export from cloud storage, where days without data are filled with placeholder zeros.

Search Console — six requests to Google at the moment our endpoint is called, cached for an hour; its window stops two days short of today.

Our own tables — visitors, the funnel, UTM tags, keyword ranks, emails.

On top of that sit plan-based throttling and a separate cron cadence per channel, so even "today's" rows in the database are of different ages.

flowchart TB
  subgraph C["1. Collect"]
    TT["TikTok<br/>lifetime counters only"]
    IG["Instagram<br/>daily series for reach only"]
    GP["Google Play<br/>CSV export, missing days filled with zeros"]
    GS["Search Console<br/>on demand, window stops 2 days early"]
    OW["Own tables<br/>visitors, funnel, UTM, ranks, emails"]
  end
  subgraph N["2. Normalise"]
    K{"Which kind of number is it?"}
    FL["Flow<br/>summed strictly inside the window"]
    LV["Level<br/>last known value,<br/>not bounded by the window"]
    RA["Rate<br/>last known value,<br/>never summed"]
    RL["Reading rules:<br/>null is not zero, rank into positions gained,<br/>reporting lag carried as data,<br/>unmeasured metric carried as a flag"]
    K --> FL --> RL
    K --> LV --> RL
    K --> RA --> RL
  end
  subgraph A["3. Analyse"]
    J["One JSON digest"]
    P["System prompt:<br/>half of it is how to read the numbers"]
    M["Model"]
    V["Strict response parsing:<br/>an empty result never overwrites good cards"]
    J --> P --> M --> V
  end
  TT --> K
  IG --> K
  GP --> K
  GS --> K
  OW --> K
  RL --> J
  FA["Source failure: connected with null,<br/>not zeros"] -.-> J

Three kinds of numbers: flow, level, rate

Every metric is one of three kinds, and the kind answers two questions at once: how to compute it, and which rows may be read while doing so. The second question is easy to lose sight of — and that is where we broke.

Flow — installs per month, post views, clicks. Computed as a sum, and only rows inside the reporting window may be read: outside it, that is a different period.

Level — followers, active device base, average rating. Computed as the last known value, and the whole history has to be readable: a level does not cease to exist because the window moved.

Rate — crash rate, CTR, engagement. Also the last known value; adding percentages up is meaningless.

What follows are six cases where the distinction was either not drawn at all, or drawn correctly and applied to the wrong question. First, briefly, what came out the other end:

Trap 1. Lifetime counters

What the data looks like. TikTok returns a running total. If the account has 1000 views, tomorrow the row says 1200, the day after 1250 — and every row contains the entire history, not the day's increment.

What came out of it. Summing those rows adds up thirty copies of the whole account history rather than thirty increments. A month whose real growth was a few hundred turned into tens of thousands, and the model wrote about explosive growth and advised doubling down on the channel.

How we fixed it. The figure for a period is the last snapshot minus the first, not the sum of the rows. One caveat about history: snapshots begin on the date the account was connected, and nothing exists before that. So if an account was connected mid-window, the honest answer is growth since the connection date rather than since the window opened; there is nothing to backfill from, because TikTok does not hand back past days.

Instagram has the same trap from the other side: since only reach has a daily series, summing the remaining rows adds the same period value several times over. So engagement is computed from post rows rather than account snapshots: a post has a publish date and its own reactions — an honest flow inside the window.

Trap 2. Search rank

What the data looks like. Rank is one of the rare metrics where lower is better: first place is 1, twentieth is 20.

What came out of it. The pair "was 20, now 4" with no explanation reads as a decline: the number went down, so things got worse. The model sympathetically offered to rescue promotion work that had just succeeded.

How we fixed it. The digest carries positions gained rather than the rank itself: a single +16 instead of a pair. The direction is baked into the quantity, so there is nothing for the model to guess. Plus a line in the prompt saying that a smaller rank number is better.

CTR scale is fixed in the same place. Search Console returns a fraction, 0.032, while the neighbouring digest block built from our own tables holds percentages, 3.2. The same field in two blocks at two scales is a ready-made — and arithmetically flawless — "CTR dropped a hundredfold". The fix is trivial: normalise everything to percent on the way in, before the two numbers ever meet in one JSON.

Trap 3. A metric that is always zero

What the data looks like. The email stats field is written once at send time and never touched again. Opens are not tracked in the product at all.

What came out of it. The case from the top of this article: confident advice to rewrite subject lines for an open rate nobody measures.

How we fixed it. Do not substitute a zero, do not substitute an industry average, do not send a number at all. The email block carries an openTracking: false flag, and the prompt explains what that flag means. After that the model gives the one appropriate piece of advice — start tracking opens — instead of treating a problem nobody has observed.

Trap 4. A level read inside the window

The most expensive of the six, and the only one the tests did not catch.

What the data looks like. The Google Play export for one project had stopped a month before the reporting window. Only placeholder rows were left inside the 30-day window:

What came out of it. The average rating resolved correctly to 5: the "walk back through the zeros and take the last non-zero" logic did what it was designed to do. The active device base resolved to 0 — its non-zero readings, going up to 15, lay just before the window boundary, and inside the window there were none at all. The model was handed a live app with zero users and answered exactly as one should answer that: advice to launch something that is already published.

Where the mistake was. The flow-versus-level distinction had been drawn here, and drawn correctly. It decided the method: a sum for a flow, the last value for a level. But the row selection stayed shared between both cases — from the window. So the code was asking "the last value among the window's rows" when the right question is "the last value, full stop". The rule was right; its scope was not. It is hard to spot precisely because the rule is already written and looks correct.

How we fixed it. The last-value lookup for a level is no longer bounded by the window: it runs over the metric's whole available history and returns the last non-zero measurement, however many days back it sits. The window stayed where it belongs — on flows.

Trap 5. Reporting lag

What the data looks like. Search Console does not return the last two days: on Google's side they are not finalised yet.

What came out of it. Put search clicks and site visitors side by side for the same dates and a hole gapes at the end of the period. In the numbers that is a search-traffic collapse, and the model duly wrote about one — while the traffic had gone nowhere.

How we fixed it. The lag cannot be hidden, but it can be carried as data. The search block ships a lagDays field — how many days short of today the window stops — and the prompt explicitly forbids comparing those two series by date and reading the gap as a collapse.

Trap 6. null and zero

What the data looks like. Every place where we substituted a 0 so the JSON schema would not break.

What came out of it. Zero is a measurement result, null is the absence of one. Once they are collapsed into one field they can no longer be told apart, and the difference between "we measured, and it is zero" and "we did not measure this" flips the conclusion.

How we fixed it. A rule with no exceptions: unmeasured travels as null, and the prompt says null means "we do not know", not "zero". Trap 3 is a special case of this rule taken as far as a dedicated flag; the degradation section at the end of this article is the same case for a network failure.

All six fixes in one place

What all six have in common is worth stating on its own: not one of the fixes is about the model. Neither a better prompt nor a smarter model repairs any of these cases. In every one of them exactly one thing changes — which number goes into the JSON.

Where this belongs in the code

First: all metric arithmetic moved into pure modules with no database access. Rows in, a finished digest block out. The point is not architectural beauty, it is that such a function can be tested for semantics rather than for arithmetic. The useful test here is not "the sum of three numbers equals their sum" but "two identical readings are a measured zero growth, one reading is the absence of a measurement, and those are different answers".

Second: roughly half of the system prompt is not the task, it is rules for reading the numbers. Not "give marketing recommendations" but an explanation of what the JSON fields mean. Three of those rules, verbatim:

Not one of them was written out of foresight. Each appeared after we read the corresponding confident wrong answer.

Third, smaller: lists in the digest are trimmed to five entries so the prompt does not bloat; a channel's accounts are aggregated; the model's response is parsed strictly — tolerating triple-backtick wrappers, normalising enum values, and never letting an empty answer overwrite cards that are already good.

What the tests did not catch

Two of the six were caught by unit tests of the pure modules — exactly the semantic checks described above. Trap 4, the device base, surfaced only on live production data.

The reason is not laziness. The mocks were written by the same person, with the same understanding of the domain, as the code. Nobody invented a mock whose metric history starts before the window, because inventing one required already knowing that the window boundary mattered. A mock does not test an assumption it shares.

One detail is instructive on its own. A test for this block existed and passed, but it was blind: the mock returned the same array of rows for two different queries — reading the window ascending by date, and looking up the last value descending. It could not tell them apart, so the check passed against any implementation, including the broken one. The weak link was the mock, not the assertion. This is the case where a green test is worse than a missing one: it occupies the place where a real check could have stood.

The practical takeaway is simple: if a mock answers several different queries, it has to answer them differently. Otherwise it is testing itself rather than the code.

Degradation: why null is the honest answer to a failure

Search Console is the only source that goes over the network during the request, and it does so with six calls at once. It sits behind an eight-second timeout, and a failure has three different outcomes rather than one: no integration — we do not call at all; half-configured — the block is marked not connected; failure or timeout — the block stays connected, but its numbers are null. Every digest block is assembled independently, so a failing source takes down only its own section, not the recommendations as a whole.

That third outcome is trap 6 extended to failures. Zeros during a Google outage look like a traffic collapse, and the model starts rescuing traffic that is perfectly fine. Null means "we do not know", and that is the only true thing to say about a source that did not answer.

What changed in the answers

What is telling is not that the recommendations got longer, but that before and after are texts about different things. Before: "the absence of keywords and competitors indicates a weak SEO strategy" — a sentence that fits any project, because it rests on no number at all.

After, on the same project: 89 visitors with zero conversions; Instagram has both higher engagement and higher follower growth than the other channels; TikTok has many views at low engagement; average rank 25.1, with some queries at the edge of the top 10.

The difference is not the model's eloquence or the size of the prompt. It was simply given numbers that mean one thing each.

The takeaway

If there is one thing to carry away: a language model does not check the meaning of the numbers it is handed. It checks the coherence of its own text — and it does that well. So a semantic error in the data surfaces not as a failure but as confident advice, and the only place to catch it is where the number is produced, not where it is read.

Decide what kind of metric it is before you compute it. Flow, level or rate determines both the formula and which rows may be read. The second half is the one people forget, and it is the more expensive one.

Never substitute a zero for "we do not know". Zero is a measurement result. The absence of one is null, or a dedicated flag — and the prompt has to say what that flag means.

Put reading rules in the prompt, not just the task. Half of our system prompt explains what the JSON fields mean, and every line of it was written after a specific wrong answer rather than out of foresight.

Test semantics in pure modules — and do not trust the mocks. A mock written by the same person as the code shares that person's assumptions. That is exactly why one of the six defects survived all the way to production.

To be fair about the weak spot of all this: it is not about machine learning, it is about data hygiene. But data hygiene is what decides whether the model's answer is useful or merely smooth — and it happens before the model gets involved.