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?
- _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.
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.