Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save marketingclouded/394ba426fca0790b20d63b84fbf0d374 to your computer and use it in GitHub Desktop.
Save marketingclouded/394ba426fca0790b20d63b84fbf0d374 to your computer and use it in GitHub Desktop.
Tracking SFMC Subscriber Behavior: Second SQL Query to Update Performance_Subscriber Data Extension with Opens and Clicks
SELECT
e.SubscriberID,
g.AllClicks,
r.AllOpens,
a.UniqueOpens,
y.UniqueClicks
FROM _Subscribers e
JOIN (
SELECT SubscriberID, COUNT(SubscriberID) AS 'AllClicks' FROM _Click GROUP BY SubscriberID
) g
ON e.SubscriberID = g.SubscriberID
JOIN (
SELECT SubscriberID, COUNT(SubscriberID) AS 'AllOpens' FROM _Open GROUP BY SubscriberID
) r
ON e.SubscriberID = r.SubscriberID
JOIN (
SELECT SubscriberID, COUNT(SubscriberID) AS 'UniqueOpens' FROM _Open WHERE IsUnique = 1 GROUP BY SubscriberID
) a
ON e.SubscriberID = a.SubscriberID
JOIN (
SELECT SubscriberID, COUNT(SubscriberID) AS 'UniqueClicks' FROM _Click WHERE IsUnique = 1 GROUP BY SubscriberID
) y
ON e.SubscriberID = y.SubscriberID
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment