0

I am trying to pull list of emails for a particular domain (which is aproblematic domain) and somehow my results does not make a sense--meaning that I'm getting way less than expected. To further test, I plugged in one JOBID that I know has 5 emails that were for this domain but only one is showing up.

select --distinct  
       w.domain
      , w.Month_no
      , w.Month_name
      , w.Year_no
      , w.jobID
      , w.id_email
      , w.EmailAddress
      , case when w.id_email_sent   is not null then 'Y' else 'N' end as flag_sent
      , case when w.id_email_bounce is not null then 'Y' else 'N' end as flag_bounce
      , case when w.id_email_open   is not null then 'Y' else 'N' end as flag_open
from(select  
          se.subscriberkey    as id_email_sent 
          , b.subscriberkey   as id_email_bounce  
          , o.subscriberkey   as id_email_open
          , se.jobID  
          , DATEPART(YEAR, se.EventDate)  as Year_no 
          , DATEPART(MONTH, se.EventDate) as Month_no 
          , DATENAME(MONTH, se.EventDate) as Month_name
          , se.domain
          , u.EmailAddress
          , u.subscriberkey as id_email 
    from ent._SENT se  
    join ent._SUBSCRIBERS u on u.subscriberid  = se.subscriberid  
    left join ent._BOUNCE b on b.subscriberid  = se.subscriberid  
                           and b.jobid         = se.jobid  
                           and b.batchid       = se.batchid 
                           and b.listid        = se.listid   
    left join ent._OPEN o   on o.subscriberid  = se.subscriberid 
                           and o.jobid         = se.jobid  
                           and o.batchid       = se.batchid 
                           and o.listid        = se.listid   
   where u.domain = 'domain.com'
     and se.jobID = 22725940
 )w 
where 1=1 

Target DE Fields

Target DE Fields:

3
  • Why are you using the ent prefix for sent, bounce and open? And why the where clause at the end?
    – zuzannamj
    Apr 26, 2023 at 19:00
  • @zuzannamj - My initial goal was to query entire enterprise not specific business unit that is why I have the ENT. prefix. And, I'm running this on child business unit not in the parent. As for the WHERE at the end, it is just a place holder but should not make any difference--in fact, all my other codes have that.
    – Araarso
    Apr 26, 2023 at 19:07
  • 1
    @zuzannamj -- Figured out. It was because I made my DOMAIN field Primary Key in the DE. I just cannot believe that I overlooked this. I made the PK: Domain, emailAddress, Subscriberkey (id_email) and worked fine
    – Araarso
    Apr 26, 2023 at 20:43

1 Answer 1

0

DOMAIN field was my Primary Key in the DE. I made these fields as PK: Domain, emailAddress, Subscriberkey (id_email) and everything worked well after making these changes.

You must log in to answer this question.

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