Data Clean-Up Part 1: Setting the Stage
An organization’s success is inevitably linked to the record-keeping within their database. You can hire the best consultants and strategists in the world to plan your fundraising – but if your data is incomplete and bogged down by “dirty data”, your organization will never achieve the financial goals needed to support your mission and values.
There is an old Chinese proverb – “A year’s planning begins in Spring”.
As we approach the beginning of spring, I think this is the perfect opportunity to talk about how we should be setting up a system to review and clean up the “dirty data” that exists within our databases. Today we will discuss what the first step in your Spring Planning process should be. Next month we will continue this dialogue with examples of best practices.
Before we can identify any “dirty data”, we need to discuss some basics. Every organization will have their own way of categorizing and capturing data, but essentially all CRM databases have 3 categories of data they can collect: Constituent Records, Gift Records, and User Defined Data.
Constituent Records – Individuals, Organizations, and other associated information (E.g. Addressee/Salutations, Address, Contacts, Solicit Codes etc.).
Gift Records – Any type of Gifts associated with your Constituent Records (E.g. Gift Types, Amounts, Campaigns, Funds, Appeals).
User-Defined Data – Custom information defined by the organization to capture information unique to them (E.g. Attributes, Actions, Notes, etc.)
Understanding what your organization wants to capture in each of these categories is integral to later steps in data clean-up. You need a map for the journey. A set of guiding principles on when a supporter becomes a constituent, how campaigns relate to appeals, how you want to segment your base, etc. These rules will differ based on each organization’s individual needs. However, clarifying them will only end up serving you well.
This is because, throughout the lifetime of your organization’s data collection cycle, there will be many different users entering data. Every user needs a clear understanding of the data mapping, processes, and procedures for inputting and exporting data. Elsewise “dirty data” will only continue to build up.
Once these decisions are made they need to be codified into a set of clear data procedures. This process can be taxing. But when we have helped clients complete this it has made things like turnover far easier to manage. Also, it helps set the stage for the next step, cleaning up current data.
The clean-up project should be led by a Database coordinator or someone on the team with a thorough understanding of the database. They should have an understanding of what the data in your database should look like – the goal is to create “data integrity” queries to look for anomalies within your data. Each type of data record will have their own set of queries to look for errors.
Start with building a few queries to look for common issues like missing fields in Constituent and Gift records (e.g. Address, Letter Codes) and inconsistencies such as tax receipt amounts of $0. As the process matures, you can create more elaborate and detailed queries to further identify and clean up mistakes and errors.
These queries should be run on a schedule (we recommend you run these queries on a monthly or quarterly period) to identify and fix any issues prior to the export of large amounts of data such as tax receipting and bulk communications. Running your data integrity query series consistently is the KEY to ensuring that your database is efficient and to avoiding big clean-ups during crunch time.
In part two of this series, we will go more in-depth on the specific types of queries you can create to review data commonly entered in pre-existing fields as well as niche queries for custom fields.
If you have additional questions or would like assistance in cleaning up your database do not hesitate to reach out!
If you would like us to tackle a data or process problem you have encountered or wondered about please let us know here.
Sentinel Consulting is a consulting firm that focuses on helping non-profits strengthen their operational efficiency. We speak the same language as our clients because we were all fundraisers at some point in our careers.