How can I write a SQL query to pull a list of all recipients and their email addresses of an email in an automation?

  • Are you familiar with the data views? Apr 17, 2017 at 15:35
  • @AdamSpriggs Semi-familiar. I've been looking into it and seems to be that I would need to know the email's JobID to pull the _sent information. I'm just looking for email address and since the email is an automation there are hundreds of JobIDs... Is there an alternative identifier to use for the email as a whole instead of JobID? Or am I able to write a query to pull all email addresses sent to over time in the data extension (set to overwrite in the query) I used for the automation?
    – Kelsey
    Apr 17, 2017 at 17:13

1 Answer 1


Here's a sample query that selects the subscribers sent a specific email in the last 30 days.

, j.EmailSubject
, s.SubscriberKey
, sub.emailAddress
, s.EventDate
from _job as j
inner join _sent as s on (s.jobid = j.jobid)
inner join ent._subscribers as sub on (sub.subscriberid = s.subscriberid)
where j.pickuptime >= dateadd(day,-30,getdate()) 
and j.emailName = 'NAME OF EMAIL HERE'

Keep in mind that these data views only hold 6 months of data and are not very performant. If you have a high volume of sends, this type of query may exceed the 30 minute timeout threshold.

If you have an Enterprise 1.0 account, you can drop the ent. prefix on _subscribers.


You must log in to answer this question.

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