Deduplicate Constituent Addresses

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 addresses 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-ADDRESS-Deduplicate Addrs-1 Update Primary(DW)] and [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-ADDRESS-Deduplicate Addrs-2 Inactivate Dupes(DW)], which are located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

The views both query all active (not DNM) addresses on a constituent’s record with the same address lines 1-4, city, state, zip code, and country. The addresses are prioritized to find the best one to be kept based on address type*, information source** and the earliest start date, with the address GUID used as a tie-breaker if needed.
* Address type priority order: Home > Business > (all others)
** Information source priority order: Self-Reported > Web-Self Reported > Bio Acquisition Project uAG > Gift > Conveyed through UM Rep. > Constituent > (all others)

If any of the duplicated addresses are the constituent’s primary address, the address 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 addresses that are duplicated and will not be kept active are inactivated by giving them a Do Not Mail 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\ADDRESS

Snapshot GUID: BBDW.FACT_ConstituentAddress.ConstituentSystemID