Why ratios belong at read time

Store the ratio and you have created a value that can disagree with its own inputs.

The mechanism is ordinary. A day's counts are written. A rate is computed and written beside them. Later the counts change — a platform restates a period, a late conversion arrives, a correction lands — and the counts are rewritten. Whether the stored rate is rewritten too depends on whether every code path that updates a count remembers to recompute every rate derived from it, forever, including the ones added next year.

It will not. And the failure is silent: a report containing a cost per acquisition that does not match the spend and conversions printed next to it, with nothing indicating which of the two is stale.

That is the hardest kind of bug to find, because both numbers look reasonable. Nobody divides by hand to check. The discrepancy is discovered, if it ever is, when somebody exports two reports and notices they disagree — and then has to work out not just which is right but how long it has been wrong, and which decisions were made on the strength of it.

Compute at read time and the failure cannot occur. There is one number, the counts, and the rate is a view of it. A restatement changes the counts and every rate follows automatically, because there is no second copy to fall out of step.

Counts and money, one row per entity per day, and nothing else. Spend, impressions, reach, clicks, conversions, conversion value, with the account's own currency stamped on the row. A re-sync of a Tuesday rewrites that Tuesday rather than adding a second one.

Every rate is computed when it is read, by the same function everywhere. That last clause is doing as much work as the first: a dashboard tile, a table, a computed summary and a programmatic result that each divide in their own module will eventually divide differently, and then the argument is about what cost per acquisition means rather than about the campaign.

One function, one definition, one place to change it. The alternative is a vocabulary that varies by screen.

Computing at read time forces a question that storing lets you dodge: what does a rate mean when its denominator is zero.

An account with no conversions has no cost per conversion. Not a zero — a zero asserts that conversions were free, which is a cheerful and false claim about the world. Not infinity either, which asserts a disaster. The honest answer is that the ratio is undefined, and the honest rendering is a blank with an explanation available.

A stored-rate system tends to write a zero there, because a column has to hold something and zero is the default. And a zero propagates: it gets averaged with real values, dragging a blended figure down for no reason, and it gets compared against a target, where it looks like the best performance in the account.

Reading a blank as a nought is the single most common way a report ends up lying to the person holding it, and a system that stores zeros is a system that helps. The one worth having refuses: results say explicitly when some ratios are undefined, so an agent reading them is told the same thing a person is.

Once you accept that a derived value should not be stored, a few other things follow.

Money is an integer in the account's own minor units with its currency beside it, never a formatted string and never a float. How many minor units make a major one is the currency's business rather than a universal hundred, which is why a figure without its code is not a figure.

Nothing converts on the way in. A total spanning several currencies is converted when it is read, labelled an estimate, and stamped with the date of the oldest rate the conversion leaned on — and where a rate is missing, the total is refused rather than invented. A stored converted amount would inherit the drift problem with a stale rate date welded on.

And a ranking across currencies that cannot be compared comes back in raw order with that fact attached, because a raw order is not a ranking and the top row is not the biggest spender.

Store what was observed. Derive what follows. The observed thing cannot be wrong in a way the derived thing hides, because there is no derived thing sitting in a table waiting to be believed.

The cost is real and small: a division per cell, and a discipline about not caching the answer somewhere convenient. The benefit is that a whole category of bug — the one where two numbers on the same page cannot both be true — has no way to occur.

The change request is the same instinct applied to writing rather than reading: keep what was decided, derive what follows. A connection reads and alters nothing. A change is stored as a record carrying the value it would replace and the checks it cleared, and it is cleared by you or by a rule you wrote. What the record cannot promise is arrival — no platform write reaches a customer account until that platform has granted write access, and the applying of a cleared change is in private beta.

Keep the primary thing and derive the rest. It is the same argument both times.

The numbers your ads dashboard rounds off is what happens when a system makes the other choice, and Currency and FX in ad reporting is the practical version.