1

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?

2 Answers 2

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.

1
  • Thank so much for the suggestion, I think this helps answer the question in some aspect but unfortunately I still need the entire history of my BU's, and that's really what i'm looking to get. A full historical extract of my active contacts so that i can match it to a list of people that i have mailed to in the past 2 years. The remaining is what will be deleted. Commented Sep 3, 2019 at 15:12
0

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.

You must log in to answer this question.

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