How to pull down subscribers that received an email by the name of the send

Question

We are trying to setup a query that will return all associated emails that received the email based on the email name. We cannot use the JobID because we want to add this to a program and not have to update the JobID for each time the email is sent. We are currently using the query below and nothing is returning. I've referenced just about every table on the ET side, but wasn't sure if you guys could point me in another direction. Thanks

We sent 'Email_Name' to a subscriber on [Data_Extension_Name] and want to pull that back based off the criteria below.

 Select A.EmailName, C.SubscriberKey, C.EmailAddress
 FROM _Job A
 INNER JOIN _Sent B
 ON A.AccountID = B.AccountID
 INNER JOIN [Data_Extension_Name] C
 ON B.SubscriberKey = C.SubscriberKey
 WHERE A.EmailName = 'Email_Name'

We receive this error when we run the query: Error 245 at line 2 of (NULL): Conversion failed when converting the nvarchar value 'Touch 1- Intro to KS' to data type int.

Answer 1

Depending on how you set up the query, I've seen issues when you are trying to Update a Data Extension that has no Primary Key. I would attempt to do an overwrite and also try the below. I would also make sure that the target Data Extension is using a Text data type (100 length) for the EmailName as that is how it is setup on the _Job data view

Also, depending on how your account is setup, you may need to use "Email" = "Subscriber Key" when joining the _Sent Data View and your Data Extension. That may be something you can test as well.

Select A.EmailName, C.SubscriberKey, C.EmailAddress
 FROM _Job A
 INNER JOIN _Sent B
ON A.JobID = B.JobID
INNER JOIN [Data_Extension_Name] C 
ON B.SubscriberKey = C.EmailAddress
 WHERE A.EmailName = 'Email_Name'

Let me know if that gives you any results or if you still show errors on the query.

Answer 2

Try

SELECT A.EmailName, C.SubscriberKey, C.EmailAddress
 FROM _Job A 
 INNER JOIN _Sent B  ON A.JobID = B.JobID
 INNER JOIN [Data_Extension_Name] C ON B.SubscriberKey = C.SubscriberKey
 WHERE A.EmailName = 'Email_Name'

AccountID is your MID. Currently, you are joining ever single send event in your account to every single job.