Additional Fields Added to _Open Query

Question

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!

Answer 1

The data views are not directly related to your sendable data extension that you used for the send.

However, your sendable data extension has a column that is related to the subscriber key and therefore you can use that to join the data views. So you need another join to your sendable data extension to query that information.