0

I am trying to get the number of opens for each day (Example: April 15, April 16, April 17 and so on....)

Date Opens
Apr 15 17500
Apr 16 5000
Apr 17 200

I am using the below query, but not getting required results.

select distinct count (o.SubscriberKey), o.EventDate as date
from [_Open] o
inner join [Emails_Sent_on_April15] s
on o.JOBID= s.JOBID   and   s.BATCHID=o.BATCHID
where o.isunique = 1
group by cast (o.EventDate as date )

Could anyone help me know, how to modify the above query to get the results like in the table.

The problem is o.EventDate in select statement also has time

6
  • Have you tried casting the o.EventDate as a date in the select statement, just like you do in the group by statement?
    – zuzannamj
    Jun 11, 2021 at 12:45
  • yes, I tried this in Query Studio- getting "Field name cannot contain the character ) " error
    – user0151
    Jun 11, 2021 at 12:56
  • 1
    Have you tried cast(o.EventDate as date) AS DATE? Maybe give it a go in Automation Studio?
    – zuzannamj
    Jun 11, 2021 at 12:59
  • Yes true works in Automation Studio but does not work in Query Studio..Thanks
    – user0151
    Jun 11, 2021 at 13:05
  • 1
    While Query Studio is tempting to use due to its simplicity, it introduces additional inconsistencies -- both in parsing and functionality. I'd recommend building this as a Query Activity and running it with an Automation as @zuzannamj suggested. Jun 11, 2021 at 14:25

1 Answer 1

1

With a shift, I was able to get this to work correctly. If you have a high volume, this may certainly time out, but if you place a date range in it, should work:

select
count(o.SubscriberKey) as cnt, 
CONVERT(date,o.EventDate) as dte
from [_Open] o
WHERE EXISTS (SELECT se.SubscriberKey FROM [_Sent] se WHERE se.SubscriberKey = o.SubscriberKey)
and o.isunique = 1
group by CONVERT(date,o.EventDate)

And if you want it also by JobID to differentiate send jobs per day, you can do:

select
count(o.SubscriberKey) as cnt, 
CONVERT(date,o.EventDate) as dte,
CONVERT(nvarchar(max),o.JobID) as JobID
from [_Open] o
WHERE EXISTS (SELECT se.SubscriberKey FROM [_Sent] se WHERE se.SubscriberKey = o.SubscriberKey)
and o.isunique = 1
group by CONVERT(date,o.EventDate), CONVERT(nvarchar(max),o.JobID)

Just replace the _Sent DV (in my EXISTS statement) with your DE.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .