Best Practice for Data Purge (database management process)

Drakea Brehon

I was wondering if anyone could share the best practices for data purging in your database. What is the process, what components/criteria are included in the purge, and how often is it performed to maintain an active list of prospects and donors, ensuring data efficiency and success?

Dariel Dixon

@Drakea Brehon I think it is probably necessary for you to define what you mean by “purge”. What are you trying to accomplish? The database is generally used to record activity and data, and while there are times we would have to delete duplicate records or things like that, I don't know of any process that has users purging records.

There's only a handful of times when I would be deleting records.

• Duplicate record cleanup
• Incomplete records* (This varies quite wildly, but generally no contact info, gifts, actions, or notes)

I really can't think of anything else. And you better have a really good reason for deleting a record.

Alex Wong

@Drakea Brehon

• gift batch is deleted after 7 years (finance decision on keeping gift batch for 7 years).
• occasionally delete queries that hasn't been used in 2 years

Otherwise, we don't purge any data, all constituent remains (aside from dup identified and merged). All gifts, action, note, attributes, etc generally will remain as well. This is for historical info of a constituent and generally useful for fundraising. Sometime we may look at attributes and do some clean up if we feel strongly it is no longer of value.

Drakea Brehon

@Dariel Dixon inactive records, non donors who have been added to the database to receive magazines etc.

Drakea Brehon

@Alex Wong this was very helpful. What process would you recommend for non donors in your database and no activity in your database?

Dariel Dixon

@Drakea Brehon I don't think inactivity is a reason to delete a record at all? What purpose does that serve, as there already is a status for that. And if they have any gifts/actions/notes on the record, you need to keep that level of documentation.

Even those that are receiving magazines, because you still have some data that is on those records. Just because a record doesn't have any gifts doesn't mean that is shouldn't be in the database.

What would be the purpose of this purge? I don't think that good data hygiene means mass deletion necessarily.

Alex Wong

@Drakea Brehon
I agree with Dariel, I think the ONLY reason why you should “purge” constituent record from your RE NXT is if you are reaching the constituent record count limit and your management doesn't want to pay for the higher quota and is asking you to “clean up”.

If above is truly the case, I would do the following:

• first handle all duplicates, by the way, if you didn't, any dup that has been merged were only inactivated by RE NXT function, which didn't use to delete the no-longer-useful record (this only changed recently). So you want to check any records like this to delete
• I don't know what your org record in constituent record, (i..e event? note? action? various attribute?). So next up is with your own knowledge of your org, query for constituent record that doesn't have any useful information at all: first must have no gift (gift = keep record for sure), then no note, no action, no event participation, no relationship, etc. This step is basically looking for completely useless record. Depending on how long ago (constituent date added) these records were, you can delete them
• if you still need to get rid of more, then is to check “less” (instead of no data at all) data, like maybe certain attribute that deem not useful anymore

Aldera Chisholm

@Drakea Brehon I have had to remove constituents in the past - but generally, we have only removed those who have no interactions on their record. No donations, no actions, no opt-in or opt-outs, no relationships, etc. An example of doing this was when the org had mass-imported past members years ago and those folks were almost all partial records. We prioritized removing folks who had no usable contact info.

Like others have said, start with de-duping and cleanup, then see where you are for record count. You can also look for records with minimal information that could be ‘downgraded’ to a relationship. Likely that's not a huge number, but if you are close to the max count, it may help.

Most folks will have an automatic “absolutely not” when asked to remove records - I have done it when it's been required, but I don't think there is a standard set of recommendations. Controversial take here, but if you have to go down this path, I would start with a query where you looked at all the summary data - output every measure you can with a “total” - eg total # gifts, total # relationships, total # events, total # actions. Look for folks who are zero across the board. That list would then need to be hand-reviewed and audited before even considering a removal. If Bob with no last name has only an address that bounced back five years ago, and has no gifts - I'd be willing to consider him.

If you have enough people to do a purge - look at why those records were entered, as the org may want to implement policy/process on entering constituents for the future.

Dariel Dixon

@Aldera Chisholm:
If you have enough people to do a purge - look at why those records were entered, as the org may want to implement policy/process on entering constituents for the future.

Okay. Let me say that this part can't be understated (emphasis is mine). Sometimes there is a reason that these records are there, and it might not be very obvious. This is a great example of measuring twice but cutting once. I really appreciate @Aldera Chisholm for saying this part…

Vered Siegel

@Drakea Brehon
I want to elevate/share a presentation from Bill Connors from last year's BBCON, which might speak to some of your considerations around deleting donors and data:

Faith Murray

@Drakea Brehon
“Third-ing” @Alex Wong and @Dariel Dixon that the only good reason to purge records is if your BB tier has reached its max, and you cannot afford the next highest tier.

Otherwise, I want to offer a consideration: to re-activate a lapsed or inactive donor is always cheaper than acquiring new. Therefore, even the old inactive records with no data (besides name and address) are a source of potential revenue, saving you the costs of an equivalent acquisition mailing. If they truly only retain name and address (and hopefully, a donor source field), you could hypothetically retain them in a separate excel file used for direct mail purposes, but you would have to consider the impact of antiquated addresses since your file would no longer be maintained by RE's native Address Update services.

I do consider that dead tributes are fair game, if they have only been added to RE as an effect of a tribute process. We don't mess with adding dead tributes in the first place - we link all tributes to a “Tribute Dummy record” to prevent record waste.

As someone said, records with gifts should never, ever be deleted. Same with alumni, board members, employees, memberships, major donor prospects, and volunteers.

Lastly, I do believe that Omatic used to offer a service where you could store non-constituent records but still have them integrate with RE. We considered that at one point as an option for email management. Of course, you would have the added cost of Omatic, which may defeat the purpose if your goal is cutting costs.

Austen Brown

@Drakea Brehon - @Faith Murray makes a great suggestion - check out List Management from Omatic: https://vimeo.com/55292178

I agree with all other points made, and would like to add this: purging records is not a sustainable long-term solution. Consider this - even if you were to go through a round of purging records each year you are still adding valuable new records to your system (e.g., new donors, volunteers, members, etc.). Over time, this approach will only result in a temporary reduction. Eventually, the ‘purge’ amounts each year won't save your org's record count from leveling-up. This is why is is crucial to start preparing for this inevitability now - before it becomes a budget issue. Echoing @Aldera Chisholm - focus on better data management and narrowing down the reasons why records are added to your system, address the root cause not just the symptoms.

Jason Weaver

@Austen Brown While I agree that the first step to take when contemplating a constituent record purge should be a renewed focus on disciplined and sustainable systems and processes that regulate when a new constituent record is added, this may not be enough. If you've ever inherited one heck of a database mess or participated in more than one migration to or from RE, you probably know what I'm talking about.

Austen Brown

@Jason Weaver - An initial purge makes sense after inheriting a new system, but I want to emphasize that a recurring annual purge is not realistic - especially if the only reason is to ‘stay below a specific record count’.

Jason Weaver

@Austen Brown If an org feels that it has to do a recurring annual purge, even after performing a large(r) one-time purge AND implementing a new disciplined and sustainable system and process that regulates, among other things, when to add a new constituent record, there is probably something wrong with QC and compliance enforcement. And I agree that, generally speaking, an org should not perform a recurring annual purge for the sake of (no other reason than) checking something off of a list.

I also think it's important to define what “recurring” means and what a “purge” is in these kinds of discussions, because I remember deleting a ½ dozen constituent records just a few weeks ago that should never have been added in the first place. After moving one gift, there was no defensible reason to leave the records intact, so I did not hesitate to delete all of them. While this was a small record purge, it was a purge nonetheless and a purge of a recurring nature that I won't hesitate to perform anytime I encounter one or more records that need to be removed from our donor management system.

Austen Brown

@Jason Weaver Echoing the sentiments shared in the comments below—I recommend purging only truly empty records, meaning those with no gifts, no contact information, no opt-ins/outs, and no recorded communications.

Vered shared a recent presentation from Bill Connors, and I’d like to add the original white paper to the conversation. While it’s a few years old, it includes a great example (starting on page 7 with the So, To Delete or Not to Delete Records? section) showing that when you carefully analyze the database, the number of truly empty records is minimal compared to the overall total.

https://billconnors.com/wp-content/uploads/Raisers-Edge-NXT-Pricing-To-Delete-or-Not-Delete-Constituents.pdf

As a consultant, this is my recommendation, but ultimately, each organization should make the decision that best fits its needs. At the end of day - it’s just that, a recommendation.