0

I'm trying to build a DE of non opens and non clicks for two separate emails: EmailABC and EmailXYZ.. I;m getting errors when using this query.. Can you help..

Select distinct
s.SubscriberKey,
s.JobID,
s.BatchID,
s.Emailname,
convert(char(19),s.EventDate,20) as SendDate
from [_sent] s
left join [_open] o
on s.Emailname = o.Emailname and s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
left join [_click] c
on s.Emailname = o.Emailname and s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
where
s.Emailname = (EmailABC,EmailXYZ)
and (o.SubscriberID is NULL and c.SubscriberID is NULL)
  • Which Salesforce product is this related to? Is it marketing cloud? If so, please tag it appropriately so the right people will see it. – Daniel Ballinger Dec 2 '19 at 19:27
  • Can you share the specific errors you get? – Omri Brandes Dec 2 '19 at 20:06
1

Try this..

    SELECT 
    s.SubscriberKey,
    j.EmailName, 
    s.EventDate as SentDate, 
    o.EventDate as OpenDate, 
    c.EventDate as ClickDate
    FROM Recipients r LEFT JOIN _Sent s ON s.SubscriberKey = s.SubscriberKey
    LEFT JOIN _Job as j ON s.JobID = j.JobID 
    LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
    LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
    WHERE j.EmailName = 'EmailABC'
    AND o.EventDate is NULL
    AND c.EventDate is NULL
| improve this answer | |
0

I'm not sure I quiet understood what's the exact error. But I'm not familiar with the first part of your WHERE clause.

Instead of writing:

WHERE s.Emailname = (EmailABC,EmailXYZ)

Try using the IN operator:

WHERE s.Emailname IN (EmailABC,EmailXYZ)

This shall do it.

| improve this answer | |
  • Thanks Omri - I am still getting this error. – REZA Y Dec 2 '19 at 20:52
  • An error occurred while checking the query syntax. Errors: Invalid column name 'Emailname'. – REZA Y Dec 2 '19 at 20:52
  • That's because there's not such a field named "Emailname" in the _Sent, _Open, data views. Check out this article explaining about the fields in the Sent data view help.salesforce.com/… – Omri Brandes Dec 2 '19 at 21:18
  • Thanks Omri - I would like to build a DE of non opens and non clicks for a specific email "EmailName" that is a reoccurring daily trigger.. Unfortunately JOBID is for when the job runs - so I can't use that in my query..any advice?? – REZA Y Dec 3 '19 at 3:22

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.