Add Missing Country to Phones

What Does It Do?

The OUD-DS3 best practice is for phone records to have an associated country.

This integrity check identifies phone records that are missing a country so that the country can be filled in using contextual information like the constituent’s primary address or the phone number format. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-PHONE-Add Missing Country(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries valid (not DNC) phone records for active constituents with a missing country. If the constituent has a primary address in DART (valid or not), the country from the primary address is extrapolated for the phone’s country. If the constituent does not have a primary address, but the phone number matches US formatting standards, the country is assumed to be the United States*.

* US phone standards: ten digits, where the first digit is not 0 or 1; eleven digits, where the first digit is 1 and the second digit is not 0 or 1.

Resources and References

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

Snapshot GUID: BBDW.Fact_ConstituentPhone.ConstituentPhoneSystemID