Gift Entry Audit AND Duplicate Record Audit

Jane Freitas

Good morning!


I have been tasked with creating and managing regular audits of gift entry and duplicate records.  Does anyone have "go to" queries that they've developed to help identify duplicate constituent records, as well as spot-checking gift entries for potential mistakes, etc.?  I have been attempting to build some queries to assist with both of these areas, but am not quite hitting the mark, especially with regards to the duplicate situations.  If anyone is willing to share queries that have been successful in managing these two areas, I'd be most appreciative. 


Many thanks, in advance, for your kind assistance.

Best,

Jane


Jane Freitas

Director of Philanthropy Services

Tidewell Hospice

Crystal Bruce

NP! Discussion moved to Raiser's Edge Community. Thanks!

Margaret Williams

I hear Omatic has a great Duplicate search tool!

 

John Heizer

For duplicate records I'd start with the built in Duplicate Constituent Management Tool in the Admin section of RE.

Debra Holcomb

I built our gift entry audits around the errors I was seeing in our database.  We run those queries weekly to keep the data clean.  Duplicates are trickier.  We use IOM to import data which helps reduce duplicates. Since there are many ways a record could have a duplicate, I check for duplicates when I output data for a communication into Excel. Then we take the ID numbers of those with duplicate addresses or emails and do some additional outreach and research.    

Caden Farr

Debra Holcomb:

I built our gift entry audits around the errors I was seeing in our database.  We run those queries weekly to keep the data clean.  Duplicates are trickier.  We use IOM to import data which helps reduce duplicates. Since there are many ways a record could have a duplicate, I check for duplicates when I output data for a communication into Excel. Then we take the ID numbers of those with duplicate addresses or emails and do some additional outreach and research.    

Debra, 


Could you go into detail as far as how to build gift entry audits? What do those queries look like, and what are some good ones you recommend I build into our weekly schedule? I am working on developing a data health plan for our org, and I am new to raisers edge, so I am just trying to learn best practices for making sure all the data is clean and tidy. 


Thanks!

Karen Diener 2

In general, I've seen that there is a set of data integrity audits common for all organizations.  Things like "if the address is blank, make sure the record is marked as having no valid address".


Other data integrity audits will depend on the rules for that organization.  In some cases, you can make the field required in order to save the record, but these will often be issues you'll have to clean up first.  For instance, I see missing constituent IDs and missing constituent codes.  In this case, you would need to clean those up and then make the fields required in order to save the record.  That should prevent the issue from returning.


A related example is for organizations that designate the primary addressee and salutation on a record to be for the individual who is the primary record holder.  So the primary add/sal for me would be Karen Diener (or Ms. Karen Diener, depending on how your organization has standardized this information) and then might be an additional "household addressee" and "household salutation" to capture both my name and my spouse's name.  So your query would search for instances where someone has a spouse linked to them, but the household addressee or household salutation is missing.


You would approach gifts in much the same way.  If your organization has not traditionally required the Campaign on a gift record and now you want to start, you would need to add the Campaign for all gifts where it is blank, and then require it so a gift cannot be saved without a campaign.  Same goes for Appeal.  If a specific Fund or set of Funds should ALWAYS use a specific Campaign, that is a great audit point.  Write a query to find gifts using that Fund but not using the correct Campaign.  Once cleaned up, you can set default values on the Campaign, Fund, and Appeal records, so that the correct code defaults when entering gifts.  You can limit data entry to only a specific set of codes too, which doesn't allow you to any other coding.


Those are some really quick (and probably poorly explained) examples.  It is really important to remember that weekly data integrity queries are a safety net, and while they are routine and necessary, they do not replace staff training.  Errors and oversights will happen.  But if someone enters 10 gifts a day and each one of them is wrong, this isn't a data integrity issue - it is a staff training / communication issue.


Good luck!  It can be a lot of work to set up, but well worth it in the long run!!


Karen

JoAnn Strommen

Karen Diener‍ gave a great response. I recall several previous discussions on what queries/audits users have.  I know there was one very extensive list several years ago.  You may want to search on the forums for topics like "data clean up" "data cleansing" etc. and see some past posts.


My clean up queries have grown and changed over time - where do I need to check required data, where could wrong data be easily entered, blank fields...