sqldataviewsoqlsalesforce-marketing-cloud

How to count unique opens in a period of time in Marketing Cloud?

I'm currently using Salesforce Data Views to retrieve how many times a user opened an email in the last 60 days. For now, I'm getting data from every time user opened, but I want to identify unique opens. My currently code is this one:

SELECT COUNT(SubscriberKey) as Count_Opens, SubscriberKey as email
FROM ENT._Open o
WHERE JobID IN (SELECT JobID FROM _Sent s WHERE EventDate > dateadd(d,-60,getdate()))
GROUP BY SubscriberKey

For example, my results are showing that my email address is associated with 104 opens. But I want to show only my unique opens, which is 33 in the last 60 days.

I am not identifying where can I make a change to retrieve just the unique event. Can I limit to no more than just one Open Event associated to a JobID and SubscriberKey? If I can, any suggestions on how to do it?

PS: sorry for my english.

Solution

  • I think you're looking for the isUnique flag. It's described here in the _Open Data View documentation.

    SELECT 
  o.SubscriberKey as email
, COUNT(*) as Count_Opens
FROM _Open o
WHERE o.JobID IN (
    SELECT distinct
    s.JobID 
    FROM _Sent s 
    WHERE s.EventDate > convert(date, getDate()-60)
)
and o.isunique = 1
GROUP BY o.SubscriberKey

    Also, Opens are scoped to business units, so the ent. prefix shouldn't apply in your case.