HOW CLEAN DATA MAKES CLEAN WATER POSSIBLE FOR COMMUNITIES
$60M
200
THE CHALLENGE
Underfunding, poor data management, and a dysfunctional billing system
A city in Mississippi faced a water crisis that left residents without water for two weeks. 40 years of mismanagement and underfunding led up to this. Recognizing the severity of the situation, the federal government stepped in and appointed an interim third-party private entity and OZ Digital Consulting to address the city’s water woes.
FEATURED SERVICES:
TECHNOLOGY:
- Microsoft Azure Databricks
- Microsoft Power BI
- Azure AI Services
“We brought OZ Digital Consulting in to look at the billing system data and they’ve been integral to this project.”
THE SOLUTION
Investing in clean data to drive a data-powered billing system
Part of the problem why these systems failed was underfunding and years of not keeping up with the data that drove the billing system; essential to capturing revenue. The city had relied on an outdated, on-premise Oracle solution for 20 to 30 years, which was more suited for utilities. It did not provide the necessary data or insights into their customers and billing. Since the existing data was not usable, we had to first ensure the data was clean. We conducted an extensive discovery process to understand what the critical data elements in the system were while generating a bill.
OUR APPROACH
A smart data strategy and AI-driven “disposition engine” transforms a city’s billing system
We began with 156,000 addresses in the system and flattened them down to about 80,000 unique properties. We achieved this by eliminating duplicates and incorrect entries. A newly developed algorithm, the “disposition engine,” classified each row in the dataset, directing the necessary action for each property. A process that resulted in over 200 data points per property, enriched by a dozen third-party data sources, brought in through county property records. We even partnered with the post office to identify abandoned properties. We cross-checked this data against the customer data provided by the power utility. The collaboration enabled us to detect properties using electricity but not being billed for water—a critical insight for identifying lost revenue.
Combining this diverse data without a common identifier required extensive spatial joins and a significant GIS component to ensure accuracy.
A three-layer Microsoft medallion framework was implemented to standardize and enrich the data:
- Bronze Layer: Raw data was ingested.
- Silver Layer: The data was flattened and analyzed using Power BI. The disposition engine, built in Azure Databricks with machine learning and SQL, enabled the city to take action in the field. Additionally, manual meters were replaced with smart IoT devices.
- Gold Layer: Once the data was well-structured, AI was deployed to detect anomalies and outliers, enhancing decision-making.
IMPACT
$60 million increase in revenue generation
Clean data has given the city better visibility into its customers and data. They now have better insights into who’s paying or not, if the correct bill is sent to the right person, and the number of addresses being served at any given time. The water utility collects around $60 million annually in revenue through better data management. They’re on target to reach $105 million and become self-sustaining for all their infrastructure needs and equipment upgrades. They expect annual revenues to go up anywhere from $120 to 140 million.
