What is BatchID in tracking data views?
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?
BatchID is the 'grouping' of the email inside the batches sent to the Mail Transfer Agent for sending.
In a Bulk send environment, this means the following: (numbers are all arbitrary)
Assume that the MTA has a max of 100 emails per batch
You have an audience size of 1000 emails
You would have 10 batches inside your send, meaning for this jobid, you would have batch id of 1 through 10 inside this field.
In a Triggered or Journey Send environment the BatchID is a bit different. The BatchID is used to differentiate between sends when the JobID, ListID, EmailAddress, etc. (the usual primary keys) are all identical, it will increase the BatchID by 1.
So for instance if it's a Welcome trigger (keeps the same Job, List, etc.) and the recipient has 2 different accounts, but uses an identical subscriber - then the first send would be BatchID = 1 and the second send would be BatchID = 2.