0

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:

SELECT 
o.Customer_ID, 
COUNT(j.EmailName) AS EmailName 
FROM Tracking_Open_All AS o 
INNER JOIN _Job AS j ON o.JobID = j.JobID 
GROUP BY o.CIP

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.