I would like to extract the reason (unsubscribed Reason) attribute as part of my SQL query to one of the data views (_Subscribers), but it shows as invalid column which is right but i need this badly ;),

Does anyone have an idea if we can extract reason value from any of the data views? my aim is to extract delta customers /subscribers who have opted out using list-subscribe feature (Reason=Unsubscribed by Salesforce Marketing Cloud RMM)

Sample query:


Subscriberkey as Subskey, 
EmailAddress, DateUnsubscribed, 
SubscriberID, **Reason**

from ent._Subscribers 

where SubscriberKey ='0031X…’'

Would be huge help!!



You can try it in the BusinessUnitUnsubscribes data view. Note it is located in Parent ( so add ENT. prefix when querying from inside a child BU):

SELECT SubscriberKey, UnsubReason FROM 
WHERE BusinessUnitID = yourMID

Result value for UnsubReason through List unsub / RMM is:

"Unsubscribed by Salesforce Marketing Cloud RMM service based on subscriber Leave/Reply email."

full documentation: https://help.salesforce.com/articleView?id=mc_as_data_view_businessunitunsubscribes.htm&type=5

  • Hi @Jonas Lamberty I tried your approach as well with ent. prefixed as specified but it returns 0 rows to me. I am executing this query from my child business unit with child mid. – mohit chhabra Mar 12 '20 at 18:58
  • Check your BU settings in Admin; Is the unsubscription set to "from this BU only"? If no, then this would be expected behavior, because then you do not have a BU level unsubscription. You could try it from Parent without ENT. Prefix and without the WHERE clause, but I am not 100% sure if that will yield a result. Let me know. – Jonas Lamberty Mar 12 '20 at 19:34
  • To use the ENT Prefix the documentation says the following: "First, ensure that the Sharing tab information in Contact Builder is set up." Not exactly sure myself what that is supposed to mean tho. – DonL Mar 13 '20 at 7:23
  • Hi @JonasLamberty tried from parent BU but didn't work , still returns 0 rows. ;(, any other alternative you may think of , appreciate ur support. – mohit chhabra Mar 13 '20 at 9:09

from my perspective the list of UnsubReason values when querying _BusinessUnitUnsubscribes can be also helpful:

  • Spam Complaint
  • Unsubscribed from list details on subscriber properties dialog.
  • Unsubscribed by ExactTarget RMM service based on subscriber Leave/Reply email.
  • Unsubscribed by Salesforce Marketing Cloud RMM service based on subscriber Leave/Reply email.
  • Unsubscribed by subscriber edit API call.
  • Unsubscribed from the Subscription Center
  • Unsubscribed via Import

(those are the values that I've found, maybe there are others)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.