3

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.

0

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'.

  • 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 Mar 24 '14 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.

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

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.