0

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!

1 Answer 1

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.

2
  • Thank you, @Adam Springs ! Just wanted to add one more join to get the status of the subscriber. (Active , Bounce, unsubscribed ) and want this query to pull only last 1-month report. could you please help me on which table I need to write that condition?
    – Saurabh
    May 28, 2021 at 8:28
  • You can join to ent._Subscribers on subscriberid to get the status. Always use the eventDate from the Sent or the date from Sendlog record. The _Job.DeliveredTime is not accurate for triggers and journey sends. May 29, 2021 at 22:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .