Fixing a water utility's customer database, and the processes that broke it

The problem

A water utility earns nothing from water it cannot bill. The industry calls the gap non-revenue water, and while some of it leaks out of pipes, a large share of it leaks out of the customer database: an account marked inactive that is in fact consuming, a meter recorded against the wrong serial number, a commercial premises billed at a domestic rate, a household with no coordinates that no meter reader can reliably find.

None of these look like emergencies. Each is a single wrong row. Together they mean the utility cannot answer basic questions about its own operation: how many customers are actually being billed, which of them are disputed, where the meters physically are, and whether revenue performance is improving or the reporting is just inconsistent.

A field survey had already been run across the service area, so for the first time there was a record of what was actually on the ground to compare the database against. That comparison produced roughly 14,000 discrepancies. Our job was to work out which of them were real, fix those, and explain the rest.

What we did

Established what was true before changing anything

A discrepancy between a survey and a database does not tell you which one is wrong. Every anomaly was verified in the field before it was treated as actionable, and anything that could not be verified was recorded as such rather than quietly corrected. Around 8,900 were validated as actionable. Around 5,200 were determined inactionable for stated reasons: accounts that were disconnected, meters with no valid serial to match against, records belonging to a different system entirely.

That distinction mattered more than it sounds. An inactionable record is not a failure and not a pending task, and conflating the two would have left the utility with a backlog that could never reach zero and a completion figure that could never be trusted.

Corrected the live database through the utility's own change control

Roughly 6,600 records were updated in the live customer database, covering about 6,000 unique accounts, since some accounts carried more than one anomaly. Every amendment went the same route: field verification, a change request raised by the utility's own ICT function, and the change effected by the system vendor. Slower than direct access, and correct, because a consultant writing straight into a billing database leaves nobody able to audit what changed.

Several categories closed completely: meter serial mismatches, meter status errors, unbilled active households found during the survey, and missing location coordinates, where every affected record was recovered. Roughly 2,300 cases were referred onward because closing them needs an operational or enforcement act rather than a data edit, such as a stolen meter or a disputed billing classification.

Made performance visible

Seven interactive dashboards were built over revenue performance, customer complaints, agent resolution rates, debt and advance payments. The utility had the underlying data before; what it did not have was a way to see it without someone assembling a report by hand, which meant questions got asked monthly instead of continuously.

Rewrote the procedures that produced the anomalies

This is the part that makes the rest durable, and the part a pure data-cleaning engagement would have skipped. Five operational units were shadowed and their standard operating procedures reviewed against what people actually did: billing and meter reading, new connections, customer care, disconnection and reconnection, and meter management.

The gaps were mundane and consequential. Walk-in customers and simple enquiries were often resolved without ever being recorded, so the complaint data understated the workload. Channels people genuinely used, including social media and email, were not formally recognised. There was no uniform format for logging a complaint, and no procedure at all for capturing customer information while the system was down, so those interactions were simply lost. Complaint closure was one-way, with no confirmation that the customer agreed the issue was resolved.

Each of those is a small process gap that manufactures bad data continuously. Fixing the records without fixing the procedures would have meant paying to clean the same database again in three years.

Results

Figures are rounded.

Every missing coordinate in the actionable set was recovered, which sounds like a footnote and is not: a meter with no location is a meter that cannot be read reliably, disputed confidently, or planned around.

What we did not finish, and why

About 73% of actionable anomalies were closed. The remaining cases were referred rather than resolved, and most of them sit with the utility: a stolen meter needs enforcement, a contested billing category needs a decision, and a subset depends on the customer acting, which means some of it may never close at all.

Stating the ceiling honestly was part of the work. The realistic maximum was always around 8,900 rather than the headline 14,000, because the inactionable records were excluded by determination rather than left pending. A completion rate measured against the larger number would have looked worse and meant less.

What I would tell someone doing the same thing