By J Anese, L Hannant, O Taudou and U Kaeding.

First published in Water e-Journal Vol 3 No 4 2018.

DOWNLOAD THE PAPER

Abstract

A tool, Advanced Customer Complaint Mapper (ACCM), has been developed by Allwater and SA Water to enhance the customer experience. It aims to map real time customer complaints with Microsoft Excel Power Map and anticipate clusters of complaints, thereby allowing for more rapid operational intervention and increase productivity. ACCM collects real time data from an asset management system (Maximo) and a Laboratory Information Management System (LIMS). This live tool is used across the Adelaide Services Alliance (Allwater and SA Water), by 24/7 customer services and operational teams (water quality, network water supply, water treatment plant, technical support officer).

Introduction

Allwater and SA Water work together to operate and maintain Adelaide’s metropolitan water and wastewater systems (Figure 1). This Adelaide Services Alliance is committed to supplying excellent and affordable water services to metropolitan Adelaide’s 1.3 million customers. During 2016-2017, water quality complaints represented more than 40% of the total number of complaints received from customers. No customer complaint mapping tools were available to 24/7 customer services or operational teams. An on-call water quality operator was used to manually map water quality customer complaints as they were reported. This map was used to determine clusters of customer complaints and to facilitate escalation to the incident manager (see figure 2).

With this in mind, the Adelaide Services Alliance designed and developed a tool, named Advanced Customer Complaint Mapper (ACCM). It visualises, in real time, customer complaints, anticipates clusters of complaints, and displays useful information to understand potential causes of the complaints (water quality sampling results, water mains failures, facilities, water quality system/zone, etc.). Now that live information is available across the Alliance, 24/7 customer services have more information to provide to customers and field crews, while on-call water quality operators experience less fatigue to identify clusters.

Highlights

• Use of latest Microsoft Excel mapping features
  (Power Map)
• New method of geographical and temporal data visualisation
• Real time data queries from Microsoft Excel to Maximo and LIMS
• Identification and alert of clusters of complaints
• Diagnosing the cause of the complaint
• Tracking water quality events in the network
• Creation of time-lapse video for communication during/after an incident

Methodology

Microsoft Excel Power Map

This tool was developed with Power Map, a feature available with the Microsoft Excel 2013 – Office 365 licence. Power Map for Excel is a three-dimensional data visualisation tool that allows review of information in new ways. With Power Map we can plot geographic and temporal data on a 3-D globe. Geographical information (latitude/longitude, address, GIS shapefile, KML file, etc.) is used to map the data on a Bing Map base map. By default, the data displayed is refreshed every ten minutes.

Maximo database query

When a customer contacts the 24/7 customer services, all information related to the complaint is immediately entered into Maximo (location, classification, cause, report date & time, status, etc.). Information on every facility is stored in Maximo (description, location, type, etc.). Water main failure information is also reported into Maximo (burst/leak, cause, status, location, report date & time, etc.).

As a result of the Maximo database query, the following layers are mapped:

• Taste & odour complaints (points);
• Appearance complaints (points);
• Other water quality complaints (points);
• Taste & odour complaints count per water quality system (polygons);
• Appearance complaints count per water quality zone (polygons);
• Other water quality complaints count per water quality zone (polygons);
• Major bursts (points);
• Major bursts (heatmap);
• High pressure complaints (points);
• Low pressure complaints (points);
• No water/pressure complaints (points);
• Sewer odour complaints (points);
• Sewer odour complaints (heatmap);
• Sewer overflow complaints (points);
• Sewer overflow complaints (heatmap).

LIMS database query

The Australian Water Quality Centre (AWQC) provides laboratory analysis, with all information stored in a Laboratory Information Management System (LIMS).

As a result of the LIMS database query, the following layers are mapped:

• Coliforms samples count > 1 cfu/100ml (stacked columns);
• Free chlorine residual samples count < 0.1 mg/L (stacked columns) with filter for every sampling point type: customer tap, tank, WTP outlet, etc.;
• Free chlorine residual samples count > 0.6 mg/L (stacked columns) with filter for every sampling point type: customer tap, tank, WTP outlet, etc.;
• Free chlorine residual samples count between 0.1 & 0.6 mg/L (stacked columns) with filter for every sampling point type: customer tap, tank, WTP outlet, etc.;
• Free chlorine residual samples average per water quality zone at customer tap (polygons);
• THM samples count < 200 microg/L (stacked columns);
• THM samples count > 250 microg/L (stacked columns);
• THM samples count between 200 and 250 µg/L (stacked columns);
• Temperature samples count < 20 degrees (stacked columns);
• Temperature samples count > 30 degrees (stacked columns);
• Temperature samples count between 20 and 30 degrees (stacked columns);

Identification of clusters of water quality complaints

By definition in the Adelaide Services Alliance, a cluster is 5 or more complaints of a single water quality issue, within 48 hours, in a defined geography. With all this information available in Maximo, ACCM is able to anticipate and help identify clusters by counting the number of complaints for the three categories of water quality complaints, taking into account the report date & time and the geographical information (water quality system or its subdivision, called a water quality zone). The Adelaide metropolitan area is divided into 8 water quality systems and 93 water quality zones. The live map helps to anticipate clusters by visualisation of actual customer complaint per water quality zone/system. The more water quality complaints received within a specific area (water quality zone/system) the darker that area (polygon) will be coloured.

Results/outcomes

The ACCM tool centralises the main information required to better respond to customers (via the 24/7 customer services team), provides additional useful information to field crews, improves incident response (Incident Manager and Operation Control Centre), and reduces fatigue (on-call water quality operator).

Next step: Integration to GIS

Excel is a powerful tool and was able to create an effective prototype. However, a more robust long-term solution can be implemented by using GIS software, such as AquaMap.

SA Water’s GIS serves as a spatial information repository for use in asset management, operations and customer enquiries. In particular, it stores location and descriptive information about corporate linear network assets including water, wastewater and recycled water mains. It provides a framework for understanding and visualising information from a variety of different sources. Point (e.g. pumping stations, treatment plants and valves), line (e.g. pipe segments) and polygons (e.g. administrative boundaries, infrastructure zones and drainage areas) are used to represent and model infrastructure assets.

Assets are uniquely identified within the system and in the case of non-pipe assets, the Maximo location identifier is used as a primary key to link with further detailed information (e.g. performance, condition and cost) that resides in the corporate Maximo system. Information within the GIS is used to support a range of asset decision-making processes.

The GIS is primarily updated by as-constructed drawings (provided by the development industry), gazettal sketches (from mains extensions) and as-constructed drawings (from SA Water capital projects). The GIS has a documented data schema, however, this was developed a number of years ago and some minor enhancements have been made over time on an ad-hoc basis.

The system has been configured to establish water main shut-off blocks and identify customers impacted by water main bursts and leaks. Overall the system has complete network connectivity, however, when exporting data for modelling purposes, some occasional connectivity issues have been found.

Esri Geocortex has been used as the basis for a web viewing tool, named AquaMap.

SA Water is currently in the process of integrating all layers from the ACCM into AquaMap.

The 24/7 customer services team will easily see any useful information to better respond to customer enquiries. For example, the agent at the customer service will be able to make a direct link between a dirty water complaint and an operational event (main isolation, water main failure, etc.). If a customer complains about a chlorine taste, the agent can access the closest sampling laboratory result and confirm or not what the customer experienced.

Note the cluster definition for other type of customer complaints (pressure, sewer odour, etc.) is under development.

Conclusion

Using a common software program available to most utilities, this tool has the ability to provide consistent real time information across the business which will ultimately enhance the customer experience. This new way of data visualisation helps to better respond to customer complaints and is an efficient decision support tool in the event of an incident.

Figure 1: Adelaide Services Alliance Overview

Figure 2: how a water quality complaint was processed before the Advanced Customer Complaint Mapper

Figure 3: ACCM - mapping the three categories of water quality customer complaint

Figure 4: ACCM - mapping the water quality complaints with other information to help diagnosis the cause

Figure 5: ACCM - counting water quality complaints per water quality system

Figure 6: ACCM – chlorine residual optimisation in the drinking water network

Figure 7: ACCM – Sewer odour complaints heatmap

Figure 8: ACCM – Pressure complaints in a particular pressure zone

Figure 9: ACCM – example of data card window that pops up when we put the cursor on the map

About the authors

Julien Anese

Julien has over 10 years’ international experience in water sector (Benin, China, New Zealand, France, Cambodia and Australia). Julien is SUEZ’s Centralised System Support Manager for Adelaide Metropolitan and a key pivotal role between Allwater and South Australia Water. A true Alliance role, Julien encompasses performing key responsibilities across Water Supply group, Waste Water group and Operations Control Group. As a major contributor to the efficiency and continuous improvement of the operation of the Adelaide Services Alliance’s metropolitan water systems, Julien is pivotal to the development of production planning strategies and the Operations Control Centre personnel and systems.

Lisa Hannant

As Senior Manager Production and Treatment at SA Water, Lisa draws on her more than 20 years’ experience as a professional engineer, with specialist expertise in water and wastewater quality and treatment, to ensure the efficient delivery safe clean drinking water and dependable sewerage services across South Australia.

Olivier Taudou

Currently Water Supply Manager at Allwater, Olivier has more than 20 years’ experience with Suez, either in providing technical and operational support as Water Treatment and Water Quality expert or in managing Operations and Maintenance of water and wastewater systems in different countries.

Uwe Kaeding

Uwe Kaeding has been involved in the water industry for the past 33 years in both the public and private sector. He has both operational and research expertise in the field of potable water treatment and distribution. He is currently the Water Quality & Environment Manager for Allwater.