0

We want to get a Data Extension with only de emails of subscribers that opened certain JobIDs. I ran this SQL Activity but the targeted Data Extension shows 0 records after it finished running.

What could be wrong?

SELECT DISTINCT s.SubscriberKey 
FROM _Sent s

WHERE

s.JobID = 222861 OR 
s.JobID = 222863 OR 
s.JobID = 222865 OR 
s.JobID = 222868 OR 
s.JobID = 222869 OR 
s.JobID = 222870 OR 
s.JobID = 222871 OR 
s.JobID = 222872 OR 
s.JobID = 222875 OR 
s.JobID = 222878 OR 
s.JobID = 222889 OR 
s.JobID = 222891 OR 
s.JobID = 222893 OR 
s.JobID = 222894 OR 
s.JobID = 222895 OR 
s.JobID = 222896 OR 
s.JobID = 222898 OR 
s.JobID = 222899 OR 
s.JobID = 223022 OR 
s.JobID = 223023 OR 
s.JobID = 223024 OR 
s.JobID = 223025 OR 
s.JobID = 223026 OR 
s.JobID = 223027

and s.SubscriberKey IN ( 
SELECT o.SubscriberKey 
FROM _Open o
WHERE o.JobID = s.JobID 
AND 
o.SubscriberKey = s.SubscriberKey 
)
  • 1
    @AdamSpriggs answer is correct, but likely the issue on your original query was all the OR statements followed by an AND. You would likely need to nest them like: (s.JobID = .... OR s.JobID = ..) AND s.SubscriberKey IN( to provide logic as you initially intended. techonthenet.com/sql/and_or.php – Gortonington Jan 15 at 19:52
2

No need to query the _Sent data view. Since the JobID is part of the _Open data view, you can simplify it like this:

SELECT distinct
o.SubscriberKey
from _Open o
where o.jobid in (
  222861
, 222863
, 222865
, 222868
, 222869
, 222870
, 222871
, 222872
, 222875
, 222878
, 222889
, 222891
, 222893
, 222894
, 222895
, 222896
, 222898
, 222899
, 223022
, 223023
, 223024
, 223025
, 223026
, 223027
)
and o.isunique = 1

The o.isUnique = 1 effectively collapses multiple opens into one row per subscriber.

  • Hi this worked great! The thing with the previous code is that it takes more than an hour and a half to finish running. An automation query will time-out after 30 minutes if it has not completed. As I was joining two system data views this causes the system to sort through huge amounts of data. Thanks a lot for your contribution! – Marchelo Jan 15 at 21:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.