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!