Major Database Cleanup - Advice on an ETA and getting team members to understand the process
As I am sure many others here have experienced, I have inherited a database that needs a *major* overhaul. I will list a few stats and facts to give an idea of the scope of what lies before me:
- ~38,000 Constituent records
- ~8,000 membership records
- ~57,000 Gift records
There is Membership cleanup to do, with more inconsistent coding like the Financial records, membership transactions carried out with no record of rhyme or reason (it's clear that at least one person was abusing their access to the system by arbitrarily granting and extending memberships for people they liked).
Lots of duplicates, as there don't seem to have been clear Policies and Procedures for things like when a person should have a Spouse record only or be given their own Constituent record. We have foundations with Individual records instead of Organization records, etc.
Plenty of table cleanup for things like Constituent Codes, Solicit Codes, etc.; in the past, it seems any user was allowed to add table entries to whatever, and we all know what happens then: You get one Solicit Code called "DO NOT MAIL," one called "SEND NO MAIL," one called "DO NOT SEND ANY MAIL," etc. *sigh* And nobody even keeping an eye on that stuff and sweeping up once in a while.
It's bad.
I've been working here for about two months and am starting to get the questions about why the cleanup isn't finished. The short answer is that most of my time is actually spent doing the things we do every day and that I have very little time left over to clean up after what others did every day for 12 years. Getting my team members to understand this in a diplomatic way is a challenge, though fortunately, I do have co-workers in the Finance department who understand. I'm working on a briefing that explains each section of what I need to do and goes over some other basic principles of data cleanup, and I think that will help.
Can anyone else who has overseen a big cleanup process (this is my first) offer any insight into how long one might reasonably expect all of this to take? It depends on a lot of different factors, but it would help me to get a sense of how others have gone about similar projects and what they faced. If I could offer anything in the way of an ETA, it would make people more comfortable and confident that I'm not just goofing off all day. I want them to understand that if this is going to be done right, it needs to be done carefully, and that choosing "fast" over "right" is what made such a big mess here.
Comments
-
I have not had to do a cleanup this extensive, but I've done some similar, more limited projects and I have a strategy for you to consider.
1.) Export all gifts cash gifts, with all data fields you might reasonably use, including either Constiuent ID or Constituent Import ID. (this trick won't work for pledge payments or recurring gifts).
2.) Do all of your gift editing in Excel (normalizing campaign, fund, appeal, package, etc.)
3.) Make sure there's a GL Post Status column and that everything is Posted
3.) Create a gift attribute on every gift that marks these as part of the cleanup process
4.) Import all of thse gifts as NEW GIFTS
5.) Globally delete all cash gifts that don't have the attribute you set up.
6.) At every step of the way, BE CAREFUL.
This doesn't help you with the individual->org conversions or your table cleanup, but depending on the method you had been using, it may at least speed up a big chunk of your process. I did a similar task with about 13,000 United Way gifts that were recorded as pledges in our system in 2013 (which is not how we have done them at any other point in history). I just exported everything, changed the gift type, imported them all, and globally deleted the original pledges they were based on. It was a highly satisfying bit of data cleaning.
Anyway, just a thought.5 -
Daniel - Well sounds like you have your work cut out for you and it must be hard with questions coming in about why it's not done.
With each organization I've come into, they've all required some level of data cleanup and of course, that work varies greatly depending on how they use the data, what their processes are, and of course how bad it is. I've found that doing clean-up projects admist the regular work, it has taken me between 3-4 YEARS to get through it all.
I think what's most important is to do a realistic assessment of what is top priority, and then downwards. Because we're system administrators, of course we want it all to be fixed and beautiful and clean.- In your examples, for instance, how critical is it that they compare campaign/appeal data since they NEVER ACTUALLY HAD IT before? If the data was a mess and never accurately populated, is it realistic for you to spend a ton of time fixing this -- and to what end? What is it going to inform, some new policy or process? Is the work spent in cleaning up old data better spent towards a goal that will be more actionable perhaps and provide the organization more true value?
- So sometimes you have to sort through what seem to be obvious clean-ups when in fact, it might be better to tackle some other types of clean-up instead that might not 'rise to the top' necessarily at first glance?
- Like I saw in another thread about cleanup, making sure you have current contacts for organizations that you mail to. That would help all future contact/correspondence with those organizations to make it to the right person - which would increase your effectiveness.
Good luck!1 -
Unfortunately, in my experience, there is no good answer. I have been working at this organization for just over 4 years now, and have managed to complete about 1% of the cleanup projects identified in my first few weeks. I've managed to start about 60% of those projects...and I've added to the list so often that it's probably double its initial size. But as you mentioned, the day-to-day (and last-minute crisis projects) keeps getting in the way.
Two months is really unreasonable for the amount of work it sounds like you have in front of you. I would try listing out the tasks, with some sort of prioritizing and a loose timeline. Then be sure it is understood (big, bold font at the top of the document) that this timeline is loose and can be easily derailed...and the timeline is more of a moving target than an ETA.
As part of your list, think through how you want to accomplish each task, because often one decision affects others (i.e. do you store a particular piece of data as a Campaign or an Appeal?). For me, consistency is key, and without deliberately thinking things thru, I end up redoing projects I had completed. Also, look at whether projects need to be done manually or if they can be done with Global Change or Import (I prefer Import; more control, less chance of disaster when you don't realize GC will change more records than what you intended). I have a Query Folder for Scrub Tasks, where I store anything related to any cleanup project.
I also find that I get a lot more accomplished when I work from home, or when everyone else is gone. In the past, I've had supervisors who understood and accommodated that, and I was much more productive. It's far easier to click the trigger for an Import or Global Change when you have no distractions, have thought it thru, and feel confident about what you're about to do.
Good luck. And if you find the magic solution to cleaning up 12 years of bad practice in 12 days (or even 212 days), please share!
3 -
I have had to do extensive cleanups before including the Campaigns Funds Appeals thing. I first sat down with my Director/superior and got their buy in and then the rest of the team was informed.
Really -- depending on the scope of the crazy you are cleaning up, just the financial area could take 3 to six months and that's if you are spending half of your work week on it. When I have had a list of cleanup, I have priortized it of course, financial always coming first and Addresee/Sals in the top 3.
It is always a minumum of 18 months and up to 3 years to clean up everything in my experience. Because you have to keep doing the daily tasks and because once you start cleaning you discover new cans o' worms that will require attention and get added to the list -- or that have to be fixed before you can fix the item you were working on.
Just breathe. Stand your ground. Have buy in from superiors. And if you can set aside an amount of time each week, blocks of time, where no one is allowed to disturb you so that the process can happen.1 -
Thanks CHristine! THese are good suggestions. I will work with my supervisor and see if I can get something like "Cleanup Fridays" set aside to focus on these tasks.0
-
Hi Daniel,
I would determine what your current policies should be, pick a date (start of the fiscal year), and then begin using the new data entry standards you have documented. Train everyone on why gifts should be coded as you recommend.
Then, when the new coding structure is in place and being used correctly, clean-up can begin. If you wait until the clean up is complete to start the correct coding structure, it could take years. Prioritize the information that is essential for raising money, not those data pieces that would be nice.
Our database has nearly 1M gifts. I'm on year two of a campaign clean-up designed to simplify our reporting and don't expect to finish it until next year. It's challenge to find an uninterrupted block of time when you can focus on the details that require several steps and planning. I'm on month four of adding historical data to about 500 funds and we've been consistently dedicated to that project and that is a more interruptable project.
Having two instances of Raiser's Edge will allow you to do this more quickly (essentially I mean two different computers with two different log ins) so while you are importing or globally changing information you can do your daily work without having to wait for RE to finish the task.
Best of luck. Write down everything you do so that a future DBA (or even you) can understand why you made the decisions you did and so you can show the work you have accomplished.
1 -
How embarrassing...it seems I attached the wrong file to an earlier message. I'd meant to include a white sheet-type document I'd written about data cleanup and why it takes so long, and instead I included a list of my own cleanup tasks.I feel so exposed, tee hee hee!
Attached is the correct document. Apologies for the mixup!0 -
Ryan Hyde:
I have not had to do a cleanup this extensive, but I've done some similar, more limited projects and I have a strategy for you to consider.
1.) Export all gifts cash gifts, with all data fields you might reasonably use, including either Constiuent ID or Constituent Import ID. (this trick won't work for pledge payments or recurring gifts).
2.) Do all of your gift editing in Excel (normalizing campaign, fund, appeal, package, etc.)
3.) Make sure there's a GL Post Status column and that everything is Posted
3.) Create a gift attribute on every gift that marks these as part of the cleanup process
4.) Import all of thse gifts as NEW GIFTS
5.) Globally delete all cash gifts that don't have the attribute you set up.
6.) At every step of the way, BE CAREFUL.
This doesn't help you with the individual->org conversions or your table cleanup, but depending on the method you had been using, it may at least speed up a big chunk of your process. I did a similar task with about 13,000 United Way gifts that were recorded as pledges in our system in 2013 (which is not how we have done them at any other point in history). I just exported everything, changed the gift type, imported them all, and globally deleted the original pledges they were based on. It was a highly satisfying bit of data cleaning.
Anyway, just a thought.I disagree with some of the process here. I would NEVER add corrected data as NEW gifts, but would import back in to the original gift record that will then have the updates as needed.
4 -
I would emphasize the need to code your CURRENT data correctly moving forward and then prioritize the rest of your historical data as deemed by you and your supervisors based on the following?
1. What data is needed IMMEDIATELY - this would be the data that impacts your current day to day reporting or communication with donors, supporters etc.
2. What table clean up can you do to facilitate a "quick check it off the list" data clean up. Sometimes getting one of these small jobs checked off my list does alot for re-energizing me in the data clean up process.
3. What can the other staff members do to facilitate or help with this. We have 4 different departments, and I've given one of the staff members in each department a very specific project that they are responsible for, and since they do not have global add/edit/delete rights, it takes them a while, but these are less critical clean up jobs, and frees me up for other critical areas. This also helps them see the importance of "doing it right" so they do not have to go back and clean up later.
Good Luck
I love the idea of "clean up fridays"....
2 -
Hi Daniel, sounds like communicating the scale of the cleanup might be necessary for your team to understand the amount of effort it's going to take. I have a few ideas for you, but for the duplicate constituents issue, you can download and use MergeOmatic for free to identify and analyze all the duplicate records in your database. This kind of graphical representation can help execs understand the size of the issue. There's another visualization that will show who created dupes when, but you may or may not want to share that with your team.Good luck with the cleanup!
0 -
Daniel Noga:
Hello everyone,
As I am sure many others here have experienced, I have inherited a database that needs a *major* overhaul. I will list a few stats and facts to give an idea of the scope of what lies before me:- ~38,000 Constituent records
- ~8,000 membership records
- ~57,000 Gift records
There is Membership cleanup to do, with more inconsistent coding like the Financial records, membership transactions carried out with no record of rhyme or reason (it's clear that at least one person was abusing their access to the system by arbitrarily granting and extending memberships for people they liked).
Lots of duplicates, as there don't seem to have been clear Policies and Procedures for things like when a person should have a Spouse record only or be given their own Constituent record. We have foundations with Individual records instead of Organization records, etc.
Plenty of table cleanup for things like Constituent Codes, Solicit Codes, etc.; in the past, it seems any user was allowed to add table entries to whatever, and we all know what happens then: You get one Solicit Code called "DO NOT MAIL," one called "SEND NO MAIL," one called "DO NOT SEND ANY MAIL," etc. *sigh* And nobody even keeping an eye on that stuff and sweeping up once in a while.
It's bad.
I've been working here for about two months and am starting to get the questions about why the cleanup isn't finished. The short answer is that most of my time is actually spent doing the things we do every day and that I have very little time left over to clean up after what others did every day for 12 years. Getting my team members to understand this in a diplomatic way is a challenge, though fortunately, I do have co-workers in the Finance department who understand. I'm working on a briefing that explains each section of what I need to do and goes over some other basic principles of data cleanup, and I think that will help.
Can anyone else who has overseen a big cleanup process (this is my first) offer any insight into how long one might reasonably expect all of this to take? It depends on a lot of different factors, but it would help me to get a sense of how others have gone about similar projects and what they faced. If I could offer anything in the way of an ETA, it would make people more comfortable and confident that I'm not just goofing off all day. I want them to understand that if this is going to be done right, it needs to be done carefully, and that choosing "fast" over "right" is what made such a big mess here.
0 -
Hi Daniel! First off, I will give my condolences because I am dealing with some of the EXACT same challenges as you! I was lucky enough that my org invested in an outside consultant to come train me, one-on-one.
Most of the other advice I have been given is stated already; however I do want to add one thing: keep track of your accomplishments. You will get knees deep into cleanup, and it will be easy to forget you cleaned out 1,300 old queries or created a whole new CFA structure. I ended up creating a document where I track the outcomes of my database cleanup, in case it ever comes up in an annual review, job interview, etc.
Best of luck!2 -
Daniel Noga:
Hello everyone,
As I am sure many others here have experienced, I have inherited a database that needs a *major* overhaul. I will list a few stats and facts to give an idea of the scope of what lies before me:- ~38,000 Constituent records
- ~8,000 membership records
- ~57,000 Gift records
There is Membership cleanup to do, with more inconsistent coding like the Financial records, membership transactions carried out with no record of rhyme or reason (it's clear that at least one person was abusing their access to the system by arbitrarily granting and extending memberships for people they liked).
Lots of duplicates, as there don't seem to have been clear Policies and Procedures for things like when a person should have a Spouse record only or be given their own Constituent record. We have foundations with Individual records instead of Organization records, etc.
Plenty of table cleanup for things like Constituent Codes, Solicit Codes, etc.; in the past, it seems any user was allowed to add table entries to whatever, and we all know what happens then: You get one Solicit Code called "DO NOT MAIL," one called "SEND NO MAIL," one called "DO NOT SEND ANY MAIL," etc. *sigh* And nobody even keeping an eye on that stuff and sweeping up once in a while.
It's bad.
I've been working here for about two months and am starting to get the questions about why the cleanup isn't finished. The short answer is that most of my time is actually spent doing the things we do every day and that I have very little time left over to clean up after what others did every day for 12 years. Getting my team members to understand this in a diplomatic way is a challenge, though fortunately, I do have co-workers in the Finance department who understand. I'm working on a briefing that explains each section of what I need to do and goes over some other basic principles of data cleanup, and I think that will help.
Can anyone else who has overseen a big cleanup process (this is my first) offer any insight into how long one might reasonably expect all of this to take? It depends on a lot of different factors, but it would help me to get a sense of how others have gone about similar projects and what they faced. If I could offer anything in the way of an ETA, it would make people more comfortable and confident that I'm not just goofing off all day. I want them to understand that if this is going to be done right, it needs to be done carefully, and that choosing "fast" over "right" is what made such a big mess here.
From your post, it looks like you have already started compiling a list of issues that require attention. You will keep adding to that list -- because you work through some of the worms in the can, you will spot others that are a layer or two down. Keep the list in priority order.
- The things that will affect the most people in the shortest period of time are the things you take care of first. I too inherited a masterful mess of gift records that spanned the history of RE use at that institution. :-/ Because that affected everyone, even outside of our department, that was the first thing I tackled.
- Set your standards for your org., do the due diligence with others to understand why something is there or not there or located in RE where it is as opposed as to where it is designed or to (maybe no one knew any better <shrug>).
- After you have your standards documented and agreed upon by uppers that may need to 'buy in' get started. As mentioned above I found that cleaning up the gifts was a priority because it did affect not just our department but finance. Pick a date to begin using those new standards, if your new Fiscal Year is soon, that is the most obvious. Then -- work backwards with most recent gift dates first in the clean up. Because I learned that most of the reports expected weekly and monthly would require going back three years, that was what was corrected first. Then worked backwards until I was done.
- Continue to work through your clean up items on the list.
- I can tell you from experience, after doing this sort of thing a few times, that with the approximate number of records you list - and depending on the number of items needing attention on your list -- AND that fact that you still have to do your job daily with all of your job duties, that it will take 18 to 24 months most likely, to complete the whole list. The most items I ever had for one or was 57.
- and to avoid the padded cell/insanity - block at least 30 minutes a day to work on some level of clean up and you can make progress -- if you always try and calendar time in chunks of a day or more it always gets interrupted.
and remember to breathe.
0 -
Good laughternoon fellow RE7ers,
I just found out that doing a table clean-up on Constituency codes will not make updates system wide. Records will be auto-magically updated but queries containing the old constituency codes will have to be manually updated. <sigh> Are there other, not intuitively obvious, elements in RE7 that will be adversely affected and need immediate attention following the table clean-up? I want my end users to be able to proceed with business as usual. Any advice or information to ease this transition for all concerned parties will be greatly appreciated!0
Categories
- All Categories
- Shannon parent
- shannon 2
- shannon 1
- 21 Advocacy DC Users Group
- 14 BBCRM PAG Discussions
- 89 High Education Program Advisory Group (HE PAG)
- 28 Luminate CRM DC Users Group
- 8 DC Luminate CRM Users Group
- Luminate PAG
- 5.9K Blackbaud Altru®
- 58 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 409 bbcon®
- 2.1K Blackbaud CRM™ and Blackbaud Internet Solutions™
- donorCentrics®
- 1.1K Blackbaud eTapestry®
- 2.8K Blackbaud Financial Edge NXT®
- 1.1K Blackbaud Grantmaking™
- 527 Education Management Solutions for Higher Education
- 1 JustGiving® from Blackbaud®
- 4.6K Education Management Solutions for K-12 Schools
- Blackbaud Luminate Online & Blackbaud TeamRaiser
- 16.4K Blackbaud Raiser's Edge NXT®
- 4.1K SKY Developer
- 547 ResearchPoint™
- 151 Blackbaud Tuition Management™
- 1 YourCause® from Blackbaud®
- 61 everydayhero
- 3 Campaign Ideas
- 58 General Discussion
- 115 Blackbaud ID
- 87 K-12 Blackbaud ID
- 6 Admin Console
- 949 Organizational Best Practices
- 353 The Tap (Just for Fun)
- 235 Blackbaud Community Feedback Forum
- 55 Admissions Event Management EAP
- 18 MobilePay Terminal + BBID Canada EAP
- 36 EAP for New Email Campaigns Experience in Blackbaud Luminate Online®
- 109 EAP for 360 Student Profile in Blackbaud Student Information System
- 41 EAP for Assessment Builder in Blackbaud Learning Management System™
- 9 Technical Preview for SKY API for Blackbaud CRM™ and Blackbaud Altru®
- 55 Community Advisory Group
- 46 Blackbaud Community Ideas
- 26 Blackbaud Community Challenges
- 7 Security Testing Forum
- 1.1K ARCHIVED FORUMS | Inactive and/or Completed EAPs
- 3 Blackbaud Staff Discussions
- 7.7K ARCHIVED FORUM CATEGORY [ID 304]
- 1 Blackbaud Partners Discussions
- 1 Blackbaud Giving Search™
- 35 EAP Student Assignment Details and Assignment Center
- 39 EAP Core - Roles and Tasks
- 59 Blackbaud Community All-Stars Discussions
- 20 Blackbaud Raiser's Edge NXT® Online Giving EAP
- Diocesan Blackbaud Raiser’s Edge NXT® User’s Group
- 2 Blackbaud Consultant’s Community
- 43 End of Term Grade Entry EAP
- 92 EAP for Query in Blackbaud Raiser's Edge NXT®
- 38 Standard Reports for Blackbaud Raiser's Edge NXT® EAP
- 12 Payments Assistant for Blackbaud Financial Edge NXT® EAP
- 6 Ask an All Star (Austen Brown)
- 8 Ask an All-Star Alex Wong (Blackbaud Raiser's Edge NXT®)
- 1 Ask an All-Star Alex Wong (Blackbaud Financial Edge NXT®)
- 6 Ask an All-Star (Christine Robertson)
- 21 Ask an Expert (Anthony Gallo)
- Blackbaud Francophone Group
- 22 Ask an Expert (David Springer)
- 4 Raiser's Edge NXT PowerUp Challenge #1 (Query)
- 6 Ask an All-Star Sunshine Reinken Watson and Carlene Johnson
- 4 Raiser's Edge NXT PowerUp Challenge: Events
- 14 Ask an All-Star (Elizabeth Johnson)
- 7 Ask an Expert (Stephen Churchill)
- 2025 ARCHIVED FORUM POSTS
- 322 ARCHIVED | Financial Edge® Tips and Tricks
- 164 ARCHIVED | Raiser's Edge® Blog
- 300 ARCHIVED | Raiser's Edge® Blog
- 441 ARCHIVED | Blackbaud Altru® Tips and Tricks
- 66 ARCHIVED | Blackbaud NetCommunity™ Blog
- 211 ARCHIVED | Blackbaud Target Analytics® Tips and Tricks
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- Luminate CRM DC Users Group
- 225 ARCHIVED | Blackbaud eTapestry® Tips and Tricks
- 1 Blackbaud eTapestry® Know How Blog
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)
- 1 Blackbaud K-12 Education Solutions™ Blog
- 280 ARCHIVED | Mixed Community Announcements
- 3 ARCHIVED | Blackbaud Corporations™ & Blackbaud Foundations™ Hosting Status
- 1 npEngage
- 24 ARCHIVED | K-12 Announcements
- 15 ARCHIVED | FIMS Host*Net Hosting Status
- 23 ARCHIVED | Blackbaud Outcomes & Online Applications (IGAM) Hosting Status
- 22 ARCHIVED | Blackbaud DonorCentral Hosting Status
- 14 ARCHIVED | Blackbaud Grantmaking™ UK Hosting Status
- 117 ARCHIVED | Blackbaud CRM™ and Blackbaud Internet Solutions™ Announcements
- 50 Blackbaud NetCommunity™ Blog
- 169 ARCHIVED | Blackbaud Grantmaking™ Tips and Tricks
- Advocacy DC Users Group
- 718 Community News
- Blackbaud Altru® Hosting Status
- 104 ARCHIVED | Member Spotlight
- 145 ARCHIVED | Hosting Blog
- 149 JustGiving® from Blackbaud® Blog
- 97 ARCHIVED | bbcon® Blogs
- 19 ARCHIVED | Blackbaud Luminate CRM™ Announcements
- 161 Luminate Advocacy News
- 187 Organizational Best Practices Blog
- 67 everydayhero Blog
- 52 Blackbaud SKY® Reporting Announcements
- 17 ARCHIVED | Blackbaud SKY® Reporting for K-12 Announcements
- 3 Luminate Online Product Advisory Group (LO PAG)
- 81 ARCHIVED | JustGiving® from Blackbaud® Tips and Tricks
- 1 ARCHIVED | K-12 Conference Blog
- Blackbaud Church Management™ Announcements
- ARCHIVED | Blackbaud Award Management™ and Blackbaud Stewardship Management™ Announcements
- 1 Blackbaud Peer-to-Peer Fundraising™, Powered by JustGiving® Blogs
- 39 Tips, Tricks, and Timesavers!
- 56 Blackbaud Church Management™ Resources
- 154 Blackbaud Church Management™ Announcements
- 1 ARCHIVED | Blackbaud Church Management™ Tips and Tricks
- 11 ARCHIVED | Blackbaud Higher Education Solutions™ Announcements
- 7 ARCHIVED | Blackbaud Guided Fundraising™ Blog
- 2 Blackbaud Fundraiser Performance Management™ Blog
- 9 Foundations Events and Content
- 14 ARCHIVED | Blog Posts
- 2 ARCHIVED | Blackbaud FIMS™ Announcement and Tips
- 59 Blackbaud Partner Announcements
- 10 ARCHIVED | Blackbaud Impact Edge™ EAP Blogs
- 1 Community Help Blogs
- Diocesan Blackbaud Raiser’s Edge NXT® Users' Group
- Blackbaud Consultant’s Community
- Blackbaud Francophone Group
- 1 BLOG ARCHIVE CATEGORY
- Blackbaud Community™ Discussions
- 8.3K Blackbaud Luminate Online® & Blackbaud TeamRaiser® Discussions
- 5.7K Jobs Board