Review Possible Duplicate Constituents

What Does It Do?

The OUD-DS3 best practice is for constituents to have all of their data on a single record and for all duplicated constituents to be consolidated into a single record.

This integrity check identifies possible duplicate constituents that have the same first name, the same middle initial (if a middle name exists), and the same last name or last names that are substrings of one another with at least one piece of shared contact information so that the two records can be reviewed by GRA and merged if they are determined to be duplicates. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-CONSTITUENT-Review Possible Duplicate Pairs(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views. That view is the primary data source for a Tableau dashboard called “Duplicate Constituents Review for GRA”, which is published in the “DEV Ad-hoc Analysis” folder.

That view queries all pairs of constituents with the same first name, same middle initial if it exists, and same or substringed last names (e.g., Jessica VanElls = Jessica Marie VanElls-Barnebee) who have at least one piece of shared contact information (full address, email, or phone), regardless of the type or status of that contact information. 

For each constituent pair, there is a score ranging from 0 to 6 that indicates how strong the match is. The score consists of one point each for name, address, email, phone, birth year, and gender. For birth year and gender, if both constituents have a known value (i.e., not 0000 or Unknown) but the values don’t match, the score is -1 for that piece of information.

The constituent pairs are prioritized in the following order (Priority 1-4).

• Priority 1: One constituent has a title of “Dr.” and the other does not

• Priority 2: At least one of the potential duplicate constituents in the pair has an active prospect manager assignment

• Priority 3: At least one of the potential duplicate constituents in the pair has recognition credit for a gift to UM in their lifetime

• Priority 4: At least one of the potential duplicate constituents in the pair is a UM alum

The dashboard is shared with GRA for manual review and merging. GRA will typically only merge constituents if there are three or more matching pieces of information. If two constituents are reviewed and determined not to be duplicates, a “Duplicate Review” attribute will be added to the records with a comment including the LID of the other constituent.

Resources and References

S:\IDS\MES\SQL Scripts\DATA QUALITY\CONSTITUENT

Duplicate Merging Procedure

Snapshot GUID: BBDW.DIM_CONSTITUENT[1].CONSTITUENTSYSTEMID --- BBDW.DIM_CONSTITUENT[2].CONSTITUENTSYSTEMID