2

In Marketing Cloud - I'm trying to write an SQL that will pull everyone who has opened an email in the last 90 days. I'm getting results from the below SQL, but when I spot-check the data, I'm finding people who are not in All Subscribers. What's going on?

SELECT EmailAddress AS SubscriberKey FROM _subscribers sub
INNER JOIN _sent s ON sub.SubscriberID = s.SubscriberID
INNER JOIN _open o ON o.SubscriberID = sub.SubscriberID
WHERE s.EventDate > DATEADD(day, -90, GETDATE())

Improve this question
3
  • _Subscribers data view also pulls from the triggered send table (hidden from UI) that stores new subscribers sent to from those TSD that do not add to all subscribers
    – Gortonington
    Commented Oct 25, 2018 at 22:01
  • @Gortonington - thank you for the response. I thought subscribers are automatically added to all subscribers once an email is deployed to them. Is that not the case?
    – Monica D.
    Commented Oct 26, 2018 at 15:13
  • For Trigger Sends, you have the choice to add the recipient to All Subs or not. If you choose not to, they will be housed inside of a table that is separate from All Subs, but will be included inside the _Subscribers Data View.
    – Gortonington
    Commented Oct 26, 2018 at 17:12

2 Answers 2

Reset to default
1

It's strange that you'd be getting results that aren't in All Subscribers since that's what your select statement is pulling from. Is this an enterprise account? Is it possible that you're selecting subscribers that are filtered from your business unit?

Also, the way you're writing it will pull anyone that has open data and send data from the last 90 days, but not necessarily open data associated with a send from the last 90 days. Do you want to make sure that you're getting opens from sends that were sent within the last 90 days? You might want to add another qualifier in your open join, like this:

INNER JOIN _open o ON o.SubscriberID = sub.SubscriberID AND s.JobID = o.JobID

Also, Email Address is not a key value in All Subscribers - Subscriber ID and SubscriberKey are, so you could be pulling duplicates.

Improve this answer
2
  • Thank you for the response. In this case, I'm just looking to measure engagement of any kind within the last 90 days (even if it was an older send), so I believe the right action is to leave that string out - would you agree with that, or is there something I'm missing? This is the only BU in the account. Good call on the duplicates - I added a "DISTINCT" after my Select :)
    – Monica D.
    Commented Oct 25, 2018 at 21:41
  • If that's the case, then you can leave out the code suggestion.
    – Matthew Wash
    Commented Oct 25, 2018 at 21:42
3

Wanted to post an update for anyone who may stumble across this - as it turns out, the reason that I was finding records that did not exist in All Subscribers was that the client was manually deleting records from All Subscribers (unsubscribes, specifically). Their intention was to manage subscription status externally.

My recommendation to them was to not remove records from All Subscribers to allow for instant opt-out and avoid manual mistakes in honoring subscription status. Since that change has been made, this issue was resolved.

Improve this answer

You must log in to answer this question.

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