0

I'm trying to select subscribers who haven't opened any email sent to a publication list over the last 6 months. Below is what I've tried, but it returns subscribers who didn't open from a single send, returning multiple rows with the same subscriber and different JobID's.

Select distinct
s.SubscriberKey as EmailAddress,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
left join [_open] o
on s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and 
s.SubscriberID = o.SubscriberID and o.IsUnique = 1
where
s.ListID = 5135643
and o.SubscriberID is NULL

Update: Got it to work by removing the JobID, ListID, and BatchID from the join.

Select distinct
s.SubscriberKey as EmailAddress
from [_sent] s
left join [_open] o on s.SubscriberID = o.SubscriberID
where
s.ListID = 5135643
and o.SubscriberID is NULL
1

Its a bit late, so I may be a little off, but try something like:

Select s.SubscriberKey as EmailAddress,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
WHERE NOT EXISTS(
  SELECT o.SubscriberKey
  FROM [_open] o
  WHERE o.ListID = 5135643
)
AND s.ListID = 5135643

That should give you those that were sent and used the specific listid, but did not open it (via NOT EXISTS subquery to _open)

It is a very similar query, but you could also try:

Select s.SubscriberKey as EmailAddress,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
LEFT JOIN [_open] o
ON o.SubscriberKey = s.SubscriberKey
WHERE o.ListID = 5135643
AND s.ListID = 5135643
AND o.SubscriberKey IS NULL
  • Hi @Gortonington, this didn't return any results. I'll try other variations of this, thanks for your help! – Kyle Szabo Nov 30 '18 at 18:15

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.