query Open or Click

Question

I'm trying to get the customers who open or click an email and I'm not sure what I'm doing wrong. If I only join the Click or Open, it works but when I do them together, it is timing out which means I'm not doing the joins right.

I only have 200.000 records in recipients

Here is my query

SELECT 
r.customerID AS SubscriberKey,
r.shouldbesentmarketingemails AS Subscribed,
j.EmailName,
c.EventDate AS ClickDate,
o.EventDate AS OpenDate


FROM [Recipients] r

JOIN _Open o
ON r.customerID = o.SubscriberKey

JOIN _Job j
ON o.JobID = j.JobID

JOIN _Click c
ON o.JobID = c.JobID

WHERE
j.EmailName = '245'

AND
r.shouldbesentmarketingemails = 'true'

Answer 1

Using LEFT JOINS with appropriate Data Views will ensure that you will get both, Opens OR Clicks, instead of just getting Opens that also have a Click:

SELECT 
r.customerID AS SubscriberKey,
r.shouldbesentmarketingemails AS Subscribed,
j.EmailName, 
s.EventDate as SentDate, 
o.EventDate as OpenDate, 
c.EventDate as ClickDate
FROM Recipients r LEFT JOIN _Sent s ON r.customerID = s.SubscriberKey
LEFT JOIN _Job as j ON s.JobID = j.JobID 
LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
LEFT JOIN _Click c ON s.JobID = c.JobID  and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
WHERE j.EmailName = '245'

Depending on the results from the above query, later add the last condition (r.shouldbesentmarketingemails = 'true') - I don't know if it's a text field or a boolean field, so you might have to do either 'true' or 1.