Query for number of Clicks/Open in Journey Emails

Question

I am trying to bring in sql query a number of Clicks and Opens per Subscriberkey in a Journeyname, for example. So far, I did something like bellow, but I couldn't group by Subscriberkey and bring the clicks/opens.

Could anyone help me? Thanks a lot! :)

SELECT distinct
job.JobID AS JobID, 
ja.JourneyActivityObjectID AS JourneyActivityObjectID, 
s.SubscriberID AS SubscriberID, 
s.SubscriberKey AS SubscriberKey, 
sub.EmailAddress AS EmailAddress, 
sub.Status AS SubscriptionStatus, 
j.JourneyName AS JourneyName, 
job.EmailName AS EmailName,
job.EmailSubject AS EmailSubject,
job.FromName AS FromName,
job.FromEmail AS FromEmail,
s.TriggererSendDefinitionObjectID as TriggererSendDefinitionObjectID,
s.TriggeredSendCustomerKey as TriggeredSendCustomerKey,
s.EventDate AS DataEnvio,
job.DeliveredTime AS DataEntrega,
c.IsUnique

FROM _Sent s
LEFT JOIN _Job AS job
ON job.JobID = s.JobID

LEFT JOIN _JourneyActivity AS ja
ON ja.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID

LEFT JOIN _Journey AS j
ON j.VersionID = ja.VersionID

LEFT JOIN _Subscribers AS sub
ON sub.SubscriberKey = s.SubscriberKey

LEFT JOIN _Click AS c
ON c.JobID = job.JobID and IsUnique = 1

WHERE j.JourneyName = 'Journey_Name'

Answer 1

You had it half done, the part for counting clicks and opens is missing and it should be added as a separate join since you cannot group by these many fields at the same time, that is why you will end up with that -

SELECT
job.JobID, 
ja.JourneyActivityObjectID, 
s.SubscriberID, 
s.SubscriberKey, 
sub.EmailAddress, 
sub.Status AS SubscriptionStatus, 
j.JourneyName, 
job.EmailName,
job.EmailSubject,
job.FromName,
job.FromEmail,
s.TriggererSendDefinitionObjectID,
s.TriggeredSendCustomerKey,
s.EventDate AS DataEnvio,
job.DeliveredTime AS DataEntrega,
x.NumberOfClicks,
x.NumberOfOpens
FROM _Sent s
INNER JOIN _Job AS job ON job.JobID = s.JobID
INNER JOIN _JourneyActivity AS ja ON ja.JourneyActivityObjectID = s.TriggererSendDefinitionObjectID
INNER JOIN _Journey AS j ON j.VersionID = ja.VersionID
INNER JOIN _Subscribers AS sub ON sub.SubscriberID = s.SubscriberID
LEFT JOIN 
    (SELECT
    s.SubscriberKey,
    COUNT(c.SubscriberKey) AS NumberOfClicks,
    COUNT(o.SubscriberKey) AS NumberOfOpens
    FROM [_Sent] s
    INNER JOIN [_JourneyActivity] ja ON s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
    INNER JOIN [_Journey] j ON ja.VersionID = j.VersionID
    LEFT JOIN [_Click] c ON s.JobID = c.JobID AND s.ListID = c.ListID AND s.BatchID = c.BatchID AND s.SubscriberID = c.SubscriberID
    LEFT JOIN [_Open] o ON s.JobID = o.JobID AND s.ListID = o.ListID AND s.BatchID = o.BatchID AND s.SubscriberID = o.SubscriberID
    WHERE j.JourneyName = 'Journey_Name'
    GROUP BY s.SubscriberKey) x
    ON s.SubscriberKey = x.SubscriberKey
WHERE j.JourneyName = 'Journey_Name'

Points for review:

1. When you do not change the field's name, there is no need to set it again (e.g. job.JobID AS JobID);
2. It is better to have INNER JOIN here, it will strengthen the relationship between Data Views and accurately bring results;
3. If you would be pulling a lot of information from the chosen journey, consider optimizing this SQL based on this article and this one;
4. You can check more pre-created examples here (the linked one has similar logic);