0

I know this code is going to be slightly jacked up... but here it goes...

I need a list of people who unsubscribed from a specific MID during 10/1/19 through 3/31/20

Select
    l.SubscriberKey,
    l.EmailAddress,
    l.ListID,
    l.ListName,
    l.Status,
    l.DateUnsubscribed as 'UnsubscribedDate'
    from [_ListSubscribers] l
    where
    l.Status = 'Unsubscribed'
    and
    MID ='123456' // I KNOW THIS IS BAD???
    and 
    DateUnsubscribed between '2019-10-01' and '2020-3-31'
  • 2
    There is no ‘MID’ attribute in the list subscribers data view - why not query the BusinessUnitUnsubscribes data view from Parent instead? You can then filter it by BusinessUnitID and join with any other dv if needed. – zuzannamj Aug 1 at 6:58
0

Confirm your Business Unit Unsubscription Settings. They should be set to "Unsubscribe from this BU only". This is assumed in the following points.

a) If you are looking for people that are currently set to Status "unsubscribed" because of an unsubscription in a certain timeframe:

You can run a basic

"SELECT fields of your choice 
FROM _Subscribers 
WHERE status = "unsubscribed" 
and 
DateUnsubscribed between '2019-10-01' and '2020-3-31'"

query on _Subscribers inside the BU in question. It will only return people with an Unsubscribe Status for the BU that the query runs in.

For this usecase, you can also make use of the _BusinessUnitUnsubscribes data view in Parent and have the BusinessUnitID as a filter criterion. That way you don't need to run the same query multiple times. You do NOT have to filter for status here, as only people with a status of "unsubscribed for this BU" make it to the Data view to begin with. If they resubscribed in the meantime, they won't show. Which leads us to b:

b) If you are looking for unsubscriptions that occurred (events) during a certain timeframe, and it does NOT matter whether the relevant recipient still has the status = "unsubscribed" now (e.g. due to resubscribe in the meantime, bounce in the meantime):

In this case, the _Unsubscribe Data View is Business Unit specific. This means: You can simply run a query against _Unsubscribe without "MID = 12345" (which is not a valid field) and the result will be all Unsubscribe Events in the BU the query runs in in the timeframe of your choice.

Bottom line: be sure that you go down the right path a) or b): ask yourself whether you need UnsubscrIBERS (people) or UnsubSCRIPTIONs (Events). Then just run the query in the right BU, or possibly make life easier in option a, with _BusinessUnitUnsubscribes.

| improve this answer | |

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.