Database Cleanup

Wendy Bermudez 2

Hello everyone!

So I recently started at a different organization (went from a seminary to a private high school) and the database I inherited is A NIGHTMARE!! First and foremost, I know I need to clean up the queries, exports, and reports (my predecessor would make new Q/E/R every time he needed to pull any info, even if it was a repeat of one he already did). I also need to clean up the constituency codes. And that's just for starters!

Is there a way to pull a list of what Q/E/Rs in the database in order to figure out what's most frequently used and what can be deleted? Or is this a project so big, it's better off left to a BB cleanup crew (so to speak) that specializes in this kind of thing?

Kim Reagles

@Wendy Bermudez Go To Tools → Delete Multiple Queries → When the window appears with all the queries, right click inside the window and Export Query List Grid. Then you can change the query type and format you want to see and pull them all that way for review.

Alan French

@Wendy Bermudez if you have access to your database via SQL Server, I have a SQL script you can have that will list all queries, when they were created, who created them, when they were last used, which folder they're in etc.

I don't currently have one that does that for exports but I could probably write one for you if needed.

Katherine Manniion

@Wendy Bermudez We do this all the time for our clients! Start by making a list of all the things you identify that need cleaning. Cons codes, tables values, duplicate records, etc. Then prioritize what is an immediate need and what can wait. Cleaning a database can be a daunting and exhausting project!

I'll send you a DM with my email info in case you need any more help, or just some cheerleading!

Faith Murray

@Wendy Bermudez in addition to what others have said, you can sort the Q/E/R by “date last run” and delete any that haven't been run for a few years. That's a great first start!

I recommend creating new folders under Q/E and for the first year, move any queries/exports that you actually use into that folder. If you haven't used them in a year, you probably don't need them. After all, Q/R can always be re-created.

Before deleting queries, though, I would check to make sure that all appeal/action info has been recorded into donor records. Sometimes people pull a list of donors to call … and then that action info never makes it back into the donor records. If you find a query called “Phonathon donors 2021”, for example, you might need to use that query to Bulk Add an action into their donor records before deleting the query.

Before deleting exports, I would check your org's Word merge folders to see what output files may be attached to merges. And before deleting Reports, I would suggest asking your Finance Office for a copy of all RE reports required from your last audit, and your Director for a copy of the last development Annual Report.

Wendy Bermudez 2

@Kim Reagles This is proving to be super helpful! Being able to sort through it (and color code everything!) is going to be a good starting point. Thank you for the tip!

Wendy Bermudez 2

@Faith Murray Super helpful and great things to keep in mind!

I definetly want to organize everything into folders, so that's part of the clean up. I'll have to reach out to anyone who's been here longer than I have to confirm if some of these things can be deleted but I'm sure they won't say no to deleting queries and exports from 2004 that were used once and never again!

As for the mailings, I hadn't realized that. Thank you for that tip! I'll add that to the review for deletion list!

Jacyn Stewart

@Wendy Bermudez
I had to do a similar clean up and I took kind of a long approach to it. I sorted all the queries into folders, including one called “archive” where I put everything I thought needed deleting. That left it available for any user to find via search if they needed it. And after a year I went through and checked ‘last run’ dates and anything that wasn't run I just deleted. This kept my folders of known good and recently used queries clean, but made sure I didn't lose anything we absolutely needed. (And did definitely delete a huge chunk right away that I could see literally hadn't been run in years and were created by users no longer with our org.)

JoAnn Strommen

Note to those doing clean up: The query last run date may or may not be accurate. If one opens a query, runs it and exports the results without making any changes, the last run date does not change. That has been our experience. I just tested again on a query from several years ago. Date did not update to today.

I told all our users to be sure to change something in the old queries if they are still used so date changes. I like the idea of moving all to achive folder or renaming folder archive and having users save in a current folder. Good ideas to find current in the several thousand queries we've accumulated over the many years.

Alan French

@JoAnn Strommen I've had the same “last run date” issue with imports - I have one that I only ever run in validation mode, just to add entries to a code table, and that does not trigger the date to be updated. I have left a note in the description in BIG CAPITAL LETTERS warning people not to delete it because it's actually still being used! ?

Wendy Bermudez 2

@JoAnn Strommen This is good to know! I'll keep that in mind as I clean things up and, for future reference, I'll be sure to tell anyone with RE access about this! Thank you for pointing that out!

Sarah Krenicki

@Jacyn Stewart I do something similar! Folders are super useful for this.

Christine Cooke 2

@Wendy Bermudez
Folders are super helpful. And yes, having one that everything “old” is dumped into until you can decipher what you need to keep and file properly.

I assume that anything more than 3 years old is probably outdated. Or if you know the name of the last data manager, anything before them is also probably a toss. It's easy enough to file all those types of things together.

In places where you don't have folder options, like in Reports, I tend to sort them and get them out of the way of current reports. I use letters from the end of the alphabet, x-, y-, z- as a lead to the name of the report so I can keep them until I figure out whether they can go away. Example: x-annual fund donors this year or y-gala attendees

Miki Martin

@Wendy Bermudez I think many of us have similar experiences of this sort, needing to clean up bad data! Recently a blog was posted that I bookmarked, which has a great schedule to use as a way to schedule out some time each day/week/month/year for tasks like this. I haven't been able to dive into mine yet but it is a great resource, I think.

I've grabbed some great tips from this post, as well. Thank you all for sharing! I love Community!

Wendy Bermudez 2

@Miki Martin Thank you for the link! The spreadsheet in it will be super helpful in tracking the clean up! And I'll definetly be reading (and sharing internally) the article and the guide within it! ?

Dina Rieman

@Miki Martin Super helpful post! Thank you all for your great ideas!

Spring Velazquez

@Miki Martin funny-I was just going to suggest watching the webinar from Cathexis!