Creating a log of all subscribers' most recent email. It's missing some people. SQL help?

Question

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?

Answer 1

Below are a couple possible solutions for your need. These are completely off the hip coding so may need to adjust it some to implement.

Using EXISTS():

SELECT 
a.SubscriberKey
, a.EmailAddress
, a.Status
, a.DateJoined
, b.LatestEmail
FROM _Subscribers a
LEFT JOIN  SentLatest b on b.subscriberkey = a.subscriberkey
WHERE EXISTS (
    SELECT TOP 1 ex.Subscriberkey 
    FROM [AllSubscribersMostRecentEmail] ex 
    WHERE ex.subscriberkey = a.subscriberkey 
    AND (b.LatestEmail > ex.LatestEmail OR ex.LatestEmail is null)
)

This one basically excludes all those that exist on the DE already and the new (b.LatestEmail) date is not greater than the existing (ex.LatestDate) date.

Even with the SELECT TOP 1 in the Exists(), you might want to validate performance and ensure it can run within the 30 minute timeout limit.

Using Outer Apply and Case:

SELECT 
a.SubscriberKey
, a.EmailAddress
, a.Status
, a.DateJoined
, CASE
    WHEN new.LatestEmail IS NOT NULL AND new.LatestEmail > old.LatestEmail THEN
      new.LatestEmail
    ELSE
      old.LatestEmail
END as LatestEmail
FROM _Subscribers a
OUTER APPLY(SELECT TOP 1 ex.LatestEmail FROM SentLatest ex WHERE a.Subscriberkey = ex.Subscriberkey) new
OUTER APPLY(SELECT TOP 1 ex.LatestEmail FROM AllSubscribersMostRecentEmail ex WHERE a.Subscriberkey = ex.Subscriberkey) old

This one basically grabs the two values via Outer Apply and then uses a case statement to compare them and display the date that is the most recent. There may be some considerations and adjustments on the CASE statement and all to make this work. This one also needs to have performance and time out considered before implementation.