Database Cleanup-Deleting Gifts

Chris Rainwater · June 2017

I've been with my organization for right at a year now as database administrator and have inherited a HUGE, disorganized database with very limited constituent and gift information.

At the start of the next fiscal year I'm attempting to have the database mostly cleaned up and best practices in place to keep things on track.

As of last month we had over 19,000 constituents and over 85,000 gifts in the database. Prior to my starting a constultant was brought in for some clean up and was shocked at the size of the database in relation to the size of our organization.

Currently I have possible duplicate records down to 78, and the total constituent count less than 10,000.

Data from the conversion to RE is often messy with just basic details.

The turning point of information in 2013 when the current Director of Development started and had some RE experience.

Even then before I came RE was just a roladex of basic constituent contact info with bare bones gift information.

I'm trying to find appropriate parameters to pair down the gift amount to a more manageable number, while leaving pertinent information for our Planned Giving Officer.

Any advice as I look at this and try to finish out my best practices guide for the organization?

*edit for clarification:
I'm wanting to keep the giving history.
I'm more looking for ways to filter out the ones that were currupted during the conversions or were entered incorrectly.
What parameters would be gifts worth getting rid of for those reasons.

Carolyn Moatz · June 2017

Do you have reason to believe the gifts are wrong or duplicated? If not, I don't understand why you'd want to delete gifts. The giving history, even if it's just amount, is important. You never know when someone who hasn't given in years will give again.

Karen Stuhlfeier · June 2017

I wouldn't delete gifts. I guess that I don't understand why you would want to delete them? The one thing that you always want to keep is the giving history..

Chris Rainwater · June 2017

Karen Stuhlfeier:

I wouldn't delete gifts. I guess that I don't understand why you would want to delete them? The one thing that you always want to keep is the giving history..

Sorry, I should have been more specific about the gifts I'm looking to filter.

I'm wanting to keep the giving history.

I'm more looking for ways to filter out the ones that were currupted during the conversions or were entered incorrectly.

What parameters would be gifts worth getting rid of for those reasons.

Chris Rainwater · June 2017

Carolyn Moatz:

Do you have reason to believe the gifts are wrong or duplicated? If not, I don't understand why you'd want to delete gifts. The giving history, even if it's just amount, is important. You never know when someone who hasn't given in years will give again.

Should have been more specific.

Finding or filtering the incomplete gifts, those entered wong, or corrupted during conversions.

JoAnn Strommen · June 2017

Another vote for not deleting gifts.

What do you mean by incomplete gifts? If it a missing pay method on a gift from 20 years ago, to me that's just the way it is. Are you able to identify those that were entered wrong or corrupted at conversion? If so, yes, I would make any corrections needed. If you have know cases of duplicate gift entry, you could adjust one to $0 or delete.

Sounds like you've done a lot of clean up. Good job!

PS no need for separate replies. Just put your reply in the thread and it can be seen by all following. (Less email clutter.)

Chris Rainwater · June 2017

JoAnn Strommen:

Another vote for not deleting gifts.

What do you mean by incomplete gifts? If it a missing pay method on a gift from 20 years ago, to me that's just the way it is. Are you able to identify those that were entered wrong or corrupted at conversion? If so, yes, I would make any corrections needed. If you have know cases of duplicate gift entry, you could adjust one to $0 or delete.

Sounds like you've done a lot of clean up. Good job!

PS no need for separate replies. Just put your reply in the thread and it can be seen by all following. (Less email clutter.)

Thanks, there's still a lot to be done.

There are a lot of instances of Cash gifts listed as $0 from the conversion, duplicate gifts, or gibberish under reference, appeal, and campaigns.

I guess I was looking at it wrong to delete these incorrect gifts from the conversion, since any record of gift or gift date can be relevant in some way.

Ryan Hyde · June 2017

Chris Rainwater:

JoAnn Strommen:

Another vote for not deleting gifts.

What do you mean by incomplete gifts? If it a missing pay method on a gift from 20 years ago, to me that's just the way it is. Are you able to identify those that were entered wrong or corrupted at conversion? If so, yes, I would make any corrections needed. If you have know cases of duplicate gift entry, you could adjust one to $0 or delete.

Sounds like you've done a lot of clean up. Good job!

PS no need for separate replies. Just put your reply in the thread and it can be seen by all following. (Less email clutter.)

Thanks, there's still a lot to be done.

There are a lot of instances of Cash gifts listed as $0 from the conversion, duplicate gifts, or gibberish under reference, appeal, and campaigns.

I guess I was looking at it wrong to delete these incorrect gifts from the conversion, since any record of gift or gift date can be relevant in some way.

I think you're the only one who can answer the question of whether or not these gifts could still be relevant in some way even if they have a $0 value and were clearly the result of an error. There's no fast and hard rule for this sort of stuff because it's all highly variable from org to org.

The rule of thumb should always be, when in doubt leave it alone. These gifts are likely not doing any harm to anyone or anything because they are too old to matter a whole lot at this point. My gut would be to leave them be, or globally add a reference or note to them that indicates that they are from a certain time period and may be innacurate.

Alicia Barevich · June 2017

Definitely good to leave it alone if you're not sure. One thought may be to add an attribute to all the gifts you think are weird...and just audit that query down as you have time, starting with the newest, as the older gifts are probably less relevant.

Chris Rainwater · June 2017

Alicia Barevich:

Definitely good to leave it alone if you're not sure. One thought may be to add an attribute to all the gifts you think are weird...and just audit that query down as you have time, starting with the newest, as the older gifts are probably less relevant.

I think this sounds like the best option for me moving forward. Globally add an attribute or updated reference and the pour through them as I can.

Thanks for the guidance on the topic!

Christine Cooke 2 · July 2017

Chris Rainwater:

I've been with my organization for right at a year now as database administrator and have inherited a HUGE, disorganized database with very limited constituent and gift information.

At the start of the next fiscal year I'm attempting to have the database mostly cleaned up and best practices in place to keep things on track.

As of last month we had over 19,000 constituents and over 85,000 gifts in the database. Prior to my starting a constultant was brought in for some clean up and was shocked at the size of the database in relation to the size of our organization.

Currently I have possible duplicate records down to 78, and the total constituent count less than 10,000.

Data from the conversion to RE is often messy with just basic details.

The turning point of information in 2013 when the current Director of Development started and had some RE experience.

Even then before I came RE was just a roladex of basic constituent contact info with bare bones gift information.

I'm trying to find appropriate parameters to pair down the gift amount to a more manageable number, while leaving pertinent information for our Planned Giving Officer.

Any advice as I look at this and try to finish out my best practices guide for the organization?

*edit for clarification:
I'm wanting to keep the giving history.
I'm more looking for ways to filter out the ones that were currupted during the conversions or were entered incorrectly.
What parameters would be gifts worth getting rid of for those reasons.

And to add on to everything everyone has said -- if you delete gifts -- you permanently destroy all giving reports.

Stephanie Roland · July 2017

Chris Rainwater:

I've been with my organization for right at a year now as database administrator and have inherited a HUGE, disorganized database with very limited constituent and gift information.

At the start of the next fiscal year I'm attempting to have the database mostly cleaned up and best practices in place to keep things on track.

As of last month we had over 19,000 constituents and over 85,000 gifts in the database. Prior to my starting a constultant was brought in for some clean up and was shocked at the size of the database in relation to the size of our organization.

Currently I have possible duplicate records down to 78, and the total constituent count less than 10,000.

Data from the conversion to RE is often messy with just basic details.

The turning point of information in 2013 when the current Director of Development started and had some RE experience.

Even then before I came RE was just a roladex of basic constituent contact info with bare bones gift information.

I'm trying to find appropriate parameters to pair down the gift amount to a more manageable number, while leaving pertinent information for our Planned Giving Officer.

Any advice as I look at this and try to finish out my best practices guide for the organization?

*edit for clarification:
I'm wanting to keep the giving history.
I'm more looking for ways to filter out the ones that were currupted during the conversions or were entered incorrectly.
What parameters would be gifts worth getting rid of for those reasons.

I experienced the same thing when I first started back in 2007. Our database was just an expensive rolodex, and gift entry was terribly inconsistent. Adding an attribute to gather all of these "weird" records and then widdle away is good starting point.

We also came to an agreement in our office that we would fix records going back only so far, and/or for current. If we are looking at data that is pre-2007 we do it with the understanding that it may not be 100% accurate and we just have to acknowledge that and move on. The farther away you get from that point in time the healthier and more reliable your data will become. We also prioritize cleaning up records for our current donors, since those are the constituents we are recognizing now. Unfortunately there are no quick fixes, it takes time and can be tedious work.

Faith Murray · July 2017

At the start of the next fiscal year I'm attempting to have the database mostly cleaned up and best practices in place to keep things on track.

I admire either your ambition or yoru free time.

I've been working on cleaning ours up for about 2 years, and there's always more stuff to do. I really only get a few weeks during summer when I can devote my time to it. Good luck and keep up the momentum!

Database Cleanup-Deleting Gifts

Comments

Categories