Better Donation Letters and Email through Data Hygiene (Part 2)
Call Donors by the Right Names to Keep Them Engaged
Editor’s Note: This blog is the second 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.
What’s in a name? Plenty if you’re a donor reading a donation letter or email.
Decades of eye tracking studies show that donors read their own names first – and then decide whether to keep reading your appeal. Of course, you want to do everything you can to make sure those donor names are correct – and that starts with a clean database.
At this point, you should have donor data clean of special and extra characters, email typos, and bad addresses. Congratulations! (If not, check out Part 1).
Now you're ready to tackle households and formatting individual names, so you can better identify and remove costly duplicates from your data.
Issue 4: Households
Humans are a social species. We live together with family and friends. Figuring out who is in a household and their relationship to each other is important because:
It saves you money when you can send one letter to a family.
It lets donors know that you care about them as individuals.
If you don't have a donor management system that supports households, go get one! It will save you so much time and headaches in the future.
If you are using Excel or Google sheets to manage donors, add a column where you can track unique households (go ahead and email me for some tips on how to do this).
There are a couple indicators that donors are in a household together – matching addresses, matching home phone numbers, and matching last names. (You may be thinking, who has a home phone number anymore? Unless your donors are vastly different than average—female and over 60— then they do.)
It's highly likely that pairs of donors who have the same last name, address, and home phone number are related to each other. They may be spouses, parent and child, or siblings.
If you know, great! Note that in your database. If you don't know their exact relationship, I'd still recommend creating households. If you send a donation request letter to Andrea & Pamela Cohen, it doesn't matter much if they are sisters, spouses, or mother and daughter.
It's also likely that donors with different last names who share an address/phone number are related. Though you may need to do a little digging to see if you should address a letter to each donor individually (perhaps they’re roommates?) or as a household (lots of married and co-habitating couples don't share last names these days).
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. HouseholdID
c. First name
d. Last name
e. Street address
f. City
g. Home phone number
Step B: Highlight Duplicates
1. Select the HouseholdID column.
2. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Repeat substeps 1-2 for the last name, street address, city, and phone number columns.
Step C: Analyze Phone, Address & Last Name Duplicates
1. Sort the list A-Z in this order:
a. Last name
b. Home phone
c. Address
2. On the last name column, select the drop-down arrow. Select Filter by Color > Filter by Cell Color > Light Red. Click OK.
Select Filter by Color > Filter by Cell Color > Light Red
3. Repeat substep 2 for the address and home phone columns.
4. On the HouseholdID column, select the drop-down arrow. Select Filter by Color > Filter by Cell Color > No Fill. Click OK.
5. You'll end up with a list that looks like this. In your donor database, combine these donor records into a household. Refer to your system's help documentation for directions on how to do this. Of course, use your best judgment! If you know Eric and Jane prefer to be mailed separately, maintain them as individual households. Do make a note of it in both of their records so future staff don't make unnecessary changes.
Step D: Analyze Phone & Address Duplicates
1. On the last name column, click the filter drop-down box. Select Filter by Color > Filter by Cell Color > No Fill. Click OK.
2. Now you'll see likely households that do not share the same last name. Combine them in your donor database using your best judgment. Perhaps you know that Ana and Clint are just roommates and keep their finances separate. Note that in both of their individual records so others don't merge the two in the future.
Step E: Analyze Address Duplicates
2. On the last name column, select the drop-down arrow. Select Clear Filter From "Last name".
3. Review the results and combine any households in your donor database.
Step F: Prevent Future Duplicates
1. Make it a habit to check for donors who may be in the same household before you create a new donor in your database. Search by last name, address, and/or phone number.
2. Likewise, set up a system to verify new donor records every 1-4 weeks, depending on your volume of new donors and donations. If possible, have someone who did not do the initial data entry conduct the review. They can check for duplicates and all the following issues, too.
Issue 5: Prefixes, Suffixes and Initials
Our names have lots of accessories that can get in the way of duplicate identification and cause minor embarrassment. By accessories, I mean all those titles and letters we sometimes put before, after or even in the middle of our names.
Looks like I was in a hurry when I added these folks' last names to our database. Those suffixes (Jr. and M.A.Ed.) belong in their own fields, not the last name field.
Of course, I've made some boo-boos with first names too. I've probably sent a few emails to folks who are wondering why I keep including their middle initial. Lax data hygiene, that's why.
No matter, we can fix these minor problems with Excel's text filters.
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. Prefix
d. First name
e. Middle initial
f. Last name
g. Suffix
Step B: Fix first names
1. Select the first row and select Sort & Filter > Filter.
2. On the first name column, click the filter drop-down box. Select Text Filters > Contains.
3. Type a period and click OK.
Select Text Filters > Contains.
4. Put the prefixes and middle initials in the appropriate columns. If necessary, adjust the salutation field to match your organization's policy. For example, would you write a letter to Jennifer or Dr. Jennifer, or even Dr. Cox?
5. Occasionally you'll run into a L. Erik or C. James. That's an imperfect indicator that the donor uses their middle name socially as their first name. Generally, I recommend leaving the first name field alone and making sure the salutation field uses their middle name. But your policy may differ.
6. Repeat substeps 3 and 4, using a comma as the filter condition.
Step C: Fix last names
1. On the last name column, click the filter drop-down box. Select Text Filters > Contains.
2. Type a period and click OK.
3. Put the middle initials and suffixes in the appropriate columns.
4. Repeat substeps 3 and 4, using a comma as the filter condition.
Step D: Fix prefixes
1. On the Prefix column, click the filter drop-down box. Deselect (Select All) and then scan the list and click the prefixes that are formatted incorrectly. Click OK.
On Prefix column, filter using drop-down box. Click Select All, then scan and click prefixes that need correction. Click OK.
2. Make the necessary corrections.
Step E: Fix middle initials
1. Sort the middle initial column A-Z.
2. Click the filter drop-down box. Select Text Filters > Does Not Contain.
3. Type a period and click OK.
4. Add a period to the first initial (likely A). Select the cell and drag it to copy the cell contents for all the instances of that initial. This should fix any capitalization errors too. Repeat this for B-Z.
Step F: Fix suffixes
There is a huge variety of suffixes, so it's easy to get stuck in the weeds. Just focus on the ones that will make a difference to your donors or will help you tell Robert Clark II and Robert Clark III apart.
1. On the Suffix column, sort the list A-Z.
2. Scan the list for inconsistencies, like Jr instead of Jr. or MD instead of M.D. Then make the necessary corrections.
Step G: Upload corrected data
1. Return to your donor database.
2. If possible, create a backup point you can revert to.
3. Import the CSV with the updated data, being sure to overwrite any existing data.
Step H: Dedupe your data
1. If your donor management system:
a. has deduping tools, run them on your updated name records. Rectify any suspected duplicates before proceeding to the next step.
b. doesn't have deduping tools, wait until you address the next issue before you manually identify potential duplicates.
Step I: Prevent future issues
Make sure you verify new donor records every 1-4 weeks, depending on your volume of new donors and donations. This will help you to catch prefixes, suffixes, and initials in the wrong places before they become a big problem.
Issue 6: Properly Capitalize Names
Remember when we used CLEAN and TRIM to remove any special and extra characters from our data? One of the reasons was that those inconsistencies in data can throw off matching algorithms used to detect duplicate records.
The same thing can happen with capitalization inconsistencies. You don't want duplicate records for Peggy, peggy, PEggy, and pEggy Clark.
More importantly, you don't want your donors to see emails like these that I fat-fingered. (Luckily these were test emails to myself!)
Enter Excel's PROPER formula. It Capitalizes The First Letter Of All Words In A Cell And Makes Everything Else Lowercase.
In English, most names start with a capital letter and are otherwise lowercase, so PROPER works well for us.
However, there are names that start with a lowercase letter or have internal capitals (McKennon van der Beek), so you'll need to manually review the changes and make any additional fixes.
Here's how.
Step A: Pull distribution 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. Middle initial
e. Last name
Step B: PROPER data
1. Open your Excel or CSV export.
2. Open this Proper Names template.
3. Select all your original donor data and copy/paste into the first tab, Original names.
4. Click the second tab, Proper names.
5. Delete any rows you don't need. If you have more than 1,200 records, copy the formulas to the appropriate size of your list.
Step C: Double check the results
1. Select the Proper names tab. This tab contacts additional columns that indicate where Excel made changes so you can double check them.
2. Copy the entire sheet, select the Verification in Process tab, and paste values (Home > Clipboard > Paste > Paste Special > Values). This makes it easier to edit.
3. Verify the correct capitalization for each updated record. This task is a mixture of knowing your donors, referring to your original data, and trusting your gut. Here are a few examples where I had to override PROPER results.
4. When you have completed the verification, copy the entire sheet, select the Final results tab, and paste values.
5. Hold CTRL and select the check columns C, E, G, I.
6. Select Delete > Delete Cells or press CTRL+-.
Select Delete > Delete Cells or press CTRL+-.
7. While Final results is the active sheet, select File > Save As.
8. Select a file repository, enter a file name, select CSV or CSV UTF-8 as the file type, and click Save.
Step D: Upload corrected data
1. Return to your donor database.
2. If possible, create a backup point you can revert to.
3. Import the CSV with the updated data, being sure to overwrite any existing data.
Step E: Dedupe your data
1. If your donor management system:
a. has deduping tools, run them on your updated name records. Rectify any suspected duplicates before proceeding to the next step.
b. doesn't have deduping tools, follow substeps 2-11.
2. Open your updated CSV.
3. Delete the salutation and middle initial columns (they aren't useful in this exercise).
4. In cell D1, enter “Full name”.
6. In cell D2, enter “=CONCAT(B2," ",C2)” and press Enter. (Note: use “CONCATENATE (B2," ",C2)” for older versions of Excel)
7. Select cell D2, and drag the formula down to the rest of your records.
8. Select the Full name column.
9. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
10. Sort/Filter your list so it's easy for you to identify the potential duplicates.
11. Resolve any suspected duplicates directly in your database before proceeding to the next step. Refer to your CRM's documentation for tips on how to manually merge records.
Step F: Prevent new problems
Make sure you verify new donor records every 1-4 weeks, depending on your volume of new donors and donations, to catch capitalization issues.
NONPROFIT DATABASE HYGIENE: NEXT STEPS
Phew! That was a lot of work. But it was worth it.
Dale Carnegie, the author of How to Win Friends and Influence People, said a person’s name is the “sweetest, most important sound in any language.”
Regular data hygiene can help you make a good first impression with donors, avoid embarrassing name mistakes, and keep the relationship sweet for years to come.
We have a few more name-related hygiene tasks yet to complete. In Part 3, you'll learn how to:
Catch misspelled names
Negotiate nicknames (Is Peggy Clark the same person as Margaret Clark, Mrs. Robert Clark, and Peggy Clarke?), and
Contend with foreign language characters like eñes (ñ) or umlauts (ü).
But until then, congratulate yourself on a job well done and take a well-deserved break. I know I'm hankering for a glass of Malbec and dark chocolate.
