Combining many RE databases into One

Mary Kern 2

Hi all,


I've just started working with an organization that has combined a number of databases into one RE database. Unfortunately speed not accuracy seemed to be the order. Most of the users inherited the data from someone who inherited the data from ... you get the idea. As far as I know there are no best practices manuals and users were taught by someone who was taught by someone ... again, you get the idea. 


Each had their own coding style, data entry style, etc. 


Have you ever encountered this? Where do you start the cleanup?


Best regards,

Mary

JoAnn Strommen

Just a suggestion. I would have an attribute or constit code (some field) marked if possible with the original source of each record. I have not encountered your exact situation but I have found it very helpful for database cleanup (all records from source A have this issue) and for answering questions as to why/how record came to be in the database.


It's one bite of the elephant at a time. I'd start with constituency code and just work tabs to the right. If you use a mail house, address data style isn't a priority IMO. They adjust/fix most. If your dealing with large #s of fixes, I'd export records to excel. Sort, fix there and then import back in.  Be sure to export ID's for constituent and address.

 

Lawrence Rush

Haphazard data migrations are a nightmare! There are so many places you could start cleanup and there's no quick solution. Cleanup will take time and it's worth taking the time to do it. Before you even start the cleanup, I would suggest briefly mapping out the history of your inherited database. What sources were merged together? Were there organization level changes in the way gifts were processed/tracked? Is significant information missing? This will help you and future users have a frame of reference and will also help trigger red flags when you see data that needs to be corrected.


Next, I would advise you and your team to decide what information needs to be saved for historical purposes. For example, there was a significant shift in the way that funds were named in our organization. It was up to our development department to decide whether all funds in RE would be converted to the new naming convention or if the "old" funds would be marked inactive and a new set would replace those. We chose the latter option. It was important to note this decision so that people pulling queries remembered that they needed to include historic funds.


From there I don't think it necessarily matters where you start, but you'll probably want to focus on the portions of the database that are most utilized. In my experience, because I've been heavily involved in gift processing, I like to start cleanup by inactiving old funds and setting up the relationships between campaigns, funds, and appeals to ensure quicker and more accurate data entry.


 

Graham Getty

Mary Kern:

Hi all,


I've just started working with an organization that has combined a number of databases into one RE database. Unfortunately speed not accuracy seemed to be the order. Most of the users inherited the data from someone who inherited the data from ... you get the idea. As far as I know there are no best practices manuals and users were taught by someone who was taught by someone ... again, you get the idea. 


Each had their own coding style, data entry style, etc. 


Have you ever encountered this? Where do you start the cleanup?


Best regards,

Mary

I would take this as opportunity to document and standardize records as you want them to be. I would start with your board members, document required fields, contact types, addressee/salutations, active vs inactive attributes, etc. Take screenshots, use these records as templates for future cleanup. Create an attribute called "Data Standardization" with a related table with 2 values: "Updated", "Issue Updating" and the date and comment field used to annotate each record (especially if there's missing data that prevents standardization). Create a query for every record that does not have this attribute (specific attirbute import id = blank) and use that query as business rule so that whenever anyone opens a record they can be prompted with message along the lines of: This record has not yet been standardized. Please follow instructions in X to standardize this record and flag with Data Standardization attirbute. Once your board members are done and you have thorough screenshots and easy to follow steps to standardized (use default sets to speed data entry, etc.), then move on to your top 200 donors. Once board and top 200 donors are done, make sure each front-line fundraiser begins to standardize her/his active prospects. Track your progress as you go with a goal of standardizing 100 records / week. Then if it's critical you could employ other members of your organization to help (or hire outside help) where you set goals for higher numbers per week to reach goal of  I would also do as much globally as you can (purchase Address Accelerator, export / import, configure global addressees and saluations, make nickname & spouse nickname fields required (even if same as first name) - so much easier to pull nametags and event lists if everyone has same field populated. Lastly I would also limit your database to 4 contact types per record (Email, Mobile, Home, Business).

Laurel Quaintance 2

Mary Kern:

Hi all,


I've just started working with an organization that has combined a number of databases into one RE database. Unfortunately speed not accuracy seemed to be the order. Most of the users inherited the data from someone who inherited the data from ... you get the idea. As far as I know there are no best practices manuals and users were taught by someone who was taught by someone ... again, you get the idea. 


Each had their own coding style, data entry style, etc. 


Have you ever encountered this? Where do you start the cleanup?


Best regards,

Mary

Hi Mary,

I have had this occur at the last 3 places I have worked. I try to prioritize the main issues.

The CFA structure and how you can use Global change to fix that and get everyone on the same page - and make sure ID's are synced with Finance

The Closest Top Constituents and Donors - Set Data Standards and get those records cleaned up and have the rest follow.

It will take a lot of time and effort - it will be one daya at a time

Another thing may be standardizing and rebuiding Constituent ID's to be numeric so you can data warehouse or use them in Excel or Access, (All six digit perhaps a specific range for each data source if that is helpful) I have found that some folks converted data and used prefixes with alpha characters which made things a nightmare if you need to sort the field or use it with Wealth ratings or other things later on.

Addressee and Salutation records - always a nightmare to clean up - but worth it. Set those standards and make them auto populate if you can.

 

Christine Cooke 2

Mary Kern:

Hi all,


I've just started working with an organization that has combined a number of databases into one RE database. Unfortunately speed not accuracy seemed to be the order. Most of the users inherited the data from someone who inherited the data from ... you get the idea. As far as I know there are no best practices manuals and users were taught by someone who was taught by someone ... again, you get the idea. 


Each had their own coding style, data entry style, etc. 


Have you ever encountered this? Where do you start the cleanup?


Best regards,

Mary

oh so much fun.... You can start by making a list of all the places you see crazy (sounds like several) -- you will be adding to the list as you go because new cans of worms will be discovered of course. Once you have a list - priortize by importance - meaning impact on daily function and fix those first and then work your way down the list.  I am still cleaning up  neglected/misunderstood data and I have been at this current job 3 1/2 years.  It's take awhile, but if you can chip away at it with at least 30 minutes a day you can really make some progress without feeling overwhelmed.  Good luck! 

Diane Zempel

Mary Kern:

Hi all,


I've just started working with an organization that has combined a number of databases into one RE database. Unfortunately speed not accuracy seemed to be the order. Most of the users inherited the data from someone who inherited the data from ... you get the idea. As far as I know there are no best practices manuals and users were taught by someone who was taught by someone ... again, you get the idea. 


Each had their own coding style, data entry style, etc. 


Have you ever encountered this? Where do you start the cleanup?


Best regards,

Mary

Hi Mary,


(I didn't read the other responses so apologize if this is duplicate info) We went through this exact thing a few years ago.  The organizaiton had gone through the database merge process prior to my working for them. One of my roles became standardizing the data to reduce/elliminate the manual clean up that was a result of the merge. I went back to the BlackBaud consultant who talked to our Org pre merge. Of course before we did any clean up we needed to know "how" we wanted information structured but she helped guide us through. I created a timeline of sorts and broke the database apart in areas of focus. Then we moved through the clean up piece by piece. I'm happy to share more detail of the steps if you'd like. Every shop is different but there may be some nuggets you can take from it.


 

Mary Kern 2

Thanks! That helps!


Best regards,

Mary