How the utility history builds

Key takeaways

Every bill writes one history row, whether or not anything moved.
Usage is stored per day as well as per period, because billing periods are not equal.
An estimated reading is stored and flagged, but never used as a year-on-year baseline.
A catch-up bill after a run of estimates is spread across the days it covers.
Two years of this makes renewals, site comparison and fault detection arithmetic rather than guesswork.

The history row

Field	Example	Why it is there
`meter`	1200034557	The partition; everything is per meter
`period_end`	2026-07-10	The sort key, so a range query is a period
`days`	30	Billing periods are not equal months
`usage`	4180.0	In the meter’s own unit, from the meter list
`usage_per_day`	139.3	What every comparison actually uses
`unit_rate`	0.241	Excluding tax, always
`standing`	0.48	Per day, excluding tax
`estimated`	false	Whether this reading was estimated
`baseline_ok`	true	Whether it may be used as a year-on-year reference
`bill_key`	s3://bills/2026/07/...	The original page, for when somebody asks

Why estimated readings are quarantined

An estimated bill is a supplier’s guess, usually based on the same period last year, which makes it circular as a comparison reference. Worse, estimates run in sequences: three or four estimated periods followed by an actual reading that corrects all of them at once. That correcting bill can be double a normal one and is not a usage spike at all.

So estimated bills are stored, flagged, and given baseline_ok: false. They are compared — a wildly wrong estimate is worth knowing about — but they are never the reference another period is measured against, and a catch-up reading is spread evenly across the days the estimates covered before per-day figures are recomputed.

Three things clean history makes possible

Fig 1. What the history is for. Each of these is a straightforward query over the rows and completely impractical over a folder of PDFs.

Site comparison, and its one trap

Comparing sites is the most requested and most misused output. Raw usage per site tells you which site is biggest, which you already knew. Usage per square metre, per opening hour, or per cover served is the number with information in it, and it needs facts about the site that the bills do not contain. That is three columns on the meter list tab and it is worth adding.

The trap is comparing sites with different equipment. A site with a kitchen and a site without are not comparable on any normalisation, and a league table that puts them next to each other produces a manager defending their numbers rather than looking at them. Compare a site with itself over time first; compare it with others only where the equipment genuinely matches.

Fault detection

The signature of failing equipment is specific and easy to query for: a step change in per-day usage that persists rather than reverting. A cold snap raises usage and then it comes back down. A fridge seal that has failed raises it and it stays raised. Two consecutive periods more than a threshold above the seasonally adjusted expectation, with no corresponding change at other sites, is worth a look.

What the history is not for

It is not a forecast. Two years is enough to compare and not enough to predict, and a confident forecast from this data would be a fiction.
It is not a carbon report. Converting usage to emissions needs factors that change annually and vary by supplier, and getting it wrong in a public claim is worse than not doing it.
It is not a substitute for a meter. If a site genuinely needs half-hourly data to manage a process, buy the monitoring; a monthly bill will never tell you when something switched on.
It is not evidence in a dispute on its own. It tells you a bill looks wrong, which is the start of a conversation with a supplier, not the end of one.

Next: what all of this costs to run.

All posts