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).
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?