Skip to content

Documentation: Data Integrity Issues related to Address data

Jump to bottom
Don Miller edited this page Jun 14, 2021 · 1 revision

What makes for a complete address?

Generally, the following information is required for a complete address:

  • Street address (which may have multiple lines as described below)
  • City
  • State
  • Postal Code/ZIP Code
  • County
  • Country (enter default value)

TIP: To avoid incomplete or inconsistent addresses, consider drop-down options where address information is entered and validations to ensure all fields are entered. And where appropriate, use default values such as “USA”, rather than a mixture of “USA, US, and United States”, for an organization that received nearly all domestic gifts.

Tip: For multiline Addresses in Excel use the Char(10) as the line feed value:

With the import wizard in salesforce: you can import into Mailing Street 1, Mailing street 2 and Mailing street 3. These all make up one field within salesforce accounts and contacts but are broken down into multiple lines in that field.

If you use Data Loader, you do not have the option to import into the 3 lines [Confirm what object this refers to - contact?]: Therefore, you will need to prepare your excel .csv file so that the 3 mailing streets cells that you exported out of salesforce are concatenated into one cell in excel with carriage returns.

Data Loader will allow you to import addresses into three lines on the address object. With any data load you will need the address record id and then populate each street number field 1, 2 or 3 in the same row field on the address object.

To accomplish this use: CHAR(10) to insert carriage returns into the concatenate function.

For example, if you have 3 mailing street columns a1, b1 and c1 use: Concatenate(a1, CHAR(10), b1, CHAR(10), c1). This will merge all the mailing street cells into one with carriage returns. Next you will need to highlight that column with the Concatenate results in it and right click, chose: format cells, chose the "Alignment Tab" and then check the "Wrap text" box.

Tip:
Validation rules to check whether address is complete. Here is an example:

It checks if the Country, State, Street, City and Zip code is blank. If it is blank a message should be displayed address fields cannot be empty.

   AND ( OR ( ISBLANK( Billing_Street_c ),

  ISBLANK( Billing_City_c ), 

 ISBLANK( Billing_State_c ), 

 ISBLANK( Billing_Country_c ), 

 ISBLANK( Billing_Zip_Code_c ) ),

NOT( AND ( ISBLANK( Billing_Street_c ),

ISBLANK( Billing_City_c ),

ISBLANK( Billing_State_c ),

ISBLANK( Billing_Country_c ),

ISBLANK( Billing_Zip_Code_c ) ) ) )

  1. Address exists but street number is not within the specified address range Create a custom picklist field called StreetNumber to store the valid street numbers
    for that address so that whenever the address is entered it checks whether that street number is within that range .If the street number is not within the range then it will
    display an error message saying the street number doesn’t exist for the specified Address.

    1. Checking the address contains required format which include No punctuation. One space between city and state. Two spaces between state and Zip code.

NPSP Documentation link on Address Management in general: https://powerofus.force.com/s/article/NPSP-Address-Management-Overview

NPSP Address Verification Options within NPSP

The NPSP documentation link: https://powerofus.force.com/s/article/NPSP-Configure-Addresses

TIP/Note: NPSP Address Verification skips any Address record that has the Verified checkbox checked (Need to confirm if this means the Address object record - I think the NPSP Address Verifications are done against the Address object which then in turn updates the Account and/or Contact records.).

Cicero API:

  • Cicero’s documentation about NPSP integration: https://www.cicerodata.com/npsp/
  • The Cicero integration for the NPSP is a native application that was built by the developers at Salesforce. If you have additional questions about Salesforce functionality and how it works, check out the Salesforce Power of Us Hub.
  • The Cicero Address Verification API requires you to enter an API Key. This should be available to you in your Cicero account. Visit the Azavea blog for more information.
  • Note: When using the Cicero Address Verification API, you can also select Prevent Address Overwrite to prevent Cicero from overwriting existing addresses during the verification process.
  • Note: The Cicero API will verify the address and append the district information to the address record. It’s important to note the address record is associated with the household in Salesforce.
    • Note: Need to confirm - it is unclear as to if the NPSP integration with Cicero Address Verification API only verifies U.S.-based addresses.

Google Geocoding API:

  • The Google Geocoding Address Verification API requires you to enter an API Key. This should be available to you in your Google account's Developers Console. See this Chatter thread for more information. SmartyStreets API
  • The SmartyStreets Address Verification API requires you to enter both an Authentication ID and an Authentication Token. These should be available to you in the API Keys section of your SmartyStreets account. Visit the SmartyStreets website for more information.
  • Note: The NPSP integration with SmartySheets Address Verification API only verifies U.S.-based addresses.
  • Note: Click the Verify All Addresses button to mass verify addresses that already exist in your organization. Remember, Salesforce only verifies those addresses you enter after you've set up your address verification service. This option is available with the SmartyStreets API only.

SmartyStreets: This is a helpful link for the team with more info on different types of address APIs - but probably not good for this document -- just general info https://www.smartystreets.com/articles/address-apis

Comparison of the three NPSP Address verification products abilities - (probably want to put this into a table of some sort):

Can it do non-US address verification? Cicero API - ? Google Geocoding API - Yes. It can verify non-US Addresses. SmartySheets API - No. US-based Addresses only. Can verify address but not overwrite existing address Cicero API - Yes. It allows this option Google Geocoding API - No. It will always overwrite. SmartySheets API - ? Rejects Ambiguous Addresses:
CiceroAPI - Yes. It allows this option. Reject Ambiguous Addresses unselected for Salesforce to choose the first suggested address when there are multiple matches. Google Geocoding API - ? SmartySheets API - ? Does it allow bulk verification of existing data/Address entries? Cicero API - No. It only begins verifying addresses and matching to districts once you turn on the integration going forward. It does not automatically verify your existing addresses in Salesforce. However, Cicero’s District Match tool makes it easy to export and update all of your addresses at once. Google Geocoding API - ? SmartySheets API - Yes. It has an option that allows bulk verification of existing data/Address entries.

General

TIP: Run Address exception reports using address verified/not verified/not zip code etc. fields on address report. Include in data hygiene plan

Gotchas: If you have a comma within Street Address - this causes Google Geocoding API to drop data to the right of the comma (123 Main Street, Suite 100 will be replaced with 123 Main Street). No error to let you know this!

Address Formatting - United States Postal Service

Now that you’ve validated the complete address information, format the addresses to be consistent with standardized format rules used by the United States Postal Service.

Domestic: USPS formatting rules are the convention for all direct mail handled by the US Postal Service. Extra charges may be assessed by printers to change addresses into the standard USPS format.

Complete information: https://pe.usps.com/text/pub28/welcome.htm

Sample guide:

  • Apartment - 222 Sutter St Apt 4
  • Avenue - 1227 23'd Ave
  • Boulevard - 55 Geary Blvd Ste 506
  • Box - PO Box 23456
  • Building - The Russ Bldg Ste 405
  • Court - 45 Queen Ct
  • Center - 56 Knollwood Ctr
  • Circle - 5 Sunset Cir
  • Department - 555 Market St Dept 405
  • Drive - 55 Rodeo Dr
  • Floor - 4 Embarcadero Ctr 18th Fl
  • Lane - 27 Lois Ln
  • Suite - 505 Market St Ste 202
  • Room - 55 Federal Way Rm 7
  • Parkway - 46 Rabbit Run Pkwy
  • Place - 78 Peyton Pl
  • Plaza - 42 Hemmingway Plz
  • PO Box - PO Box 789
  • Road - 26 Bronxville Rd
  • Route - 67 Lindley Ln Rte 66
  • Square - 23 Berkeley Sq
  • Street - 45 Scott St Apt 56
  • Terrace - 57 Scenic Ter
  • Unit - 123 Spruce St Unit 3
  • Way - 74 Wrong Way

Other examples of USPS preferred or acceptable

  • County Road N E (USPS preferred)
  • County Road N East (USPS acceptable)

International formats:

Clone this wiki locally