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 ?


Been there myself... No, there is no such query. Here's hero to do it:

  • open support ticket with a request for the complete list based on contact key.

  • tell them if you want it as csv attached to the case or in a data extension (preferable)

  • once you have the data extension populated you can segment down against records you want to keep or delete directly


We discussed with support at length on this one. Depending on your set up the way to do this without opening a support ticket is to create a mobile push or mobile connect list in Mobile studio. Based on that you will be able to filter for contacts which are not linked to synchronised data extension and then select them for deletion in the Contact Builder UI. The reason this works is that Mobile Studio and Journey Builder use the Contact Builder (and therefore All Contacts) data model so even if you do not have a relevant mobile subscription for the contact they can be returned in a list.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.