First, restate the question in words and reconcile the figure against a known total
Start by writing down the question the figure was supposed to answer, in plain words and in four parts: the measure (what is counted), the filter (which records are in and out), the period (which dates, in which time zone) and the grain (per customer, per order, per session, per month). Do it before opening the query, because the site's analysis guide makes the point that plain-language querying answers the question as the model understood it, and the table it returns does not say which choices it made. Then ask the tool to restate, in the same four parts, what it counted, from where, over what period and with what filters. Where the two statements differ, you have found the first cause; where they agree, the fault is further down, in the formula, the query or the data.
Next, reconcile. Take the overall total behind the figure, with no breakdown, and compare it with a total obtained another way for the same scope: the finance ledger, the booking or order system, the ad platform's own report, or a simpler query somebody trusts. The SQL tutorial lists the usual reconciliation pairs, and the shape of the gap points at the cause. A total above the independent one usually means rows were repeated; a total below it usually means a filter removed rows the definition includes; a total that matches while a breakdown does not usually means the dimension, not the measure, was defined differently. Google Analytics documents a plain-language search box that returns a report or figure for the property, and as the analysis guide states it, the Google Analytics help centre is the record of what that box can and cannot answer; the same rule applies to that box as to any assistant, which is to confirm the dimension, metric and date range it chose before comparing its figure with anyone else's.
Carry one mismatch back to its cause with the five whys. The retention figure is too high because the numerator is too large; it is too large because renewals were counted rather than customers; renewals were counted because the query joined contracts to invoices and counted rows; it joined them because the prompt described the tables without saying what one row represents; the prompt said nothing because there is no schema note for the model to be given. The fix sits at the last why, and the prevention sits one step further back, in the note that every future prompt receives. Treat the trace as evidence rather than opinion: each answer in the chain should be something you can show in the query, the sheet or the log. The applications section covers analysis among the other uses of AI; this page starts once a figure is already disputed.
Diagnosis table
Diagnosis
| Symptom | Check | Likely cause | Fix |
|---|---|---|---|
| The figure is tidy and plausible, and the breakdown or total differs from the dashboard's for the same words | The tool's restatement of measure, filter, period and grain against the question as written | The query answered a different question from the one asked: another definition, filter or period | Write the definition first, point the tool at the governed definition and run the question again |
| A spreadsheet or dashboard figure moves when rows are added, or a rate differs by where it is read | The formula's ranges, lookups, blank handling, dates and the level the ratio is computed at | A generated spreadsheet formula or calculated field that returns a number but counts the wrong thing | Read the formula piece by piece, recompute a sample by hand and reconcile to a known total |
| The total is above the source system's, or below it with no reason, or channels sum to more than the whole | Row counts against distinct counts at each step; key uniqueness on each side of every join; each filter's position | A generated query whose joins double count or whose filters silently drop rows | Aggregate to the join grain before joining, move filters to where they belong and reconcile |
| Two reports with the same name disagree, and each is right by its own rules | Where the definition for the measure lives, and whether the tool and the dashboard read it | No shared metric definitions, so the model and the dashboard each define the measure their own way | Write the definition into a metrics layer with formula, grain, filters and owner, and make both read it |
| The figure reached a deck, a client or a decision with no record of who checked it | The review tier the figure's use deserved, and the log of what was checked | The output never verified against the source before it was shared | Reproduce the figure independently at the tier its use demands, and record the sign-off |
| The figure rests on a handful of events or a period cut short, and swings between runs | The count beneath the rate, the period's boundaries and whether the data had finished arriving | A figure read from a small sample or a partial period presented as a result | Show counts beside rates, pool or lengthen the window, and mark provisional periods |
Fix, verify and prevent each cause
The query answered a different question from the one asked
The commonest cause has nothing to do with arithmetic. A model translating a question into a query has to choose which table holds the data, what counts as a conversion or a customer, which date range and time zone, whether sessions or people are the unit and how channels are grouped, and the AI for analysis and reporting guide names each of those choices as a place where the tool can pick something reasonable that is not what the asker meant. The result is a confident answer to a neighbouring question: sign-ups where paying customers were meant, last-touch where first-touch was agreed, a month that includes the current partial one.
Fix: write the definition in the organisation's own terms before asking again, give it to the tool with the question, and where a governed definition exists point the tool at that rather than at raw tables, so it inherits the meaning instead of inventing one.
Verify: ask the tool to restate what it counted and compare the restatement with the definition line by line; then reconcile the total to the independent figure and write down the explanation for any gap.
Prevent: keep a short log of every mismatch found this way, because the analysis guide treats that log as the list of standing instructions every future query receives: which definitions, which tables, which time zone, which touch model.
A generated formula or calculated field counts the wrong thing
A formula that returns a sensible-looking number is the trap the tutorial on checking AI-written formulas and calculated fields is built around. The failures it lists are silent: a range that stops at the last sample row so new data is left out, a reference that slides when the formula is copied, a lookup that returns the nearest key instead of an exact one, blanks counted as zeros, a text date compared as a word, and a rate averaged across rows where a ratio of totals was meant. Looker Studio's help centre documents calculated fields, and the tutorial applies the same checks to a dashboard field as to a sheet formula, with the central rule that a ratio is an aggregated numerator divided by an aggregated denominator at the level it is reported.
Fix: read the formula piece by piece against the written definition, check every range, reference, lookup, blank rule and date boundary, and rebuild any ratio from totals at the reported level.
Verify: recompute a few rows, or one location and one month, by hand from the raw values, compare with the formula's result for the same slice, then compare the overall total with the known figure gathered before you started.
Prevent: keep a reference row whose correct figure is known by hand and check it after every edit, save the formula beside its definition and the checks run, and mark it for re-checking when columns, sources or the definition change.
A generated query double counts or silently drops rows
When the total is above the source system's, the tutorial on checking AI-written SQL has fan-out as its first suspect: a join on a key that is not unique on one side repeats the rows on the other, so orders joined to a touches table with several rows per order are counted once per touch, and customers joined to invoices are counted once per invoice. When the total is below the source's, the suspect is a filter: a condition on the right-hand table of a left join placed in the wrong clause turns it into an inner join and quietly removes the records the definition includes, and a test or cancelled flag applied too broadly does the same. Both run cleanly and return a tidy table.
Fix: write down what one row represents at each step, check key uniqueness on both sides of every join, aggregate each side to the join grain before joining, count distinct identifiers where the definition counts people or orders, and move each filter to the clause where it does what the definition says.
Verify: compare the row count with the distinct count at each step, compare the count before and after each join, and reconcile the final total to the independent figure; an unexplained gap means a step above is still wrong, so go back rather than adjusting the result.
Prevent: save the verified query with its definition, tables, checks and reconciliation result so the next person starts from it, and add every quirk the trace uncovered, such as a table with several rows per order, to the schema notes the model is given. The checklist for reviewing AI-written code gathers those grain, join and reconciliation checks into one gate before the answer leaves the team.
No shared metric definitions
Two tools asked for last month's conversions return two numbers, and the metrics layer and definitions guide explains that neither is wrong by its own rules: they differ in counting unit, attribution rule, date assignment, time zone, filters and formula, and nobody wrote down which one the business meant. An assistant is a third tool making the same choices, so without a shared definition the model and the dashboard each define the measure their own way and the argument has no referee.
Fix: write the definition once, with its formula, grain, baked-in filters, time rule, owner and known differences, into a metrics layer or the modelled tables that implement it, and make the dashboard and the tool read from it rather than from raw tables.
Verify: run the same question through the dashboard and the assistant against the layer and confirm they agree; then ask the owner to approve the definition, because a definition nobody owns belongs to whoever edited the dashboard last.
Prevent: version every definition and log every change with the person and the reason, so a renamed field or a widened window cannot change a number silently, and treat a dashboard or a generated query found computing its own version of a defined metric as a defect rather than an alternative.
The output was never verified before it was shared
Sometimes the query was wrong and the real failure is that nothing stood between the table and the deck. The accuracy and review workflows guide sizes review to the consequence of error, with internal and reversible output read only by the person using it and published output checked against its sources with a recorded sign-off; the analysis guide maps AI-produced figures onto those tiers, with figures that move budget or reach a client or a board reproduced independently at the top. A figure shared without that step is not a verified number, however often it has been repeated.
Fix: assign the tier the figure's use deserves, reproduce it from the source by following the stated query or steps, and record who checked it, when and against what.
Verify: a second person, not the one who prompted the tool, works through the routine for reviewing an analysis before it is shared and arrives at the same figure; if the number has already gone out, the tutorial on correcting a number after a report has gone out takes over, from sizing the error to telling the people who acted on it.
Prevent: make human reviewThe step where a named person checks AI-assisted work for accuracy, sources, originality, privacy and disclosure before it is published or sent. at the right tier a condition of any generated figure leaving the team, keep the sign-off in a log that records tier, prompt version, reviewer and defects found, and read that log monthly so a rise in defects after a model or prompt change is noticed.
A figure read from a small sample or a partial period
A generated figure can be computed correctly and still mislead, because the count beneath it is tiny or the period is not finished. The guide to reading small numbers explains why rates built on a handful of conversions swing between periods with nothing changed and why the extremes of any ranking come from the smallest units, and the metrics layer guide adds that platforms restate recent days as late data arrives, so a period read before it has finished is provisional. An assistant asked for the best-converting campaign will happily return the one with three orders, and an assistant asked for this month will return a month that is half over.
Fix: put the count beside every rate, pool similar units or lengthen the window until the count can carry the decision, and mark any period the source restates as provisional.
Verify: read the figure as a range rather than a point, compare it with the metric's own stable baselineAn estimate of what a metric would have done without a given change, campaign or promotion, against which the effect is measured. and normal spread, and check whether the decision would change at the other end of the range.
Prevent: add minimum volumes and a flag for low counts to the reporting layer so every generated figure inherits them, write "too few orders to tell" as a finding where it is one, and when the measure is a retention or repeat rate, state the cohortA group of customers who share a starting event and period, tracked together over time. it was read on, because a rate on a cohort that is still being formed is not a result.
Prevention: definitions in one place, a reference row for every formula and a verification step before sharing
Every cause above is a choice the tool made that nobody had made for it. The prevention routine is therefore mostly about making the choices once, writing them down and putting them in front of the model before it writes anything. The first part is the metrics layer: one definition per measure, with formula, grain, filters, time rule, owner and known differences, that the dashboard reads and the assistant is pointed at. Where the organisation has no layer yet, a dictionary and modelled tables do the job, as the metrics layer guide says; what matters is that retention, new customer and conversion each mean one thing, that the meaning has an owner, and that a change to it is a versioned change with a log line rather than an edit. A KPIA metric tied directly to an objective. that reaches a board should be a defined one, read from that layer, and any generated figure claiming to be it should reconcile to it.
The second part is a reference row for every formula and a reconciliation for every query. Keep a customer, a location or a month whose correct figure is known by hand, and check it after every change to a sheet, a calculated field or a saved query. Keep the independent totals the reconciliations use, finance's revenue, the order system's count, the platform's spend, in a known place with their own dates, so the comparison is a lookup rather than a search. Save every verified query and formula beside its definition, the checks run, the result of the reconciliation and the name of the person who checked it, and mark each for re-verification when the schema or the definition changes. A library of verified queries is what lets plain-language querying be handed to more people, because the hard choices have already been made and proved.
The third part is a stored test setA collection of real inputs for an AI task, each noting what a good output must contain, run to compare prompts or models and rerun when they change. of questions. Keep the questions the team asks most, each with the answer the layer gives for a fixed period, and rerun them whenever the prompt, the model, the vendor or the schema changes, comparing the answers with the recorded ones before anyone uses the tool for new work. The failure this catches is the quiet one: a model update that starts choosing last-touch where it used to choose first-touch, or a renamed column the tool now guesses at. Add to the set every mismatch the five whys uncovers, so the test grows from real failures rather than invented ones.
The fourth part is the verification step before sharing, sized by tier. Internal orientation can rest on reading the query; a figure that moves budget or leaves the team is reproduced by someone other than the person who prompted the tool, and the sign-off is recorded. Automated checks cost little and belong on every tier above internal; on the modelled tables the metrics layer guide names them: totals that reconcile to the source within a stated tolerance, unique keys at the stated grain, and a comparison of each certified metric with its previous run so a sudden move is flagged before a viewer sees it. The weekly habit that holds it together is a short read of the review log and the reconciliation gaps, with any unexplained gap treated as a defect that day. When the same tool is producing invented facts in prose rather than wrong figures in tables, the diagnosis for AI content that contains invented facts separates those causes. When an assistant is answering customers wrongly rather than analysts, the diagnosis for an AI chatbot giving wrong answers applies. The AI troubleshooting section lists the neighbouring symptoms.
A furniture rental firm finds a double-counted join (illustrative)
Varnishold Furniture Rental
Varnishold Furniture Rental, an invented office furniture rental company with 1,240 customers holding active contracts at the start of the quarter, presents a quarterly retention figure of 91% that an assistant produced from the warehouse. The finance team's figure for the same quarter is 74%, and the two sit in the same board paper.
Restating the question shows the first gap. The assistant counted contracts renewed in any month of the quarter, 1,061 of them, against customers active at the start, while finance counts customers still renting at the quarter's end. Many customers hold several contracts, so the numerator was never the same kind of thing as the denominator. The reconciliation shows a second gap: the generated query joined contracts to invoices to pick up the renewal date, and 67 customers with two invoices in the period were counted twice, taking the row count to 1,128.
Rewritten against the definition the firm now writes into its metrics layer, retention is customers active at the start of the quarter who hold at least one active contract at its end, counted as distinct customers. The corrected query returns 923 customers, or 74.4%, and the gap of 5 against finance's 918 is explained by customers who renewed and then cancelled in the final week, which finance closes out of the count and the warehouse records a day later.
The firm keeps one reference customer with two contracts and three invoices in the quarter, whose figure is known by hand to count once, and checks it after every change to the query. It adds a reconciliation against the finance total to every generated query before the result leaves the analytics team, stores 14 test questions with their answers for a fixed quarter, and reruns them after each prompt or model change. The next rerun, two months later, catches a renamed contract-status column the tool had started guessing at, before any figure reaches a paper.
Common mistakes
- Comparing the figure before restating the question. Two numbers for different questions will never reconcile, however many times they are re-run.
- Trusting the tool's explanation over the query or formula. The explanation can describe the query the model meant to write.
- Adjusting the result to close an unexplained gap. A gap with no reason means a step is wrong; fix the step.
- Letting the tool define the metric. Definitions live in the metrics layer, with an owner, and the tool is pointed at them.
- Reading a rate without its count. A figure on a handful of events or a half-finished month is a range, not a result.
- Sharing a generated figure at the wrong tier. A number that moves budget is reproduced by a second person and signed off.
- Fixing the number and not the prompt. Without a schema note, a saved query and a rerun test set, the next request repeats the same choice.
