2

I am trying to write a query to bring all the records for Open and Click event based on Subscriber Key.

So I want to see records like; if someone open email on 1/1/2010 9:10 but no click then click column value should be blank or N/A and if he again open same email at 1/1/2010 10:00 and Click as well then Click Column should be filled with Click event date.

SELECT  j.jobid ,
        j.SchedTime ,
        J.EMAILNAME AS EMAILNAME ,
        J.EMAILSUBJECT AS EMAILSUBJECT ,
        C.EVENTDATE AS CLICKDATE ,
        O.EVENTDATE AS OPENDATE
FROM    CLICK_BACKUP_PLAN AS C
        INNER JOIN Job_Backup_Plan AS J ON C.JOBID = J.JOBID
        INNER JOIN OPEN_BACKUP_PLAN AS O ON O.JOBID = C.JOBID
WHERE   C.SubscriberKey = 'ABCD@XYZ'

1 Answer 1

1

I'd probably do something like this. Use the job_backup_plan Data Extension as the source and then left join on the activity tables by jobid. I'd use a left join, because the subscriber may not have opened or clicked on an email in the send.

The group by and the max() functions will return the most recent eventdate.

select
j.jobid
, j.schedtime
, j.emailname
, j.emailsubject
, max(c.eventdate) as clickdate
, max(o.eventdate) as opendate
from job_backup_plan as j
left join click_backup_plan as c on (c.jobid = j.jobid)
left join open_backup_plan as o on (o.jobid = j.jobid)
where 
(o.subscriberkey = 'abcd@xyz.com' or c.subscriberkey = 'abcd@xyz.com')
group by 
j.jobid
, j.schedtime
, j.emailname
, j.emailsubject

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.