We have queried _Sent and _Open data views to get the openers / non-openers for the last 30 days. In the second step, we would like to inner join it with _Job data view to get emailname and emailid. However, we would also like to group by the end results on how many times a specific subscriber opened an email/job.

The fields we have in the resulting Data Extension are:

  • emailname(text, _Job)
  • Customer_ID(INT)
  • emailid(_Job, INT)
  • OpenDate (date)
  • SendDate (date)
  • EmailAddress(email)
  • JobID(INT)
  • SubscriberKey (text)

I guess we don't need all of those, just the Customer_ID, EmailName and how many times an email has been opened. Here's the (incomplete) code:

COUNT(j.EmailName) AS EmailName 
FROM Tracking_Open_All AS o 
INNER JOIN _Job AS j ON o.JobID = j.JobID 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.