A recent study conducted by MIP Fund Accounting revealed that some 75% of nonprofits use at least four or more digital systems within their overall technology environment. That means having to manage more data than ever before, coming from an assortment of applications. Nonprofit data governance demands will likely remain at this level – or grow as organizations feel the need and the freedom to employ additional platforms to ensure optimal supporter engagement and the most effective donor experience. And this will require data management professionals to obtain the right tools and technology to maintain their data – which is certainly among a nonprofit’s most critical assets.
When you add a messy main CRM database into the mix, an organization’s overall data management challenges can seem quite overwhelming. A messy database is likely the result of several factors (which we describe further in the next section) but suffice it to say that if your main CRM system is not clean, and procedures to keep it clean are not in place, it will only get messier as more and more data come in from all the digital systems in its orbit.
In any event, whether a new nonprofit database manager has joined the team (and is recovering from the shock) or a seasoned database professional has simply reached her breaking point, what comes next is the daunting process of database cleanup. Knowing where to start can feel paralyzing, especially when there’s no standard rating or scoring system for ‘dirty’ or ‘messy’ nonprofit data. Nevertheless, you have to start somewhere, and we want to provide some recommendations for cleaning up your nonprofit database and keeping it clean from now on.
Questions to Consider When Faced with a Messy Database
When developing a strategy and plan to clean up a messy database, the best place to start is with an investigation of how it got messy in the first place.
A messy main CRM database could be the outcome of this growing number of ‘satellite’ applications and inconsistent data importing utilities and processes. Or it could be the consequence of changes in policies, procedures, and personnel over the years. It could also be the result of end-user training (or a lack thereof) or of leaders for whom data health and data quality just didn’t resonate. Or a combination of some or all of the above.
There are also more tactical explanations, which could be remedied more easily: think of ‘low-hanging fruit. These include database configuration that has been neglected over the years, the lack of consistent data entry or data importing procedures, inconsistent data collection processes (those ‘satellite’ systems again, perhaps managed in other departments), or an unreasonable number of users who have more permissions than necessary to add or change records or fields, or to make mass updates.
Below are several questions that can help determine and prioritize data cleansing efforts:
- Are there drop-down tables – standard or custom – with more values than are necessary? Taking advantage of code tables and pick-lists both standardizes data entry and increases data entry speed and accuracy. That said, tables can get cluttered and list values can get redundant and confusing to the point of doing more harm than good. For example, if you have a status table that includes both ‘former donor’ and ‘lapsed donor’, which is the right value to choose? They mean the same thing and are redundant values.
In this scenario, a determination should be made about which value to use going forward, the specific meaning should be documented for end-users, and the obsolete value(s) should be updated to the correct value. Examples like this also provide an opportunity to reexamine security permissions to ensure that the right data management professionals have the right configuration permissions. Data entry/management policies should be reviewed as well to ensure that new, approved values are added only when necessary.
- Are there fields that should be required but aren’t? Are there required fields that shouldn’t be? Field requirements will vary across organizations, and will even change within an organization over time, as it evolves and starts collecting more strategic information from various source systems. For example, 20 years ago, mobile phone numbers and email addresses were likely optional to collect, but today most organizations require that contact information for individuals. That same organizational progression, along with changes in best practices, may make other data no longer required and actually purgeable – think about social security numbers, payment card numbers, etc.
There may also be fields marked as required that are less risky to hold onto, but simply not (or no longer) necessary to maintain; those should be cataloged and reviewed. These can be especially problematic if end-users have been adding generally useless ‘placeholder’ data because they can’t complete a data entry task until all required fields are populated.
- Missing addresses (and other contact info)? Every organization wants to take the opportunity to re-engage past supporters. If a large number of your records have bad addresses or are missing an address, consider employing an address-append service to obtain contact information for those with whom your organization has lost touch over the years. A January 2022 study found that some 10% of individuals move residences every year.
Other services can append up-to-date email addresses and phone numbers. While many organizations still find tremendous success with direct mail fundraising that leverages residential addresses, direct email is catching up quickly. In 2022 people still change email addresses at more than twice the rate of changing residential addresses.
There are real dollar costs involved with employing data-append services, but the ‘immediate gratification’ of up-to-date data, the ability to re-engage past supporters, and the long-term effectiveness of a far cleaner database, should make the investment more than worthwhile. (And, Omatic can certainly help expedite integrating the updated contact data into your main CRM system.)
- Is your record and gift coding structure still right for your organization? If your CRM revenue reporting:
- is challenging to create
- is problematic to understand without narration
- requires a lot of manual manipulation
- does not ‘tie out’ with reporting from the finance system
chances are that coding structures need to be reviewed, revised, or reworked. This is typically an overarching endeavor, affecting your organization overall, and your revenue and finance teams in particular. The added benefit of code restructuring is that it can provide the opportunity to clean up and purge unnecessary or unused structures, further streamlining data entry and reporting ease.
- Are there areas of your database that are underutilized, or that are not being used properly? Similar to the discussion of required fields, as an organization evolves and its database evolves along with it, you may find any or all of the following:
- Areas of the database that the organization once intended on using but never actually did
- Areas of the database that could be better leveraged
- ‘Workarounds’, which (for this piece) we define as fields or entire database areas intended for one thing but used for something else.
This would be the time to examine those database characteristics, determine the best approach, and put an action plan or a project plan in place. Features and functions not being used can be disabled, and their data purged (if truly not useful) or archived. Or you can reexamine, and determine if it makes sense to the organization to focus on effectively using an area of the database that’s currently fallow.
Data recorded in ‘workaround’ locations can be moved to the proper location. For example, an organization kept tribute giving data (in honor of, in memory of) in custom fields for many years until tribute-specific database functionality was made available to them. It was a project, but they were able to clean up and remove custom fields by moving the tribute gift data to the proper tribute fields themselves.
Where to Start the Cleanup
Once you have asked the right questions about your database and determined the best approach(es) based on the feedback and input from your team, you can begin working on the cleanup itself. This will likely need to be a team project and perhaps will even need to include some external resources. Below are some recommendations to consider along the way.
1. Identify tools to assist with cleanup
Manual database cleanup is rarely the right approach. In addition to the tremendous time consumption, manual procedures are prone to human error. With that in mind, it makes sense to identify which software tools are going to be most valuable to expedite data cleansing. There are a variety of utilities you might consider, including native database cleanup functions as well as external data integration solutions (including those developed by Omatic) that can help transform data and reload the updated data at high speed. Your organization may already have some of these tools available.
2. Identify current – or needed – data service subscriptions
Your organization may be currently subscribing to services that will overwrite stale data with fresh information or append new, updated information to existing records in your main CRM database system. Or, if you are not currently subscribed to data services, now may be the right time to consider a subscription. While the most common service provided may be National Change of Address (NCOA), there are also services that can provide up-to-date phone numbers, email addresses, birth/death dates, and a variety of other demographic information.
3. Create a set of processes to validate existing data and check for errors
Start creating a simple library of validation reports or database queries to check for missing, non-valid, or erroneous data. These can be used to identify records that need to be adjusted or corrected as part of your overall cleanup project, and then used routinely afterward (eg, weekly, monthly) to ensure that data remain clean and valid. This is a great way to determine if records are incomplete without forcing fields to be required. These validation checks can also determine if drop-down values aren’t being utilized – perhaps they can be deleted or perhaps end-users need a better understanding of when they should be properly used.
It may not be surprising to discover that these processes can help identify areas where current data management team members need more or better training. This may also be the ideal time to revise or augment any existing written policy and procedure guides or data entry manuals. If protocols for entering data are outdated or individuals are not following data entry policies, you’ll see it in these validation reports or queries, and you can build as-needed, ad hoc training to ‘nip it in the bud’.
4. Optimize the impact of bulk data integration
Nonprofit CRM databases may be the center of an organization’s data universe, but in today’s nonprofit technology ecosystems, there are often numerous ‘satellite’ systems that feed ancillary data into your main CRM system. Main databases typically have multiple input sources, which over time can become the culprit of messy (or downright bad) data. Your database cleanup process should include reviewing and improving the data integrations from external systems to prevent incongruent or erroneous data from being added to your database in the first place.
A few considerations include (but are not limited to) the following:
- Are there checks in place to catch and remediate data quality issues before data are imported?
- Does the import process effectively match records and prevent duplicates?
- Does the cadence or frequency of integrating data from other systems ensure that record information is as current and complete as possible?
This is an area, especially, where Omatic solutions can help ensure that data integrated into your main CRM database from other systems or platforms are free of errors and don’t contribute any mess to the data that you work so hard to maintain.
The Time You Invest
Nonprofits are managing more data and data sources than ever before and that’s not likely to change. And so, while database cleanup is imperative, cleanup focuses on addressing the ‘sins of the past’ rather than about putting measures in place that ensure your data remains current, clean, and complete as you face the future. You will continue to collect data at an ever-increasing pace, and you’ll want to ensure the quality of that information as effectively as possible. (To learn more about data collection, please read our blog, also updated for 2022.)
The time you invest in data cleanup will be time well-spent. If you can guarantee the accuracy, quality, and reliability of your organization’s data, the end-users of that data will be more trustful internally and more confident in doing their jobs. But looking at this challenge more long-term, the time you invest in data cleanup combined with effective data collection, hygiene, and maintenance moving forward will likely prevent having to manage another massive cleanup effort in future years.
For more information on how Omatic can help you with strategic, efficient, and comprehensive data collection, or for a product demonstration, please complete the form below, or contact us at email@example.com.
[pardot-form width=”250″ height=”500″ id=”1205″ title=”Demo”]
Stu Manewith, CFRE joined Omatic Software six years ago and serves as the company’s Director of Thought Leadership and Advocacy. In that role, he is Omatic’s nonprofit sector domain specialist and subject-matter expert and is responsible for actively promoting and demonstrating Omatic’s position as the nonprofit industry’s leading partner in the areas of data health and integration. Prior to Omatic, Stu spent 13 years at Blackbaud, working with Raiser’s Edge, Financial Edge, and Blackbaud CRM client organizations as a consultant, solution architect, and practice manager. Previously, Stu spent the first half of his career as a nonprofit executive, fundraiser, and finance director, working in both the healthcare and arts/cultural arenas of the nonprofit sector. He holds business degrees from Washington University and the University of Wisconsin, and he earned his CFRE credential in 1999.