0

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

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.

| improve this answer | |
  • Thanks very help but when I try to export, all the fields are null? O_O – GS65 Mar 14 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 Mar 18 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. – Stephan de Paly Mar 18 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 Mar 23 at 15:07

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.