SQL Query to Identify All Contacts?

Question

I am looking for a way to identify all contact within Marketing Cloud who don't have an email or sms activity within the past 2 years. I've already done a data extract to get that activity but I want to cross match it to something. I've tried _ListSubscribers and _Subscribers but that doesn't get me everyone I've ever contacted on each of my BU's. Is there a method like a _Contacts table or something I can use to pull them?

Answer 1

I think you need to JOIN the _Subscribers view with the _Sent view and the _SMSMessageTracking view.

• _Sent will show you the subscribers who received emails, however, this is limited to the subscribers emailed in the last 6 months (not 2 years like you asked).
• _SMSMessageTracking will give you subscribers who received SMS messages. In the documentation, I don't see any limitations on how far back you can go.

The SQL query you need would look something like this:

SELECT SubscriberID, EmailAddress
FROM _Subscribers
LEFT JOIN _Sent ON _Subscribers.SubscriberID = _Sent.SubscriberID
LEFT JOIN _SMSMessageTracking ON _Subscribers.SubscriberID = _SMSMessageTracking.SubscriberID
WHERE _Subscribers.SubscriberID = NULL OR _SMSMessageTracking.SubscriberID = NULL

You can write this SQL query in a Query Activity or a more user-friendly tool like DESelect which does not require you to have SQL knowledge.

The reasons your subscribers view may be different from what you expect could be related to the following:

• The contacts you're expecting might not be defined as a Subscriber. If they were uploaded in data extensions, a Subscriber relationship needs to be defined for that data extension.
• If you have the feature enabled described in this answer, your contacts could have been deleted from your Subscribers view because of the Data Retention Policy of the data extension.
• I'm not sure if the Contacts definition in Mobile Studio is the same as Subscribers in Email Studio.

I hope this helps.

Answer 2

You can use a Filtered Mobile List to create a list which you can query which. I would suggest following this help doc You will need to modify the filter in step 2 to get the results you desire.