I am trying to get tracking information on people who did not open a specific email campaign. I was able to get it to work using the below query:
Select distinct
s.SubscriberKey,
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.JobID = 2008197
and o.SubscriberID is NULL
However, this only gives me: SubscriberKey, JobID, BatchID, and SendDate. When the campaign was sent, the DE had additional fields, EFFORT or CUST_NUM for example. Is there a way to be able to pull that information into the query as well? Each time I try, I get an error saying the field is not valid.
I found another solution where they use inner join to bring in data from the Subscriber view, but that only allows specific information to be pulled back in with it too, right?
inner join _Subscribers sub
Thanks in advance for any help with this!