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

Description and use

The author explains how to close a monthly dataset, detect and fix a normalization bug, and implement rules for accurate aggregation, denominator disclosure, and story limits.

Problem

Ensuring monthly audit reports are based on fully closed, accurate datasets and preventing misleading aggregates caused by data artifacts.

Outcome

The author discovered a normalization bug that inflated audit counts; by enforcing strict closing rules and aggregate safeguards, they eliminated misleading numbers and improved report integrity.

Workflow

• 1. Wait until the last day of the month to close the dataset. 2. Verify that all data for the window is present and no rows are pending. 3. Count total rows and distinct domains; compare the ratio to expected values. 4. Apply a DISTINCT ON query to keep one observation per domain per window. 5. Compute aggregates using count(score) as the denominator for averages. 6. Publish each figure with its denominator. 7. Enforce a minimum sample size before reporting a segment. 8. Cap the number of derived stories per edition. 9. Exclude runaway rows, self‑audits, single‑domain outliers, and any figures that cannot be recomputed from the closed window.

Evidence

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.

Source

86.9% of the rows in the audit table were produced by that loop.

Source

No numerical findings before the data exists.

Source

Distinct entities, not rows. Every aggregate counts distinct domains, never raw rows.

Source

Publish the denominator. Every figure ships with the count behind it — and it has to be the count of values that fed that figure.

Source