1 / 47

Implementing CLEAN_Address Address Validation Software at VCCCD

Implementing CLEAN_Address Address Validation Software at VCCCD. 2006 CONFERENCE. October 23 & 24. Topics. Benefits and features of CLEAN_Address validation software VCCCD implementation approach Modifications to Banner forms and SSB Integration with CCC Apply

Albert_Lan
Download Presentation

Implementing CLEAN_Address Address Validation Software at VCCCD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ImplementingCLEAN_AddressAddress ValidationSoftware at VCCCD 2006 CONFERENCE October 23 & 24

  2. Topics • Benefits and features of CLEAN_Address validation software • VCCCD implementation approach • Modifications to Banner forms and SSB • Integration with CCC Apply • Ongoing monitoring of address status • Error Messages • Demo/Examples 3CBG Conference - October 23rd and 24th

  3. 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 3CBG Conference - October 23rd and 24th

  4. 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 3CBG Conference - October 23rd and 24th

  5. 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 3CBG Conference - October 23rd and 24th

  6. 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% 3CBG Conference - October 23rd and 24th

  7. 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 3CBG Conference - October 23rd and 24th

  8. 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 3CBG Conference - October 23rd and 24th

  9. 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 3CBG Conference - October 23rd and 24th

  10. CLEAN_Address Architecture • CLEAN_Address has an Enterprise Service Oriented Architecture (SOA) • CLEAN_Address server connects to database on back-end • Clients do not connect to CLEAN_Address server directly • Enterprise Architecture • Seamless Fail-over • Redundancy • Load Balancing • Scalability • Oracle RAC (Real Application Clusters) • Service Oriented Architecture (SOA) • Multiple databases can be supported from a single CLEAN_Address server installation 3CBG Conference - October 23rd and 24th

  11. Enterprise Architecture CLEAN_Address Server Oracle Database CLEAN_Address Server • Seamless Fail-over • Redundancy • Load Balancing • Scalability • 100% Availability

  12. Service Oriented Architecture (SOA) Oracle Database - PeopleSoft Oracle Database - Development CLEAN_Address Server CLEAN_Address Server Oracle Database - Alumni Oracle Database - Production • Multiple databases can be supported from one CLEAN_Address server installation • Address Verification Servers supply a service to any Oracle database that needs them • USPS data is updated centrally on the CLEAN_Address server every 2 months

  13. Database Architecture Oracle Database Banner Forms / Internet Native / Web Self-Service CLNADDR BANNER BWGKOADR Web Self-service CLEAN_Address_Banner_UE SPRADDR CLEAN_Address_Banner SPRTELE CLEAN_Address Server CLEAN_Address.Verify GTVZIPC STVCNTY CLN_Postal_Codes

  14. 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 3CBG Conference - October 23rd and 24th

  15. 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. 3CBG Conference - October 23rd and 24th

  16. 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 3CBG Conference - October 23rd and 24th

  17. Batch Address Verification • PL/SQL Procedures for Batch updates of existing address records • Addresses are updated “in-place” in the database without exporting, importing • The following Banner tables are supported: SPRADDR -- Student / HR / Finance / Alumni Address Table SARADDR -- Electronics Admission Address Table SHBDIPL -- Diploma Address Table SPTADDR -- Temporary SPRADDR Table ROTADDR -- Financial Aid Temporary SPRADDR Table SRTADDR -- Electronic Prospect Address Table loaded from search and test score tapes, or entered on Web for Prospects. SOBSBGI -- College Code Address Table SRTHSCH -- High School Address Table SRTPCOL -- Prior College Address Table SPREMRG -- Emergency Contact Address Table 3CBG Conference - October 23rd and 24th

  18. Maintenance • A bimonthly subscription service keeps the USPS data updated every 2 months • A monthly subscription option is also available • About 2 GB of USPS data is shipped on 3 CDs • Zero downtime when applying updates • Existing address records should be verified at least every 6 months using the Banner batch address verification procedures • ZIP Codes, ZIP+4, and street names can change periodically 3CBG Conference - October 23rd and 24th

  19. Pricing • Based on student headcount plus options • Delivery Point Validation (DPV) included • Choice of O/S platform included • Includes 6 bimonthly subscription updates • Includes FREE technical support and maintenance, with 24-hour emergency support • Includes all Banner Batch and Real-time interface packages and support 3CBG Conference - October 23rd and 24th

  20. 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 3CBG Conference - October 23rd and 24th

  21. VCCCD Implementation • Explored several options • CLEAN_Address • Evisions • USPS • Evisions postponed due to Argos • Chose CLEAN_Address because of real-time integration, specific to Banner 3CBG Conference - October 23rd and 24th

  22. 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 3CBG Conference - October 23rd and 24th

  23. 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 3CBG Conference - October 23rd and 24th

  24. Implementation Steps • Inactivated addresses in error, excluding: • Candadian • Early Warning System • Suite/Apartment errors • Payroll, if paid within 2 years 3CBG Conference - October 23rd and 24th

  25. 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 3CBG Conference - October 23rd and 24th

  26. 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 3CBG Conference - October 23rd and 24th

  27. 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 3CBG Conference - October 23rd and 24th

  28. 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 3CBG Conference - October 23rd and 24th

  29. ErrorMessages 3CBG Conference - October 23rd and 24th

  30. Banner FormsExamples 3CBG Conference - October 23rd and 24th

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

  32. If valid, remaining address is auto-completed to USPS standard 3CBG Conference - October 23rd and 24th

  33. Address error can be over-ridden in forms 3CBG Conference - October 23rd and 24th

  34. Invalid address can be saved

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

  36. Multiple matches will provide a suggestion list

  37. Self-ServiceExamples 3CBG Conference - October 23rd and 24th

  38. Partial address can be entered

  39. Errors can not be overridden in Self-Service

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

  41. 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 3CBG Conference - October 23rd and 24th

  42. Summary • Reduction of over 1000 pieces of returned mail to 20 (average mailing 11,000) • New clients – implement now! • Wish we’d had it back in 1998 • Enforces standards • Clean up at conversion time (Cuesta) • Current clients – a must for self-service address entry 3CBG Conference - October 23rd and 24th

  43. Contacts • Bill Pearce, pearce@sigcorp.com • Runner Technologies, Inc. 6001 Broken Sound Pkwy NW Suite 620 Boca Raton, FL 33487-2766 561-395-9322 / 877-784-0003 sales@RunnerTechnologies.com www.RunnerTechnologies.com 3CBG Conference - October 23rd and 24th

  44. Questions??? 3CBG Conference - October 23rd and 24th

More Related