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.

Attribution to: Michelle

Possible Suggestion/Solution #1

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.

Attribution to: Adam Spriggs

Possible Suggestion/Solution #2

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

Attribution to: Timothy
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/30734