0

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:

  1. 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.

  1. 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?

2

You could conditionally set the latestEmail column with a CASE statement (along with a LEFT JOIN to the target DE):

SELECT 
a.SubscriberKey
, a.EmailAddress
, a.Status
, a.DateJoined
, case 
    when b.eventDate <= mr.LatestEmail then mr.LatestEmail
    when b.EventDate > mr.LatestEmail then b.eventDate
  end AS LatestEmail
FROM _Subscribers a
LEFT JOIN SentLatest b ON b.SubscriberKey = a.SubscriberKey
LEFT JOIN AllSubscribersMostRecentEmail mr on mr.subscriberkey = a.subscriberkey
/* action: update */

Edit: If the end date exists, it'll keep the newest one. If the subscriber has no match, then it'll set the LatestEmail to null. No ELSE in a CASE statement just returns null by default.

And also keep in mind, that queries like this will nearly always timeout and you'll have to come up with some other method. The data views are awful performance-wise.

I've been leveraging the Einstein data recently. It's not as specific as LatestEmail, but will suffice if to goal is to ID active vs inactive subscribers.

|improve this answer|||||
  • Thanks Adam, I'll give this a try. What is the CASE statement doing? Will it ksip trying to update the record if it can't find a date? – TCassa Feb 10 at 13:16
  • I edited my answer and added a few other considerations. – Adam Spriggs Feb 10 at 14:25
0

You might get older data via support - Worth the try.

There is no way through SFMC directly to get this in hindsight, as far as I know.

To set up a future process: For these usecases, it is a good idea to periodically pull a full export of your relevant data views (in your case _Sent) to a data extension (add & update), adding the delta of new records each time.

Then base your report on the resulting DE, not the data view. The data view will always keep its 6 month limitation. You might still want to enter a data retention time in the DE, but you can use something higher, like 5 years or similar.

According to a Salesforce source, the most performant and efficient way to do this is actually without SQL (which is perfectly possible via SELECT ... FROM _Sent). Instead, set up a file location of type "Safehouse", give it a name "mySafehouse" and export to that location. By setting up the location, you can reimport from there by choosing the "mySafehouse" location as source, using add & update to get the delta of new records.

You just have to think of the Safehouse as the non-public part of the FTP, so your files just get copied and uploaded there, and reimported without going public.

|improve this answer|||||
  • 1
    Support would not provide any data beyond the 6mths in the dataview, you could leverage data extracts if you need older data – EazyE Feb 10 at 14:24
  • Good to know, thanks! In that case - Don't ask support! :D – Jonas Lamberty Feb 10 at 14:42

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.