Implementing CLEAN_Address Address Validation Software at VCCCD

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, users
  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 cant 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.gov www.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
BANNER
CLNADDR
BWGKOADR Web Self-service
CLEAN_Address_Banner_UE
SPRADDR
CLEAN_Address_Banner
SPRTELE
CLEAN_Address Server
GTVZIPC
CLEAN_Address.Verify
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 ZIP4 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, ZIP4, 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
• Mailers4

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
• Havent implemented yet
• Upgrade process is very smooth

3CBG Conference - October 23rd and 24th
29
ErrorMessages
3CBG Conference - October 23rd and 24th
30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33
Banner FormsExamples
3CBG Conference - October 23rd and 24th
34
Minimum data can be entered
3CBG Conference - October 23rd and 24th
35
If valid, remaining address is auto-completed to
USPS standard
3CBG Conference - October 23rd and 24th
36
Address error can be over-ridden in forms
3CBG Conference - October 23rd and 24th
37
Invalid address can be saved
38
Area code matched to zip code range is
customizable
39
Multiple matches will provide a suggestion list
40
Self-ServiceExamples
3CBG Conference - October 23rd and 24th
41
Partial address can be entered
42
Errors can not be overridden in Self-Service
43
If valid, remaining address is auto-completed to
USPS standard
44
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
45
Summary

• Reduction of over 1000 pieces of returned mail to
  20 (average mailing 11,000)
• New clients implement now!
• Wish wed 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
46
Contacts

• Bill Pearce, pearce_at_sigcorp.com
• Runner Technologies, Inc.
• 6001 Broken Sound Pkwy NW
• Suite 620
• Boca Raton, FL 33487-2766
• 561-395-9322 / 877-784-0003
• sales_at_RunnerTechnologies.com
• www.RunnerTechnologies.com

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