0

I'm trying to return the results of subscribers who have been sent emails, when they've been sent, if they've opened/clicked and whether or not is house bounced/they've unsubscribed.

I'm using the following code which is working but it is creating duplicate rows rather than grouping by subscriber key and jobID;

SELECT
s.SubscriberKey, 
s.JobId, 
s.EventDate,
j.EmailName,
b.Bounces,
o.[Opens],
c.Clicks, 
u.Unsubscribed
FROM (SELECT 
    snt.SubscriberKey, 
    snt.JobId,
    snt.EventDate
 FROM _Sent snt
 GROUP BY snt.SubscriberKey, snt.JobID, snt.EventDate
) s
LEFT JOIN (
    SELECT JobID, COUNT(*) as [Bounces], bnc.SubscriberKey
    FROM [_Bounce] bnc
    GROUP BY bnc.JobID, bnc.SubscriberKey
) b
ON s.SubscriberKey = b.SubscriberKey
LEFT JOIN (
    SELECT SubscriberKey, COUNT(*) as [Opens], JobID
    FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN (
    SELECT cl.JobID, COUNT(*) as [Clicks], cl.SubscriberKey
    FROM [_Click] cl
    GROUP BY cl.JobID, cl.SubscriberKey
) c
ON s.SubscriberKey = c.SubscriberKey
LEFT JOIN
 (SELECT un.JobID, 
  COUNT(*) AS [Unsubscribed], 
  un.SubscriberKey
  FROM [_Unsubscribe] un
  GROUP BY un.JobId, un.SubscriberKey
 ) u
ON s.SubscriberKey = u.SubscriberKey
LEFT JOIN _Job j
ON s.JobId = j.JobId

For example, this returns;

SubscriberKey JobID EventDate EmailName Bounces Opens Clicks Unsubs
001 14503 09/03/2022 Example 1 null 2 4 null
001 14503 09/03/2022 Example 1 null 1 2 null
002 14503 09/03/2022 Example 1 null 3 4 null
001 14505 08/03/2022 Example 2 null 1 0 null
001 14505 08/03/2022 Example 2 null 1 3 null

What I'm struggling to achieve is grouping the rows by the subscriber key and the Job ID.

Is anybody able to support?

Thanks in advance

1 Answer 1

1

You're applying GROUP BY inside the Inner Queries, that's the reason. I'd first gather everything raw and then apply GROUP BY in the end.

SELECT
s.SubscriberKey, 
s.JobId, 
s.EventDate,
j.EmailName,
count(b.SubscriberKey) as Bounces,
count(o.SubscriberKey) as Opens,
count(c.SubscriberKey) as Clicks, 
count(u.SubscriberKey) as Unsubscribed
FROM (SELECT 
    snt.SubscriberKey, 
    snt.JobId,
    snt.EventDate
 FROM _Sent snt
 GROUP BY snt.SubscriberKey, snt.JobID, snt.EventDate
) s
LEFT JOIN (
    SELECT JobID, bnc.SubscriberKey
    FROM [_Bounce] bnc
) b
ON s.SubscriberKey = b.SubscriberKey
LEFT JOIN (
    SELECT SubscriberKey, JobID
    FROM [_Open] op
) o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN (
    SELECT cl.JobID, cl.SubscriberKey
    FROM [_Click] cl
) c
ON s.SubscriberKey = c.SubscriberKey
LEFT JOIN
 (SELECT un.JobID, 
  un.SubscriberKey
  FROM [_Unsubscribe] un
 ) u
ON s.SubscriberKey = u.SubscriberKey
LEFT JOIN _Job j
ON s.JobId = j.JobId
group BY
s.SubscriberKey, 
s.JobId, 
s.EventDate,
j.EmailName
1
  • Thanks! That's resolved the issue.
    – John Green
    Mar 10, 2022 at 8:02

You must log in to answer this question.

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