2

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
3

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
| improve this answer | |
  • Ok so does that mean, ent includes data from ALL BUs while without ent, I will only get data for the BU I am working in? – kuml May 30 '17 at 14:06
  • Just clarifying - SELECT * FROM ent._Subscribers WITH (NOLOCK) will give different results as compared to this SELECT * FROM _Subscribers WITH (NOLOCK)? – kuml May 30 '17 at 14:07
  • 1
    Yes. "ENT" relates to the Admin/top level business unit. Your local BU, you don't need to use a schema name for... it's just "_Sent" or "_Click" etc. – Macca May 30 '17 at 14:08
  • I'd give _Subscribers a look, but I've had a bunch of issues with the local version of the _Subscribers view of late. Support investigated a case for me a while ago and reported a "known bug". It's a bit of a curious beast and the view seems a bit flakey. I'm not assuming the "known bug" would be fixed any time soon. Your approach of looking at _Sent seems more reliable. _Sent refers to sends from the BU you're running the query in. Joining to it with an inner join will give your what I think you're after - people sent to from this BU. – Macca May 30 '17 at 14:17
  • So, ... ENT._Subscribers INNER JOIN _Sent... – Macca May 30 '17 at 14:20
1

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.

| improve this answer | |
  • Hi Maneesh, when i run this query, i see data from other BU data also SELECT * FROM ent._Subscribers WITH (NOLOCK). I am running this query in our BU only which is a child BU in marketing cloud. I did try running the query without ent as suggested by Andrew above and in that case I do not get any results SELECT * FROM _Subscribers WITH (NOLOCK) – kuml May 30 '17 at 14:26
  • 1
    You'll have to use ENT with _Subscribers that All Subscribers is an enterprise level data. For other data views like Sent, Open, you can skip that – Maneesh May 30 '17 at 21:46

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.