I'm trying to find the Subscriber Status (Email) and insert into a Data Extension so SFMC users can have better segmentation options. I'm perfoming a query (in a child BU) to get the status and insert in the DE. The problem is that I have found some inconsistencies with the results
I can't find most subscribers in _Subscriber dataview, and when I look in the parent account dataview (ent._Subscriber) the subscriber has an active status, eventhough they are Unsubscribed
When I look in Parent Account All Subscriber UI, I see this status:
But when I look in Child Account All Subscriber UI, the subscriber has a different status (And I can't find this status querying the _Subscriber dataview):
I tried to find unsubscribe infomration by performing a query in the Child BU, looking in Data Views _Unsubscribe, _BusinessUnitUnsubscribes and _Subscribers (both in Child BU and using the ent. prefix).
SELECT econ.User_ID, uns.EventDate as unsEventDate, entuns.SubscriberKey as entunsSubscriberKey, ent.unsEventDate as entunsEventDate, buns.UnsubDateUTC as bunsUnsubDateUTC, entbuns.entbunsUnsubDateUTC as entbunsUnsubDateUTC, entsubs.status as entsubsstatus, subs.status as subsstatus FROM [econ] econ LEFT JOIN _unsubscribe uns ON econ.user_ID = uns.SubscriberKey LEFT JOIN ent._unsubscribe entuns ON econ.user_ID = entuns.SubscriberKey LEFT JOIN _BusinessUnitUnsubscribes buns ON econ.user_ID = buns.SubscriberKey LEFT JOIN ent._BusinessUnitUnsubscribes entbuns ON econ.user_ID = entbuns.SubscriberKey LEFT JOIN _subscribers subs ON econ.user_ID = subs.SubscriberKey LEFT JOIN ent._subscribers entsubs ON econ.user_ID = entsubs.SubscriberKey where user_ID = '5a72fb604cedfd000f1eabee'
This results in:
User_ID -> 5a72fb604cedfd000f1eabee
_Unsubscribe.eventdate -> 27/01/2021 06:01
ent._Unsubscribe.eventdate -> 27/01/2021 06:01
_BusinessUnitUnsubscribes.unsubdateutc -> 27/01/2021 12:01
ent._BusinessUnitUnsubscribes.unsubdateutc -> 27/01/2021 12:01
_Subscribers.Status -> null
ent._Subscribers.Status -> active
If I try to send an email to this Subscriber (in this Child BU), it will not be sent.
I have an unsubcribe information and I can set the status as Unsubscried, but I can't find the current status of the Subscriber (as it is in the Child BU UI), so if the subscriber becomes active again, I won't be able to find this.
There is an away to get the current Subscriber Status in the Child BU?