2

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:

SELECT 

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

from ent._Subscribers 

where SubscriberKey ='0031X…’'

Would be huge help!!

Thanks

0

3 Answers 3

5

The _BusinessUnitUnsubscribes Data View is a very convenient source for obtaining the unsubscribe reason, but records will only appear in this data view for BUs that have the 'Subscribers will be unsubscribed from this business unit only' option enabled in Business Unit setup.

As an alternative, you can extract the unsubscribe reason from the unsubscribe tracking extract.

Create a new Data Extract Activity, with a 'tracking extract' type and ensure you select both of the following options when configuring the activity:

  • Extract Unsubs
  • Include Unsub Reason

Then configure the subsequent automation activities as required for your use case (e.g. File Transfer to move the Data Extract from the Safehouse), File Import (to DE), etc.

A field named 'UnsubReason' will be appended to the exported Unsubs.csv data extract file with the Unsubscribe reason.

2

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 
ENT._BusinessUnitUnsubscribes
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

4
  • 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. Mar 12, 2020 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. Mar 12, 2020 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, 2020 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. Mar 13, 2020 at 9:09
1

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)

You must log in to answer this question.

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