Implementing CLEAN_Address Address Validation Software

1. Attention Committee Members:The following presentation came from the Banner group at CCC. Check it out! Perhaps there are topics we can use from it.Slides 38—55 contain samples of the error messages Robin found when she tested the software here.

2. ImplementingCLEAN_AddressAddress ValidationSoftware

3. Topics • CLEAN_Address Overview • Benefits and features of CLEAN_Address • Error Messages • Demo/Examples

4. CLEAN_Address Overview • CLEAN_Address is a 100% integrated solution for Address Verification in Banner • Real-Time Seamless Integration • Banner Forms / INB (Internet Native Banner) • Web Self-Service • Integrated Batch Verification • Subscription updates for USPS data • Simple step-by-step integration guides

5. Address Validation - Benefits • Improved data quality for contact information • Addresses • Telephone Numbers • Speed up data entry time with fewer errors • Allow end-user self-service updates on the Web • Reduce centralized data entry • Reduce or eliminate undelivered/returned mail • Reduce costs of mailing • Faster mail delivery

6. 1-10-100 Data Quality Rule • It costs 1 unit to fix a bad address at the point of entry when customer is present • It costs 10 units to fix a bad address that is saved in the database and identified via batch • Running reports, proactive phone calls, user’s time • It costs 100 units to fix a bad address that was sent mail and the mail was returned • Processing returned mail, contacting customer via phone/email, updating address, resending mail • In general, dealing with exceptions

7. Hypothetical Cost Savings • 100,000 mailings per year • Assume best case of 15% bad addresses • 15,000 undeliverable mail pieces • 15K x $0.39/letter x 2 deliveries = $11,700 postage • Paper and printing x 2 = $5,000 • Employee time dealing with returned mail: $8,300 • Total cost of bad addresses: $25,000 • Additional Postage Savings: $16,000 • First Class $0.39 postage down to $0.23 Standard

8. Real-time vs. batch verification • On average, databases without address verification will contain 15-22% undeliverable addresses • Batch Address Verification • Can correct a lot of these bad addresses • Will identify all of the bad records with a status code • Real-Time Address Verification • Corrects and validates addresses at point of entry • Empowers data owner to correct their own address • Notifies user of a bad address so they can confirm with the customer (student, employee, vendor) • Can reduce 15-22% down toward 0%

9. Integration Approaches • Batch Update only • Verification program is outside of the database • Export addresses nightly, run through stand alone program, import to staging table, update address • Real-Time verification with Popup Window • Popup window where user enters address • Address verification can be bypassed by user • More complicated user interface • Extra steps, not seamless with application, mouse usage • Incorrect “valid” address could be selected • Now you have 2 problems: • an incorrect but valid address • you can’t identify it in the database

10. Integration Approaches • Manual Real-Time Verification • Use a free web site to verify each address www.usps.govwww.clean-address.com • More steps, double entry, easily bypassed • Real-Time Seamless Integration • Address verification is done automatically without user intervention • User is notified real-time only if there is a bad address • No training is required • Integrated Batch Verification • Addresses are updated in the database directly • No exporting / importing CLEAN Address CLEAN Address

11. Integration with Banner • CLEAN_Address provides several integration components for Banner • CLEAN_Address_Banner PL/SQL Package • Batch Address Verification for 10 address tables • Banner Forms integration code • Web Self-Service integration code • Open PL/SQL interface for non-Banner systems (PeopleSoft, SAP, Oracle, custom) • Enables real-time and batch verification in any application that uses an Oracle database • Flat Text file address verification (CLEANFile) • Integration can be done in a few hours

12. CLEAN_Address_Banner package • PL/SQL stored package in the database • Contains several procedures: • Batch Address Verification • Real-Time Address Verification APIs • Used for integrating into Forms and Web Self-Service • Batch / Real-time Telephone Verification • ZIP Code and County table synchronization • Error code description and help • Rules can be customized by address type, i.e.: • expand the street suffix • ignore campus addresses, etc.

13. CLEAN_Address_Banner package • Enforces the Banner Address “rules” • All business rules are stored centrally in this package • Pre- and Post- Verification User Exits for custom rules • Optional parameters – expand street suffix • 30 character Street limitation • 20 character City limitation • Puts apartment on same line as address • Assigns correct county code • Appends ZIP+4 to ZIP Code • Stores verification date and address error code

14. Options • GeoCoder • Identifies the Latitude and Longitude coordinates for an address • Used to compute distance, bearing, and perform radial searches • I.e. How many students live within 5 miles, 10 miles of the college? • Demographic Data • Mailers+4

15. Implementation Steps • Implementation committee • Student • Payroll • Finance • Decided to use 5-digit FIPS County Codes • Cleared county codes from spraddr, sogsbgi • Ran sync_postal_codes (CLEAN_Address script) to populate gtvzipc and stvcnty

16. Implementation Steps • Decided to verify addresses in place • Batch_verify_address (SPRADDR) • Batch_verify_SOBSBGI • Error lists (based on CLEAN_Address script) distributed to users: • Employees – paid within last 2 years • Student – registered in last 5 years • Vendors – invoice in last 2 years

17. Implementation Steps • Inactivated addresses in error, excluding: • Canadian • Early Warning System • Suite/Apartment errors • Payroll, if paid within 2 years

18. Banner Changes • All changes provided by Runner • Forms • Changed goqclib to add or modify WHEN_VALIDATE_ITEM triggers • Added 2 procedures (cut and paste) to SPAIDEN, PPAIDEN, FOAIDEN, SAAQUIK, and FTMVEND (not using RPAIDEN) • Self-Service • Modified bwgkoadr.sql, bwgkoad1.sql bwgkoga1.sql • Modified local address entry packages

19. CCC Apply Changes • Added delivery point, etc. to table and view • Changed script to set new address errors: • E04 = Mailing Clean Addr Err • E04 = Permanent Clean Addr Err • E02 = Phone Clean Address Err • Also added to GTVSDAX error list

20. CCC Apply Changes • Changed trigger to bypass address insert if Clean Address error • Changed SWACCAP to do CLEAN_Address real-time check (if address or phone is “touched”, errors are cleared) • Ran script to verify addresses for in-process records

21. Ongoing maintenance • Will run same batch process as above every 6 months • Required by the USPS to qualify for bulk mailing • Haven’t implemented yet • Upgrade process is very smooth

22. ErrorMessages

26. Banner FormsExamples

27. Minimum data can be entered 3CBG Conference - October 23rd and 24th

28. If valid, remaining address is auto-completed to USPS standard

29. Address error can be over-ridden in forms

30. Invalid address can be saved

31. Area code matched to zip code – range is customizable

32. Multiple matches will provide a suggestion list

33. Self-ServiceExamples

34. Partial address can be entered

35. Errors can not be overridden in Self-Service

36. If valid, remaining address is auto-completed to USPS standard

37. Issues/Suggestions • Must keep overrides to a minimum – procedural issue • Sometimes unavoidable because of Early warning system • Double-check at USPS or clean-address site • Keep abreast of patches

38. The following slides werecreated by Robin whenshe tested the product

39. CLEAN ADDRESSExamples

40. SPAIDENFind personChoose Address Tab

41. Change Street Line1 to 7335 and Enter or Save. Error message – Address out of Range Click on OK for more options More options No specific address given as a choice – just a range. Click OK and 2nd box appears. Click OK and both boxes disappear. Address is changed to 7335

42. Roll back and “Do you want to save” box appears. Choose Yes.

43. Exit out and open SPAIDENIncorrect change was saved. If choose No to saving changes, change was not made.

44. Changed City to Clinton TownshipSave button

45. Change State to AZ. Click on Save –

46. Exit out – return to SPAIDENAZ is listed as the state

47. Change Zip to 48309Click on Save. Banner message appears. Click on OK and Save.

48. Exit out. SPAIDEN. All incorrect changes were saved.

49. Change County to 009using the search button. Click on OK and Save.

50. Click on SPAIDEN – all changes saved.