For about 18 months I've been running an automation to keep a list of all subscribers and the date of their most recent email. I have two queries and two data extensions. Number 1 is:
SELECT AccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, Domain, TriggererSendDefinitionObjectID, TriggeredSendCustomerKey FROM (SELECT AccountID, JobID, ListID, BatchID, SubscriberID, SubscriberKey, EventDate, Domain, TriggererSendDefinitionObjectID, TriggeredSendCustomerKey, row_number() over(partition by SubscriberKey order by EventDate desc) as rn from _Sent) as T where rn = 1
This puts them in a data extension called SentLatest - which follows the same template of the _Sent system table. This works fine - there is one line for each subscriber, and it has the date of the most recent email they received.
I then run another query:
SELECT a.SubscriberKey , a.EmailAddress , a.Status , a.DateJoined , case when mr.LatestEmail is null then b.eventDate when mr.LatestEmail >= b.eventDate then mr.LatestEmail end AS LatestEmail FROM _Subscribers a LEFT JOIN SentLatest b ON b.SubscriberKey = a.SubscriberKey LEFT JOIN AllSubscribersMostRecentEmail mr on mr.subscriberkey = a.subscriberkey
Which puts them in the DE called AllSubscribersMostRecentEmail. What I want it to do is update every record in there IF the latest email date has changed since the last time it ran. If it is the same, then keep it as it is. It took me a while to get this to work as if a subscriber had dropped off the 6 month end of the send tables it was overwriting LatestEmail with a blank value which I do NOT want. I want them to stay with that date even if they've dropped off the send log limit.
When checking today, I found a record who received an email from us on June 26, 2021, but the query is not updating them on AllSubscribersMostRecentEmail - it's blank still. So I think my 2nd query is wrong somewhere. Does anyone have any suggestions of how I could update these values if the eventDate is more recent, and leave it alone if it can't find it/it's not?