About this question
Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the SQL Reference, I have been using this join:
...
FROM _Sent s LEFT JOIN _Job as j ON s.JobID = j.JobID
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
LEFT JOIN _Click c ON s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
LEFT JOIN _bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1
My results show the same email sent, with the same JobID, sent exactly at the same time, to the same subscriber, but with different batch numbers. On top of that, only the first record has an open date:
According to documentation, BatchID is "The batch ID number for any batches used in the send" - but what does it mean, really? And how should the BatchID be treated in queries?
