Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Success Stories

Learn how Alteryx customers transform their organizations using data and analytics.
STORIES WANTED

Showcase your achievements in the Maveryx Community by submitting a Success Story now!

SUBMISSION INSTRUCTIONS

Customer Water Leak Detection And Notification

brianscott
11 - Bolide
2018 Excellence Awards Entry: Customer Water Leak Detection And Notification

Name:  Brian Scott
Title:  Innovation Manager
Company:  City of Tallahassee
Collaborators:  David Carnes
 
anotherCotLogo.png
 
Overview of Use Case:
 
As a government municipality and utility owner that is responsible to the entire community, helping foster a sustainable environmental footprint and the proactive notification of customers of potential issues are pillars of the City of Tallahassee operating philosophy.  After the implementation of an automated meter data management system the technology group was asked to determine if there was a way to predict if a customer had a water leak.  The existing process was one reliant on manual evaluation of abnormal consumption that utilized analysis of monthly consumption totals.  Customers receiving monthly water bills in excess of one thousand dollars were not unheard off, and customers could often have continuous consumption for sixty days or more before a field technician was dispatched to the service address.  
 
Describe the business challenge or problem you needed to solve:

The goal was to replace the existing, manual process of looking for abnormal consumption and generate an automated tool that could periodically scan water consumption, detect meters recording anomalous consumption that was consistent with a leak, and initiate communication to customers about the probable water leak.  The most meaningful metric for measuring improvement was a reduction in time between the start of abnormal consumption and notification to the customer.  Secondary objectives were to provide dashboards for field technicians to allow them to prioritize field visits for very large leaks and/or customers for whom automated communications were not possible.  
 
Describe your working solution:

An Alteryx workflow was developed to identify consumption profiles that were consistent with a likely leak on the customer side of the meter; this analysis involved evaluating the previous seventy two hours of consumption for a meter, determining if there was some level of consumption each hour, and if there was, determining if the average daily consumption was in excess of ~ 2.5X daily consumption for the over the past year.  Meters matching a profile consistent with a leak were then associated with customer metadata from the billing system and previous contact metadata from a different data store.  Metadata about identified meters and a critical customers is exported for special attention from analysts. 
 
generating tdes.png
 
For customers with appropriate communication metadata and appropriate opt in characteristics, detailed emails are sent out detailing the recent consumption trends and the likelihood that there is a leak on customer end.  Customers with phone numbers in the billing system also receive an robo call regarding a likely leak.  
 
EmailToCustomer.png
 
 
The aggregated meter, customer, and contact history data is published to a Tableau server environment, which houses a dashboard for dispatchers to prioritize field technician visits for critical leaks.  A separate dashboard has been created for the key account representatives, allowing the tool to highlight problems associated with specific high-profile accounts, such as the hospital, local universities, and large apartment complexes.  
 
invoking tableau exports.png
 
 
waterLeakDashboardSnap.png
 
 
The workflow has been published to the server environment and runs every morning.  Customers are automatically notified of new leaks detected with fresh data and field technicians have an updated environment when they start work each morning.  
 
Describe the benefits you have achieved:

The average number of days that a customer experienced continuous consumption of water before receiving a notification has dropped from over fifty days to seven days.  Field technicians have been able to prioritize visiting sites with the largest leaks.  Anecdotal reports from many customers indicate that after repairing a leak and communicating with the billing department that customer savings exceeding five hundred dollars are not uncommon.   
Comments
Wesley Mendonca
8 - Asteroid

Hi Brian, How Are?

 

I liked your project but I still have some questions. 

 

In order to answer some questions. May you provide your workflow or a example to see what was done ?

 

I hope you have a great week.

 

See ya,