4

I am running a few similar queries to find the number of emails Sent/Opened/Clicked in the last 30 days. The query for Sent works fine, but the other two aren't. Not sure what the problem is. I do not get syntax errors, and it will start successfully - however, no data is populated into the data extension (whereas the data for _Sent DOES populate the data extension) -- I am also running a secondary query to check these counts, and all three of those work as expected.

Here is an example of a query that does not populate the data extension as it should - any ideas?:

SELECT 
    'Clicked in Last 30 Days' as dimension_name
    , count(*) AS dimension_count
    , j.EmailName as email_address
FROM 
    _Click p 
JOIN  
    _Job j on j.JobID = p.JobID
WHERE
    (p.EventDate BETWEEN DATEADD(day, -30, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP)
AND
    (j.SendClassification = 'Default Commercial')
GROUP BY 
    j.EmailName

--------------------EDIT-----------------------

The solution ended up being that the text field I was trying to populate was limiting to 50 characters. Updating the text field length to something like 255 works fine now.

2 Answers 2

1

I went through a local copy of this view (though its about 1 year old). I'm pretty sure j.SendClassification is not what you want in your AND clause... you want j.SendClassificationType = 'Default Commercial'.

1
  • Thanks for the tip! It seems to be working as I expected using 'SendClassification' but I will give it a try using 'SendClassificationType' instead. Timothy helped me troubleshoot this issue, and the solution ended up being that my DE was using a text field with a length of 50 characters, which was not long enough for the values I needed to populate it. I will go ahead and update my question to reflect the solution.
    – Michelle
    Commented Mar 24, 2014 at 21:31
0

I would contact SFMC support and have them tell you why the query failed. Could be a primary key violation or timeout.

If they report no errors, I'd remove the send classification clause and see if you get results.

1
  • I will give them a call and see, but just FYI - I did try it without the send classification and still no results.
    – Michelle
    Commented Mar 24, 2014 at 15:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .