For years, Opelika Utilities, of Opelika, AL, serving nearly 20,000 customers, had employed a manual process to identify potential dead meters and extremely high/low monthly meter readings throughout their service area. An office team member would spend an entire day, once a month, reviewing the meter reports,painstakingly reviewing the daily Microsoft Excel reports line- by-line of more than 14,000 readings. Due to this daunting manual process, there was a high probability of significant amounts of information being overlooked; including revenue loss. They had no way of knowing how vast of a problem they had on their hands. They needed an automated way to review meter readings, analyze, and, automatically generate work orders so that non- operating meters could be attended to.
In 2016, GISinc partnered with Opelika Utilities to connect GIS and Cityworks, with the billing system, utilizing backend processing scripts to develop a report of meter reading discrepancies. The scripting process was very intensive on the hosted servers and would take hours to complete. The effort proved to be a major improvement in process, which led to an estimated increase of annual revenue of over $100,000.
In April 2017, Opelika Utilities and GISinc traveled to Esri’s Philadelphia office to participate with the Esri water team in a Business Intelligence Summit. Armed with literally hundreds of thousands of records of data (service connection, meter usage and work order history, etc.) we could connect all the data to Insights for ArcGIS. Insights for ArcGIS handles large amounts of data very efficiently. The process of getting the necessary data into Insights for ArcGIS includes prepping the billing usage data tables, then uploading the items into Portal for ArcGIS. This creates a copy of the items in a relational Data Store. The Data Store is a PostgreSQL database designed to streamline how data is served for Web GIS. Upon uploading the data, each data item is then available for analysis inside of Insights for ArcGIS. By creating a relationship in the Insights for ArcGIS workspace between the service connection and the billing usage history, using the meter number in both datasets, we were quickly able to visualize and filter the billing data for each meter. A filter was then added to the only show records where the last three readings all read zero, which immediately displayed which meters were dead in the system.
The results were remarkably eye-opening. Out of 14,000 meters, Opelika was looking at results that were telling them over 20%, (3,000 ) were dead. This translated into an estimated $25,000/month revenue loss ($300,000/year).
Using Insights for ArcGIS and further analysis, they could identify a trend of dead meters of a specific manufacturer, model number, and meters that were recently installed in new developments in their network. The good news was these meters were still under warranty, therefore, replacing the majority of the dead meters came at no expense to Opelika Utilities.
Some of the next steps include GISinc working with Opelika Utilities to streamline the results to include visualization and integrations with Operations Dashboard, Collector, and Cityworks. Additionally, Opelika Utilities is identifying the next big question and data to push through Insights for ArcGIS; as this analysis is just the beginning.