Address Cleanup Project
In our July data extract, Polaris received 648,040 address fields total from Symphony. This number includes information found in Symphony address 1, 2 and 3 fields. During the December reload, CCS and Polaris worked to adjust the original city and zip code mapping to reduce errors and invalid address fields. At the end of the process, roughly 13,000 address fields remained that could not be mapped properly.
Data Cleanup Assignment
City and Zip Code Mapping
Polaris authenticates the city and zip code values in a patron record against a Postal Code table. The Postal Code table includes unique entries for each combination of postal code, city, state and county. As you’ll see in the screenshot below, some postal codes have multiple entries in the Postal Code table, because they encompass multiple cities.
In our initial data load of the Polaris test environment, cities in patron addresses were being automatically populated by the first city and zip code combination for a given zip code in the Postal Code table. In many cases, this was incorrect. For example, all patrons in zip code 60047 were assigned a city of Hawthorn Woods, even though that zip code includes Lake Zurich, Hawthorn Woods and Long Grove. To remedy the issue, CCS staff worked closely with Polaris to identify a number of sequential steps to properly map address information. This process was run during the time trial and data reload in first two weeks of December.
Step 1: Match both the incoming CITY and POSTAL code values to the Polaris Postal Codes table, updating the record if a match was found. This process updated the majority of addresses in the test database--552,980!
Step 2: Match the CITY and first 5 digits only of the POSTAL code to the Polaris Postal Codes table. The Postal Codes table stores only 5-digit zip codes. There were some zip codes in our data extract that had a 9-character code, which originally prevented these addresses from mapping correctly. This updated an additional 41,464 codes in Polaris test.
Step 3 : Map incoming postal codes of 99999 to the Polaris zip code of 99999. The 99999 postal code indicates an invalid address in both Symphony and in Polaris. This updated an additional 2,093 address fields.
Step 4: There are some incoming postal codes that contain a note similar to ‘DO NOT MAIL." Since Polaris cannot handle text in the postal code field, these were updated and the postal code set to 99999. This migrated an additional 1,325 codes.
Step 5: Update incoming address fields by ignoring zip code and looking at CITY and STATE. This could be done only on those address entries for City and State where an individual city had only one specific postal code (ex: Lincolnwood has two postal codes : 60646 and 60712. Polaris couldn't match on City and State because there were two potential combinations). If a match was found for City and State, the corresponding zip code was automatically input.
At the end of the process, over 40,000 address fields remained unmapped. The majority of those fields had no information in them aside from patron barcode. However,13,000 address fields remained that contained some address information, but could not be mapped through the above steps. There are a number of potential causes, including incorrect spellings, incomplete information, alternate use of the field and data entry errors in Symphony. These addresses need to be cleaned up in Symphony in order to map over properly to Polaris. Incorrect addresses in Polaris will prevent print notices and mailings from being sent and limit staff ability to verify patron information.
Below, you'll find examples of unmapped address fields, including causes and solutions.
Example 1: Misspelled Cities
If a city was misspelled in Symphony, Polaris was unable to find a correct match in their zip code/city tables:
This resulted in missing city, zip code and state information in Polaris.
In this example, the city needs to be corrected to Winthrop Harbor, IL.
Example 2: Incorrect Formatting
In the first screen shot below, there is no comma in between Lake Geneva and WI. The mapping process thought that "Lake Geneva WI" was the city name, and could not find a match in the Postal Code table. A similar error occurred in the second screen shot. "Elgin Il." is not a valid city in the Postal Code table.
Both examples resulted in missing zip, city and state values. The proper format in Symphony should be Lake Geneva, WI and Elgin, IL.
Example 3: Information in Incorrect Fields
In some instances, information was input into the wrong field in Symphony. In the example below, a telephone number was input into the zip code field, and the zip code input into the city/state field. The Polaris process was unable to find a zip code match, and so the address was mapped to an invalid postal code. In this example, the city/state format should be Libertyville, IL. The zip code field should read 60048. The telephone in the Zip code field needs to be moved to the phone field.
Example 4: Alternate Uses of Address fields
In some cases, the address fields in Symphony are being used to store additional patron information, such as guardian name. This information needs to be moved elsewhere in the patron record if libraries wish to retain it in Polaris. CCS may be able to assist with bulk cleanup for some instances.
Debra will be emailing each library lead with the library's list of library cards and addresses that could not be mapped properly. Libraries are responsible for fixing their patron records prior to Friday, April 6. Please note that this document will contain only addresses that could not be mapped from the July 28 data extract. There may have been additional data entry errors that have occurred since. Libraries should review the Patrons by City/State report in Web Reports to identify additional patron records that may need to be cleaned up.