2

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.

1
+50

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.

1
  • that worked! Thanks for that help. I didn't think to join the subscriber key with the email address. Really appreciate the help!
    – Realrx7
    Feb 13 '15 at 14:37
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.

8
  • The query is still not pulling back anyone. We know we sent a email to a single prospect (myself) using the [Data_Extension_Name] as our "list" at time of send. Any other thoughts as to why we can't trace this back using the email name?
    – Realrx7
    Jan 15 '15 at 14:57
  • Do you get results when you take off the where clause? Jan 15 '15 at 16:16
  • There could be a whole host of issues, including column names not matching exactly the target data extension, querying timing out after 30 minutes, using email name instead of email subject, the account structure and if the data extension is shared. More information is needed to troubleshoot.
    – mobo
    Jan 15 '15 at 18:48
  • Nothing is returning when we remove the where clause. The column names are all pulled from the ET wiki for the available tables to query off of. I've asked their support to provide any insight and of course it isn't in there "support" tier to give me feedback.
    – Realrx7
    Jan 15 '15 at 21:35
  • Has the data extension ever been the source of a send?
    – mobo
    Jan 20 '15 at 20:20

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.