Pulling data from data views for one business unit only using query

Question

SO, I want to pull data from data views for our business unit only in marketing cloud.

Currently, it seems like when I run a query something like:

SELECT * FROM ent._Subscribers WITH (NOLOCK)

It pulls data from ALL business units.

My next approach was to join to one of the tracking data views as it ties to the business unit but I ran a few queries and it seems like the MID# is OYBAccountID (not AccountID). Can anyone please confirm?

And by looking at the data, all records in trackable data views seem to have our MID# as OYBAccountID value (I don’t see any other OYBAccountID#). Hence, I unable to differentiate our BU data with other BUs.

Currently, I have created this query but getting no results:

select top 10 snt.Domain, count(*) AS DomainCount
from ent._Subscribers snt
JOIN ENT._Sent tst ON snt.SubscriberID = tst.SubscriberID
WHERE tst.OYBAccountID  = 'XXXXXXX'
group by snt.Domain
order by DomainCount desc

Answer 1

If what you're trying to do is select results from your child BU you're running the query in, don't use the "ENT" schema. The way to refer to the "_Sent" data view specific to the Business Unit you're running the query in is "_Sent" and not "ENT._Sent". You' don't need AccountID or OYBAccountID in your WHERE clause.

From your description of you challenge, the primary problem you have is you have a "Customers" data extension that contains what should be the Business Unit's subscribers. However, from time to time you manually upload ad-hoc "Lists" and send to these lists, so you can no longer rely on your Customers data extension as the single source of truth for who are the subscribers in your Business Unit. When you send to one of these ad-hoc lists, records are added to All Subscribers, but All Subscribers is not specific to your Business Unit. Rather, All Subscribers contains subscribers from all Business Units in the Enterprise 2.0 account.

One approach would be to treat all Subscribers you have sent to in _Sent as subscribers of your Business Unit and take these together with your Customers Data Extension records as your total population. To do this, you'll need an automation with two queries - one to amass subscribers in _Sent together with those in Customers and the second to summmarise this data. One query could do this, but these are pretty slow running queries across potentially very large data sets.

Query 1

Overwrites a table called "DistinctSubscribers" that has two fields - Email (Text 254).

SELECT
    s.EmailAddress AS Email
FROM
    _Sent snt WITH (NOLOCK) INNER JOIN
    ENT._Subscribers sub
        ON  snt.SubscriberID = sub.SubscriberID
UNION
SELECT
    Email
FROM
    Customers WITH (NOLOCK)
WHERE
    Email LIKE ('%@%.%')

This gives you a table containing one record for each email address in either Customers or _Sent.

Query 2

Overwrites a table called "DomainCounts" that has two fields - Domain (Text 254) and DomainCount (Number).

SELECT TOP 10
    RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) AS Domain,
    COUNT(1) AS DomainCount
FROM
    DistinctSubscribers
GROUP BY
    RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY COUNT(1) DESC

Answer 2

OYBAccountID is the MID of the BU. AccountID is always the Parent BU ID. These Data Views also can pull the data from all BUs. It depends upon where you are running your queries.

If you run in parent BU, OYBAccountID tells you the individual MIDs.
If you run in a child BU, you'll only get that BUs data, so OYBAccountID will be one single value only.

Also, these data views can only go back till 6 months of tracking information.