Written By: Laura Berthiaume - May 15, 2018
Blog6 min read

Where to Start the Cleanup Process With a Messy Nonprofit Database

Data management needs won’t be decreasing anytime soon for nonprofits, and so these organizations must always be on the lookout for tools and talented individuals to maintain their most critical asset — their nonprofit database. For the success of the nonprofit to continue into the future, nonprofit data managers know a lot is riding on their ability to properly manage their database.

When a data professional is first hired to work in a system which is brand new to them, they may run into the chance that the database they will be working from is an absolute mess. Even seasoned nonprofit data professionals dealing with a messy database have their breaking point and tell themselves that enough is enough.

What comes next is the daunting process of cleaning up the horrid database, but knowing where to start can feel somewhat overwhelming. Everyone’s definition of “dirty” or messy will be different, but here’s a breakdown of our recommendations when trying to come up with some solutions for cleaning up your database.

Questions to Ask Yourself With “Dirty” Databases

The most common sources that we’ve identified as contributors to an overall lack of cleanliness in databases include configuration that has been neglected over the years, a lack of processes for how data needs to be entered, and an infinite number of users having rights to add or update records.

Messy donor databases can be a hassle to clean up. Here's our recommendation to start the cleanup process.

The only way to effectively clean up a messy database involves identifying how it got messy in the first place. Most of it comes down to your organization’s data collection strategy, but there are also several questions you can ask to learn how predecessors went about data management. Answers to these questions can help in prioritizing cleanup efforts.

  • Are there code tables or attributes that are way more expansive than they need to be? — Code tables and attributes can increase data entry speed and accuracy, but sometimes, can also lead to unnecessary complexity. For example, when someone is adding a new donor phone number, different users might enter the phone type as a “cell” or “mobile” phone, when both types represent the same thing. In this case, it’s important to distinguish strictly defined table entries that only you as the data manager can add to, and provide documentation to those entering data to assist them in choosing one specific type over another.
  • Are fields not required that should be? Are fields required that shouldn’t be? Are people plugging junk into those fields as a result of having to put something there? — Required fields will vary across organizations, but this could be any area that has slipped through the cracks. If a nonprofit has been around for several decades, perhaps retaining cell phone numbers and emails from donors at one time was never required, but now these pieces of data are critically important to have. On the other side of the coin, there’s the possibility that several required fields are unnecessarily required and should be revisited.
  • Is a large percentage of your constituent base missing an address? Do you need to consider an address finder service to try to obtain contact information for constituents you’ve lost touch with over the years? — As the years carry on, you may find that there are opportunities to re-engage constituents from the past but you don’t have the most accurate information on them. Addresses change all the time and there may be no way to recover them without the help of a third-party address finder.
  • Does your campaign, fund, and appeal structure work for your organization? — Campaigns, funds, and appeals are the foundation on which nonprofit organizations build for tracking successful fundraising efforts. In order to manage gifts and expenses, nonprofits must have a proper understanding of how these different record types and their structure impact one another. If a common piece of feedback received upon accepting your role as a new data manager is that reporting on money raised or efforts put forth to raise funds is a challenge, the source could be the current/historical campaign, fund and appeal structure. Any restructuring to these areas must make sense for your organization, and more importantly, for your finance department.
  • Are there areas of the database that are underutilized, or misused? — The value of data cannot be undersold, but many nonprofits have areas within their databases that are not being utilized to their true potential. Whether it’s workarounds used over the years to capture data in places other than the ‘intended’ areas of a database, or newly implemented modules that have not yet been utilized, this is a good question to ask. We see all too often databases that have proper modules to capture information such as Tributes and Planned Gifts, for example, but indicators of that information ‘hidden’ elsewhere within the database due to organizations not always having such modules. Any new database add-ons or module implementations should also be coupled with cleanup efforts to move historical data into their new proper homes for reporting to always be consistent.

database clean up checklist

Where to Start the Cleanup

Once you’ve asked yourself the right questions about your database, cleaning up your data can officially begin. Here is what we recommend as a step-by-step process to help you achieve better data hygiene.

1. Identify tools to assist with clean up

Manually cleaning your database is never a good option, as it is prone to further human error and only results in a staggering amount of mind-numbing man hours to complete. There are so many tools nonprofits can use to help with database cleanup, so it never hurts to see what’s out there. Your organization may have tools available to use right away, you just have to know where to look and who to ask for access.

2. Identify current service subscriptions

If there are any current service subscriptions your nonprofit is engaged in, try to figure out if there is any way they can be used to improve the cleanliness of your data. For example, perhaps there are quarterly updates you can start running to standardize data like addresses or even figure out if any constituents have passed away in the past year.

3. Create a process to error check

One advanced concept to think about is creating a simple library of “error checking” queries or reports that can be run once a week to help you identify sources of database challenges. Grouping records can help identify areas that are incomplete or incorrect. They can check whether new addresses are complete. Or whether certain table entries are never being attached to records where you know they should be.

An example of this would be identification of all new phone numbers being input as ‘Home’ but you know only a portion are ‘Home’ and others are ‘Mobile.’ Grouping records by what you as a data manager defines as ‘incomplete’ or ‘incorrect’ may also result in identification of areas for training current staff. This can also be the ideal time to revisit any existing data entry manuals. If protocols for entering data are somewhat outdated or individuals are not following rules to the letter, you’ll see trends in records being captured or flagged by these “error checking” queries or reports.

4. Reduce the impact of bulk data

When trying to ensure the integrity of data, knowing where to look for “bad” data is an effective tool to help mitigate these issues. Databases often have multiple input sources (think of other systems that are integrated), which over time become common culprits of collecting bad data. If data is being imported in bulk, the process of data cleanup should consider how to improve the import process to prevent bad data from being added to the database in the first place. Are there checks in place as or after data is imported to catch bad quality? Does the import process need tweaking to accommodate for changes you’re continually making to records once they’ve already been added to your database?

The Key to Success

Data cleanup can be time-consuming and cause tremendous headache for nonprofit data managers, but the work is very much necessary to guarantee the accuracy and reliability of an organization’s data. Once you have a plan in place to clean up your database, the best thing you can do to prevent a massive cleanup effort in the future is to revisit your strategy and overall approach to data collection and maintenance.

At a high-level, proper data collection involves documenting any and all changes you make, and communicating any changes to how data is stored and structured with those responsible with entering data. To learn more about the importance of data collection, be sure to read our previous blog post on this subject.

  • 7
blog author profile picture

About Laura Berthiaume

Laura is the Consultant Team Lead on the Professional Services team at Omatic Software, where she specializes in data migrations and database audits. She also conducts software implementations, delivers customizations, and provides short-term staffing support. Prior to joining the Omatic team, Laura held two roles at the University of North Florida and its Culture Institute, the Museum of Contemporary Art Jacksonville. She has over eight years of nonprofit and Raiser’s Edge experience and is also proficient with Financial Edge, NetCommunity, and Crystal Reports for The Raiser’s Edge. Laura holds a bachelor’s degree in Marketing and Entrepreneurship from Syracuse University and a master’s degree in Higher Education Leadership from the University of North Florida. In her spare time, she volunteers as an alumni ambassador for Syracuse University and The Players Championship.