0

I have a SQL query that needs to count the sent, Opened, Clicked and Bounced of a certain emails. I try to join this with our sendlog because there is all the additional information (the X1, X2, X3, X4 and X5 in my example query). In the target DE I neatly see all email variants and the sent, opened, clicked and Bounced fields are fild. However, all fields now have the same value. So for example:

Sent:100
Opened :100
Clicked :100
Bounced:100

I this because I do only a j.JobID = sl.JobID on the sendlog?

Here is my sample query:

    SELECT
    j.EmailName,
    j.TriggererSendDefinitionObjectID,
    ja.JourneyActivityObjectID,
    sl.X1,
    sl.X2,
    sl.X3,
    sl.X4,
    sl.X5,
    COUNT(s.JobID) AS Sent,
    COUNT(o.JobID) AS Opened,
    COUNT(c.JobID) AS Clicked,
    COUNT(b.JobID) AS Bounced
    FROM _Job j
    INNER JOIN _Sent s ON j.JobID = s.JobID 
    INNER 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
    INNER 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
    INNER 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
    INNER JOIN _JourneyActivity ja ON j.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
    INNER JOIN SendLog sl ON j.JobID = sl.JobID
    WHERE sl.X1 IS NOT NULL
    and s.EventDate > dateadd(dd,-1,getdate())
    GROUP BY 
    j.EmailName, 
    j.TriggererSendDefinitionObjectID, 
    ja.JourneyActivityObjectID, 
    sl.X1,
    sl.X2,
    sl.X3,
    sl.X4,
    sl.X5
1
  • remember to mark my answer as accepted, if it has been helpful Jan 9, 2023 at 15:06

1 Answer 1

1

This is because you are doing an INNER JOIN - this will only return records if the combination of SubscriberID , JobID, ListID, BatchID exists across all of the data views you are joining. You should use LEFT JOIN instead, and ensure you are joining with _Job on the left side of the JOIN statement, i.e.: LEFT JOIN _Open o ON j.JobID = o.JobID...

This way, you will return the rows which do exist in _Job but not necessarilly in _Open, and similar for the other tables/data views you are adding to your query.

2
  • Beat me to it lol. Dec 21, 2022 at 15:49
  • 1
    By a fraction of a minute I assume, @Gortonington . But thanks for drawing my attention to the answer, since I realized I wrote INNER instead of LEFT in my suggested query. Dec 21, 2022 at 15:57

You must log in to answer this question.

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