2

I am trying to populate a DataExtension which will contain a list of all email sends along with their send count, list name, email name and subject.

This is what I currently have and it does successfully validate:

My Code:

SELECT
TOP 50
_Job.JobID,
_Job.DeliveredTime,
_Job.EmailName,
_Job.EmailSubject,
_Sent.ListID,
_ListSubscribers.ListName,
_Job.SalesForceTotalSubscriberCount AS SendCount
FROM _Job
INNER JOIN _Sent ON (_Sent.JobID = _Job.JobID)
INNER JOIN _ListSubscribers ON (_Sent.ListID = _ListSubscribers.ListID)

I run this query activity but it fails to populate the DataExtension. Has anyone tried to get this kind of Data out of ExactTarget?

  • 1
    How is your data extension setup? What fields do you use there? – Kelly J Andrews Sep 17 '14 at 12:39
0

Your query syntax appears to be valid but there are a few things to check that can cause issues.

First one is what Kelly mentioned. Verify your data extension has correct data types and field lengths that match up with each of the fields you're pulling from their respective data views. Data view info found on the below link.

http://help.exacttarget.com/en/documentation/exacttarget/interactions/activities/query_activity/#shared

The second, more likely issue, is the performance considerations of your query. Queries have a 30 minute timeout. I would recommend placing the query activity into a program/automation so you can confirm if it's completing or showing errors. If a query is timing out, it may be possible to make the query perform faster with indexes applied. You may want to specify a date range (see below example for last 30 days) or a specific email or list ID. Hope that helps!

WHERE _Job.DeliveredTime > dateadd(day,-30,getdate())
| improve this answer | |
0

Ok, I have found why it wasn't populating.

My SQL is fine, the two joins on the _Sent and _ListSubscribers was taking a long time to complete hence why it wasn't populating.

As this was just a one off report, I create two more Data Extensions to house the List Subscriber and Sent records that I needed and then did my join on those data extensions instead. The query was able to lookup my data extensions more quickly.

| improve this answer | |

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.