0

I'm trying to figure out open rate over time based on domain however, it seems to error or timeout every time I run it. I've limited it to just gmail, yahoo, and AOL for now.

SELECT 
  o.domain
  , s.eventdate
  , count(o.subscriberkey) AS Open
  , count(s.subscriberkey) AS Sent 
FROM _Sent s 
LEFT JOIN _Open o ON s.jobid = o.jobid 
WHERE o.domain like '%gmail.com%' 
  OR o.domain like '%yahoo.com%' 
  OR o.domain like '%aol.com%'
Group By o.domain, s.eventdate

I would like the output to look something like this:

| Domain | EventDate | Opens | Sent   |
+--------+-----------+-------+--------+
| gmail  | 6/1/2016  | 5,000 | 20,000 |
| yahoo  | 6/1/2016  | 1,000 | 15,000 |
| aol    | 6/1/2016  | 500   | 10,000 |
| gmail  | 5/29/2016 | 6,100 | 35,000 |
| yahoo  | 5/29/2016 | 1,200 | 20,000 |
0

Is it because you're grouping by raw event date, so every single timestamp is a group?

Try convert(varchar, s.eventdate, 101) in your select. That should change the date to 'mm/dd/yyyy' format and group by the whole day.

Maybe also try putting in a date range in case the query is just timing out because the dataset is so big. (e.g. 'where s.eventdate between '2016-05-01' and '2016-06-01')

| improve this answer | |
  • It took a way shorter time to run but unfortunately, it still errored. I also limited s.eventdate >= '3/1/2016' – Hanna Jun 10 '16 at 17:50
  • any other thoughts? – Hanna Jun 16 '16 at 17:16

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.