Cleaning millions of meter reading data points to improve reporting capability.
Custom Analytics + Software Solutions | Data Quality
Helping Tauranga City Council automate a manual data cleansing process to free up six weeks per year of manual effort and improve both the quality and accessibility of critical business data.
Tauranga City Council manages Tauranga’s Three Waters infrastructure which regulates wastewater and stormwater, and supplies drinkable water to all urban and rural residential properties. The Three Waters system is designed to support New Zealand’s environment, health and safety more effectively.
The Challenge
Every year, Tauranga City Council is required to report its water consumption and other metrics to Water NZ. These metrics must be accurate, and therefore depend on having a high quality around the water meter reading data. Unfortunately, there are numerous data quality issues.
The Solution
Harmonic worked with Tauranga City Council to create a cleaning algorithm that treats data quality issues found within water meter reading data. Working closely with key subject matter experts from the council, Harmonic captured information about the numerous data quality issues that exist within water meter data such as the reason for the issue and how they had traditionally been rectified by the council. Using this understanding, a cleaning algorithm was developed that automatically treated the various data quality issues found within the water meter data.
Two different products were developed to allow Tauranga City Council to use the cleaning algorithm with greater ease and convenience:
- A website application where staff can upload water meter data for cleaning. This provides staff with the capability to calculate water consumption and other metrics of interest.
- A software package that was integrated within the Tauranga City Council IT environment so that the cleaning of water meter data can be conducted automatically on a daily basis, exposing clean data in their data warehouse.
The Result
- Improved reliability of data and productivity by automating data cleaning processes.
- Enhanced reporting capability by enabling more accurate calculation of water consumption and other related metrics.
- Supported staff in carrying out key processes relating to the delivery of Tauranga’s drinking water services.
This was a greatly successful collaborative project with a friendly and motivated team, resulting in condensing a 4-6 week labour intensive data cleaning exercise down to a push of a button and a brief wait for calculation. This allowed for more timely reporting with a significant improvement in accuracy.
— Karen Grant, Senior Waters Engineer