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
  • 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 Jan 15, 2019 at 19:52

1 Answer 1

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.

1
  • 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, 2019 at 21:54

You must log in to answer this question.

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