It’s Week 10 and in real time, COVID-19 is a top news headline. All the cleaning and handwashing inspired me to create this week’s assignment: data cleanup. Put your One Hour on the calendar this week to clean the names and addresses in your database, so you can be confident that your data source will reflect well on you.
Have you ever received a piece of mail with your name misspelled or with an out-of-date version of your name? Last week I received a solicitation letter – from an organization where I’ve been involved for many years – and my name was correct in the address block, but my former surname was in the salutation. Again. Accurate names and addresses give recipients the impression that you are organized, with good attention to detail, and above all, that you care about them.
The first 10 years of my career, I was responsible to manage the fundraising database. Here are my favorite ways to find and fix name and address errors:
If your data is in Excel, make a copy with all the name, address, and salutation fields, and record ID if you use it. Either keep a copy as backup, or find errors while using the duplicate copy, and make your changes in the original. If you are working from a CRM, query for all the records you would use in a mailing, and output relevant fields:
Record ID
Constituency
Title or Honorific
First Name
Middle Name or Initial
Last Name
Suffix 1
Suffix 2 (If you’re working with academics, attorneys, or physicians, it’s wise to allow two Suffix fields)
Nickname
Organization/Business
Address 1
Address 2
City
State
ZIP
Salutation or Formal Greeting
Alternate or Informal Salutation
It’s great if your CRM allows you to sort on these fields on the output screen and make changes easily in the record you’re viewing. If that’s not easy, export it to Excel as a scratch copy, find the errors there, and toggle back to the CRM to make updates.
Sort 1: Sort by Last Name, then First Name
This configuration will help you to eliminate duplicates three ways:
First Review: Scan the list for doubles and almost-doubles, which happens when people use nicknames or go by their middle names. You might see two records for the same person, one with Christina in the First Name Field and another with Tina, or a record with M. Louise in the First Name field and another with Louise. CRMs with a Middle Initial field instead of a Middle Name field can give you a little logistical challenge. Some CRMs have a Duplicate Finder utility but if they only look for an exact match, they will miss examples like these.
Second Review: You might also find duplicates with different Constituency codes. Consolidate records under the strongest relationship to your organization.
Third Review: Ensure that your name and salutation fields agree. Manipulate the sheet so you see just relevant fields: hide all columns except Title, Last Name, and Formal Salutation, then scan to see if they don’t agree. If you also work with informal greetings, restore your sheet to full view, then hide all columns except First Name, Nickname, and Informal Salutation to ensure that you have the right contents for your future communications.
Sort 2: Address 1, then Last Name
Purpose: Listing all records by street address helps you to identify multiple records in the same household. You may want to select one record to receive all mail and make others inactive, or you may choose to combine records into one. Think about how you communicate, why you’re trying to reach multiple people under the same roof, and what works best for your organization. You probably don’t want to send two annual reports to the same house, or continue to solicit one household member after the other has given — but you would in the case of, say, siblings who are alumni of your school, sharing a house. Those are separate relationships that stand on their own.
Sort 3: City, then ZIP
Purpose: Find and fix misspelled town names. I’ve seen “Philadelphia” misspelled at least three ways and this is an easy fix because the typos will display in a cluster and should be easy to spot. If your CRM has a table for cities instead of free text, make sure it’s clean too, so no one can select a table option containing an error.
Sort 4: ZIP code, then City
Purpose: Find and fix errors, usually caused by incomplete updating or typos, where the City has the wrong ZIP assigned. The odd entries should jump right out at you as you scan the columns.
Sort 5: Suffix
Sort your data by Suffix, or first by Suffix 1 and then Suffix 2. The purpose is to ensure that your data is consistent. Do you want to use MD or M.D.? You may choose to hard-code leading commas in the field (e.g. “, MD”) to prevent free-floating commas in merged address output. Decide what works best for your organization.
There are my favorite ways to keep data clean and organized. This is also a task that you can delegate to an eagle-eyed staff member or volunteer, so if that’s an option, I encourage you to take it. Have a great week!