Transcript Slide 1

Implementing
CLEAN_Address
Address Validation
Software at VCCCD
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
2
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
3
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
4
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
5
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%
6
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
7
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
CLEAN
Address
– 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
CLEAN
Address
– Addresses are updated in the database directly
– No exporting / importing
8
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
9
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
10
Enterprise Architecture
CLEAN_Address
Server
Oracle
Database
CLEAN_Address
Server
–
–
–
–
–
Seamless Fail-over
Redundancy
Load Balancing
Scalability
100% Availability
11
Service Oriented Architecture
(SOA)
Oracle
Database
Development
Oracle
Database
Production
CLEAN_Address
Server
CLEAN_Address
Server
Multiple databases can be supported from
one CLEAN_Address server installation
Oracle
Database
PeopleSoft
Oracle
Database
Alumni
– 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
12
Database Architecture
Oracle Database
Banner Forms /
Internet Native /
Web Self-Service
CLNADDR
CLEAN_Address_Banner_UE
CLEAN_Address_Banner
CLEAN_Address
Server
CLEAN_Address.Verify
CLN_Postal_Codes
BANNER
BWGKOADR
Web Self-service
SPRADDR
SPRTELE
GTVZIPC
STVCNTY
13
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
14
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 SelfService
– 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.
15
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
16
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
SARADDR
SHBDIPL
SPTADDR
ROTADDR
SRTADDR
SOBSBGI
SRTHSCH
SRTPCOL
SPREMRG
-- Student / HR / Finance / Alumni Address Table
-- Electronics Admission Address Table
-- Diploma Address Table
-- Temporary SPRADDR Table
-- Financial Aid Temporary SPRADDR Table
-- Electronic Prospect Address Table loaded from search and test
score tapes, or entered on Web for Prospects.
-- College Code Address Table
-- High School Address Table
-- Prior College Address Table
-- Emergency Contact Address Table
17
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
18
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
19
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
20
VCCCD Implementation
• Explored several options
– CLEAN_Address
– Evisions
– USPS
• Evisions postponed due to Argos
• Chose CLEAN_Address because of realtime integration, specific to Banner
21
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
22
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
23
Implementation Steps
• Inactivated addresses in error, excluding:
– Candadian
– Early Warning System
– Suite/Apartment errors
– Payroll, if paid within 2 years
24
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
25
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
26
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 inprocess records
27
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
28
Error
Messages
29
30
31
32
Banner Forms
Examples
33
Minimum
data can
be entered
34
If valid,
remaining
address is autocompleted to
USPS standard
35
Address error
can be overridden in forms
36
Invalid
address can
be saved
37
Area code
matched to
zip code –
range is
customizable
38
Multiple
matches will
provide a
suggestion list
39
Self-Service
Examples
40
Partial address
can be entered
41
Errors can not
be overridden
in Self-Service
42
If valid,
remaining
address is autocompleted to
USPS standard
43
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
44
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
45
Contacts
• Bill Pearce, [email protected]
• Runner Technologies, Inc.
6001 Broken Sound Pkwy NW
Suite 620
Boca Raton, FL 33487-2766
561-395-9322 / 877-784-0003
[email protected]
www.RunnerTechnologies.com
46
Questions???
47