I have the DE and SQL set up and the results "work" kind of. It gives me a count of the opens/clicks but for some reason it is only pulling results for subscribers that have ALL 3 (a sent, an open, a click).
I have used Left Join from the query and it doesn't seem to work. (I also tried every version of join I could think of: inner, outer, right, full outer)
Results are always the same....any thoughts?
Select
s.SubscriberKey,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate,
c.[Click],
o.[Open]
FROM (
SELECT
sn.SubscriberKey,
sn.JobID,
sn.BatchID,
sn.EventDate
FROM [_sent] sn
WHERE JobID = <ENTER JOB ID>
) s
RIGHT JOIN (
SELECT SubscriberKey, COUNT(*) as [Open], JobID
FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
RIGHT JOIN (
SELECT SubscriberKey, COUNT(*) as [Click], JobID
FROM [_Click] cl
GROUP BY cl.SubscriberKey, cl.JobID
) c
ON s.SubscriberKey = c.SubscriberKey
WHERE s.JobID = c.JobID
AND s.JobID = o.JobID
AND s.JobID IS NOT NULL
5 个回答
@Guy - No I still got the same results. I modified the left join to Full Outer...
Select
s.SubscriberKey,
s.JobID,
s.BatchID,
convert(char(19),s.EventDate,20) as SendDate,
c.[Click],
o.[Open]
FROM (
SELECT
sn.SubscriberKey,
sn.JobID,
sn.BatchID,
sn.EventDate
FROM [_sent] sn
WHERE JobID = 499749
) s
Full Outer Join (
SELECT SubscriberKey, COUNT(*) as [Open], JobID
FROM [_Open] op
GROUP BY op.SubscriberKey, op.JobID
) o
ON s.SubscriberKey = o.SubscriberKey
Full Outer Join (
SELECT SubscriberKey, COUNT(*) as [Click], JobID
FROM [_Click] cl
GROUP BY cl.SubscriberKey, cl.JobID
) c
ON s.SubscriberKey = c.SubscriberKey
WHERE s.JobID = c.JobID
AND s.JobID = o.JobID
AND s.JobID IS NOT NULL
