1

I am trying to create a query in marketing cloud that will give me both ContactKey and SubscriberKey in the results.

The background: we are setting up MobileConnect. It seems that between MobileConnect and Email Studio there are 4 IDs in play:

  • SubscriberKey (email - user specified)
  • SubscriberID (email - system generated)
  • ContactKey (mobile - user specified)
  • ContactID (mobile - system generated)

I have used this question as a reference to run queries to get a list of MobileConnect contacts. However, the issue is that these data views only have the ContactID.

It is also apparently not possible to query the _AllContacts data view (as per this article).

So how can I run a query that will result in a DE that shows contacts from both Email Studio and MobileConnect, along with their SubscriberKey and ContactKey, mobile number, email address, etc?

An alternative way of putting it: is there a query that will return all my MobileConnect contacts with their ContactKey (which will be the same as their SubscriberKey)?

Thanks

Improve this question

1 Answer 1

Reset to default
5

You can create filtered lists in Mobile Connect that will be available as a source to standard SQL queries.

The method of obtaining those contacts is described here: Permanently remove Contacts or Subscribers and associated data from Contact Builder

Go into Mobile Connect:

  • Click Manage and in the List tab create a filtered list

  • You will be asked to select a starting population - there you can select All Contacts

  • This creates a sort of All Contacts list, but you can narrow it down to Mobile Connect by introducing the condition Mobile Number is not empty or if you select a specific phone number and keyword combination in the population screen described in the previous step.

  • Query it with Select SubscriberKey from [your filtered list name]

If you want to avoid things like that you can also go with a different route: Add the SubscriberKey as a duplicate attribute to MobileConnect as user created attributes become available from the _MobileAddress data view.

You would need a query to that duplicates the key column to a second one and an automated import definition that populates the new duplicate value in MobileConnect.

Improve this answer
4
  • Potentially dumb question, but where is "Manage"? In MobileConnect I only have two main tabs: Overview and Administration. I cannot see "Manage" anywhere within either of those tabs.
    – Ben
    Commented Nov 26, 2019 at 21:33
  • I found "Manage" in MobilePush and discovered this is just All Contacts in Contact Builder. So have been able to create the list. Do you know if thereiIs there any way to automatically refresh this list?
    – Ben
    Commented Nov 26, 2019 at 21:40
  • 1
    In Automation Studio you have a activity called "Refresh Mobile Filtered List" - you can use it to refresh the list just before your query starts.
    – Rafał Wolsztyniak
    Commented Nov 27, 2019 at 8:48
  • Great, thanks for the info
    – Ben
    Commented Nov 28, 2019 at 2:51

You must log in to answer this question.

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