Joining SendLog and Click Data View does not return any results

Question

I want pull out the list of customers that did not click on the mail we sent.

We maintain a SendLog in which we have additional attributes logged (Example: emailname, subject etc.)

I understand that we can use Sent data view. But I wish to use SendLog instead as it has additional fields.

Here is the query I have used to join SendLog & Clicks. But the results is null. Please could anyone help me fix this problem

select  se.EmailAddress,se.TimeStamp as sentdate
from SendLog se
inner join [_click]  cl
on  se.JobID=cl.JobID and se.BatchID=cl.BatchID and se.SubID=cl.SubscriberID
where se.EmailName_ = 'hello'  and cl.SubscriberID is null

Answer 1

Your issue is you are doing an INNER JOIN, which requires the record to exist in BOTH tables (DEs) in order to be returned. The OPs code is basically saying return all records that match on both tables but are empty on the second one. Since it cannot exist but also not exist, it returns 0.

As you are looking for records that are both in and NOT in the _Click DV, this is causing you to return 0 results.

What you want to do is instead utilize a LEFT JOIN (pulls all results from initial table (de) and any that match in second (de).

By then pulling only records that have a click subID of null, it will remove any matches and will instead only pull those in the send log that are not in click.

Like this:

select  se.EmailAddress
,se.TimeStamp as sentdate
from SendLog se
left join [_click]  cl
on  se.JobID=cl.JobID and se.BatchID=cl.BatchID and se.SubID=cl.SubscriberID
where se.EmailName_ = 'hello'  and cl.SubscriberID is null

Another option too is to instead utilize NOT EXISTS instead of a left join.

Something like:

select  se.EmailAddress
,se.TimeStamp as sentdate
from SendLog se
WHERE NOT EXISTS (
  SELECT TOP 1 cl.SubscriberID FROM [_click]  cl
  WHERE se.JobID=cl.JobID and se.BatchID=cl.BatchID and se.SubID=cl.SubscriberID
)
AND se.EmailName_ = 'hello'

They will likely process and function the same, so its more a matter of preference.

Answer 2

Welcome to SFSE!

Please review _Click Data View documentation, where it is shown that the SubscriberID field is not nullable, thus you cannot have WHERE clause with cl.SubscriberID is null and INNER JOIN at the same time, which makes you have 0 records in the result.

You should either adjust conditions in the WHERE clause or select another type of JOIN, depending on what would suit your needs better.