A Blank Is Not a Zero

A zero says we measured and found none. A blank says we could not measure. They render similarly, they sit in the same column, and they mean opposite things, and the moment a product decides to fill blanks with zeros to make a table look tidy, every number in that table becomes an assertion nobody can check.

This is the least glamorous property an analytics product can have and, over a couple of years, close to the most valuable. Almost every wrong number that survives review does so because it was plausible, not because it was checked. The defences against that are mostly typographical: a blank cell, a null instead of a zero, a column whose name says it cannot be summed.

Four things a missing number can mean

"There is no number here" is not one condition. It is at least four, and they have different fixes, which is the whole reason to keep them distinguishable.

We did not measure. No data was collected for this range, this dimension, or this join. Nothing is wrong; you asked a question outside what was recorded.

We measured and found none. A genuine zero. Runs happened, the thing did not occur.

We measured but cannot compute. A rate whose denominator is zero. Not a zero rate. An undefined one.

We measured and are not allowed to tell you. A value suppressed because it falls below a small-number threshold.

Zero runs is not zero percent

The clearest case, and the one that has to be handled at the type level rather than by convention.

A presence rate is mentions divided by answered runs. If there were no answered runs, the rate is not zero. It is undefined, and the two must not collapse.

This matters because zero sits at the bottom of the scale, and everything downstream treats bottom-of-scale as the worst possible outcome. Charts render it as a floor. Alerts fire on it. Somebody reads "0% presence" and starts a content programme, and the actual situation was that the scan had not run yet.

So the function that computes an interval returns nothing when the sample is empty, rather than an interval around zero. That is a slightly annoying return type to work with, and the annoyance is the point: every caller is forced to decide what to display when there is no evidence, rather than inheriting a zero by default and rendering it as a fact. It is the same reasoning behind reporting a rate as a range rather than a point: the awkward shape is doing work.

There is a related case that is subtler. Scans ran, cost money, produced answers, and no brand was configured for them to look for. Runs happened, so the denominator is not zero, but no hit was possible. Reporting that as 0% presence would be a false sentence: it claims we looked and you were absent, when nobody had said what looking meant. Those runs need their own marker: counted toward coverage and cost, excluded from any absence claim.

The columns that must never be summed

Spreadsheets have trained everyone that a column of numbers has a total. Some do not, and the ones that do not are rarely labelled loudly enough.

Distinct counts across a partition. A column recording "how many distinct engines cited this URL on its busiest day" cannot be added across days, because the same engine appears in several of them. Two days at three engines is not six engines. It is somewhere between three and six, and the underlying data to resolve it was aggregated away.

Cumulative measures down a funnel. A funnel step's revenue is the revenue of every visitor who reached that step, so a paying customer contributes at every step they passed. Sum the column and you multiply your revenue by roughly the number of steps. The drops between steps do sum; the levels do not.

Visitor counts across steps or segments. A visitor present at four steps appears four times. Distinct-counting is what makes the number meaningful and also what makes it non-additive.

Ratios of any kind. Per-visitor value, conversion rate, share of voice. Averaging a column of ratios weights every row equally regardless of its denominator, which is how a segment with eleven visitors comes to dominate a company-wide number.

The general rule is that a column is summable only if each row's value refers to a disjoint set of things. The moment rows overlap (same visitor, same engine, same money), addition stops being defined, and no amount of correct arithmetic afterwards recovers it.

The grain trap, which is the expensive one

Here is the version of this that costs real money and passes every test.

Suppose you ask an answer engine one question, once, and then score that single answer against four brands: yourself and three competitors. One API call. One cost. But if the results are stored one row per brand, four rows now exist, and each carries the run count and the cost of the call that produced them, identically, because they all describe the same call.

Now sum the cost column. Your bill is reported at four times its real value.

Every test passes. The arithmetic is correct: those really are the values in those rows. The sum really is the sum. What is wrong is the grain: the measure belongs to the call, and the rows are per brand.

This shape is everywhere once you look for it. Any time one event is annotated with several labels, dimensions, or entities, its own measures get replicated across the resulting rows. Order line items carrying the order's shipping cost. Sessions carrying a visitor's lifetime value. Crawl events carrying a page's size.

The defence is not a comment. It is one function that folds the rows, used by everything, with the per-row measures and the per-event measures separated by construction so that no caller can add them up wrongly even if they try.

Two memories, one date range

The last one is not a bug and gets reported as one constantly.

Different tables have different retention horizons, for entirely reasonable reasons: some records are small and cheap to keep for years, others are large or more sensitive and kept for a shorter window. A product that stores a rollup of citation counts and a record of individual sessions will keep the first for longer than the second, because a rollup is tiny and a session record is not.

The consequence is that a sufficiently long date range reaches past one horizon and not the other. Real citation counts appear beside blank revenue cells. The instrument has two memories and is telling you so.

Filling those revenue cells with zeros would produce a beautiful table in which pages that earned money last year appear to have earned nothing. It is the single most tempting cosmetic fix in this whole area and the one that does the most damage, because it converts "we no longer hold this" into "this did not happen," and no later correction can undo it: the evidence is gone.

The same principle applies to stored answers, which are kept for a shorter time than the rates computed from them. When a date range reaches past that, the honest behaviour is to say so and name the shorter range actually searched, rather than presenting partial evidence as though it were the whole story. Partial evidence silently labelled complete is worse than no evidence, because it is convincing.

Four questions to ask of any number

Portable beyond this product, and each one takes about ten seconds.

What is the grain? What does one row represent? If the answer is "one call, annotated four ways," then some of its columns are replicated and cannot be summed.

What is the denominator, exactly? Not roughly. A rate whose denominator includes runs the subject was never eligible for is understated by exactly the fraction it was absent, and nothing on the number's face reveals it.

Is this a distinct count? If so, over what partition, and does that partition align with how the number is being aggregated? Distinct-per-day summed across days is the classic error.

What does empty mean here? And can the product distinguish between its several meanings, or has it already collapsed them?

A tool that answers all four in its own documentation is one you can build on. A tool that answers none of them is one where you will eventually present a confident number to someone who checks it, and discover that the arithmetic was right the whole time.