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

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 _Send s
LEFT JOIN _Open o
ON s.SubscriberKey = o.SubscriberKey
LEFT JOIN _Click c
ON s.SubscriberKey = c.SubscriberKey
GROUP BY s.SubscriberKey
| improve this answer | |
  • 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 at 5:59
0

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.

| improve this answer | |

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.