0

I'm trying to build an SQL query to get the Open Rate Percentage by domain for an email send through Journey build or scheduled send. Below is the query I have trying to figure out, but it's giving me both sent and open the same number count, not sure where I'm making mistake. If anyone could help me out with this!

   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
1

I think the issue was that you were using the open domain field instead of the sent domain field. This means that (due to your conditional) only those that are in the Open DV will be counted and since you need to be sent to in order to open, then the numbers will be identical.

Another thing I would look at is converting eventdate to date only (removing time) or this could greatly increase your results as although a job may be sent at a single time, each sent record will be sent at different times (minutes, seconds, etc.)

Try something like:

SELECT
  s.Domain
  , CONVERT(date, s.eventdate) as eventDate
  , COUNT(o.SubscriberKey) as [Open]
  , COUNT(s.SubscriberKey) as [Sent]
  FROM [_Sent] s
  LEFT JOIN [_Open] o
  ON s.jobid = o.jobid
  WHERE 
     s.Domain like '%gmail.com%' 
  OR s.Domain like '%yahoo.com%' 
  OR s.Domain like '%aol.com%'
  GROUP BY s.Domain, CONVERT(date, s.eventdate)

This query returned correct results for me when I ran it.

3
  • Hi @Gortonington - Thanks for replying, I've tried your query and trying to run it in query builder for a specific Job Id, it's still giving me the same number for both sent and open. I'm not sure how to attach the screenshot here, but it still not working.
    – darkCloud
    Jun 3 at 14:31
  • Also, @Gortonington - I'm was researching how to get Open Rate % through SQL, some formula we could do? I'm have not come across to any formula as of now.
    – darkCloud
    Jun 3 at 14:34
  • Is your added where statement for jobid using the sent dv field? If you do it on open jobid it will only grab those that exist in open dv Jun 3 at 18:30

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.