1

Right now I'm getting a total opened with my query, but I need to find the unique opened. So each subscribers can only be one time in the file per email. It must be something with grouping per EmailName and Subscriber to get the unique.

SELECT
_Open.AccountID,
_Open.JobID,
_Open.ListID,
_Open.EventDate,
_Open.SubscriberKey,
_Job.EmailName,
_Job.EmailID
FROM
_Open
INNER JOIN _Job
ON _Job.JobID = _Open.JobID
WHERE DateDiff(day,_Open.eventdate,GetDate()) = 1

Edit: just found out theres an _Open.IsUnique I could use, I will get back if it works :-)

3

Okay, that was pretty easy. My final query that works looks like this:

SELECT
_Open.AccountID,
_Open.JobID,
_Open.ListID,
_Open.EventDate,
_Job.EmailName,
_Job.EmailID
FROM
_Open
INNER JOIN _Job
ON _Job.JobID = _Open.JobID
WHERE DateDiff(day,_Open.eventdate,GetDate()) = 1
AND _Open.IsUnique = 'true'

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.