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)
2
  • Which Salesforce product is this related to? Is it marketing cloud? If so, please tag it appropriately so the right people will see it. Dec 2, 2019 at 19:27
  • Can you share the specific errors you get? Dec 2, 2019 at 20:06

2 Answers 2

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
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.

4
  • Thanks Omri - I am still getting this error.
    – REZA Y
    Dec 2, 2019 at 20:52
  • An error occurred while checking the query syntax. Errors: Invalid column name 'Emailname'.
    – REZA Y
    Dec 2, 2019 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/… Dec 2, 2019 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, 2019 at 3:22

You must log in to answer this question.

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