0

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:

Subscriber Status in Parent Account

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):

Subscriber Status in Child BU

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?

2

The status in _Subscribers in the child BU will never be accurate based on how business unit membership and the unsubscribe options are handled.

You can determine it accurately, but you have to do it from the parent BU. I've outlined my go-to method here on my blog.

Create a subscriber for each BU:

select
  s.SubscriberID
, s.SubscriberKey
, s.Status
, s.EmailAddress
, s.DateUnsubscribed
, s.DateJoined
, s.DateUndeliverable
, b.businessUnitMID
, b.businessUnitName
from _subscribers s
outer apply (
    select 1 businessUnitMID, 'Business Unit Name 1' businessUnitName union all
    select 2 businessUnitMID, 'Business Unit Name 2' businessUnitName union all
    select 3 businessUnitMID, 'Business Unit Name 3' businessUnitName union all
    select 4 businessUnitMID, 'Business Unit Name 4' businessUnitName union all
    select 5 businessUnitMID, 'Business Unit Name 5' businessUnitName
) b
/* name: subscribers_all */
/* target: subscribers_all */
/* action: overwrite */

Set unsubscribes per BU:

select
  u.BusinessUnitID businessUnitMID
, u.SubscriberKey
, u.subscriberId
, 'unsubscribed' Status
, u.UnsubDateUTC DateUnsubscribed
, case
    when u.businessUnitID = 1 then 'Business Unit Name 1'
    when u.businessUnitID = 2 then 'Business Unit Name 2'
    when u.businessUnitID = 3 then 'Business Unit Name 3'
    when u.businessUnitID = 4 then 'Business Unit Name 4'
    when u.businessUnitID = 5 then 'Business Unit Name 5'
 end businessUnitName
FROM _BusinessUnitUnsubscribes u
/* name: subscribers_all_unsubs */
/* target: subscribers_all */
/* action: update */

I'd put subscribers_all and subscribers_all_unsubs in Shared Data Extensions so you can then utilize them in the child business units.

1
  • Thank you a lot, Adam
    – Leto
    Mar 3 at 16:08

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.