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 that gap non-revenue water. Some of it leaks out of pipes. A large share of it leaks out of the customer database. An account marked inactive that is actually consuming. A meter recorded against the wrong serial number. A business billed at a domestic rate. A household with no coordinates, which no meter reader can find.

None of these look like emergencies. Each is a single wrong row. Together they mean the utility cannot answer basic questions about itself. How many customers are actually billed. Which ones are disputed. Where the meters physically are. Whether revenue is improving or the reporting is simply 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 neither a failure nor a pending task. Conflating the two would have left the utility with a backlog that could never reach zero and a completion figure nobody could trust.

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 already had the data. What it did not have was a way to see it without somebody assembling a report by hand. So the questions were asked once a month instead of all the time.

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; 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. That sounds like a footnote. It is not. A meter with no location cannot be read reliably, cannot be disputed confidently and cannot be planned around.

What we did not finish and why

About 73% of actionable anomalies were closed. The remaining cases were referred rather than resolved. Most of them sit with the utility. A stolen meter needs enforcement. A contested billing category needs somebody to decide. Some cases wait on the customer, so they 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