Customer Complaint Surveillance: Tools to Establish Alert Thresholds

On this page:

Importance of establishing thresholds
Alarm Estimation Tool
Threshold Analysis Tool
Which tool is right for me?
Instructional videos for using these tools

Importance of Establishing Thresholds

Establishing thresholds is a key step in deploying Customer Complaint Surveillance (CCS). The simplest approach uses counting algorithms that compare the number of complaints over a predetermined time (e.g., one day, two days, etc.) to an established threshold. Thresholds are based on historical records of customer water quality complaints.

EPA has developed two tools, the Alarm Estimation Tool and the Threshold Analysis Tool, that use historical complaint data to establish alerting thresholds. Both tools allow utilities to:

Establish a baseline for different thresholds using historical complaint data.
Apply a configurable scan algorithm to compare different alerting thresholds.
Determine when an alert would have been generated in an historical dataset.

Alarm Estimation Tool (AET)

The Alarm Estimation Tool is a Microsoft Excel spreadsheet with macros that generate alerts based on historical complaint data. This data must be properly formatted and entered into the spreadsheet. The alerts can be viewed on a time-series plot for visual interpretation.

Download the Alarm Estimation Tool (xls) (262.5 KB) (Excel) Free Viewers

Threshold Analysis Tool (TAT)

The Threshold Analysis Tool is a software application that analyzes a variety of data file formats, including .txt, .csv, .xls, .xlsx, and .tab. It offers three different statistical methods (percentile, standard deviation, and recurrence intervals) to determine thresholds for CCS. Utilities can also set thresholds for temporal units (e.g., week days, weekends) or spatial units (e.g., treatment source, pressure zone), if their complaint data contains those details.

Download the Threshold Analysis Tool (zip) (4.03 MB) (Excel) Free Viewers

Which Tool is Right for Me?

Both tools will give the same results with the same data sets. The AET does not offer all the functionality of the TAT, but it is simpler to use. For users unfamiliar with statistical methods, the AET provides a straightforward approach to establishing thresholds using historical data. Although the AET supports fewer data file formats than the TAT, it provides a visual presentation of alerts.

The TAT supports a robust analysis of historical data using multiple statistical methods and sub-unit analysis for temporal or spatial data. In addition, the TAT supports more data file formats than the AET, making it easier to import historical data into the tool.

For a comparison of the two tools, see the table below.

FEATURE	Alarm Estimation Tool	Threshold Analysis Tool
Format of Tool	Microsoft Excel spreadsheet (.xls) with Visual Basic macros	Stand alone software
Supporting Materials	User Manual Sample data Quiz	User Manual Sample Data FAQs
Acceptable Data	Accepts only a single “Date-Time” format	Accepts a variety of “Date-Time” data formats (e.g., date, date-time, etc.) and spatial units User can import a variety of file types (e.g., .txt, .csv, .xls(x), etc.)
Analysis Options	Date-Time analysis only	Date-Time analysis (Optional) Analysis by spatial or temporal identifiers (e.g., by zip code, day of week, etc.)
Basis for Statistical Analyses	Recurrence Interval	Standard Deviation, Percentiles, and Recurrence Interval
Display Output Options	Graph Table (with Date-Time)	Table(s) (with Date-Time) only.

Instructional Videos for Using These Tools

The following six videos provide an introduction and instructions for using the AET and the TAT. The following links exit the site