Recovering Lost Revenue Through Data

OVERVIEW

This municipality bills approximately 12,500 accounts for water, sewer, trash, and recycling services. They were preparing to implement a mass meter changeout and potentially deploy an AMI system — but leadership recognized that without clean data, the changeout would be fraught with challenges. Their current meter reading system used drive-by AMR technology, and their billing system had been in place for years with limited data validation. Raybern was engaged to perform a comprehensive forensic data analysis of the billing and meter reading systems to identify anomalies, quantify risk, and provide a prioritized remediation roadmap before the meter exchange began.

Raybern began with a deep dive into the municipality's meter inventory data. The analysis immediately revealed that endpoint IDs were being stored as meter IDs across the system — meaning the serial numbers stamped on the physical meter bodies were not being tracked separately from the radio transmitters attached to them. While this wasn't causing immediate billing errors, it posed a serious risk for the upcoming meter changeout: field technicians would have no reliable way to validate which physical meter was being removed and replaced. Additionally, Raybern identified 13 accounts with duplicate meter assignments where multiple meters were listed as active on a single account, many with conflicting sizes (e.g., a 1-inch Badger meter and a 2-inch Metron meter on the same account). Several accounts also contained placeholder or dummy meter IDs such as '123456' and '12345678', indicating that meters had been changed out in the field but never properly finalized in the billing system. Raybern also found 68 accounts with inconsistent meter size notation — typos and formatting errors like '9/8"' instead of '5/8"', '5/8\'' with trailing apostrophes, and entries like '70"' that appeared to be data entry errors. These inconsistencies would create procurement and reporting challenges during the mass changeout.

The analysis identified 85 accounts assigned a service code that appeared anomalous — potentially indicating unmetered, flat-rate, or non-billed accounts. These accounts required investigation to confirm whether the correct service code was applied and whether each account was being accurately billed for the services it received. Raybern also examined the five distinct service codes used across the system to establish the differing rates for water and sewer services across commercial and residential classifications, flagging accounts where the classification may not match the actual service being provided.

Raybern conducted a thorough analysis of the data files exchanged between the meter reading system and the billing platform. When the municipality initiates a billing cycle, an export file is generated containing customer information, meter details, endpoint identifiers, and read type codes that instruct the reading system how to process each meter's data. The field equipment then collects readings from each endpoint — including the raw reading, timestamp, endpoint type, and any tamper codes indicating potential issues like tilt (tampering), detached wires, reverse flow, or leak events. Raybern's analysis of these exchange files revealed critical findings: 853 accounts were returning an invalid endpoint type ('25 GD 1\''), indicating that these endpoints had failed completely — they could transmit a signal, but that signal contained no valid information. These 853 failed endpoints represented the municipality's highest priority for replacement during the upcoming meter changeout. One account was returning a gas endpoint type in a system that should have no gas endpoints installed, suggesting the endpoint was programmed incorrectly and may have been producing inaccurate high-usage readings. Eleven additional accounts were returning default endpoint types rather than the custom-configured types, indicating a mismatch between the read type codes in the billing system and the actual endpoint hardware in the field — meaning readings for these accounts may not have been processed correctly.

Read type codes are critical configuration settings that tell the meter reading system how to process raw readings from different meter manufacturers and endpoint types. Because an Itron endpoint can be attached to meters from various manufacturers (Badger, Neptune, Metron, etc.), each with different register types and dial configurations, the read type codes must be correctly configured to ensure accurate billing. Raybern's review confirmed that the current read type codes matched the dial counts for all accounts — not an anomaly in itself, but an important baseline. However, Raybern flagged that when the municipality begins installing meters from new manufacturers during the changeout, the read type code configurations would need to be updated to accommodate the new equipment. Failure to do so would result in readings being processed incorrectly, potentially causing systematic over- or under-billing across hundreds of accounts.

On the whole, while the municipality's billing was generally accurate for most accounts, the data anomalies identified posed significant risk for the upcoming meter changeout and long-term data integrity. Raybern provided a prioritized remediation roadmap: First, investigate and correct the 85 accounts with anomalous service codes to ensure accurate billing. Second, resolve all duplicate meter assignments by identifying the correct active meter and finalizing inactive meters with proper stop dates. Third, investigate the account with the gas endpoint type to confirm equipment identifiers and endpoint programming. Fourth, review the 11 accounts with default endpoint types to verify dial resolution and read type code accuracy. For the meter changeout itself, Raybern recommended prioritizing the replacement of the 853 failed endpoints, as these represented immediate revenue risk and the highest-impact improvement. Raybern also recommended establishing procedures to ensure meter body IDs and endpoint IDs are tracked separately going forward — enabling proper warranty tracking, procurement planning, and field validation during the exchange.