How can I write a SQL query to pull a list of all recipients and their email addresses of an email in an automation?
Here's a sample query that selects the subscribers sent a specific email in the last 30 days.
select j.emailName , 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