I have a query, I want to bring in all the Sends (for particular email) and opens. But in my query it only brings the matching Opens. For example email sent to 1000 subscribers and total unique opens are 100, thus query returning 100 records. I want query should return 1000 records and then wherever subscriber matches for open it should pull in else let the field be blank.

Here is my query, (i already tried LEFT vs RIGHT join in 2nd join which is Open and send.

 s.SubscriberKey as Email,

O.Eventdate as OpenDate,
C.Eventdate as ClickDate

[_Sent] S


LEFT Join [_Open] O ON S.AccountID = O.AccountID
AND                    S.OYBAccountID = O.OYBAccountID
AND                    S.ListID = O.ListID
AND                    S.BatchID = O.BatchID
AND                    S.JOBID = O.JOBID
and S.SubscriberID = O.SubscriberID
AND                    S.SubscriberKey = O.SubscriberKey

LEFT Join [_CLICK] C ON     S.AccountID = C.AccountID
AND                    S.OYBAccountID = C.OYBAccountID
AND                    S.ListID = C.ListID
AND                    S.BatchID = C.BatchID
AND                    S.JOBID = C.JOBID
and S.SubscriberID = C.SubscriberID
AND                    S.SubscriberKey = C.SubscriberKey

WHERE j.emailname = 'onboarding_phase_1_1_v2'

(o.isunique = 1 or o.isunique is null)
(C.isunique = 1 or C.isunique is null)

cast(s.EventDate As Date) = cast('2019-06-11' as Date)
  • 1
    I am assuming 'Email' is your primary key. You will need to pull that from _Sent (s) not _Open (O) as open will not have subkeys for any non opens. E.g. s.SubscriberKey as Email – Gortonington Jun 13 '19 at 2:12
  • I already made that but it didn't work. – Sohail Jun 13 '19 at 2:52

the issue is the where clause and o.IsUnique = 1

If the subscriber doesn't have an open this would be null and not be included. It needs to be updated to and (o.isunique = 1 or o.isunique is null)

| improve this answer | |
  • Good Catch... :) Thank you. – Sohail Jun 13 '19 at 2:51
  • So after doing this.. now I can see their are various emails (subscriber keys) those are blank? – Sohail Jun 13 '19 at 3:12
  • Gortonington comment answers this issue. You are using o.subscriberkey where you should be using s.subscriberkey – EazyE Jun 13 '19 at 3:13
  • Yeah.. I think I am tried for the day :) ... U r right.. – Sohail Jun 13 '19 at 3:36
  • One last question... Here is my query...I made the chnages in query... in Updated question... THis time how do exclude the clicks those does not have the opens... In my results their are around 20 clicks those does not have associated opens....??? – Sohail Jun 13 '19 at 3:40

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.