2

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 send, 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:

enter image description here

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?

Thanks!

4

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)

  1. Assume that the MTA has a max of 100 emails per batch
  2. You have an audience size of 1000 emails
  3. You would have 10 batches inside your send, meaning for this jobid, you would have batchid 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 its a Welcome trigger (keeps same Job, List, etc.) and the recipient has 2 different accounts, but use an identical subscriber - then first send would be BatchID = 1 and the second send would be BatchID = 2.

  • In my context it comes from JB - but still not sure how to understand this.. it’s one subscriber, same subscriber key, and sent date is exactly the same - so why 5 different records, whit 5 different batches? – zuzannamj Sep 19 at 16:21
  • It would appear that the email was sent 5 times. The batch is there to help differentiate Trigger sends when all the other data points are identical. – Gortonington Sep 19 at 16:28
  • Ahh, so the sent time would mean “published time” in this context? Thanks!! – zuzannamj Sep 19 at 16:29
  • Sent time would be time it was sent still. It looks like it was sent 5 times at the same time. So likely if this was unintentional I would review your Journey re-entry settings and entry filters, etc. – Gortonington Sep 19 at 16:30
  • Thanks - l’ll test this scenario and see what I get! – zuzannamj Sep 19 at 16:32

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.