Database Integrity

Abby Lau

I've been using RE for almost eight years. In terms of data cleanup, until now, my role has mostly focused on cleaning up records as instructed, when instructed. Recently, I’ve been given the responsibility of maintaining the overall data integrity of our database and be more pro-active about it. Beyond handling duplicate records and address clean-up, I’m looking for guidance on where to begin and how to build a solid, comprehensive plan for ongoing data integrity.

Does anyone have recommendations, best practices, or workflows that have worked well for them? Thank you in advance!

JoAnn Strommen

Welcome to the BB community, @Abby Lau

For me a key part is to have regular checks of the data. I have a list of queries run on a weekly/monthly/quarterly/semi-annual basis looking for missing or wrong data. I started my list of queries based on issues I was finding.

I have a number looking for blank data fields where there should be data. Others checking for data entered incorrectly, like email contains , / email contains @gmial, etc. Some are to check on our specific procedures for data - an estate record was created - is constituent code 'estate.'

I tried searching forum for previous posts but didn't find what I was looking for. I know @Austen Brown has a pretty comprehensive list of queries.

Abby Lau

Hi JoAnn thanks for the warm welcome!

Thank you for your insight, I think it would be a great idea for me to adopt those practices as well! We're also nearing our constituent limit so I need to clean up some old records as well. It's a top priority for me right now but how often would you run an inactive records query?

Also how often would you do a code clean up? Ours has been untouched for several decades so there is a lot for me to tackle haha.

Thanks again for your feedback!

JoAnn Strommen

I feel your pain. Database clean-up and better coding/data entry was a prime reason I was hired. I've been working on different things for 6 years now and every so often find another spot to clean. I come across info that even the 2 staff here for several decades do not know what it means. LOL

Your org needs to determine when a record is coded as inactive and policy for keeping records of non-donors in the db. Most orgs keep all donors. Inactive may be if no gift in 5 or 10 years. With alumni, we keep record regardless.

For code clean up, small bites. For codes, are there some that are essentially the same? Use the clean up feature to change B to A and delete B. Are some too specific, only relevant for one period of time? Can they be combined into a new broader code.

Do you have duplicates? Merging dups can lower record count but it can be labor-intensive to review them.

Have you been running NCOA scans? Are addresses up to date? That's another side of integrity.

Did someone upload the "Chamber" list with limited info that is now out of date for companies. Consider deleting if not records you need in the database/not active prospects.

Jack Briggs

There are (as I discovered recently!) a bunch of clean-up related queries in RE that might serve as inspiration. If (in RE7/database view), you go to Tools > Install Samples, there are a bunch of clean-up type queries in there that might give you a jumping off point in addition to JoAnn's advice.

Lee Grisham

The database is your oyster, @Abby Lau!

Here's some possible questions to get you started:

Do we mainly reach out via email? Start with the email queries @JoAnn Strommen mentioned.

Do we mail anything physically? Check on AddressFinder, NCOA, Query on "Has no valid address" and Primary Address Home "not blank", Addressees, Salutations, etc.

How do constituents get added? What fields you need/like to have that wouldn't be autopopulated during that creation process? Check your imports/sources to add in advance if possible or a query to update afterwards

What grinds my gears? How could you address things that annoy you or others with global changes, data restructuring, policies and trainings?

Since business rules don't apply to NXT, there will always be work to do to keep it clean. Kudos to you if that's what you love to do! 😁

Don Burrhus

We have a collection of queries that check for missing data where there should be (and v/v!) as well as conflicts in data such as illogical pairings of Constituent Codes. There are also queries for any instances of specific data Types that we use for data coming in from certain external sources as these need human oversight. These queries are presented in a DBV Dashboard that gets reviewed many times per week. This has helped us tremendously in keeping data 'cleaner'. We also do periodic de-duplication of addresses and phones/email addresses. In an initial de-duplication effort, we were able to (very carefully!) remove hundreds of thousands of duplicative data instances through mass-changes and deletions.

Austen Brown

Here's an on-demand webinar that talks through database clean-up, 20 queries to look at every week and few real-world data clean-up projects for inspiration: https://prengersolutions.com/webinar/cleaning-your-donor-database-a-practical-step-by-step-guide/

Abby Lau

Thank you everyone for your extremely helpful responses and resources. Really helps to know where and how to start, it makes these new duties feel a lot less daunting haha.

Madeleine Holdsworth

@Abby Lau A great place to start is the Prenger Solutions webinar on data cleanup:

https://prengersolutions.com/webinar/cleaning-your-donor-database-a-practical-step-by-step-guide/

Then develop cleanup queries to address your org's specific needs. Good luck.

Abby Lau

Thanks Madeleine! I will check out that webinar!