We are so pleased to have yet another excellent guest post from Joseph Conley, an educational consultant with Blackbaud. This week he will delve into some top tips to fix your ‘dirty database’. Thank you Joseph and over to you!
It may be that you’ve never had to work with a dirty fundraising database. If so, you are clearly a non-profit unicorn! For the rest of us though, this is simply a common and maddening part of life.
In previous posts, I wrote about how to choose the right donor management system for you, as well as the top mistakes people make when using them. Today, I’d like to share with you the steps for fixing a database that has such mangled coding that it makes it inefficient and a pain to work with.
Step One: Get to Know One Another
Sometimes your first impulse might be to dive in head-first, your proverbial machete swinging, to destroy the tangled mass that has become your database.
Resist the urge, as your very first task is to learn more about it. It’s the same principle with driving a new car; even though you’re raring to put the pedal to the metal, first you need to get to know how your new machine works.
Even if you are familiar with the software, need to gain a deep understanding of how your organization uses it. What information do you need to track? Where and how is it being tracked? Why have things been done this way in the past? Otherwise, you run the risk of losing important information or accidently creating obstacles to workflow.
Step Two: Audit Your Codes
All databases are built using codes. With your particular system, it may consist of check boxes, radio buttons, drop-downs, tables or some combination thereof. They have a very bad habit of rebelling, however, and it is imperative that you wrangle them under your own control.
For example, you may find that your system boasts codes titled “email”, “e-mail”, “email 1”, “e-mail 1”, “email1” and so on. Chances are that pulling the correct email will be more difficult, and surely you won’t want all of these codes anyway.
Keeping your database clean and organized is important, but doing so is not a solitary task. There may be coding that, while appearing redundant on first glance, actually might act as an identifier of problematic areas. Make sure the whole team is involved in this process, and it shouldn’t be too difficult to pinpoint the source of coding-related issues plaguing your database system.
Step Three: Train the Team
Have you ever tried to clean around a toddler? Every time you put something away, they’re right behind you and yanking it back out since they don’t know any better.
In many ways this can also happen with your staff — just as you need to be trained in both how to use the database and how your organization uses it, so do they. I’ve seen Database Administrators (DBAs) fail because they try to be a lone warrior standing against a horde of bad data.
Work closely with your team to train together in the meticulous art of database system management, and it will become infinitely easier to spot bad code and other threats to your system’s operational effectiveness. After all, the goal is to seamlessly integrate this new addition with the rest of your organization’s infrastructure.
Step Four: Budget for Clean-Up
Perhaps your database is so dirty and convoluted that you need to bring in outside help in the form of assistance from software companies or third-party consultants.
Regardless of what route you take, there are still costs associated with caring for your system. Cleaning a database is a precise and patience-testing task — so don’t assume that it’s just a quick task you can complete “in your free time” (a myth among non-profit workers as it is). Additionally, nor is it fair to your DBA if you expect them to work an extra ten hours a week until the project is fixed.
Instead, opt for alternative solutions that will protect your employees as well as your coffers — assigning those 10 hours a week needed to clean and fix the database can come out of the 40 existing for other tasks, for example. Sometimes sacrifices need to be made to benefit operational efficiency, and if moving employees to system repair tasks for a fraction of their normal working hours is what it takes, so be it. This way, your database will be fully operational, free of glitches and provide everyone with far fewer frustrations, more than making up for the time invested in cleaning and maintaining it.
Step Five: Systemagic
Let’s say that yourself and your team have been trained, learned your database and know what coding you need to clean up. You are confident that your staff isn’t going to undo your hard work, and that the time needed to take care of it has been adequately segmented out of your schedule.
Now the real work begins. It is important to be systematic during this process. All relational databases have some tool for searching for and grouping records, and it is crucial that you utilize them to find and address outlying issues as well as sufficiently segment your workload.
For example, you might consider exporting portions of your database to Excel and performing your cleanup there. This is often more efficient than going record by record, especially if you are already a wizard with spreadsheets. If you use your wisdom and experience, your database will fall under your spell and work like magic.
Step Six: Maintain, Maintain, Maintain
You’ve done it! You’ve cleaned your database. Maybe it’s not as spic and span as a brand new frying pan, but you can at least work with and trust your data now. That being said, don’t ever think that it’s time to put your database’s regular maintenance and upkeep on the backburner. Dirty data is insidious and will creep back in if you aren’t careful — it is almost as if it’s waiting for the opportune moment to infiltrate and devastate.
Make sure to have plans in place to audit your data on a regular basis, and perhaps consider tasking someone vastly familiar with the intricacies and coding of your database in such a role. If you have a new employee, it may be possible that they inadvertently altered some sensitive code without meaning to. It happens. Always either assign yourself or someone trustworthy to go back and double-check their work. In addition, this helps to ensure that any new hires have retained their training and legitimately care about the correct operating procedures and protocols that you’ve put in place.
Additionally, implement a means of consistent and transparent communication of changes across the entire team to minimize ambiguity and the damage it can cause. To put it bluntly, dirty data will never stop in its mission to invade your database; yourself and your entire organization need to work as a true team to combat it, with wisdom and experience empowering your defense.
About the author:
Joseph Conley, bCRE-P, is an educational consultant with Blackbaud. Prior to that, he worked in the Development office of the Archdiocese of Boston. When he is not nose deep in a database, Joe can be found 50 feet underwater scuba-diving or (very reluctantly) running around the streets of Boston.