0

I´m having trouble to query Opened emails from a Journey accurately. I want to keep a Data Extension with all the openings from the journey <'Homem_Casado_30_55'>, version number=2.

So this is my query:

select 
  o.SubscriberKey as 'EMAIL'
, o.eventdate as 'Data_abertura'
, o.jobid
, o.ListID
, o.BatchID
, o.TriggererSendDefinitionObjectID
, j.EmailName
, j.EmailSubject
, j.DeliveredTime
, j2.JourneyName
, j2.VersionNumber
, j2.JourneyStatus
from _Open o 
join _Job j on o.jobid = j.jobid
join _JourneyActivity j1 on j1.JourneyActivityObjectID = o.TriggererSendDefinitionObjectID 
join _Journey j2 on j2.VersionID = j1.VersionID
where j2.JourneyName = 'Jornada Homem 30 a 55 anos' and j2.VersionNumber = 2

The problem is that I´m getting more data than expected, with some duplicated rows. There is even a case with different opening dates with the same "TriggererSendDefinitionObjectID"! Is it possible?

I also need to make the same process for _clicks, but I´m having the same issue

Any Help?

1 Answer 1

1

You have to specify if you only want unique opens -- otherwise, _Opens reflects every open of an email by a subscriber:

select 
  o.SubscriberKey as 'EMAIL'
, o.eventdate as 'Data_abertura'
, o.jobid
, o.ListID
, o.BatchID
, o.TriggererSendDefinitionObjectID
, j.EmailName
, j.EmailSubject
, j.DeliveredTime
, j2.JourneyName
, j2.VersionNumber
, j2.JourneyStatus
from _Open o 
join _Job j on o.jobid = j.jobid
join _JourneyActivity j1 on j1.JourneyActivityObjectID = o.TriggererSendDefinitionObjectID 
join _Journey j2 on j2.VersionID = j1.VersionID
where j2.JourneyName = 'Jornada Homem 30 a 55 anos' and j2.VersionNumber = 2
and o.isunique = 1

if you wanted to deduplicate it by subscriberkey + jobid + emailname:

select top 1 with ties
  o.SubscriberKey as 'EMAIL'
, o.eventdate as 'Data_abertura'
, o.jobid
, o.ListID
, o.BatchID
, o.TriggererSendDefinitionObjectID
, j.EmailName
, j.EmailSubject
, j.DeliveredTime
, j2.JourneyName
, j2.VersionNumber
, j2.JourneyStatus
from _Open o 
join _Job j on o.jobid = j.jobid
join _JourneyActivity j1 on j1.JourneyActivityObjectID = o.TriggererSendDefinitionObjectID 
join _Journey j2 on j2.VersionID = j1.VersionID
where j2.JourneyName = 'Jornada Homem 30 a 55 anos' and j2.VersionNumber = 2
and o.isunique = 1
order by row_number() over (partition by o.subscriberkey, o.jobid, o.EmailName order by o.eventDate)

The row_number() over (partition by will number rows from 1 to x for each unique combination of the proceeding fields, sorted by the order by clause. The top 1 with ties gives you all the rows that where row_number() is 1.

3
  • Thank you Adam! It worked fine...So just to make sure I´m understanding the concept here: If I receive the email and open it the first time, then later on I open that email to read it again, the view _Opens counts 2 times for me? Another issue with this query is that I´m trying to bring the column j.DeliveredTime to compare how much time on average the recipients are taking to read the email, but the column results are blank. How is this possible? The join between _Opens and _Job should have the column DeliveredTime completed filled right? How can an email be opened without being delivered? Apr 10, 2021 at 0:23
  • Yes, on your first question. In my opinion, you shouldn't ever use the dates in _job as they are misleading for triggered (and journey) sends -- they're always the date of last publish, not the send. The sent/open/click eventDate is the actual date the event occurred. Apr 10, 2021 at 11:27
  • Great! Thank you so much Adam! Apr 10, 2021 at 17:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .