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? – Adam Spriggs Apr 17 '17 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 '17 at 17:13

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.