[SalesForce] Query to get ContactKey and SubscriberKey in results

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

Best Answer

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.

Related Topic