I'm looking for a comprehensive query/script/code I can run to see if a subscriber is sendable in the Marketing Cloud. Would anyone happen to have one they would be willing to share? Something that finds your suppression lists/exclusion lists & queries them, checks to see if the subscriber is held, unsubbed, etc.? I'm not worried about mobile sends at the moment.
Is Marketing Cloud Subscriber Sendable
marketing-cloudsubscriber
Related Solutions
Thanks for sharing your point of view on this problem. I am confronted with the same discussions and the client's faces always turn white when I explain to them the different options.
To be honest I push them to option 3. I explain that SFMC is not Salesforce and the concept of a sandbox is still blurry. I generally don't get pushback. If I do, I scope for option 1 then when they see the difference in prices, they go for option 3 (with a little prayer and a lot of trusts - never got any issue with option 3 and I did a lot of implementations).
I feel that we have difficult conversations because of a product gap. SFMC should be better at managing sandbox and prod.
Updated March 2020 to accomodate for v2 authorisation and tenant specific endpoints.
Please see the example of how I use this for bulk deletion. You should use the REST API, using the DeleteByListReference endpoint. Here it works together with a sendable DE, and SSJS activity in Automation Studio:
<script runat="server">
Platform.Load("Core", "1");
//AUTHENTICATE
var url = "https://[yoursubdomain].auth.marketingcloudapis.com/v2/token";
var contentType = "application/json";
var payload =
'{"grant_type": "client_credentials","client_id": "[YOURID]","client_secret": "[YOURSECRET]","account_id":"[YOURMID]"}';
var accessTokenResult = HTTP.Post(url, contentType, payload);
var accessToken = Platform.Function.ParseJSON(accessTokenResult["Response"][0]).access_token;
if (accessToken != "") {
//EXECUTE
try {
var deleteUrl =
"https://[yoursubdomain].rest.marketingcloudapis.com/contacts/v1/contacts/actions/delete?type=listReference";
var payload1 =
'{"deleteOperationType": "ContactAndAttributes","targetList": {"listType": {"listTypeID": 3},"listKey": "[Data Extension External Key]"},"deleteListWhenCompleted": false,"deleteListContentsWhenCompleted": false}';
var headerNames = ["Authorization"];
var s1 = "Bearer ";
var headerValues = [s1.concat(accessToken)];
var result = HTTP.Post(deleteUrl, contentType, payload1, headerNames, headerValues);
} catch (ex) {
Write("Exception Error: " + Stringify(ex));
}
}
</script>
Remember to have the Data Extension created as sendable, and the only column required is the Subscriber Key. Also do ensure that Contact Deletion is enabled on your account. Here is how you do it.
Warning: When deleting contacts using the GDPR functionality you will render the SDKs unable to communicate with the Marketing Cloud servers. You need to be very, very careful to ensure that you're not deleting contacts that are still valid.
Best Answer
In short: "Being sendable" is not a property a subscriber has. How comprehensive you can get an answer to this question depends on your system setup and context.
Whether a specific email to a subscriber is sendable is always determined at send time (meaning that a record unsubscribing/ landing on an auto suppression list the very second before you send could change the outcome). If you try this with script and SQL automations, you will only be able to run these hourly, so you will never have 100% coverage.
And since context is relevant, you will not get a simple yes/ no answer to your question "is subscriber sendable?". It depends.
==
Whether an email is sent out to a particular subscriber is not just determined by the subscriber being on certain lists with a specific status (as you allude to), but also determined by other, more "situational" factors, such as exclusion script on an individual email being present or the type of send classification used (Transactional / Commercial) for a specific sendout, even the data extensions excluded in a sendout manually.
see this post for an attempt at a visualization: Email Send does not send to everyone in Data Extension, no reason for Exclusion
When we limit your question to not take into account super-specific factors such as errors in the email, exclusion script, raiseError() or individual DEs manually excluded, then you approach this topic as follows: Basically, go through the flowchart linked and try to control for each factor as best you can.
"globals" (note this is a made up category, not something official):
list detective status can to be determined via API through validateEmail ( https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/validateEmail.html), or manually by trying to import data into a list and checking for import errors.
Subscriber Status on All Subscribers can be determined through SQL or AMPScript lookups (there are ample resources about these e.g. on stackExchange). You specifically are interested in "held" or "unsubscribed" statuses applying. "held" is always global. If "unsubscribed" is really global depends on the Business Unit Settings (= whether you have to check the parent BU or the respective child BU, in the seconds case, special mention to the _businessUnitUnsubscribes Data view: https://help.salesforce.com/s/articleView?id=sf.mc_as_data_view_businessunitunsubscribes.htm&type=5 ).
Again, these apply in near-real time, and you don't ever have full control when subscribers receive these statuses, as this happens in the backend, consider e.g. list-unsubscription or bounces being reported - you can only always get an approximation if you check this, say, hourly. What matters is what the status is at send time.
You can have auto suppression configurations that apply (again: at send time) globally, which essentially can be queried the same as All Subscribers, or any Data Extension. You just need to understand how many exist in your system and apply to your context. If more are added, then also more queries need to be added or you lose visibility. So it pays to have a fixed concept for your auto-suppression configurations in mind.
"situationals":
auto suppression configurations (ASCs) do not necessarily apply globally, but can be set up dependent on the Sender Profile used. If you have a fixed concept to your Sender profiles and related auto suppressions, you can also control for this through making the relevant lookups. Or of course, if you don't have such ASCs.
Send Classifications: there are only two, commercial and transactional. The key difference is that transactional classification ignores the All Subscribers Status of "unsubscribed", and that other Auto Suppression Configurations could apply.