Better Donation Letters and Email through Data Hygiene (Part 3)
Spot Tricky Donor Database Errors with Nicknames and Foreign Language Characters
Editor’s Note: This blog is the third in a series on nonprofit database hygiene. Author Brianna Klink de Ruiz looks at why a clean donor database will help you connect better with donors and save you time (and money!). Plus, she includes step-by-step instructions on how to fix common data issues.
At this point, you should have data that’s free of special and extra characters, email typos, and bad addresses. (If not, check out Part 1 and Part 2).
Now, you're ready to tackle households and format names so you can find those duplicate donors hiding in your database. That’s going to save you money on postage and printing — not to mention create a better donor experience!
In other words, you’re ready to find out whether Peggy Clark, Margaret Clark, and Mrs. Robert Clark who all live in Sun City, AZ are the same person.
Issue 7: Misspelled Names
As America becomes more diverse, so have the donor names in our databases. But most of us have typical names with typical spellings.
The Social Security Administration even has a database of popular first names going back to 1879! (Don't go there now unless you want to while away several hours feeding your curiosity).
In its infinite wisdom, Microsoft has embedded thousands of first names in its spell check tool. We'll use that functionality to find likely errors. Note this technique works best for first names, but you can also use it for last names.
Step A: Pull contact list
1. From your donor database, pull a CSV or Excel list of your individual donors' names, regardless of whether they are in a larger household or not. Include:
a. DonorID
b. Salutation
c. First name
d. Last name
e. Email (I include this because sometimes people have their correct spelling in their email)
2. Sort the list A-Z by first name.
Step B: Correct misspelled first names
1. Select the first name column.
2. Click Review > Spelling
3. Go through the names Excel identifies as potential misspellings. If it's an error, fix it. If it's correct, click Add to Dictionary. This way, Excel will know it's correct the next time you run spell check.
(Optional) Step C: Correct misspelled last names
1. Repeat Step B on the last name column. This isn't as effective, but may be necessary if your database is particularly messy.
Step D: Prevent future misspellings
1. Make sure you verify new donor records every 1-4 weeks, depending on your volume of new donors and donations, to catch misspelled names.
Issue 8: Nicknames
If you’ve read our other data hygiene blogs, then you should have caught 80-90% of your database errors and duplicates by now. And that’s amazing work! Give yourself a pat on the back.
But that dastardly 80/20 rule is now rearing its Gorgon head. In my opinion, nicknames are the tentacles of donor databases. As soon as you remove one, another one pops up to take its place.
Take this example: In December, you receive a check from Margaret Jones and her street address. Then a couple months later, she donates online, entering Peggy in the first name field and using a P.O. Box address.
Then her husband sponsors a table for the gala, using Mr. & Mrs. Rob Jones. They write a check at the event from their private foundation – The R & M Jones Foundation – with a different P.O. Box address and signed by Maggie Jones. Oh, the insanity!
But we can slay these monsters.
Here's how I recommend doing this chore (And if anyone figures out an easier way, please email me!).
Step A: Pull contact list
1. From your donor database, pull a CSV or Excel list of your individual donors' names, regardless of whether they are in a larger household or not. Include:
a. DonorID
b. First name
c. Middle initial
d. Last name
e. Number of total gifts
f. Date of last gift
g. City
h. State
i. Email
2. Sort the list A-Z in this order:
a. First name
b. Date of last gift
c. Total gifts
Step B: Limit your field
In my experience, donor records with nicknames and a history of only 1 or 2 gifts are likely suspects for duplicates. Your experience may vary.
1. On the total number of gifts column, click the filter drop-down box. Click Select All to deselect all items, then select 1 and 2 and click OK. (If you have a particularly large database, you may want to select 1 and then repeat this process for your 2 gift donors.)
Step C: Identify suspects
1. Scan the column of first names for likely nicknames (reference this list, but start to develop your own internal list based on your donors).
2. Highlight the rows that have a potential nickname.
Step D: Analyze your suspects
1. Click the filter drop-down box. Select Filter by Color > Filter by Cell Color > (the color you used).
2. Now it's time to tap your memory and do a little digging in your donor database. Does Betsy Parker sound familiar? Don't you have a monthly donor named Elizabeth Parker? Could they be the same person? What other data points in their records could indicate they are one and the same?
Correct any definite duplicates directly in your donor database. Tag or otherwise make a note in your database for the records you need to further investigate.
Step E: Prevent future issues
1. Make it a habit to check for similar first names/nicknames before you create a new donor in your database. Last name, address, and/or phone number can help you narrow your search and make positive identifications.
2. Also make sure you check for duplicates by nickname every 1-4 weeks, depending on your volume of new donors and donations.
Issue 9: Foreign Language Characters
This one's a doozy.
I live in Phoenix, where Spanish names are commonplace. Here's an example of how a donor record looks in my database.
But when I export my contacts to a CSV so I can import them into my mail system, I get this garbage.
Then I export a PDF to share some results with my boss and everything is back to normal!
What did I ever do to deserve this? Technically, nothing.
Remember who we blamed for all those non-printing characters back in Part 1? Programmers. Programmers did this to you. (Programmers also made many of the things that make our lives easier, so they aren't all bad).
There's this thing called character encoding. I won't bore you with the details (but if you care, this is a good overview). What you need to know is that some programs/systems encode text a little differently. And that can cause problems when you transfer data.
You have two options here:
a. Replace all foreign characters with their equivalent in the 26-letter English alphabet.
b. Test different types of encoding to see what works best for the names in your database and your systems.
Option A is what most banks, utilities, and mailing services do. They need speed and efficiency, and an eñe or accent clogs their system.
Option B is what your family and friends do, because they care about you more as an individual than how fast they can address your birthday card.
Ideally, option B is what every nonprofit would choose. Because we speak to donors as individuals. We care.
But our world isn't ideal. Pursuing option B requires tedious testing. You must weigh the costs and benefits of option B.
Or, you can create your own Option C. Maybe you do option A for mass-produced letters but use the proper spelling for hand-written cards. Perhaps you focus on perfecting just the Polish names because they make up 10% of your database.
If you are up for it, here are the rough steps to take. Whenever possible, lean on your systems' knowledge bases and support teams to help you navigate encoding options. And if you can, delegate this task to one of your more technically-savvy team members.
Step A: Investigate export encoding options
1. Identify the names with foreign characters in your database. If you can, only export those names. Otherwise, create a tag or other note so you can easily search for them.
2. Go to your donor database and see if there are any file format options when you export data. Download and review each type in the following order.
a. CSV UTF-8
b. CSV
c. Excel (latest version you have access to)
d. Unicode text
3. Note which file formats had the fewest and most errors.
Step B: Investigate import encoding options
1. Go to your mail system (or any other program you are trying to import in to).
2. Import and review each type in the order of least export errors to most.
a. CSV UTF-8
b. CSV
c. Excel (latest version you have access to)
d. Unicode text
3. Note which file formats had the fewest and most errors on import.
Step C: Document future Process
1. Create a process with explicit instructions on how to export and import data for each different system you need to pass data to/from.
2. Identify any manual corrections you may need to make (i.e. You could never get ó to appear correctly, so search for the bad output text ^% and replace it with ó).
3. Re-test your process every few months or anytime you are notified that one of your systems had a major update.
Congratulations!
YOU DID IT! Cue the confetti shower and dance music! You now have good, reliable donor data.
Your updated donor data will now help tell you:
⦁ Which messages connect with your donors
⦁ When your appeals perform best
⦁ Who is most likely to respond to your appeals
With each donation request letter and email, you will feel more confident.
And best of all, your updated database will provide the best experience for your donors. Peggy Clark won’t complain that her tax receipt was wrong – because you found and merged those separate records for her: Peggy Clark, Margaret Clark, and Mrs. Robert Clark.
Your updated donor data will save you time, energy, and money—so you truly can do more with less effort.
Go CELEBRATE! You deserve it!
