[SalesForce] Query for Opens from for an email in a Journey

Is it possible to query for open data from an email in a Journey. I've been having trouble extracting that into a data extension. I'm using this query

SELECT c.JobID,c.SubscriberKey
FROM _Open c
INNER JOIN (
    SELECT JobID,EmailName 
    FROM _Job 
) j ON j.JobID = c.JobID
WHERE j.EmailName = 'Journey Email 1'

My results data extension has EmailName, JobID, and SubscriberKey. There are no errors that result from this however I'm not getting any data either.

Best Answer

I assume you are trying to execute the query from a child Business Unit. If so, append an Ent. prefix to your Data Views:

SELECT c.JobID, c.SubscriberKey
FROM Ent._Open c
INNER JOIN (
    SELECT JobID, EmailName 
    FROM Ent._Job 
) j ON j.JobID = c.JobID
WHERE j.EmailName = 'Journey Email 1'

Related Solutions

[SalesForce] Query NOT IN _Open from JobID .. incl. EmailAddress

Assuming that the SubscriberKey in your account is not the default of email address, you would need to also query the _Subscribers dataview.

You would just need to add the EmailAddress field to your DE and use the below query instead.

Something like the below should work:

select distinct s.subscriberKey, sub.EmailAddress
from _sent s
INNER JOIN _Subscribers sub
ON s.SubscriberKey = sub.SubscriberKey
where s.JobID = 4791255
and s.SubscriberKey IS NOT NULL
and s.SubscriberKey NOT IN (
  SELECT o.SubscriberKey 
  FROM _Open o  
  WHERE o.JobID = s.JobID
  AND o.SubscriberKey = s.SubscriberKey
)

[SalesForce] Query against subscribers in a journey send

Hi please try the below approach instead querying users you can use subscribers and find-out the email send for the user

    SELECT 
    a.EmailAddress as email
,   a.SubscriberKey as External_Id
,   b.EventDate  
,   c.EmailName
FROM
    Ent._Subscribers a  INNER JOIN _Sent b  ON a.SubscriberKey=b.SubscriberKey 
                        INNER JOIN  _Job c  ON b.JobID=c.JobID
WHERE
    c.EmailName IN ('NGB Email 3 - 82f87a6eb77e4687ba09fa088ec05abe')

Best Answer

Related Solutions

[SalesForce] Query NOT IN _Open from JobID .. incl. EmailAddress

[SalesForce] Query against subscribers in a journey send

Related Topic