Better Donation Letters and Email through Data Hygiene (Part 1)
How to Get Your Donor Database Ducks in a Row
Editor’s Note: This blog is the first in a series on nonprofit database hygiene. Author Brianna Klink 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.
Has this ever happened to you?
You crafted the "perfect" donation request letter. It had a compelling story, beautiful photos, and a good design. Your staff spent hours folding, stuffing, sealing, and stamping envelopes. You even paid extra for first-class stamps to get it in mailboxes right away.
Then your appeal letter tanked. What gives?
Donation letters succeed or fail on three factors. The right message. The right time. The right person.
And all of these factors hinge on the quality of information in your database.
The Power of Good Data
Good donor data tells you which messages connect with your donors, when appeals perform best, and who is most likely to respond to your appeals. With each donation request letter and email, you feel more confident.
Good donor data helps you avoid errors and provide the best experience for your donors. So your donor Peggy Clark won’t complain that her tax receipt was wrong – just because your database has three separate records for her: Peggy Clark, Margaret Clark, and Mrs. Robert Clark.
Good donor data will save you time, energy, and money—so you truly can do more with less effort.
From Blame to Donor Database Fame
It doesn't matter how your donor database got ugly. Blame won't fix your problem. But smart data hygiene will.
Below are three of the most common data hygiene issues I see in my work at Abeja and how you can fix them. In part two of this blog series, I’ll cover some of the more advanced issues.
You can follow my process no matter where you store your donor data—Excel, Access, Bloomerang, Virtuous, or other donor management software.
If you are using a commercial donor database, familiarize yourself with its duplicate record identification and address verification tools (if any). You'll leverage those throughout the donor data hygiene process.
Also note that the instructions utilize Microsoft Excel, but you can easily translate the concepts to Google Sheets if your organizations doesn't have Microsoft Office.
Issue 1: Special & Extra Characters
Uh-oh. You have records that look perfect in your donor database.
But when you send your data file to your email system or direct mail vendor, you see errors like this extra space after the donor name.
Why does this happen?
You can blame programmers. Long story short, there are lots of ways for programs to encode (save) and decode (display) digital characters.
To make our lives easier, many programs have ways to resolve little inconsistencies in your data—like extra spaces or carriage returns—so they are invisible when displayed. But not all programs can fix those errors, so those little inconsistencies that your CRM hides for you can show up as ugly errors in your email service.
Similarly, those inconsistencies can throw off matching algorithms used to detect duplicate records. So you may end up with three different records for one donor because there was a single space before or after their name. That makes sending an accurate year-end tax receipt extra fun.
Luckily, there is a fix. Let me introduce you to Microsoft Excel's CLEAN and TRIM formulas.
CLEAN removes the first 32 non-printing characters in the 7-bit ASCII code. In non-nerd terms, it removes tabs, carriage returns, line breaks, and the like. The stuff that is used to format or layout text.
TRIM removes the extra spaces at the beginning or end of cells. It does not remove the spaces between words, so " Anna Mae " will be corrected to "Anna Mae".
Follow these steps to clean and trim your donor data in Excel.
Step A: Pull original data
1. From your donor database, pull a CSV or Excel list of your donors' contact information. At a minimum, include:
a. DonorID
b. Salutation
c. First name
d. Middle initial
e. Last name
f. Email address
g. Phone number
h. Street address
i. City
j. State
k. Zip
2. If your database supports households, spouses, second donors or the like, pull the same information for any additional donors tied to a record.
3. If possible, save your query so you can pull similar information later. You'll be pulling a list at the start of each phase of the data hygiene process.
Step B: CLEAN & TRIM data
1. Open your Excel or CSV export.
2. Open this Clean & Trim template.
3. Select all your original donor data and copy/paste into the first tab, Original data.
4. Click the second tab, Clean and trim.
5. If you have more than 1,200 records, copy the formulas to the appropriate size of your list.
6. While Clean and trim is the active sheet, select File > Save As.
7. Select a file repository, enter a file name, select CSV or CSV UTF-8 as the file type, and click Save.
Step C: 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 D: Dedupe your data
1. If your donor management system has deduping tools, run them on your cleaned and trimmed records. Rectify any suspected duplicates before proceeding to the next step.
2. If it doesn't, open your updated CSV.
3. Select the email and address columns.
4. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
5. Sort/Filter your list so it's easy for you to identify the potential duplicates.
6. 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 E: Prevent new problems
Let's avoid this problem in the future. If you are regularly importing data to/from your CRM, use these steps to clean and trim your data so you don't introduce new inconsistencies into your systems.
Issue 2: Email Typos
Most individuals have a personal email account that is unique to them and not shared with other donors in their household. For that reason, CRMs will often compare the email address field when searching for duplicates. But duplicate identification is more difficult when an email address is misspelled or missing an @ symbol or proper domain.
Luckily, programmers added a hint system to email servers. They send undeliverable messages like this.
Even luckier, your CRM or mail system will likely interpret these messages and set the donor's email status to "bounced" or "bad".
If your system doesn't have email status, don't despair! Email Brianna for tips and tricks that will
help you identify those bad addresses (and solve any sleep issues you may be having).
But you still need to fix those bad addresses in order to continue to communicate with your donors and identify duplicates.
You have two options to resolve the email errors: use a paid email validation service or fix them manually.
Here's how to manually resolve the most common email typos.
Step A: Pull bounced email list
1. From your donor database, pull a CSV or Excel list of your donors with bad email addresses. At a minimum, include:
a. DonorID
b. First name
c. Middle initial
d. Last name
e. Email address
f. Email status (Active, Invalid, Bounced, etc.)
Step B: Correct emails
1. Fix as many emails as you can relying on your good judgment. You should be able to correct "@gamil.com" to "@gmail.com" and see that Brianna entered ”Briannna” vs. “Brianna” to her email address when she signed up for your newsletter.
2. For the harder addresses, call or send a note to those donors. If you have many people to contact, I suggest scheduling a separate time to complete that task.
Step C: Upload corrected emails
1. Return to your donor database.
2. If possible, create a backup point you can revert to.
3. Import the CSV with the updated emails, being sure to overwrite any existing data.
Step D: Dedupe your data
1. If your donor management system has deduping tools, run them on your updated records. Rectify any suspected duplicates before proceeding to the next step. If you have any donors that appear to share an email address (e.g. Laura Smith, Brianna Smith, and Nicholas Smith all have the email address: thesmithfamily@gmail.com), leave those for now.
2. If it doesn't have deduping tools, open your updated CSV.
3. Select the email column.
4. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
5. Sort/Filter your list so it's easy for you to identify the potential duplicates.
6. 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 E: Schedule regular maintenance
Now that your emails are valid, you'll want to stay on top of correcting bad addresses. I recommend scheduling time once or twice a month (depending on how often you send bulk emails) to process your bounces.
Issue 3: Addresses
A bad email address is an annoyance, but bad or duplicate mailing addresses will cost you money! Paper, ink, and postage add up quickly—especially if you are sending two or three donation request letters to the same household, or worse, sending a letter that will never be delivered!
The United States Post Office has you covered here with its CASS and NCOA databases.
CASS stands for Coding Accuracy Support System. It's the system that standardizes and validates mailing addresses so the USPS can deliver your donation letter faster.
It also can make your mailing eligible for postage discounts. If you've ever ordered something online and seen a screen like this, you've seen CASS at work.
NCOA stands for National Change of Address database. This is how your college alumni association found you after you took that job across the country. Your donors likely won't notify you when you move, but they will tell the post office where to forward their mail to.
Your donor management system may have CASS and NCOA functions built directly into the system.
If your system doesn't have that capability, you have two paid options:
1. Get CASS and NCOA results from your print/mail vendor. When you send a bulk mailing (200+ pieces) via a print/mail service, they will provide you with .csv files that identify which of your donors moved, who have unresolvable addresses (no street number, no city, etc.), and any potential duplicates.
2. Use a mailing list update service before you send a letter in house.
Now I love to save a buck, and I know you do, too. But please don't be a martyr and decide to manually change "Street" to ST for every address.
Pay for CASS/NCOA processing—your time is better spent connecting personally with your donors!
Step A: Pull distribution list
1. Follow your vendor's instructions for pulling contact information for the records they will run against the NCOA and CASS databases.
2. Provide the list to your vendor.
3. Sit back and relax. (You deserve a break!) Or call some major donors and update them on that afterschool program they love. Write a thank you letter for donations. Source stories for your next appeal ...
Step B: Upload CASS/NCOA results
1. When your vendor has provided the updated list, 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 C: Dedupe your data
1. If your donor management system has deduping tools, run them on your newly-standardized and updated addresses. Rectify any suspected duplicates before proceeding to the next step.
2. If it doesn't has deduping tools, open your updated CSV.
3. Select the street address column.
4. Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
5. Sort/Filter your list so it's easy for you to identify the potential duplicates.
6. Resolve any suspected duplicates directly in your donor database before proceeding to the next step. Refer to your CRM's documentation for tips on how to manually merge records.
Step D: Schedule regular maintenance
Setup a regular maintenance schedule to standardize new address records and ensure you can continue to mail donors who move. If you outsource mailings, make it a habit to upload the CASS/NCOA results after every direct mail appeal. If you mail in-house, decide how often you want to outsource NCOA/CASS processing.
Nonprofit database hygiene: Next Steps
But wait, there's more! Part two of this series will show you how to:
Track households,
Organize all the prefixes, suffixes, and initials that accompany our names, and
Easily capitalize names while handling exceptions like de la Rosas or van der Beeks,
Nonprofit database hygiene is a process that takes time. But I promise it will pay off in a better picture of your donors—who they are, what they respond to, and when best to communicate with them. And this information will lead to more effective and efficient fundraising – whether you use donation letters, email appeals, social media or a combination of all three.
