I found this link Can I query to all contacts in contact builder

and it was very helpful but it only returns the subscriber keys. I want to query emails for duplicates since contactkeys - there are no duplicates.

Is there some way to extract email from the above instead of ONLY subscriberkey?

To add, I have a list in mobile connect that has the rule email address is NOT null

1 Answer 1


You can simply extend the query in step 4 of the linked approach with a join on the _Subscribers data view based on the subscriberkey that exists in both sources to pull the email address into the target data extension.

It would look something like this:

SELECT a.subscriberkey, b.emailaddress from [name of MobileConnect/MobilePush filtered list name] a JOIN _Subscribers b ON a.subscriberkey = b.subscriberkey

This works for contacts who have been emailed and got added to All Subscribers. If you need to match with a data extension that holds your contacts regardless if they have been emailed you need to join this data extension instead. Follows the same principle but the field names for subscriberkey and emailaddress might be different and from your question it looks like you should add a WHERE statement that checks if the email field is not empty.

  • Thanks very help but when I try to export, all the fields are null? O_O
    – GS65
    Commented Mar 14, 2020 at 19:50
  • Hey so I queried _subscribers and it seems like we only have 7000 emai address, this seems WAY too low. Am I doing something wrong? I queried _sent and we have apparently sent 3 million emails. There is no way we have only 7000 emails in our database
    – GS65
    Commented Mar 18, 2020 at 9:03
  • @GS65 this is a bit hard to judge from the outside. What number do you get when you go to All Contacts and select the Email channel on the left side? This includes all existing contacts who received an email unless someone deleted subscribers in email Studio directly. Commented Mar 18, 2020 at 14:18
  • I get 1.2m which is why its weird :( But the contactkey are NOT emails, they are 003 numbers. Each should have an email no?
    – GS65
    Commented Mar 23, 2020 at 15:07
  • Tip: Assuming your [name of MobileConnect/Mobile filtered list] is All_Subscribers, you'd want to reverse the elements in the JOIN. Selelect FROM _Subscribers and JOIN [name of MobileConnect/Mobile Filtered List]. This should run quicker because _Subscribers is smaller! (Eventually avoiding a timeout)
    – LimeWeb
    Commented Jun 21, 2023 at 21:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .