Research Guides
Keep Row Coverage With a Website Research Total
A report that shows zero can mean a measured outcome, a missing measurement or a filter that selected nothing. If every empty result is converted to zero before the report is written, readers cannot tell the difference. Keep row coverage with the total until you know what the empty result represents.
This method is for reviewing an aggregate in a website research export. The example uses an invented dataset, not Colquhoun.shop production data or a claim about its reporting behavior.
1. Keep three values, not one
PostgreSQL's aggregate documentation distinguishes counting input rows, counting non-null field values and summing non-null values. In particular, a sum with no input rows returns null, while a count can return zero. Replacing the null is possible, but deciding what zero would mean belongs to the report's data contract.
For a single selected slice, fetch the three values together:
SELECT COUNT(*) AS matched_rows,
COUNT(clicks) AS measured_rows,
SUM(clicks) AS click_total
FROM audit_snapshot
WHERE cohort = 'sample';
The query assumes each input row is a relevant record, not a placeholder created by an outer join. If an outer join retains a cohort with no matching measurements, count a known non-null identifier from the measurement side to distinguish actual matches from the placeholder.
2. Test four distinct input cases
- No matching records: the counts are 0 and 0; the total is null. That alone does not tell you whether a legitimate empty slice or a broken filter caused it.
- One matching record, missing value: one matched, zero measured, null total. A row exists, but it does not supply a click count.
- One measured zero: one matched, one measured, zero total. Zero is present in the source as a measurement.
- Partial measurements: values of 4 and null yield two matched, one measured and a sum of 4. That is a sum of present values, not evidence that both records had measurements.
Run the same fixtures through the displayed report and the exported file. A dashboard could preserve the distinction internally while its CSV silently writes a zero in each missing cell.
3. Attach the coverage to the editorial conclusion
If a report says “4 clicks,” add the coverage when it matters: “4 recorded clicks across one measured row; one matching row had no click value.” Decide whether that partial total answers the question. If the claim requires full cohort coverage, label it incomplete rather than implying the missing row contributed nothing.
An empty slice can legitimately represent zero events if an independent coverage check establishes that the relevant source was complete and the filters were correct. Have the reporting rule state that assumption. A null default in a query or a missing upload does not establish it.
4. Verify groups and dates separately
The example uses one aggregate without grouping. In a report grouped by cohort or date, a missing group can instead disappear from the result entirely. Compare the expected cohort or date list with the actual output before interpreting absent rows. A missing date label and a date row with an explicit zero carry different evidence.
Check the same query against a known populated interval and the suspected empty one. Capture the filters, reporting window, match count, measured count and source completeness. This gives someone else enough information to reproduce the finding.
5. Keep the test with the export
Store the four synthetic cases alongside the report instructions. Retest them after a schema, join or display change. A useful acceptance note names what was preserved: “Measured zeros remain zero; missing measurements and absent groups remain distinguishable; partial sums retain coverage.” That says more than “the dashboard loaded” and avoids inventing activity data the source did not supply.