2

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'
4
  • Can you be more specific on what "doesn't work"? Is the query failing? Or is the output not what you expect? Nov 20, 2019 at 10:53
  • it is timing out
    – Duicug
    Nov 20, 2019 at 11:02
  • 1
    Kind of curious why you do not group by subscriber and job ;) are you sure you want to have JOINS and not INNER Joins? Nov 20, 2019 at 11:24
  • 1
    There are few things that can be improved. 1) try using left joins so you will end up retrieving all even data across click, opens and job. 2) Email name seems to be weird, is it EmailID you're after? 3) Also the data view will have multiple rows of events hence you'll need to filter to unique records so that you get one row output from each data view. Nov 20, 2019 at 11:28

1 Answer 1

5

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.

2
  • Thanks Zuzan, that is exactly what I was looking for :)
    – Duicug
    Nov 20, 2019 at 13:35
  • Glad it helped!
    – zuzannamj
    Nov 20, 2019 at 14:06

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.