How to End Your Nonprofit Data Nightmares Once and For All

Stop me if you’ve heard this one before.

You’re trolling through your organization’s database, trying to run a report that gives you a list of top donors or folks who haven’t given in a while or some other useful, obvious thing you’d want to pull out of your database.

But things don’t seem quite right.

You know for a fact that Mary Stanton gave $5,000 last year, but for some reason when you run the report for donors who gave more than $1,000, she isn’t on the list.

Frustrated, you look up her name manually. It takes you a minute, but you realize that her donation was logged as “5,000” instead of “5000” so it wasn’t being added properly into the total and shows her donations for the year as $0.

Then, you realize that the donation data has been logged in 100 different ways. Some have dollar signs. Some have decimals. Others are written in hieroglyphics (that might be an exaggeration).

Then it dawns on you: The data you have is completely dirty.

Oh, what’s that? You have heard this one before?

Unfortunately, many nonprofits face these issues with data. They have gobs and gobs of information sitting in a database or spreadsheet, but most of it is hobbled together in some ad-hoc way that makes it nearly impossible to use. If anything, it might be making life harder rather than easier.

Troubles with dirty data

While having trouble with dirty data can be a huge and daunting problem, diagnosing and understanding those problems is usually pretty straightforward. Most of these troubles stem from just a few major issues with how the data is handled within the organization.

There are three big ones that are most prevalent.

Problem #1: No standardization of data practices

If you want usable data, you need to set up ground rules.

Everyone in your organization needs to know how, where, and when data gets logged into which systems. This needs to be documented and enforced.

Problem #2: No training on data capture

Documenting data practices is great–unless no one ever sees them.

As is wont to happen within any organization, often times there are clear and specific data standards that get created and then sit in a binder, on a shelf, collecting dust. As time passes, staff comes and goes, and soon those standards are completely gone.

Problem #3: Data integrations are not set up properly

Finally, the error may not be a human one at all. It could be stemming from integrations or data transfers from one program to another. When data flows from your email service to your CRM, is it properly formatted? Does your online donation widget record names and amounts in the same way that your staff manually logs in-person donations?

Dealing with data from multiple sources can make the entire process extra tricky. It can turn otherwise strong data practices into a steaming pile of unusable data points.

Let’s solve the problems

While these problems aren’t necessarily “easy” to solve, they are solvable.

Many organizations go years–or decades–with underlying problems with their data systems or processes. It’s often an unspoken thing that only comes up for a brief second before everyone in the room collectively sighs, silently remembering that the problem will hobble the project or idea they were just discussing.

But it doesn’t have to be this way. With a plan and some hard work, you can whip your data into shape and put it to good use.

Step #1: Undergo a data audit

In order to fix problems with your data, you need to first understand exactly what problems you’re facing. This requires doing a data audit (fun, I know).

There are many ways to go about this process, but all data audits essentially boil down to looking at three characteristics about your data:

1. Location – Is the data being stored where it should be?
2. Condition – Is the data clean and in the right format?
3. Value – Does the data’s value make sense?

Some of this process can be automated, like flagging values that are way out of bounds for what you expect or quickly identifying non-numeric entries in a field that should be represented by a number. Other portions of the data may need to be examined manually.

Whatever shape your data is in, you want to come out of the data audit with a firm understanding of what specific problems exist within your data–things that need to be changed now–and also a list of systemic issues with the way data has been input.

The latter part is more difficult to diagnose, but can usually be found by identifying patterns or inconsistencies that appear multiple times. Like, data in a certain field not being inputted correctly after specific intervals.

What we’re looking for in our data audit are three key things:

1. Places where there need to be new/updated data standards for people
2. Places where data is flowing in from outside sources and any problems with those systems
3. Places where data isn’t properly stored or logged in the way you’d like or need it for future analysis

Of the three, the last one is probably the most difficult to master and takes some additional forethought and planning. What you’ll need to do is essentially think into the future about what kind of reporting or analysis you will want to be able to do based on this data. Then, work backward to define how the data will need to be organized and cataloged in a way that makes that analysis possible (and hopefully simple).

As an example, you may know that down the line you’ll want to analyze donation data by zip code. But the zip code may be included in a single “address” field, making it difficult to quickly analyze.

In this case, you would want to make a note that the address field should be broken into separate fields, including one specifically for zip code.

Step #2: Create/clarify data standards

From your data audit, you should have an actionable list of specific data standards that need to be created or enforced.

It’s a good idea to catalog all of the data fields that you’ve reviewed and set specific rules and standards for each field, including the value, location, and condition. Altogether, this forms your organization’s taxonomy for their data–sets of rules and relationships between the data.

This taxonomy should be rooted in the intended use of the data–tied directly to how it will need to be analyzed later down the road.

These data standards should be documented in a shared location. But, they shouldn’t just be a list of arbitrary rules that sit on the shelf. Add meaning and context by setting expectations about how the data will be used and why it’s important that the data standards are enforced and maintained.

Step #3: Clean up existing data

The next step is to make changes to the data you currently have. This is often a time-consuming process as all of the individual data may need to be entered manually according to the new standards that have been set.

But don’t get overwhelmed.

To help with the feeling of being overwhelmed, start by identifying data you’re already cleaning manually (e.g. that quarterly report you always spend 5 hours preparing) and work on those fields before moving on.

Step #4: Assign one person to “own” database management

One reason many nonprofits struggle with maintaining their data is because no one is actually in charge of managing that part of the organization.

Everyone expects someone else to keep tabs on how the data is going into the system, but in reality, no one is enforcing the rules–and things break down.

So, the next step is a critical one. Your organization should assign one person as the “owner” of the database or data system(s). It doesn’t need to a full-time position, but it should become part of the regular duties of that person to monitor and report on data issues.

This person will also be in charge of maintaining and updating data standards, planning for changes in those processes, and providing ongoing training and support on the way the data is maintained and used.

Step #5: Use APIs and custom integrations where possible

Let’s face it: we’re human. No matter how hard we try to keep our data clean, there will be mishaps, mistakes, and miscounts. One way to mitigate this and maintain your data is to use APIs or technical integrations whenever possible.

Rather than having to closely watch and correct data issues manually, technical integrations (when done correctly) can keep your data always in compliance. A proper API integration that transfers data from point A to point B is like a staff member that works 24/7 and follows your data standards perfectly every time.

Another option is to make your software smarter about catching and preventing errors as people add data into your system. If you’ve defined stricter standards around how a field should be formatted, your software can help enforce those standards by displaying validation errors and requiring people to properly format their data before submitting it.

Ideally, you’d want to automate and integrate all of your systems to combine and merge your data into a central database. But even having a few basic API integrations set up between a few of your main systems can save hundreds of hours–and possible mistakes–each year.

Step #6: Institute ongoing training and education

Last but not least, don’t expect your new-found data purity to last forever without some effort.

Team members change, responsibilities change, and priorities also change. This means that you’ll need to plan to reinforce these data policies regularly in order to make sure they stick.

Not only should you have regular training for new (and existing) staff, but you should also share information about how the data is used that will help codify the importance of data management. Share specific cases where data is being used to make a difference and how it’s made possible by clean, usable data going into the system.

Feel free to vent

What hidden data gremlins are lurking in your shop’s CRM? Long-time, pent-up frustration over how data gets handled? No clue how to even get started with how to wrangle that mess of a thing that your organization calls a database?

Share your questions about nonprofit data management with us and I’ll be happy to offer any help or advice.