How I close a monthly dataset, and the month I found out mine had been lying

I publish a monthly report from my own audit data. First of the month, one edition, findings only from the window that just closed.

The rules that make it publishable are almost entirely rules about what gets thrown out. I learned most of them the expensive way, and one of them by discovering that a number I had already published was computed from a table where the majority of rows were an artifact of a bug.

The rule that came first

No numerical findings before the data exists.

That sounds too obvious to write down. It isn't, because the pressure runs the other way: you have a publication slot on the first of the month, an outline, a headline shape you like, and a dataset that hasn't closed. It is very easy to write "early data suggests" around a number you don't have yet and backfill it.

So the rule is mechanical. The window closes on the last day of the month. Nothing gets written before that. If the data doesn't support a section, the section doesn't ship — not "ships with a hedge."

The hedge is the tell. "Early signals indicate", "we're seeing", "trending toward" — every one of those is a sentence written before the number existed, and I have yet to write one that turned out to be worth keeping.

The month the dataset was lying

I found a normalization bug: two spellings of the same URL living as separate rows, and a scheduled job that read one and wrote the other, so it re-audited forever. The mechanism has its own post earlier in this series. What matters here is the number that fell out of the cleanup.

86.9% of the rows in the audit table were produced by that loop. One domain alone had 1831 rows; after the fix and the merge it had 19.

Every aggregate I had computed from that table was weighted by which domains happened to be caught in the loop. Not slightly wrong — wrong in a way that has no relationship to the question I was asking, because the loop's victims were selected by URL spelling, which correlates with nothing.

I had already published from it.

What that added to the rules

Distinct entities, not rows. Every aggregate counts distinct domains, never raw rows. If one domain can contribute 1831 rows to a mean, the mean is that domain's opinion.

-- one observation per domain per window, most recent wins
WITH per_domain AS (
  SELECT DISTINCT ON (domain_id) domain_id, score, created_at
  FROM reports
  WHERE created_at >= :window_start AND created_at < :window_end
  ORDER BY domain_id, created_at DESC
)
SELECT count(*) AS domains, count(score) AS scored, round(avg(score), 1) AS mean_score
FROM per_domain;

DISTINCT ON with a matching ORDER BY is the whole guard. Drop the ORDER BY and Postgres gives you an arbitrary row per domain, which is a different and much subtler bug than the one I was fixing.

And note there are two counts in that select, which is not padding. count(*) counts domains; avg(score) silently skips NULLs. Run it on a window where three domains produced a score and one didn't, and you get domains = 4 next to a mean computed from three values. Publish the first number as the denominator of the second and you have overstated your sample in exactly the way this whole post is about — with a query I wrote for this post, which is how I found it. count(score) is the honest denominator for anything derived from score.

Publish the denominator. Every figure ships with the count behind it — and it has to be the count of values that fed that figure. It's the number a reader needs to weigh the claim, and — more usefully — it's the number that would have caught my bug. A mean over "1963 reports" and a mean over "19 domains" look nothing alike, and I would have noticed the first time the denominator was two orders of magnitude off from my mental model.

A minimum n, decided before looking. Segments below it get reported as "not enough data" rather than as a number with a caveat. Deciding the floor after seeing the segments is how you end up with a floor that happens to include the interesting result.

One derived story at a time, capped. A closed dataset can produce endless spin-offs, and each one is a chance to re-slice until something looks significant. A hard cap per edition — mine is three — makes that a budget instead of a temptation.

What gets thrown out, concretely

• Runaway rows — anything from a job that didn't converge. Not de-weighted. Deleted, then the aggregate re-run.
• Self-audits. My own domains, my staging environments, the test fixtures that point at real URLs. They cluster at high scores for obvious reasons and they were in my table.
• Single-domain outliers driving a segment. If removing one domain moves a segment's headline, the segment isn't a finding, it's that domain.
• Anything I can't recompute. If I can't re-derive a figure from the closed window with a query I can paste into the report's own notes, it doesn't go in. This one kills more sentences than all the others together, and it kills the ones I'm most attached to — the ones I "remember" from looking at a dashboard.

The uncomfortable part

The bug didn't come from carelessness in the analysis. It came from the analysis trusting the storage layer, which was written months earlier by someone with different concerns — me, not thinking about aggregates.

There's no rule that prevents that. The closest I have is: before the first aggregate of a new window, count the rows and count the distinct entities, and look at the ratio. If it's not roughly what you'd expect, stop and find out why before computing anything else.

Mine was 1963 to 19 in the worst case. I never looked, because the ratio isn't a finding — it's plumbing, and plumbing doesn't make it into an outline.

The report itself goes out today, on my own site. This post is the method, not the findings — the numbers live there.