Send Log Join with _Job, _Click , _Open Need help

Question

I am using a send log feature to store the sent email details -- like Name, EmailAddress, issueDate, CertificateNumber. The send log already has JobID, ListID, BatchID,S ubId, TriggeredSendID, ErrorCode as default fields.

Now we’d like to be able to pull a report with Email Name, email address, personalization fields stored in send log, and whether they opened/clicked/ContentLink. We need to join this send a log with _job, _click, _open.

Please help me to get the desired result using a query. I tried the below query but I am not getting the correct result for o.eventDate as openDate, c.eventdate as clickDate, c.LinkContent columns.

I have a total of 90 records in send Log. but the query result showing more than 4000 records also LinkContent link is not what we sent in the email. link is formulated like this in each email.

https://baseURL/?CertificateNumber="value"&issuedate="value".

But when executed the below query I am getting a URL that is not related to that certificatenumber or say that email. Getting link with a different certificate number against that row.

Please guide me to get the correct query.

Select Distinct
j.JobID,
j.EmailName,
j.DeliveredTime as SendTime,
l.CertificateNumber,
l.Name,
l.Email,
l.issueDate,
o.eventDate as openDate,
c.eventdate as clickDate,
c.LinkContent
from [_Job] j
join SendLog l on j.JobID = l.JobID
Inner Join _sent s 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 o.IsUnique = 1 and s.SubscriberID = o.SubscriberID
left join [_click] c
on s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and c.IsUnique = 1 and s.SubscriberID = c.SubscriberID

What am I doing wrong? What should be the correct way to pull that information? Thanks!

Answer 1

The SendLog is by subscriber, so you'll need to add subscriberkey to your join:

Select Distinct
  j.JobID
, j.EmailName
, j.DeliveredTime as SendTime
, l.CertificateNumber
, l.Name
, l.Email
, l.issueDate
, o.eventDate as openDate
, c.eventdate as clickDate
, c.LinkContent
from _Job j
inner Join _Sent s on s.JobID = j.JobID 
inner join SendLog l on l.JobID = j.JobID and l.subscriberkey = s.subscriberkey
left join _Open o on s.JobID = o.JobID and o.IsUnique = 1 and s.SubscriberID = o.SubscriberID
left join _Click c on s.JobID = c.JobID and c.IsUnique = 1 and s.SubscriberID = c.SubscriberID

Also, keep in mind that you're joining to activity data views, so you'll have multiple records per jobid and subscriberkey/id for each sent row. The primary key on your target data extension should be configured to handle that.