[SalesForce] Query All contacts for mass contacts delete

I need to delete 2M Contacts from All contacts.

Actually, my colleague synced the Contact object from Salesforce without checking the box "only import contact with email address".
I checked it later in order to only get contacts with email address in the Contact synchronized DE.

However, it seems that all the contacts that have been imported once on SFMC through MC connector stay in the SFMC in All Contacts…
Is there any to query the All Contacts table in order to get all the contacts without email address and import them into a Data Extension, so that we can use the delete contact feature on a Data Extension ?
If not would you have some solution that could address my need ?

Best Answer

No need to create a support case, this doc has ways to accomplish your use case: 'Permanently remove Contacts or Subscribers and associated data from Contact Builder'

Prepare to delete any Contacts without channels Note: You must have MobileConnect or MobilePush to complete these steps.

  1. 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.

  1. Use an SQL Query Activity to query from this list into a new Data Extension (DE).
  2. Create a new DE with two fields, one titled SubscriberKey, make this field a Primary Key. Create one non-primary key which can be nullable. Make the DE sendable.
  3. In Automation Studio, create an SQL query that resembles the below.

select subscriberkey from [name of MobileConnect/MobilePush filtered list name]

  1. For the target Object, select the newly created DE. Save it, then place it in an Automation and run the Automation once to populate the DE.
  2. Per our best practices documentation, create a backup of this DE should you need it at a later date.
  3. Follow the 'Contacts Delete' documentation to delete the DE and the Contacts contained within from your account.
Related Topic