Address Validation Rule

 

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

NoteThis 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'.

mceclip0.png

mceclip1.png

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. 

1.jpg

Step 2 : Select main table and click on show details

2.jpg

Step 3 : Select Staging configuration tab and click on Edit

3.jpg

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.

4.jpg
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; 

 

4.1.jpg

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 ;

5.1.jpg

 

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; 

mceclip0.png

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. 

mceclip1.png

 

 

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'.

mceclip6.png

 

Address Validation Rule 

Step 9 : Go to Manage Rules, Click Add Rule, Select Address Validation rule from Process Quality.

9.jpg

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.

10.jpg

 

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

mceclip3.png

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

13.jpg

 

Match Statuses 

Step 14  Following are the Match statuses 

14.jpg

  • 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)mceclip8.png
  • Skip (If user Skips Suggestion) 
    mceclip10.png

  • Modified update (Once current address fetched from DB gets updated and Saved by user)
    mceclip7.png

    Step 16 : In this screenshot, this icon represents that which address from suggested or complete address is going to be writeback

    mceclip4.png

    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. 

    mceclip6.png


Review Window: 

mceclip5.png

Step 17  Once record statuses gets Modified update, Update, Skip then count for Pending records decreases and Count for Reviewed records increases. 

mceclip11.png

 

Step 18  User can Select all records and perform any action (Accept or Skip suggestion), clear all records selection or Discard.

mceclip12.png

Step 19  Once user clicks Finish button given on bottom, then Commit button appears in Address tab on rule detail page. 

mceclip13.png

mceclip14.png

 

Step 20 Once commit is done, then Fixed records and Reviewed records count gets updated in Rule summary

mceclip16.png

 

Step 21 User can also create view 

mceclip18.png

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. 

mceclip19.png

mceclip20.png

 

 

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.

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk