Add Primary Flag for Constituents with No Primary Addresses

Category: Address

Frequency: Monthly

Platform: SQL Server

Developer: IDS

Analyst: DMT
 

What Does It Do?

The OUD-DS3 best practice is for constituents to have a piece of primary contact information if any contact information is available.

This integrity check identifies constituents without a primary address but with valid addresses in DART so that one of those addresses can be made the constituent’s primary address.

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-ADDRESS-Create Primary from Valid Address(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries active individuals without any primary addresses on their record in DART but who have a valid address on their record. If the constituent has multiple valid addresses, the following ranking system is used to select the address to be marked as primary:

  1. Address information source: Self-Reported > Web-Self Reported > Bio Acquisition Project uAG > Gift > Conveyed through UM Rep. > Constituent > (all others)

  2. Address type: Home > (all others)

  3. Address start date (if null, fill in with the date that the address was added to DART) in ascending order

  4. Address GUID as a tiebreaker

Resources and References

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

Snapshot GUID: BBDW.FACT_ConstituentAddress.ConstituentSystemID

Print Article