I'm working on a feature for marketing cloud and I need to create a Data Extension with those fields : NumberOfOpenMails,NumberOfClicks and all the other fields relative to the subscriber. I found that I could do some SOQL query to find this with AutomationStudio but as I'm a beginner, I don't quite understand all. I found this example of query that retrieve subscribers with No Opens or Clicks. Here is the code used :

Select distinct
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
left join [_open] o
on s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
left join [_click] c
on s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
s.JobID = JobID
and (o.SubscriberID is NULL and c.SubscriberID is NULL)

I've understand I should change JobId to my email's Job Id but I don't quite understand why this :(o.SubscriberID is NULL and c.SubscriberID is NULL) retrieve the number 0 of clicks and emails. For my example should I do a COUNT of o.subscriberID and c.subscriberID to get my new fields?

EDIT : result of the query with OverWrite property enter image description here


Sorry doing this on the fly, but below should get you to where you need. Please keep in mind that this will be a fairly intensive query as it is hitting 3 data views (I would recommend pushing your data views into DEs and then querying them and possibly using a send log instead of the sent data view.)

  1. It takes the send audience and limits it to a specific Job ID
  2. It joins that send audience with opens and counts each time that subscriber appears inside of open (total number of oopens) - can use the IsUnique field if you want only unique opens
  3. Joins the send/open audience with the click audience and counts the subscriber for total number of clicks.


convert(char(19),s.EventDate,20) as SendDate,
    FROM [_sent] sn
    WHERE JobID = 123456
) s
    SELECT SubscriberKey, COUNT(*) as [Open], JobID
    FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
    SELECT SubscriberKey, COUNT(*) as [Click], JobID
    FROM [_Click] cl
    GROUP BY cl.SubscriberKey, cl.JobID
) c
ON s.SubscriberKey = c.SubscriberKey
WHERE s.JobID = c.JobID
AND s.JobID = o.JobID
| improve this answer | |
  • Thanks a lot for you help! However when I try to put this query in automation studio , it says : "× An error occurred while checking the query syntax. Errors: Incorrect syntax near the keyword 'Open'." I don't understand why because it seems correct SQL – Otor Jul 3 '18 at 7:23
  • 1
    The only thing I see is maybe there is no condition for left join ("ON") but maybe I missed something – Otor Jul 3 '18 at 12:26
  • I accidentally used WHERE instead of ON. Will edit. I also am going to add brackets to open and click column names to make sure it is not confused by the Open function in SQL. – Gortonington Jul 3 '18 at 13:16
  • You forgot to put the brackets also before the FROM but now it says : Errors: Incorrect syntax near the keyword 'ON'. – Otor Jul 3 '18 at 13:23
  • 1
    I found the problem ! The join was on JobId where it should be on subscriber keys – Otor Jul 4 '18 at 10:18

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.