New job, new database... clean up required! Help Please!

Options
Carley Haynes 2
Carley Haynes 2 ✭
Eighth Anniversary First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion
Hi Community!


I've just moved to a new organization and am faced with an RE database that was converted from a completely different database about 4 years ago. Unfortunately the conversion / implementation was a bit of a mess with fields not mapping properly etc. Since then it hasn't really been used. They've had one person here for 3 months trying to clean it up but that didn't work out. I've now come in and while it looks like the majority of the data has been brought across from the old database I'm finding random holes! A gift here or there, a relationship here or there etc. 


Just to caveat this. I have full access to the old database and that database is completely up to date with payments / gifts / relationships etc. 


I'd love some advice please! Thoughts on going forward? My thoughts are that I have a few options.

1. Delete all the gifts and relationships and start again. I have full access to the old database so it's a simple (ha!) import process to ensure it all comes across. This would also involve manually applying payments to pledges etc. afterwards

2. Use excel to attempt to compare the data to find the holes and then fill them in (I'm not great at this so would include a learning curve)

3. Go through each record and see how we go! We have just over 11000 records so it's not the biggest database in the world but it's still a lot of work! 


I'd love to hear from anyone that has faced this issue before or from anyone that has thoughts at all on a way forward! I'm reluctant to delete all and start again but with the time it would take to check all records I'm fearing this might be the only option. 
Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic
    Do you have any idea why the holes are there? Was there a problem with conversion?


    If I were in your shoes I'd probably opt for option 1 but I don't know how easy it would be to delete all the relationships. Can that be done globally?  To consider for importing - so you don't have to manually apply pledge payments, how old do the gift records go?  How much analysis of data do you do?  Does it matter if you know that person made quarterly, montly or just one payment? I'm just thinking that for gift records older than 3 years do you need all the pledge payment details (amount, date, pay method, check date, etc.) in RE.  Just thinking it might be easier to just create a lump sum gift amount for at least older records and/or staff payroll if you have those.  I know when we converted 10 years ago, that's how past gifts came in. I didn't have to go in and apply payments to pledges. just a thot...
  • Cammi Derr
    Cammi Derr ✭
    Ancient Membership Facilitator 2 Photogenic

    Carley Haynes:

    Hi Community!


    I've just moved to a new organization and am faced with an RE database that was converted from a completely different database about 4 years ago. Unfortunately the conversion / implementation was a bit of a mess with fields not mapping properly etc. Since then it hasn't really been used. They've had one person here for 3 months trying to clean it up but that didn't work out. I've now come in and while it looks like the majority of the data has been brought across from the old database I'm finding random holes! A gift here or there, a relationship here or there etc. 


    Just to caveat this. I have full access to the old database and that database is completely up to date with payments / gifts / relationships etc. 


    I'd love some advice please! Thoughts on going forward? My thoughts are that I have a few options.

    1. Delete all the gifts and relationships and start again. I have full access to the old database so it's a simple (ha!) import process to ensure it all comes across. This would also involve manually applying payments to pledges etc. afterwards

    2. Use excel to attempt to compare the data to find the holes and then fill them in (I'm not great at this so would include a learning curve)

    3. Go through each record and see how we go! We have just over 11000 records so it's not the biggest database in the world but it's still a lot of work! 


    I'd love to hear from anyone that has faced this issue before or from anyone that has thoughts at all on a way forward! I'm reluctant to delete all and start again but with the time it would take to check all records I'm fearing this might be the only option. 

    Excel formulas like vlookup and "if" formulas for checking would be very useful for you.  Depending on how much new data has been added to existing records, you might want to build queries to identify any of the suspect data based on 'date added' that was entered prior to any newer data and then delete them and just re-add the old data as needed.  I don't know if you are hosted or not, but I would be sure to have a backup of the current RE db.  It's not difficult to do a global delete on relationships via a "Relationship" query either.  I am not sure what type of identifier you use for the constituent ids between the 2 databases, but this would be important so that you can be sure that you are updating the correct records and not creating a mess of duplicate constituents. 
  • Carley Haynes 2
    Carley Haynes 2 ✭
    Eighth Anniversary First Reply
    Hi Kedar,


    That sounds like a really good strategy! Would you consider a spot check enough on the new data? I just don't want to end up with the same problem! 


    Thank you! 

    Carley
  • Carley Haynes 2
    Carley Haynes 2 ✭
    Eighth Anniversary First Reply

    JoAnn Strommen:

    Do you have any idea why the holes are there? Was there a problem with conversion?


    If I were in your shoes I'd probably opt for option 1 but I don't know how easy it would be to delete all the relationships. Can that be done globally?  To consider for importing - so you don't have to manually apply pledge payments, how old do the gift records go?  How much analysis of data do you do?  Does it matter if you know that person made quarterly, montly or just one payment? I'm just thinking that for gift records older than 3 years do you need all the pledge payment details (amount, date, pay method, check date, etc.) in RE.  Just thinking it might be easier to just create a lump sum gift amount for at least older records and/or staff payroll if you have those.  I know when we converted 10 years ago, that's how past gifts came in. I didn't have to go in and apply payments to pledges. just a thot...

    Hi JoAnn,

    I don't know why the holes are there unfortunately. The person who imported them all has since been let go and not on great terms. I believe the relationships could be done fairly easily from what I've seen. Great question about the gifts. I'm kind of reluctant to lump sum the gifts but I see your point about the value of the information versus the work involved! I"ll check with the Director and see what her thoughts are on this. 

    Thank you,

    Carley.

     

  • Carley Haynes 2
    Carley Haynes 2 ✭
    Eighth Anniversary First Reply

    Excel formulas like vlookup and "if" formulas for checking would be very useful for you.  Depending on how much new data has been added to existing records, you might want to build queries to identify any of the suspect data based on 'date added' that was entered prior to any newer data and then delete them and just re-add the old data as needed.  I don't know if you are hosted or not, but I would be sure to have a backup of the current RE db.  It's not difficult to do a global delete on relationships via a "Relationship" query either.  I am not sure what type of identifier you use for the constituent ids between the 2 databases, but this would be important so that you can be sure that you are updating the correct records and not creating a mess of duplicate constituents. 

     

    Hi Cammi, 

    Thanks for the reply! I've used excel to find constituents who were missed but do you have any tips for gifts? The constituent ID is the same between the two database (for the most part - a few random different ones!) if that helps? We are hosted so I believe a backup is done regularly anyway, phew! 


    Thank you!

  • Melissa S Graves
    Melissa S Graves ✭
    Ninth Anniversary Facilitator 3 Photogenic
    So have gifts been entered in both systems since the import? Can you export a count of gifts per constituent from the two systems and compare them in excel? any records with different counts are missing gifts.
  • Carley Haynes 2
    Carley Haynes 2 ✭
    Eighth Anniversary First Reply

    Melissa Graves:

    So have gifts been entered in both systems since the import? Can you export a count of gifts per constituent from the two systems and compare them in excel? any records with different counts are missing gifts.

    Hi Melissa, That's an awesome idea! I will see if I can get that information! Brilliant!!! Thank you so much!!! smiley

Categories