3

We're trying to find the number of subscribers who are marked as "ACTIVE" in a Data Extension. Seems simple enough, right?

Here's our SQL

SELECT a.Email 
From [Newsletter List] a
JOIN _ListSubscribers b ON a.email = b.subscriberkey
WHERE Status = 'active'

But, when we run it the number is WAY too low (700K out of 1.1MM).
I know that's low because we sent email to 1MM names just this week.

The DE is part of a child BU. No email is sent from the parent BU.
Does this impact the query? What are we missing here?

6
  • Was this an uploaded list? Are you able to verify that you didn't bounce ~400K emails? Commented Oct 18, 2017 at 17:48
  • It's a Data Extension, and I can confirm that 400K didn't bounce -- just a few days ago we sent 1MM+ emails and the delivery rate was about 99% Commented Oct 18, 2017 at 18:06
  • Did you also check against the number of mails that were actually sent out? The marketing cloud has a filter mechanism in the background (not documented anywhere) that causes certain addresses not being sent to. Those won't count as bounces and therefore not appear in the system views. We had cases with customers, where a very substantial part of a list was affected. Generic addresses like admin@, info@, postmaster@ are some examples that are ruled out for a send without a support engagement. Commented Oct 18, 2017 at 18:24
  • I'm familiar with the role addresses being filtered at times... however, the list is for a quick-serve food company, so it's unlikely that there are 35%+ role addresses. Commented Oct 18, 2017 at 18:27
  • This is a fun one. My guess: Use _Subscribers instead of _ListSubscribers - I'm surprised duplicates didn't error your query. And it doesn't necessarily give a reason for the major count difference, but you can't properly query the All Subscriber status of a subscriber within a child business unit - it will only be able to reference the Parent business unit All Subscribers, which may not be useful in your case. Commented Oct 18, 2017 at 21:33

2 Answers 2

1

If you use _ListSubscribers, you should specify the ListID by using WHERE ListId = “123” in the query. Otherwise use _Subscribers data view. The ListID can be found in the Properties tab in All Subscribers or your subscriber list.

1
  • 1
    You'd want to use ent._Subscribers is you're not in the parent business unit. Commented Apr 14, 2019 at 20:49
1

So you could do this non-programmatically with a plugin like DESelect and then build something that would look like this: enter image description here

If you do want to write a query, I would indeed go with _Subscribers (_ListSubscribers is if you're using Lists instead of DEs and using that OOTB SFMC subscription center).

So:

SELECT a.Email 
From [Newsletter List] a
JOIN _Subscribers b ON a.email = b.subscriberkey
WHERE Status = 'active'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .