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 s.SubscriberKey, s.JobID, s.BatchID, 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 where 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?