Deduplicate Constituent Phones

What Does It Do?

The OUD-DS3 best practice is for contact information to exist one time in DART and not be duplicated.

This integrity check identifies duplicate phone numbers on a constituent’s record so that the duplicated record(s) can be inactivated.

How Is This Done?

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

The views both query all active (not DNC) phone numbers on a constituent’s record with the same unformatted phone number. The phone records are prioritized to find the best one to be kept based on information source*,  phone type**, and the earliest start date, with the phone GUID used as a tie-breaker if needed.

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

** Phone type priority order: Cell Phone > Home > (all others)

If any of the duplicated phones are the constituent’s primary phone, the phone record that will be kept active is updated to the primary if it is not already the primary. This batch must be processed first.

All of the phones that are duplicated and will not be kept active are inactivated by giving them a Do Not Call flag with reason “Duplicate on DART Record” and end-dated with the integrity check run date. This batch is processed second.

Resources and References

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

Snapshot GUID: BBDW.FACT_ConstituentPhone.ConstituentPhoneSystemID