How to query Last Opened Date, Last Send Date, Last Clicked Date from _Open, _Click and _Send Data Views

Question

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.

Answer 1

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.

Answer 2

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.