1

I tried to do the SQL for get who opened an email in journey builder. I use this SQL below, but no records populates my Data Extension.

I tried to use whith and without the prefix 'ent' before the Data View. What can be wrong?

SELECT c.JobID as [JobId],
c.SubscriberKey as [SubscriberKey],
EventDate,
'Open' as [Action]
FROM ent._Open c
INNER JOIN (
SELECT JobID, EmailID
FROM ent._Job 
) j ON j.JobID = c.JobID
WHERE j.EmailID = '13802'
AND c.EventDate BETWEEN GETDATE()-30 AND GETDATE()

i tried to use another sintax and no records populates, i do n

Select 
a.SubscriberKey, 
a.[JobID], 
'Opened' as [Action], 
b.EmailName 
From _Open a 
inner join _Job as b 
on a.JobID = b.JobID 
where 
b.EmailName ='Email Name' 
AND (getdate() - a.EventDate) between 0 AND 7)

1 Answer 1

0

I would try this...It might be the syntax in the query that is giving you a 0 match result.

SELECT
c.JobID,
c.SubscriberKey,
c.EventDate, ** assuming you mean open date here? need to define it. **
'Open' as [Action]
FROM _Open c
INNER JOIN
_Job j
ON
j.JobID = c.JobID
WHERE j.EmailID = '13802' ** are you sure this value is not the JobID? Change to JobID if so...EmailID != JobID. **
AND c.EventDate BETWEEN DateAdd(Day,-30,GETDATE()) AND GETDATE()

Hope this helps!

4
  • Unfortunaly does not works, @Bread2287. :/ Oct 24, 2019 at 21:02
  • are you sure the emailID is '13802'? I'm under the impression that the email ID is usually some GUID hash...Maybe try j.EmailName instead to see if you can at least get the query to work and isolate what is wrong with it.
    – Bread2287
    Oct 24, 2019 at 21:08
  • Yes, is the EmailID. Oct 24, 2019 at 21:17
  • Hello, @Bread. I think i have news; i tried to do a simple a query like and no records returns. I created a case with support to know better and update the case Oct 24, 2019 at 22:37

You must log in to answer this question.

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