Address Validation Rule

 

Address validation, or address verification, is a rule ensuring that street and postal addresses are valid. It can be done upfront when a user searches for an incomplete or incorrect address, or by comparing and formatting data in a database with reference postal information.

In the application, there are changes in:

  • Staging Configuration (Profiling section)
  • Rule Detail (Workbench)

Staging Configuration for Address Validation Rules

Note: This setup is necessary only for the initial configuration on Oracle source. It's mandatory to complete this step; otherwise, during the execution of Address Validation rules, the user will receive an error message in the rule remarks stating "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 Address Validation 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
Choose the Subject Area, set Golden Record Criteria, tick the Enable Validation checkbox, and provide a Description.

Additional Configuration setup
Verify the "Write-back Post Process" option, meaning enable it if the user intends to activate write-back on the main table. Additionally, specify the "Case type" preference, indicating whether the user wants to maintain the format of the Suggested Address as fetched by default, either in lower or UPPER case.

 

4.jpg
Staging Configuration setup
Choose the Staging table, Match key, Match count, Match rule, Record identifier, Golden record ref, Record type, Merge status, Time stamp identifier, and then click the Save button located at the top, as illustrated below:

 

4.1.jpg

Step 5 : Upon saving the changes, a success notification will inform the user that the modifications have been saved successfully. Simultaneously, an error message will appear, indicating that "Fields are not mapped to address data types Address Line 1 or Country." At the bottom, the Edit Mapping grid will be displayed as illustrated below:

5.1.jpg

 

Edit Mapping

Step 6 : After configuring the staging settings, click on the Edit button to modify the mapping. Initially, Match Data Types are assigned randomly to column names, so the user needs to update these Address Match Data Types based on the required columns. The user can choose all columns by clicking Select All or include individual columns, as depicted 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 

The default completeness rule is as follows:

  • "address_line1" is mandatory.
  • "country" is mandatory.
  • Either "city" or "zip_code" must be mandatory.
    Users have the option to add this rule for all records or apply it selectively to specific filtered records. For instance, in the example provided, the `CUST_STATE` column is chosen as a mandatory field for all addresses where the country is set to 'US'.

mceclip6.png

 

Address Validation Rule 

Step 9 : Navigate to "Manage Rules," click on "Add Rule," and choose the Address Validation rule from the Process Quality category.

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: Visit the Rule Detail page and execute the rule, as shown below:

Step 12: After the rule is executed, the following data is retrieved:

  • 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 : Visit the Addresses tab, where the matched addresses will be visible in the grid. To edit or update any record, simply click on the 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 the presented screenshot, this icon indicates the specific address chosen for writeback from the suggested or complete address.

    mceclip4.png

    Users can edit the current address in the review window, validate it, and receive a success or error message accordingly. The label next to the current address will show "Edited," and users can also revert to the original value if needed.


mceclip6.png


Review Window: 

mceclip5.png

Step 17  When the record statuses are modified to Update, Skip, or Update, the count for pending records decreases, while the count for reviewed records increases.

mceclip11.png

 

Step 18  Users have the option to select all records and take actions such as accepting or skipping suggestions. They can also clear the selection for all records or choose to discard the changes.

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  Users can add comments to the Match Status column by clicking on any status, entering a comment, and saving it. A comment icon with a count will start to appear on the main grid, and the count increases with each new comment. If there are no comments, hovering over the comment icon will display it.

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