Get Healthy (Data)!

Clean and reliable data. It’s what we all want in our databases. Sometimes it can seem like an unattainable dream, but by instituting a handful of policies and practices, you can make it happen. If we take a step back, there are really only two areas to consider on our way to our goal of clean and reliable data – 1. Adding good data to begin with and 2. Fixing issues when they occur.
 
Let’s start by examining the first area – adding good data to begin with. I’ve been training users for years about data entry and best practices and in each and every session, I’ve always shared two rules to live by.
 
The first is “Search first”. There are two main reasons for this rule: it prevents duplicates from being added to the database and it saves time. When I share this, everyone nods along as I talk about preventing duplicates, but I get some skeptical stares when I mention that searching first saves time. Inevitably, someone will question this assertion. So let me explain:

• When we search for a record before adding a new one to the database, we save time first by removing the need for our front line data entry/sales staff from having to enter in all the information that we require for a new record when a record already exists.
• We also introduce the opportunity for identifying incorrect data contained in the record before we are trying to use that data for analysis or a communication. It’s always less time to fix a problem ahead of time, whether it’s during the interaction with the constituent or as part of a regular clean-up process.
• But that’s not all – and this comes back to duplicates – we also save the time that we would have to take later to clean up duplicate records.

When talking about searching, I also like to share some good search techniques:

• Less is more – when you add search criteria into a search form in most databases, the database only looks for records that meet ALL of the criteria you entered, which means that you can miss records by adding to much criteria and narrowing the search. So only enter criteria into one field and/or only add the first few letters of the value you’re looking for. If you get too many results, you can always add more criteria and repeat the search to narrow the list.
• Use wildcards – wildcards allow you to expand your search by allowing variability in search criteria. For example, maybe I know a constituent’s name is Schultz or Shultz, but I’m not sure on the spelling. Using a wildcard “S%ultz” would search for any character combination appearing between the “S” and the “ultz”.
• Use advanced search options and checkboxes – for example, include inactive records. Part of maintaining a healthy database is inactivating old and unused records, however, sometimes there are reasons to reactivate records, such as when a constituent reengages with you. Other advanced search options might also give you options to search based on aliases, alternate IDs, nicknames, or deceased records.

Searching for existing records only gets us part of the way in adding good data to your database, we also need to consider implementing and maintaining data entry standards. This is where my second rule comes into play. I call the second rule the “Three C’s” – when entering data you must be Consistent, Correct, and Complete.

• Consistent – The first of the three C’s is the most important – yes consistency is even more important than correctness! When we enter data in a consistent fashion, it is easier to find issues with the data that need correction.
• Correct – Even though we can find data issues more easily when we are entering data consistently, correctness is still extremely important. No one likes to spend time and money on fixing data issues.
• Complete – missing information can prevent a record from actually being useful to you. It’s important to collect and enter enough data that the record can be used for analysis and communication.

As an organization, you should have a data entry policy that addresses what information is required, what information is desired when possible, where that data should be entered, and how that data should be formatted.
 
For example, does your organization prefer to use formal name formats in your communications? Title should be a required piece of data. Should at least one type of contact information be added (address, email, phone)? Which type? When should data be added as an attribute or would be more appropriate as a note? What are your rules for address entry and abbreviations?
 
Even with the best policies and procedures for data entry, “bad” data happens. So we also need to consider your practices for fixing issues when they occur. Fixing data issues should be a part of your regular schedule, not something that you only do on special occasions or right before the big mailing.
 
One major issue that we need to fix regularly is duplicate records. Let me begin by saying that the person who invents a way to prevent duplicate records from being created in database with 100% effectiveness is going to become a multi-bajillionaire – we would all buy whatever they are selling. Until that happens, we all just need to accept that duplicates happen.
 
Instituting a policy of regular duplicate searches and merges helps to keep your duplicate fix workload from becoming Mt. Everest and instead keeps it at speed bump levels instead. It also helps to ensure that your constituents are always receiving appropriate benefits when they interact with you.
 
A few best practices for duplicate maintenance:

• Search for and merge duplicates often – no less frequently than once a month, but weekly is better (see above about your constituents getting appropriate benefits)
• Have multiple duplicate search processes that evaluate possible duplicates at different match percentages
  • Run the processes in decreasing match order and address duplicates at higher match percentages first before running the next process
• If a search returns a match that is NOT a duplicate, mark the records to indicate that, which will allow you to remove those records from later searches or at least make evaluating them as duplicates in the future much faster
• If you find a duplicates in the course of your daily work, take a moment to merge the records if you have system rights to do so, or if you aren’t granted those rights, take a moment to document the duplicate and pass the note on to the appropriate person

While duplicates are a big data issue, it isn’t the only issue. Despite our best efforts with our data entry policies and following the Three C’s, sometimes incorrect and incomplete data sneaks in. The good news is, if everyone took our policies about consistency in data entry to heart, issues with correctness and completeness are easier to fix.
 
With these types of issues, query can be your best friend:

• Want to find missing data that should be there? Use the “Blank” query operator. For example, blank title fields or blank address lines
• Common data entry areas? Use the “Contains” or “Does not contain” operators. For example, email addresses that do not contain an “@”
• Records not connected properly? Use multiple criteria to find records have a particular indicator of relationship, but are missing the actual connection. For example find children not included in a household with their parents by creating a query that looks for records with a relationship type one of Child, Son, Daughter (and/or a birthdate indicating that they are under 18) that are not members of a household
• Use “Browse” within a query to quickly access and move between records included in the results, and fixing the issue as you go

With a little planning and making sure to incorporate regular maintenance into your routine, your data becomes a thing of beauty – a reliable source of information that makes doing your job easier!
 
Need help documenting and implementing your policies and a head start getting your data health up to snuff? Our Altru experts can ensure your data is clean and in the right state to leverage effectively. Learn more about available services to clean and enrich your data at arts.blackbaud.com