Add Primary Flag for Constituents with No Primary Emails

Category: Email

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 email address but with valid email addresses in DART so that one of those email addresses can be made the constituent’s primary email.

How Is This Done?

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

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

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

  2. Email type: Home > (all others)

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

  4. Email GUID as a tiebreaker

Resources and References

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

Snapshot GUID: BBDW.FACT_ConstituentEmailAddress.ConstituentSystemID

Print Article