aacrao 2013 - address verification in higher education › files › rpq8yugeqb5bun79… · address...

Address Verification inHigher Education with CLEAN_Address

Runner TechnologiesBarry Allweiss – VP Sales & Marketing

CLEAN_Address() – Copyright 2000-2010 Runner Technologies, Inc. All Rights Reserved.

AgendaAbout Runner Technologies Overview of Address VerificationCLEAN_Address Integration ApproachCLEAN_Address Demo’sReportingData Enhancement Services Technical discussion / Q&A


About Runner Technologies Oracle & Higher Education Experts15 Years in Data Quality / Address VerificationOver 600 customers using CLEAN_Address

� 500 customers in Higher Education� Higher Ed integrations: Ellucian’s Banner,

Colleague, & Advance; PeopleSoft


What is Address Verification?The process of taking an address “as-is” and:

Verifying if it’s deliverable

Correcting it if necessary

Standardizing - returning a valid deliverable address in a consistent format

Or providing an address suggestion or error list to help the user provide better input


Why use Address Verification?On average, databases without address verification will contain 15151515----22% undeliverable22% undeliverable22% undeliverable22% undeliverable addresses.

New student applications

Students move 3-4 times

Importing student records of test scores

Keeping in touch with lost Alumni


Why is Mail non-deliverable?Address is missingmissingmissingmissing components

Apartment or Suite #

Directional (N, S, E, W, etc.)

Street Suffix (St, Ave, Blvd, Ln, etc.)

Address has invalidinvalidinvalidinvalid componentsIncorrect Street Suffix

Incorrect Directional

House number transposed

Street name is spelled incorrectlyspelled incorrectlyspelled incorrectlyspelled incorrectly


What’s your Address Standard?1515 N Federal Hwy Ste 305Boca Raton, FL 33432-19531515 N Federal HwySte 305Boca Raton, FL 33432-1953STE 3051515 N FEDERAL HWYBOCA RATON, FL 33432-19531515 North Federal HighwaySuite 305Boca Raton, FL 334321515 North Federal Hwy Ste 305Boca Raton, FL 33432-19531515 North Federal Highway Suite 305Boca Raton, FL 33432-1953

Each institution can customize it’s own Standard:

Spell out street suffix?

Abbreviate directional?

Where does Apt go?

Same Line, Above, Below


Address Verification BenefitsSpeed up data entry time with fewer errors

Reduce costs of mailing – Save $$$ � Reduce or eliminate undeliverable / returned mail

� Faster mail delivery

Save Time tracking down bad addresses to correct them

Allow end-user self-service updates on the Web�Reduce centralized data entry


Who can benefit from improved Address Verification?Recruitment / AdmissionStudent RecordsAdvancement /FoundationHuman Resources

Employee payroll and benefits mailings

Job Applicants

Accounts ReceivableValid addresses for credit card transactions

Accounts PayableVendor addresses

Institutional Research

Gartner study - on average, databases without address verification will contain 14-22% undeliverable addresses


CLEAN_Address Integration ApproachCLEAN_Address provides a 100% IntegratedAddress Verification solution

Real-Time Seamless Integration

Batch Integration


Real-Time VerificationReal-Time?

� Corrects and validates addresses at point of entry

(as you type the address into your SIS/CRM)

Seamless Integration:

� Address verification is done automatically without user intervention

� User is notified real-time only if address is non-deliverable

� No training is required


Who wants to see a demo?- Banner - Colleague- PeopleSoft - Advance - CLEAN_Entry GUI


Banner Forms IntegrationIntegration into existing Banner FormsNo client software to install or maintain

County accurate to house number level

Address type specific rules (spell out street suffix)

Seamless: User is not notified unless there is an issue

ZIP and ZIP+4 added automatically


Error Messages and Suggestion ListsUsers are only notified when there is an error

Suggestion List provides correction possibilities based

on address error


CLEAN_Entry - real-time integration into any3rd party software


CLEAN_Address Integration ApproachReal-Time Seamless Integration

Batch Integration


Batch VerificationBatch Verify?� Verify a group (table) of addresses at once

Integrated Batch:� Addresses are verified & updated directly in the

database

� No exporting / No importing of data � DBA’s will save time

� Identifies all bad records with a status code


CLEAN_Address Integration ComponentsRealRealRealReal----timetimetimetime - integration for Forms or SISRealRealRealReal----timetimetimetime Web SelfWeb SelfWeb SelfWeb Self----ServiceServiceServiceService - integration for Self-Service processesBatchBatchBatchBatch - Address Verification for all address tablesOpen PL/SQL interfaceOpen PL/SQL interfaceOpen PL/SQL interfaceOpen PL/SQL interface & Restful Web Service Restful Web Service Restful Web Service Restful Web Service for Integrated systems (Parking Permit, Housing, Advancement, etc.)

Enables real-time and batch verification in any application

Flat Text fileFlat Text fileFlat Text fileFlat Text file batch address verification (CLEANFile)Address Data EntryAddress Data EntryAddress Data EntryAddress Data Entry form (CLEAN_Entry) for fast integration with non-Banner integrations


Banner Forms / INB SupportSupport for all address related forms in Banner INB (21 forms total):GOQCLIBGOQCLIBGOQCLIBGOQCLIB-------- Address Form Reference Library supporting:Address Form Reference Library supporting:Address Form Reference Library supporting:Address Form Reference Library supporting:

- SPAIDEN -- General Person for Student- PPAIDEN -- General Person for HR / Personnel- APAIDEN -- General Person for Alumni- FOAIDEN -- General Person for Finance- FTMVEND -- Vendor Maintenance- FTMAGCY -- Agency for Finance- FTMCUST -- Customer Maintenance Form- FTMFMGR -- Financial Manager Detail- SPAEMRG -- Emergency Contact Form - SAAQUIK -- Quick Admissions- SRAQUIK -- Quick Recruit- APAEHIS -- Employment History Form- GOAEMAL -- Email Maintenance Form

APAERVW APAERVW APAERVW APAERVW -------- Employer Review FormEmployer Review FormEmployer Review FormEmployer Review FormPEA1PAYPEA1PAYPEA1PAYPEA1PAY -------- OneOneOneOne----Time Payment and Quick hireTime Payment and Quick hireTime Payment and Quick hireTime Payment and Quick hireSAAETBL SAAETBL SAAETBL SAAETBL -------- AdmissionsAdmissionsAdmissionsAdmissionsSHADIPL SHADIPL SHADIPL SHADIPL -------- Diploma AddressDiploma AddressDiploma AddressDiploma AddressSHARQTC SHARQTC SHARQTC SHARQTC -------- Transcript RequestTranscript RequestTranscript RequestTranscript RequestGOAMTCH GOAMTCH GOAMTCH GOAMTCH -------- Address Matching FormAddress Matching FormAddress Matching FormAddress Matching FormSOASBGI SOASBGI SOASBGI SOASBGI -------- High School / College Code AddressHigh School / College Code AddressHigh School / College Code AddressHigh School / College Code AddressGOAADDRGOAADDRGOAADDRGOAADDR -------- Address Review and Verification FormAddress Review and Verification FormAddress Review and Verification FormAddress Review and Verification Form


Batch Address VerificationPL/SQL Procedures for Batch updates of existing address records

Addresses are updated “in-place” in the database without exporting or importing

The following 11 Banner tables are supported:SPRADDR -- Student / HR / Finance / Alumni Address TableSARADDR -- Electronics Admission Address TableSPREMRG -- Emergency Contact Address TableSHTTRAN -- Transcript Request TableSHBDIPL -- Diploma Address TableSPTADDR -- Temporary SPRADDR TableROTADDR -- Financial Aid Temporary SPRADDR TableSRTADDR -- Electronic Prospect Address Table loaded from search and test

score tapes, or entered on Web for ProspectsSOBSBGI -- College Code Address TableSRTHSCH -- High School Address TableSRTPCOL -- Prior College Address Table


/******************************************************************************************

* Procedure Name : Batch_Verify_SPRADDR

*

* Description : Batch Verify the addresses in the Banner Student Address table (SPRADDR)

* * fn_max_verify

* - maximum number of address to verify

* * fb_update

* - TRUE = update the record and show results

* - FALSE = don't update, only show results

* * fb_only_unverified

* - TRUE = Only update unverified records

* - FALSE = Update ALL records

* * fv_address_type

* - Banner Address Type - only verify this type

* - NULL = to verify all address types

* * fn_days_back

* - Verify Addresses that were updated/created this many days back

* - Note: You can enter fractional days such as 0.25, 0.5, etc

* - NULL = all addresses

* * fb_skip_international

* - Skip international address verification in batch mode

* * fb_verify_inactive

* - Verify inactive addresses as well as active ones

* - This essentially ignores the "to date" and status indicator on the record

* * fd_from_date / fd_to_date

* - Specify a date range for the verification based on SPRADDR_ACTIVITY_DATE

* - This is useful for segmenting the data to create parallel batch procedures,

* increasing throughput

******************************************************************************************/

PROCEDURE Batch_Verify_SPRADDR (

fn_max_verify IN NUMBER DEFAULT 3000000

,fb_update IN BOOLEAN DEFAULT TRUE

,fb_only_unverified IN BOOLEAN DEFAULT TRUE

,fv_address_type IN VARCHAR2 DEFAULT NULL /* SPRADDR_ATYP_CODE */

,fn_days_back IN NUMBER DEFAULT NULL

,fb_skip_international IN BOOLEAN DEFAULT TRUE

,fb_verify_inactive IN BOOLEAN DEFAULT FALSE

,fd_from_date IN DATE DEFAULT NULL

,fd_to_date IN DATE DEFAULT NULL

);

Batch Verification Procedure


ReportingSupplied Reports:

Address Error Summaryshows the detail of all addresses with deliverability errors

Compare Address Changesshows before and after addresses after a batch verification

Duplicate Addressesshows duplicate addresses after all records have been standardized

Radius Searchshows all alumni within a specified radius of a Zip Code


Address Error Summary Reports---------------------------

ADDRESS ERROR SUMMARY

---------------------------

Code Description

---------- ------------------------------------------------------

. No Error 449

U Unknown Street 16

R Range Error 9

N1 Suite/Apt Missing 5

CC Suite/Apt Invalid 12

M3 Street Number Invalid 7

M Multiple Matches 1

Z Invalid ZIP/Postal Code 1

---------- ------------------------------------------------------

Total: 500

Error Summary Reports – can print before updating the addresses& after updating to see difference


Compare Address Changes Report


Data Enhancement Services Change of Address Processing

List Enhancement


Change of Address ProcessingUSPS statistic:USPS statistic:USPS statistic:USPS statistic: 18% of the population moves each year18% of the population moves each year18% of the population moves each year18% of the population moves each year.Solution - locate lost individuals and be proactive as they move!

NCOANCOANCOANCOALinkLinkLinkLink – NNNNational ational ational ational CCCChange hange hange hange OOOOf f f f AAAAddressddressddressddress� USPS change of address processing for residential and business

� Pre-built interfaces for exporting / importing / reporting

MMMMCOACOACOACOA –––– MultiMultiMultiMulti----SourceSourceSourceSource CCCChange hange hange hange OOOOf f f f AAAAddressddressddressddress� Collection of address changes compiled from several sources:

• credit card companies, banks, magazine subscriptions, catalog houses, insurance groups, and utility companies

CCOACCOACCOACCOA –––– CCCCanadian anadian anadian anadian CCCChange hange hange hange OOOOf f f f AAAAddressddressddressddress


List Enhancement OptionsConsumer & Cell Phone AppendConsumer & Cell Phone AppendConsumer & Cell Phone AppendConsumer & Cell Phone Append� Adds phone numbers to your records based on the provided

name and address

Email AppendEmail AppendEmail AppendEmail Append� Adds residential email addresses to your records


CLEAN_Address ArchitectureEnterprise ArchitectureEnterprise ArchitectureEnterprise ArchitectureEnterprise Architecture

Seamless Fail-over

Redundancy / Load Balancing / Scalability

Oracle RAC (Real Application Clusters)

Centralized ArchitectureCentralized ArchitectureCentralized ArchitectureCentralized ArchitectureSOA – Service Oriented Architecture

Multiple databases can be supported from one CLEAN_Address server installation (Development, Test, Production)

CLEAN_Address server connects to database on back-end


Enterprise Architecture

OracleDatabase

CLEAN_AddressServer

– Seamless Fail-over

– Redundancy

– Load Balancing

– Scalability

– 100% Availability

O/S Platforms:

• Windows

• Linux

• Solaris

• AIX

• HP-UX

EmbeddedUSPS

Database (2GB)

CLEAN_AddressServer

EmbeddedUSPS

Database (2GB)

SQL Net /Oracle Pipes

SQL Net /Oracle Pipes


Service Oriented Architecture (SOA)Oracle

Database-

Development

CLEAN_AddressServer

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

Colleague-

Production

OracleDatabase

-PeopleSoft

CLEAN_AddressServer

OracleDatabase

-Alumni


Database Architecture – BannerOracle Database

BANNER (BANINST1)

SPRADDR

GTVZIPC

STVCNTY

SPRTELE

CLNADDR

CLEAN_Address.Verify

CLEAN_Address_Banner

BatchVerification

for 15 tables

BWGKOADRWeb Self-service

CLN_Postal_Codes

CLEAN_AddressServer

Banner Forms INB/Web Self-Service

Transaction

CLEAN_Address_Banner_UE

CLEAN_Address_Banner_AJAX

Web Self-ServiceReal-Time AJAX

Sync PostalCodes / County

GOREMAL

EmbeddedUSPS

Database (2GB)


Database Architecture – PeopleSoft Oracle Database

PeopleSoft (sysadm)

PS_Addresses

PS_State_Names_Tbl

PS_Country_Tbl

PS_Personal_Phone

CLNADDR

CLEAN_Address.Verify

CLEAN_Address_PeopleSoft

CLN_Postal_Codes

PeopleSoft Screens/ Web Self-Service

CLEAN_Address_PeopleSoft_UE

CLN_Address_PeopleSoft

BatchVerification

CLEAN_Address_PeopleSoft_AJAX

CLEAN_AddressServer

EmbeddedUSPS

Database (2GB)

SQL*Net /TCP/IP


User Exit PackageA User Exit package is supplied to support customizations of business rules� Provides PRE and POST transaction triggers

� Enables customization or skipping by address typePre_Address_Verify:

-- Don't verify Campus Addresses (CA) or Dorm Rooms (DM)

if upper(fv_address_type) in ('CA', 'DM') then

fb_verified := TRUE;

end if;

Post_Address_Verify:

-- Use different rules for Alumni (AL) addresses

if upper(fv_address_type) in ('AL') then

-- Expand street suffix

CLEAN_Address_Banner.Set_Expand_Street_Suffix(TRUE);

-- Expand the street directional N S E W -> North, South, East, West, etc.

CLEAN_Address.Expand_Street_Directional(f_address_rec.address_line_1, 30);



CLEAN_Address.Expand_Street_Directional(f_address_rec.address, 30);

-- Try to put apt on same line as street, or on the line after if it doesn't fit

CLEAN_Address_Banner.Set_Suite_Apt_Before_Street(FALSE);

CLEAN_Address_Banner.Set_Append_Suite_To_Street(TRUE);

end if;


International Address VerificationSupports 240 Countries and Territories


CLEAN_Address Engine(COM Object / Shared Object

on a server in your data center)

Data storesData is kept current through subscription updates

Oracle Database

clnaddrdCLEAN_Address

daemon for Oracle

cleanzipSyncs ZIP/City/State

to reference table

clnaddrwsCLEAN_Address

Web Service

cleanfileFlat Text File

processor

SQL ServerDatabase

SOAP/XMLWeb Service

Input file(plain text)

Output file(plain text)

USPS DataDPV – Delivery Point Validation

LACSLink™ – Rural Route conversion

RBDI – Residential Business Delivery Indicator

GeoCoder – ZIP+4 level Lat/Long; Census Tract

MatchMaker – Near Roof-Top level Lat / Long

Canada PostData

Global DataLocal or Web Service

NameData

PhoneData

EmailDNS

Lookup

CLNADDR Schema

Oracle PL/SQL procedure:

CLEAN_Address.Verify()

Census+

Demographic

Data

NCOALink™National Change

of AddressProcessing

(USPS or

Multi-Source)

DB2Database

MySQLDatabase

CLEAN_Address Suite Architecture

CLEAN_EntryData Entry Screen


County Code ConsiderationsCLEAN_Address can support County codes of any format

5-digit FIPS County Codes

2-char State + 3-digit FIPS County Code

3-digit FIPS County Codes (single state)

Custom county codes with name variations (Reverse Lookup from name)

Example:

12099 - Palm Beach - FIPS

FL099 - Palm Beach - Alpha FIPS

099 - Palm Beach - Single State 3-digit FIPS

123 - Palm Beach - Single State custom county code


CLEAN_Address PricingAnnual Subscription - based on the Student FTE

Includes:

SIS integration (Banner, PeopleSoft, Colleague, Advance)

Bi-monthly updates of the USPS data

Free Technical Support and Maintenance

Free SIS upgrade patches

Options:International Address Verification

Change of Address Processing / Phone & Email Append

GeoCoder down to house level

CLEAN_Entry


Free Technical Support9am-6pm EST Mon-Fri24-Hour Emergency Support Pager

Support Methods:Phone: 561-395-9322 / 877-784-0003Email: [email protected]: www.RunnerTechnologies.com

Support Page

FAQ Pages

Newsgroup: CLEAN_Address newsgroups- sign up from our Web site


MaintenanceA bimonthly subscription service keeps the USPS data updated every 2 months

A monthly subscription option is also available

Zero downtime when applying updates

Existing address records should be verified at least every 6 months using the batch address verification procedure

ZIP Codes, ZIP+4, and street names can change periodically


Top 10 Features1. No client software to install

2. Seamless real-time integration with no popup windows

3. Integrated batch processing in the database without exporting / importing

4. Customizable business rules and data standards down to address type level

5. Address errors are recorded in the database for easy reporting

6. Telephone, Name, and Email checking in real-time and batch.

7. Enterprise Service Oriented Architecture (SOA) - one installation serves all databases

8. Complete solution for all entry points: Banner INB, Web self-service, and Batch for existing records

9. Low annual subscription pricing for the entire institution (Prospect to Alumni + HR and Finance)

10. Support for every country in the world with Web service option


SummaryCLEAN_Address will

Save money by reducing or eliminating undeliverable mail

Improve the quality of your database

Save time by reducing administrative effort to deal with data quality issues

CLEAN_Address provides 100% seamless real-time and batch address verification for Banner

Affordable pricing for every institution size


Contact InformationBarry AllweissVP Sales & Marketing

561-395-9322 [email protected] Technologies6530 West Rogers Cir, Ste 31Boca Raton, FL 33487-2753www.RunnerTech.com

aacrao 2013 - address verification in higher education › files › rpq8yugeqb5bun79… · address...

Documents