I'm trying to build a series of queries to keep a definitive list of all subscribers, along with the date of their latest email from us. I've almost got it working but not quite. The sequence is as follows:
- I check the _Sent data extension to identify the most recent email for each subscriber on our list, using the following query:
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 places them in a DE called SentLatest.
- I then get a list of all subscribers from _Subscribers, with an added field called 'LatestEmail' to get a list of all subscribers, along with the date of their latest email from us. For this I use a left join between _Subscribers and the SentLatest DE I created above (this is an Add and Update query)
SELECT a.SubscriberKey , a.EmailAddress , a.Status , a.DateJoined , b.EventDate AS LatestEmail FROM _Subscribers a LEFT JOIN SentLatest b ON a.SubscriberKey = b.SubscriberKey
This seems to mostly work, but here's my problem. The system data tables keep records for 6 months. After 6 months, data drops off the end of the tables. So as of today, the 10th of February, I can run my query and will have, on my new DE called AllSubscribersMostRecentEmail, a complete list of all subscribers with a LatestEmail date filled in as long as they appear on the _Sent list (from having received an email in the last 6 months).
If I filter my new data extension to show only those with a LatestEmail date of precisely 6 months ago - 10th August 2019, I can see several hundred subscribers who last received an email from us on that date. However, when I run my query again tomorrow, their email send records will have disappeared from the _Sent table, and that record of their last email disappears.
I hoped my query would keep this record of their latest email, but every time the query runs, if it can't find the subscriber on the _Sent list, it overwrites their 'LatestEmail' date with a blank value. How do I make it not do that, and retain their latest email date after it disappears from the system tables?