“Data, Data, everywhere, nor any Data to believe…”

   – Samuel Taylor Fundraiser**

Now that we have plenty of time on our hands, it’s really time to take on the dirty work.  You know what I’m talking about- Database Cleanup.  Like the growth of dust and spider webs in your attic, fundraising databases similarly turn dusty, stale and old over time- and basically lost at sea to the point that not only is it hard to search for what you need to find, but the reports you count on can no longer be believed, leading to bad decision making!

While dreadfully similar to cleaning out your garage once a year, cleaning your database can still be done once you have a plan in place.  And it’s not that hard.

As the creator of DonorPerfect Online, I’ve personally converted more than 1,000 database nonprofit CRM systems and in the process cleaned them up. The #1 secret is to be able to easily segment your data properly.  This means using queries, filters, and sorting to isolate segments of data that likely need to be cleaned up.  Think of these tools as your cleaning weapons to strategically identify rogue data and clean them up.

Here are are my top 4 strategies for a healthy, beautiful and growing fundraising database:

1.  Sort every field in alphabetical order, both ascending and descending.

  • In the 1,000+ databases I’ve converted, I’ve always caught dozens if not hundreds of errors this way in every single database, because you will find entries that are blank and should have data, entries that start with ‘weird characters’, such as “$, #, ~, `, %, *” and entries that should not start with numbers or alphabetical characters and vice-versa.
  • This method also easily reveals misspellings and typos.  For example, by listing out the Donor’s Last Name field, you might have 35 Smiths in the database, but just scanning them you will be able to easily find that “Smth” and “Smithh” don’t belong and can change it on the fly (and especially since these mistakes always seem to be for major donors!)
  • For Date fields, listing them in sort order also finds all kind of anomalies- it’s typical to find gifts not only from the 1920’s or earlier, but also gifts in the far ‘future’, say 2119, which are clearly accidents as well.
  • Sort any amount fields as well.  Again, you will likely discover both negative gifts (and depending on your software, that might be allowed… Good fundraising software never allows negative gifts and allows you to add an adjustment instead) and you will likely find abnormally large gifts that are unfortunately mistakes as well.
  • Finally, it’s surprisingly fast to scan and fix data this way, even in databases with thousands, tens of thousands, or even hundreds of thousands of records.  We humans have an uncanny knack of identifying patterns or in this case, missing patterns, and as a result, can catch these errors which a computer or even modern Artificial Intelligence algorithms let slip by.

2.  Archive/mark Donors who have not given in 3 years.

  • It’s silly to keep sending expensive mailings to donors if they have not engaged your organization in 3 or more years.  Instead, archive these donors so that they do not pollute your overall efforts, or in good fundraising systems, you can mark them as inactive.
  • Even better- experiment with reactivation campaigns for this segment with targeting message to see if they are even worth keeping at all.

3.  Create a pivot table for coded/flagged fields.

  • You should end up with counts for important coded/flagged fields, such as Source Codes, General Ledger/Fund Accounts, Gift Types, Campaigns, etc, letting you know how many entries have a particular coded value, and even the sum of the gifts for that coded value.
  • Good fundraising software has this functionality built in, but if not, you can typically run a “Group By” custom report on the coded field, or simply export out the data and run a pivot table in Microsoft Excel.
  • You can tell a lot by running this analysis on these key fields and the trick is to find all of the codes that only have a handful of entries, or those which have outlier gift totals.  These are likely anomalies or mistakes, and can be merged with larger categories, depending on your overall database size.

4.  Check for and merge duplicates.

  • Good fundraising software has a duplicate checker built into it.  If it doesn’t, that’s a telltale sign it’s probably missing other important functionality that’s hurting you and your organization.
  • In my experience, all databases have duplicates, and it ranges anywhere from 2% to as much as 15% of the database, and sometimes even more, and especially for those organizations where they receive a high proportion (20% or more) of online gifts.  I.E., Donors are terrible at data entry and routinely misspell their own name, email address, and addresses!
  • Duplicates invalidate decision making, because the reports and data relied to make a decision is wrong.  Worse, duplicate donor records directly harm revenue, by under recognizing donors and not understanding their full capacity, and lowers the trust donors and even board members place in the organization.
  • The best duplicate checkers use Artificial Intelligence and Machine Learning to check for duplicates, but surprisingly, it’s my understanding that this technology doesn’t exist yet in any major fundraising software solution.  At the same time, good fundraising software should allow you to control the sensitivity of the duplicate check- wider for small databases and narrower for larger ones- as the resulting size of possible matches will be dependent on the overall size of the database.

I hope these tricks of the trade can help your data cleansing efforts, and ultimately retain more donors to raise more money.

It’s unheralded work, but leads to a real payoff so that you and your key stakeholders can believe your own data and make evidence based decisions.


** With inspiration from the original, ” Water, water everywhere, / Nor any drop to drink.” Lines from “The Rime of the Ancient Mariner,” by Samuel Taylor Coleridge 

Leave a comment