Deduplicate Constituent Emails

What Does It Do?

The OUD-DS3 best practice is for any duplicated email addresses on a constituent record to be inactivated so that only one active record exists for each unique email address per constituent.

This integrity check identifies duplicate email addresses on a constituent’s record so that the duplicated record(s) can be inactivated and invalidated. 

How Is This Done?

This integrity check is run using two SQL Server views called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-EMAIL-Deduplicate Constituent Emails-1 Update Prim(DW)] and [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-EMAIL-Deduplicate Constituent Emails-2 Inactivate Dupes(DW)], which are located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

Those views query all email addresses which appear multiple times on a constituent’s record. It identifies which record to keep based on the information status*, the oldest record (based on date added, then on start date), and the system ID (only used as a tie-breaker if needed). If any of the other duplicate email records are the primary, this ‘best’ email is updated to primary. All emails which are duplicates and aren’t the ‘best’ record are inactivated (end-dated and given a Do Not Email flag) and invalidated.

* Information status order: Self-Reported > Web-Self Reported > Bio Acquisition Project uAG > Gift > Conveyed through UM Rep. > Constituent > (all others).

The primary update must be processed before the other records are inactivated and invalidated.

Resources and References

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

Snapshot GUID: BBDW.FACT_CONSTITUENTEMAILADDRESS.CONSTITUENTEMAILADDRESSSYSTEMID for the records that will be inactivated and invalidated; null for the record that is being kept and made primary (as applicable).