Cleaning one's database

Nicole Totans

I am i in the process of trying to come up with ideas to do proper database cleaning. Does anyone have a plan that they use to thoroughly clean their database? If so ,please share or if you know some services that I could possibly use that do a good job in assisting with database cleaning. Thank you for all of your assistance in advance. 

Dariel Dixon

I think the biggest thing is to start by assessing what the biggest issues in your database are.  Every org has a different problem child, so you may need to determine which issue you want to start with first.  And some issues are more complex than others.  For instance, I don't have a big problem with duplicates (Thank goodness!), but my addressee/salutation table is a mess as are my constituent codes.  But I've determined which are problems that I need to put some significant time to resolve, and which I can handle quickly.

Elizabeth Hoff

Like Dariel I don't have many duplicates and our addressee and salutations were a mess. So I  pulled a report with addressee and salutations and we are fixing those since they are very time consuming when pulling reports for mailings. We also track DOB so I pulled another DOB report and we are verifying or deceasing individuals and updating gender plus marital status to unknown if we do not know. I also pull reports for addresses, emails, and phone numbers that are duplicated on donor record. I have a pretty good handle on the database, but it has taken some time. We also only use so many constituent codes: Individual, Business, Corporation, Donor Advised Fund, and Foundation. Plus all records have a Region code (we use those) and employee or board member if applicable.  

Katherine Mannion

As you decide what you think needs to be worked on, create queries and save them in your own query folder.  I had one marked Cleanup Tasks.  Then you'll have them to run on a schedule (like, monthly) so you can KEEP things clean.  I'd start with simple fixes like Title 1 = Blank or Nickname = Blank. Then you'll have a feeling of accomplishment as you move on to more complicated tasks, especially if you have a real mess on your hands. It's also easier to visualize good Addressees and Salutations when your building blocks are set.


Good luck!

Dan Snyder

Nicole Totans‍, all the advice below is great. I would just add to Katherine Mannion's that you can set up dashboards in database view to review using the audit queries you create. I have a few that I check each week to try and keep the database clean.

Nicole Totans

Thank you so much everyone! I knew I would get great advice if I posted my question here.

Naomi Cantrell

@Elizabeth Hoff
First time on Blackbaud Community and I like your response. I have inherited a mess of a database and I'm not sure where to start cleaning it up. But I would agree that addressee and salutation fields are a good place to start. You mention cleaning those up with reports? How do you do that?

JoAnn Strommen

@Naomi Cantrell Welcome to the BB Community forums.

As @Dariel Dixon said, what is the biggest issue to your org daily functioning. While add/sal may be a mess is there something else that is more crucial. For me there were several other things that needed attention first.

Fixing the add/sal actually came several years into being here. It's a big project and IMO one needs to know what add/sal formats are needed by the org and have a good handle on db use.

Your profile doesn't say what your experience is with RE or other databases. That can also be a factor with what to handle first. Whatever you decide, always remember to test with a small sample before changes to the entire database.

Christopher Horn

@Naomi Cantrell
I'll step back a bit to the strategy level, since I don't know the specific needs of your org.

A CRM database is probably at least being used for fundraising, marketing, grants, financial recordkeeping, and executive analysis. So that can be used as a starting point for triaging a disaster database and making it useful again. Any database chaos that is keeping people from using the database for those purposes is high priority, especially if it's having an impact on financial analysis or fundraising success.

The cleanliness of public facing data like names, addresses, contacts, and addressees is also high priority, since mistakes there can make the org look unprofessional, result in returned mail, or offend donors. Records that will be part of appeals are higher priority than ones that won't. Duplicate records should be merged where possible to improve reporting quality and to reduce duplicate mailings.

Other projects that don't affect reporting quality, organizational workflows, or public facing data have to be considered based on priority and time investment relative to other projects. For example, removing blank phone numbers/emails won't do much to increase efficiency, increase reporting accuracy, or improve communication. But, it also doesn't take that long.

In general, you want data stored in a way that means you're able to query that data later. If you can't access data, what was the point of entering it? Problems such as the same data being stored in multiple fields, duplicate entries in tables, use of free type fields for regularly queried information, or poorly defined fields and table entries can make it very complicated or impossible to pull that information into a report or a query. Since this usually occurs with regularly used fields, such situations are usually high priority for data cleanup projects and updated policies & procedures.

If I was inheriting a messy database and starting from scratch, I would start by reviewing and noting how the organization is currently using the database and looking for pain points, unnecessarily complex systems, and places where staff are resigned to inaccurate reporting or things just not working right. Those are likely places where how fields are being used should be reexamined, and the data in those fields moved to better locations.

I would also examine and write up notes on how fields are currently being used, noting especially where a field is being used in a way that would make it difficult or impossible to query on information, as well as where a field will display in unintended or unexpected ways in Web View.

In addition, I would set up maintenance queries a. to review new records, especially from other staff, b. to check for common errors and typos, c. to review records that will be pulled into appeals for the first time in a long time, and d. to track any changes that will routinely need to be made to all records entered in web view. As I find common mistakes, I usually check for other instances of that mistake with a query, and keep those queries for future data cleanup.