Address validation (also known as address verification) is a rule that ensures street and postal addresses exist. An address can be verified in one of two ways: upfront, when a user searches for an address that is not correct or complete, or by cleansing, matching and formatting data in a database against reference postal data.
In the application, there are changes in:
- Staging Configuration (Profiling section)
- Rule Detail (Workbench)
Staging Configuration for Address Validation Rules
Note: This is going to be required for first time setup on Oracle source only. Also, it is mandatory to setup this, otherwise on AV rules execution, user will be notified by an error message in remarks of rule that "MM configurations missing'.
Step 1 : Navigate to Profile -> Profiling -> Select an Oracle data source, select main table and staging configuration table. In the below screenshot, main table is CUSTOMER_ADDRESSES and staging table is CUSTOMER_ADDRESSES_DVSUM.
Note: It is required to make sure that Staging table exists for AV rule to be run.
Step 2 : Select main table and click on show details
Step 3 : Select Staging configuration tab and click on Edit
Steps 4 : Setting up Configuration, Additional Configuration, Staging Configuration fields
Configuration setup
select Subject Area, Golden Record Criteria, check Enable Validation, provide Description
Additional Configuration setup
check Write-back Post Process i.e if user wants to enable write-back on main table, Case type i.e if user wants to keep the format of Suggested Address as it is fetched by default, in lower or UPPER case.
Staging Configuration setup
select Staging table, Match key, Match count, Match rule, Record identifier, Golden record ref, Record type, Merge status, Time stamp identifier and hit Save button given on top as shown below;
Step 5 : On Saving the changes, user will be notified that changes are saved successfully. Also, an error message will be thrown displaying that "Fields are not mapped to address data types Address Line 1 or Country" and on bottom, Edit Mapping grid starts appearing as shown below ;
Edit Mapping
Step 6 : Now once staging configuration is set, click on Edit button to edit mapping. Because at first, randomly Match Data Type gets assigned to column names, so user is required to update those Address Match Data Types w.r.t required columns.
User can select all columns by clicking Select All or can Include individual columns as shown below;
Step 7 : Columns are mandatory to be mapped against following Address Match Data Types
- City
- Country
- State
- US ZIP code
- ADDRESS LINE 1
Following address match data types will be mapped as:
- Address Line 1 (Street Address)
- Address Line 2 (Suit/Apartment)
- Address Line 3 (Info/Atten)
- Other Address Lines
Google response of street address will writeback to Address Line 1. Likewise suit/apartment, Info/Atten will writeback to Address line 2 and Address Line 3 respectively. In this way, the quality of addresses data will be improved.
Step 8 : Completeness Rule
Below is default completeness rule.
- address_line1 is mandatory.
- country is mandatory.
- From city and zip_code, one of these is mandatory
User can add it as well either for all records or with specific filtered out records. For example given below; CUST_STATE column is selected as mandatory field for all addresses that has country as 'US'.
Address Validation Rule
Step 9 : Go to Manage Rules, Click Add Rule, Select Address Validation rule from Process Quality.
Step 10 : Provide all the required fields here.
Note: Table name field should be Main table not the staging configuration table as Rule will be created on Main table.
Step 11: Go to Rule detail page and Run the rule as shown below;
Step 12: Once Rule is executed, following data is fetched;
- Run Status
- Scanned Records (Total records got scanned)
- Review Status
a. Pending (requires review)
b. Modified (if user accepts suggested address OR current address fetched from DB gets updated and Saved)
c. Skipped (If user skips suggested address) - Commit Status
a. committed
b. commit failed - Readiness Score
- Remarks
Step 13 : Go to Addresses tab, addresses that got matched will be displayed under the grid. If user wants to edit/update any record, can be done by Clicking on Review button as shown below;
- Address Line 1, Address Line 2, Address Line 3, Other address lines, City, state, country are mapped together in Complete Address column.
- Complete Address detail information is fetched from User's DB
Match Statuses
Step 14 Following are the Match statuses
- INC (Incomplete) indicates that the provided address data is not complete and is returned if any of the below conditions is fulfilled:
- address_line1 is empty.
- country is empty.
- city and zip_code both are empty.
- OK is returned if following conditions are fulfilled:
- matched_location_type is “ROOFTOP”.
- matched_address_line1 exists.
- matched_city exists.
- matched_zip_code exists.
- matched_country exists.
- Provided address and validated address are exactly same without any case difference.
- SD (Standardized) is returned if following conditions are fulfilled:
- matched_location_type is not empty.
- matched_address_line1 exists.
- matched_city exists.
- matched_zip_code exists.
- matched_country exists.
- There is only Case difference between provided address and validated address (example: HENRY STREET, Henry Street).
- There is only ZIP Code deference between provided address and validated address (example: 63122-6604, 63122 or 63122, 63122-6604).
- There is abbreviated form found in the validated address (example: Henry Street, Henry St).
- ER (Enrich) is returned if following conditions are fulfilled:
- matched_location_type is “ROOFTOP”.
- matched_address_line1 exists.
- matched_city exists.
- matched_zip_code exists.
- matched_country exists.
- There is at the very least 50% match difference between provided address_line1 + address_line2 and matched_address_line1+ matched_address_line2.
- Validated address is a bit changed from provided address (example: Henry Road, Henry St).
- There is only ZIP Code deference between provided address, validated address (example: 63121, 63122).
- NM (No match)is returned if none of the above status is returned.
Record Statuses
Step 15
There are following record statuses;
- Update (If user Accept Suggestion)
- Skip (If user Skips Suggestion)
- Modified update (Once current address fetched from DB gets updated and Saved by user)
Step 16 : In this screenshot, this icon represents that which address from suggested or complete address is going to be writeback
User can Edit current address from review window, validate it. If found then success message will be displayed otherwise error message will be displayed. Also, "Edited" text right next to Current address label will show. User can also reset the value to the original value.
Review Window: 
Step 17 Once record statuses gets Modified update, Update, Skip then count for Pending records decreases and Count for Reviewed records increases.
Step 18 User can Select all records and perform any action (Accept or Skip suggestion), clear all records selection or Discard.
Step 19 Once user clicks Finish button given on bottom, then Commit button appears in Address tab on rule detail page.
Step 20 Once commit is done, then Fixed records and Reviewed records count gets updated in Rule summary
Step 21 User can also create view 
Step 22 User can also add comments on Match Status column. For that, click on any status, add comment, and Save it. Comment icon with count starts appearing on main grid. The comment count also increases with every new comment added. If there's no comment then on hovering comment icon appears.
Postal Addressing Standards supported in Address Validation rule (Bag of words)
|
Primary Street Suffix Name |
Commonly Used Street Suffix or Abbreviation |
Postal Service Standard Suffix Abbreviation |
|
AVENUE |
AV |
AVE |
|
AVE |
||
|
AVEN |
||
|
AVENU |
||
|
AVENUE |
||
|
AVN |
||
|
AVNUE |
||
|
BOULEVARD |
BLVD |
BLVD |
|
BOUL |
||
|
BOULEVARD |
||
|
BOULV |
||
|
CAUSEWAY |
CAUSEWAY |
CSWY |
|
CAUSWA |
||
|
CSWY |
||
|
CIRCLE |
CIR |
CIR |
|
CIRC |
||
|
CIRCL |
||
|
CIRCLE |
||
|
CRCL |
||
|
CRCLE |
||
|
COURT |
COURT |
CT |
|
CT |
||
|
DRIVE |
DR |
DR |
|
DRIV |
||
|
DRIVE |
||
|
DRV |
||
|
HIGHWAY |
HIGHWAY |
HWY |
|
HIGHWY |
||
|
HIWAY |
||
|
HIWY |
||
|
HWAY |
||
|
HWY |
||
|
LANE |
LANE |
LN |
|
LN |
||
|
PARKWAY |
PARKWAY |
PKWY |
|
PARKWY |
||
|
PKWAY |
||
|
PKWY |
||
|
PKY |
||
|
PLACE |
PL |
PL |
|
ROAD |
RD |
RD |
|
ROAD |
||
|
STREET |
STREET |
ST |
|
STRT |
||
|
ST |
||
|
STR |
||
|
TRAIL |
TRAIL |
TRL |
|
TRAILS |
||
|
TRL |
||
|
TRLS |
|
Full Form |
Abbreviation |
|
Apartment |
APT |
|
Building |
BLDG |
|
Department |
DEPT |
|
Floor |
FL |
|
Unit |
UNIT |
|
Suite |
STE |
|
Room |
RM |
Guide on using Address Validation API
Here is a link to an article for a complete guide on address validation API.
0 Comments