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?

  • Was this an uploaded list? Are you able to verify that you didn't bounce ~400K emails? – Tom Callahan Oct 18 '17 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% – George Rosedale Oct 18 '17 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. – Stephan de Paly Oct 18 '17 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. – George Rosedale Oct 18 '17 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. – AnonWonderer Oct 18 '17 at 21:33

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


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

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.

  • You'd want to use ent._Subscribers is you're not in the parent business unit. – Adam Spriggs Apr 14 '19 at 20:49

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.