0

Recently, I have queried the subscriber tracking information from data views for _Open, _Click and _Send Data Views and the final data extension holds the Unique Contact Data (Criteria : contact received more than 3 emails.

How to obtain the "Last Email Received Date", "Last Email Clicked DateTime" and Last Email Opened DateTime ?

Can you please help me here anybody.

2 Answers 2

4

You can utilize the SQL function MAX() to get the most recent date from the field. This combined with a GROUP BY will ensure you only get the most recent date with each unique record.

As you are working with 3 data views, the below may time out, so you may need to split it into multiple queries to ensure it runs, but below should do it:

SELECT
s.SubscriberKey,
MAX(s.EventDate) as Last_Email_Sent,
MAX(o.EventDate) as Last_Email_Open,
MAX(c.EventDate) as Last_Email_Click
FROM _Sent s
LEFT JOIN _Open o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN _Click c
ON s.SubscriberKey = c.SubscriberKey
GROUP BY s.SubscriberKey

Edit : Typo on the name of the "_Sent" data view.

1
  • 1
    Thanks and you are correct. I had sliced them up into individual queries and pointed to the target DE for update.
    – Yesh
    Jul 17, 2020 at 5:59
1

You can make use of Row Partition in SQL Query. Below is the query which will able to fetch you the latest send date per subscriber. You can do the same fr Open & Click as well.

select
  x.AccountID
, x.OYBAccountID
, x.JobID
, x.ListID
, x.BatchID
, x.SubscriberID
, x.SubscriberKey
, x.EventDate
, x.Domain
, x.TriggererSendDefinitionObjectID
, x.TriggeredSendCustomerKey
from (
  select
    b.AccountID
  , b.OYBAccountID
  , b.JobID
  , b.ListID
  , b.BatchID
  , b.SubscriberID
  , b.SubscriberKey
  , b.EventDate
  , b.Domain
  , b.TriggererSendDefinitionObjectID
  , b.TriggeredSendCustomerKey
  , row_number() over (partition by b.SubscriberKey order by b.EventDate desc) ranking
  from _sent b
) x 
where x.ranking = 1

Reference - https://sprignaturemoves.com/picking-rows-with-partitions/

Let me know if this helps.

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.