Tracking Email Performance in SFMC: Updates Email_Performance_Sched w Opens, Clicks, Bounces, Unsubscribes

Updates Email_Performance_Sched w Opens, Clicks, Bounces, Unsubscribes

SELECT
  e.JobID,
  g.Clicks,
  r.Opens,
  a.Bounces,
  y.Unsubscribes
FROM Email_Performance_Sched e
JOIN (
  SELECT JobID, COUNT(JobID) AS Clicks FROM _Click WHERE IsUnique = 1 GROUP BY JobID
) g
ON e.JobID = g.JobID
JOIN (
  SELECT JobID, COUNT(JobID) AS Opens FROM _Open WHERE IsUnique = 1 GROUP BY JobID
) r
ON e.JobID = r.JobID
JOIN (
  SELECT JobID, COUNT(JobID) AS Bounces FROM _Bounce WHERE IsUnique = 1 GROUP BY JobID
) a
ON e.JobID = a.JobID
JOIN (
  SELECT JobID, COUNT(JobID) AS Unsubscribes FROM _Unsubscribe WHERE IsUnique = 1 GROUP BY JobID
) y
ON e.JobID = y.JobID