I'm trying to figure out if it's possible to build a SQL query that will show count of distinct sends/clicks/opens grouping by an attribute in the associated data extension. I'm struggling to find the join criteria between data extension & _sent / _click / _Open tables.

Can it be done? If so, how?

1 Answer 1


I'm assuming you want to counts of a recent send. You can join on the JobId on _Sent, _Click, and _Open data views. The JobId can be found in the Tracking of recent sends. You can also join on SubscriberKey, but not all records will exist in Open or Click data views.

SELECT COUNT (DISTINCT SubscriberKey) AS Number_Of_Records
FROM _Sent s
INNER JOIN _Open o ON s.JobId = o.JobId
INNER JOIN _Click c ON o.JobID = c.JobId
WHERE s.JobId = '12345'
  • Thanks - the JobID could be helpful here (however I'm not seeing it in my data extension). How can I join from my data extension to the 'tracking of recent sends'? As for SubscriberKey, that won't help as the same subscriber would exist in multiple data extensions. Commented Jun 24, 2019 at 20:53
  • Are you sending to the same sendable data extension across multiple sends? You can grab the JobId from Email Studio > Email > Tracking > Sends. And then build a where clause of all the JobIds where you use that sendable data extension. Commented Jun 24, 2019 at 20:56

You must log in to answer this question.

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