0

We are using the following query to get tracking data into a staging data extension. The data we are getting is looking weird - the open and click dates have a timestamp that is earlier than the actual sent date.

Now I'm not sure whether there's a problem in the query, or in the data itself? I think for the Click, it is just pulling the same timestamp for each record, but not sure why the Open results have different values then:

`SELECT s.SubscriberKey, s.TriggererSendDefinitionObjectID, 
s.JobID, s.BatchID, s.EventDate,j.EmailName, j.DeliveredTime, 
b.Domain, b.BounceCategory, b.SMTPBounceReason, b.EventDate as BounceDate, 
o.EventDate as OpenDate,  c.EventDate as ClickDate
FROM _Sent AS s INNER JOIN _Job as j ON s.JobID = j.JobID 
INNER JOIN _Click as c ON j.JobID = c.JobID
INNER JOIN _Open as o ON c.JobID = o.JobID
LEFT JOIN _bounce AS b ON o.JobID = b.JobID`

And a screenshot of the results: enter image description here

6

You need to match the sends more precisely than just on JobID, and use a LEFT JOIN instead of INNER JOIN, like in this example form SQL Reference: Query: Find Subscribers with No Opens or Clicks

So your SQL should look more like this:

SELECT s.SubscriberKey, s.TriggererSendDefinitionObjectID, s.JobID, s.BatchID, s.EventDate, j.EmailName, j.DeliveredTime, b.Domain, b.BounceCategory, b.SMTPBounceReason, b.EventDate as BounceDate, o.EventDate as OpenDate, c.EventDate as ClickDate
FROM _Sent s INNER 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
  • Thanks a lot!!!! This works!! – julia2019 Sep 16 at 13:53
  • Does this really achieve the right output? I currently believe that there will barely be any bounces although there might be plenty. What i think the query actually does is: after first left join: Persons where an email has been sent and opened. after second left join. people that were sent to and have opened and clicked. third left join: people that were sent to and have opened and clicked and bounced. But maybe my understand is just wrong? interfacett.com/blogs/… – Johannes Schapdick Sep 17 at 6:12
  • Hi @JohannesSchapdick - I was also curious about this bit, and I reproduced that exact query in my account and everything seems to be in order. I am getting the same amount of bounces in this query, as I am getting when I query the _Bounce DV alone. And above query pulls results for "just bounced", without any opens and clicks. But I will be happy to correct this query if you have any suggestions, seems a lot of people have been asking for this on SFSE lately. – zuzannamj Sep 17 at 7:46
  • What i usually do in this case is writing three queries that only have one inner join. Sent and Click, Sent and Open, Sent and Bounce. The first query overwrites the dataextension, the second and third only update. I am just curious if your version is a faster way to achieve the same. Might be good if someone with even more proficient sql knowledge can answer ;) – Johannes Schapdick Sep 17 at 7:53

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.