finding lost customers mike morris rick ehlin city of noblesville utility igic conference 2008
TRANSCRIPT
FINDING LOST FINDING LOST CUSTOMERSCUSTOMERS
Mike MorrisMike MorrisRick EhlinRick Ehlin
City of Noblesville UtilityCity of Noblesville UtilityIGIC Conference 2008IGIC Conference 2008
GIS DivisionGIS Division
Mike Morris- GIS Mike Morris- GIS CoordinatorCoordinator
Rick Ehlin- GIS Rick Ehlin- GIS TechnicianTechnician
1998- GIS Began1998- GIS Began 2001- As-built 2001- As-built
review and other review and other GIS functionsGIS functions
2004- GIS Division 2004- GIS Division FormedFormed
2007- GIS Division 2007- GIS Division ExpandedExpanded
City of NoblesvilleCity of Noblesville
Located in Hamilton CountyLocated in Hamilton County Population of 39,350 (2005)Population of 39,350 (2005) Approximately 32 sq mi.Approximately 32 sq mi. Noblesville is in the No. 10 spot in the book, “Best Places to Raise Your Family: The Top 100 Noblesville is in the No. 10 spot in the book, “Best Places to Raise Your Family: The Top 100
Affordable Communities in the U.S.”Affordable Communities in the U.S.”
Noblesville UtilitiesNoblesville Utilities
Class 4 Wastewater UtilityClass 4 Wastewater Utility Approx. 15,500 CustomersApprox. 15,500 Customers 429 Miles of Sewer Pipe429 Miles of Sewer Pipe Finished $9.3 million Phase I Finished $9.3 million Phase I
expansion in October 2007.expansion in October 2007. Started Phase II of expansion in Started Phase II of expansion in
December 2007 at cost of $15.5 December 2007 at cost of $15.5 million.million.
Service AreaService Area
Finding “Lost” CustomersFinding “Lost” Customers
Honor SystemHonor System Who is not paying?Who is not paying? Potential revenue lostPotential revenue lost Helped to update 911 addressesHelped to update 911 addresses
Tools for solving our problemTools for solving our problem
ArcGISArcGIS ExcelExcel AccessAccess Field ChecksField Checks
Nuts and BoltsNuts and Bolts
Convert .xls to .mdbConvert .xls to .mdb• Used Excel to edit dataUsed Excel to edit data• Convert to .mdb for geocodingConvert to .mdb for geocoding
Nuts and BoltsNuts and Bolts
Covert .xls to .mdbCovert .xls to .mdb Confine 911 data to sewer boundaryConfine 911 data to sewer boundary
Nuts and BoltsNuts and Bolts Confine 911 data to sewer boundaryConfine 911 data to sewer boundary
• 911 data countywide911 data countywide• Over 21,000 addresses in service area, but only 15,500 Over 21,000 addresses in service area, but only 15,500
customers.customers.
Nuts and BoltsNuts and Bolts
Covert .xls to .mdbCovert .xls to .mdb Confine 911 data to sewer boundaryConfine 911 data to sewer boundary Geocode customer database to 911 Geocode customer database to 911
addressesaddresses
Nuts and BoltsNuts and Bolts Geocode customer database to 911 addressesGeocode customer database to 911 addresses
• Create Address LocatorCreate Address Locator
Nuts and BoltsNuts and Bolts Geocode customer database to 911 addressesGeocode customer database to 911 addresses
• Create Address LocatorCreate Address Locator• Run Geocode and analyze resultsRun Geocode and analyze results
Nuts and BoltsNuts and Bolts
Covert .xls to .mdbCovert .xls to .mdb Confine 911 data to sewer boundaryConfine 911 data to sewer boundary Geocode customer database to 911 Geocode customer database to 911
addressesaddresses Manual address matchManual address match
Nuts and BoltsNuts and Bolts Manual address matchManual address match
Nuts and BoltsNuts and Bolts
Problems we ran into while manually Problems we ran into while manually address matching.address matching.• Addresses we were billing were not in Addresses we were billing were not in
911.911.• Poor data entry on customer databasePoor data entry on customer database• Names of streets varied by locationNames of streets varied by location• Street directional differed between Street directional differed between
address locator and customer database.address locator and customer database.
Nuts and BoltsNuts and Bolts
Covert .xls to .mdbCovert .xls to .mdb Confine 911 data to sewer boundaryConfine 911 data to sewer boundary Geocode customer database to 911 Geocode customer database to 911
addressesaddresses Manual address matchManual address match Field Check address found in Field Check address found in
customer database, but NOT 911.customer database, but NOT 911.
Nuts and BoltsNuts and Bolts
Field check addresses found in Field check addresses found in customer database, but NOT 911customer database, but NOT 911• Houses converted into apartmentsHouses converted into apartments• Apartments over garagesApartments over garages• New builds in older areasNew builds in older areas• Commercial downtown propertiesCommercial downtown properties• Updated 911 database with field Updated 911 database with field
checked addresseschecked addresses
Nuts and BoltsNuts and Bolts
Covert .xls to .mdbCovert .xls to .mdb Confine 911 data to sewer boundaryConfine 911 data to sewer boundary Geocode customer database to 911 Geocode customer database to 911
addressesaddresses Manual address matchManual address match Field Check address found in Field Check address found in
customer database, but NOT 911.customer database, but NOT 911. Re-Geocode with updated address Re-Geocode with updated address
information.information.
Nuts and BoltsNuts and Bolts
Re-Geocode with updated address Re-Geocode with updated address information.information.• Match success rate went up to 99%.Match success rate went up to 99%.• Manually matched addresses that were Manually matched addresses that were
field checked.field checked.• Addresses that were left are potential Addresses that were left are potential
unpaid customers.unpaid customers.
Verifying Service Verifying Service
From 911 addresses selected all From 911 addresses selected all points that were identical to points that were identical to geocoded addresses (customer geocoded addresses (customer database).database).
Switched selection set to give us all Switched selection set to give us all addresses that are in our service addresses that are in our service area, but NOT in our billing database.area, but NOT in our billing database.
Verifying ServiceVerifying Service
Non-paying layerNon-paying layer 2341 potential non-paying customers 2341 potential non-paying customers
identified for field verification.identified for field verification. Selected points that fell within 250 Selected points that fell within 250
feet of sewer line.feet of sewer line. Left us with 874 properties we Left us with 874 properties we
declared to be “non-septic.”declared to be “non-septic.”
Verifying ServiceVerifying Service
Split up city into 5 sections for field Split up city into 5 sections for field checks.checks.
Check for occupants and report Check for occupants and report findings.findings.
Gave billing office list of occupied Gave billing office list of occupied properties for further verification.properties for further verification.
The AftermathThe Aftermath
Average residential Average residential bill is $32.58bill is $32.58
Commercial varies Commercial varies upon size of water upon size of water metermeter
Sent out letters to Sent out letters to potential “lost potential “lost customers”customers”
The AftermathThe Aftermath
As of February 2008 the billing office have As of February 2008 the billing office have gone through 178 addresses.gone through 178 addresses.
104 addresses were determined “non-104 addresses were determined “non-paying”.paying”.
10 addresses new accounts were opened 10 addresses new accounts were opened w/out letters being sent.w/out letters being sent.
94 letters were mailed out with 7 94 letters were mailed out with 7 responses. This has a potential of responses. This has a potential of $3,062.52 more revenue$3,062.52 more revenue
New Charges = $450.00 (Jan) New Charges = $450.00 (Jan)