0

We would like to get all opens data together with email sent/delivered data for the last 6 months. The goal is to separate openers from non-openers by filtering the resulting data extension. We are using an SQL which produces results, however the target data extension cannot be filtered on EventDate (email open time), as all records have open-time, even though we used LEFT JOIN between _sent and _open data view. Did I join on the wrong field? Here's the code:

SELECT DISTINCT
CONVERT(varchar,s.EventDate,20) AS SentDate,
s.subscriberkey,
CONVERT(varchar, o.EventDate,20) AS OpenDate
j.emailname,
j.emailid
FROM _Sent AS s

LEFT JOIN _Open AS o 
ON s.jobID=o.jobID AND s.subscriberID = o.subscriberID
AND o.IsUnique = 1
AND CAST(s.EventDate as date)>=CAST(getdate() -180 as date)

INNER JOIN _Jobs AS j
ON s.jobid = j.jobid
Improve this question

1 Answer 1

Reset to default
1

In you where clause you can have a condition on the _open dataview which is removing the null values

also the dataviews only have 6mths worth of data so I removed the date filter

you could update it to

SELECT DISTINCT
CONVERT(varchar,s.EventDate,20) AS SentDate
,s.subscriberkey
,CONVERT(varchar, o.EventDate,20) AS OpenDate
,j.emailname
,j.emailid
FROM _Sent AS s

LEFT JOIN _Open AS o 
ON s.jobID=o.jobID AND s.subscriberID = o.subscriberID 


INNER JOIN _Job AS j
ON s.jobid = j.jobid

where  (o.IsUnique = 1 or  o.IsUnique is null)
Improve this answer
1
  • Thanks Eazy. Can I ask you also, when I removed the inner join with _job I got substantially less rercords (150k vs 1.1 million). Why is that happening? Btw the target DE does not have a primary key (i.e. it's getting overwritten).
    – nix9247
    Commented Nov 5, 2019 at 15:21

You must log in to answer this question.

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