[SalesForce] SQL query to identify all contacts without email address

I've created a SQL query to get me all unsubscribed contacts, but is there a query I can write to get all contacts without an email address into one data extension? There isn't a data view for contacts that I can find and all subscribers inherently have email addresses. Any thoughts?

Best Answer

This help page has the best information 'Permanently remove Contacts or Subscribers and associated data from Contact Builder'

In MobileConnect or MobilePush, create a filtered mobile list that meets 4 conditions:

Contact Key is not null AND Email Address is null AND Mobile Number is null AND Device ID is null ​

Note: This is sourced from these attribute paths:

System Data | Contact | Contact Key

System Data | Email Addresses | Email Address

MobileConnect Data | MobileConnect Demographics | Mobile Number

MobilePush Data | MobilePush Demographics | Device ID

This provides every record that does not belong to any channel (Contact Key with no source in All Contacts). Follow the below only if you need to review your data. Otherwise you can delete Mobile Filtered Lists using Contacts Delete.

Related Topic