Switch Primary from Invalid Email to Valid Email

What Does It Do?

The OUD-DS3 best practice is for valid email address to be flagged as primary when a constituent has a valid email address available. This will allow more constituents to be contacted via email.

This integrity check identifies email records on constituents that have both (1) an email that is marked as primary but is invalid (i.e., has a Do Not Email flag), and (2) a valid email address so that the valid email can be made into the primary and the old primary email can be end-dated and marked as Do Not Email.

How Is This Done?

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

That view queries all constituents with both an invalid primary email address and a valid non-primary email address. A valid email address is one which does not have a Do Not Email flag (i.e., can be emailed). It notes that the valid email address should be marked as primary (this must occur first), then that the invalid email address should be given an end date of the integrity check run date. If someone has multiple valid email addresses, the following order of email information source is used to select the new primary: Self-Reported > Web-Self Reported > Bio Acquisition Project uAG > Gift > Conveyed through UM Rep. > Constituent > (all other), and if there are multiple emails of the same type, the email with the earliest start date is chosen and the system ID is used as a tiebreaker after that.

First, the valid email address records are made primary, and then the old (invalid) primary email records are given an end date and a Do Not Email flag.

Resources and References

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

Snapshot GUID: BBDW.FACT_CONSTITUENTEMAILADDRESS.CONSTITUENTEMAILADDRESSSYSTEMID for the bad to-be-former-primary emails; null for the new-primary email