I query the _Opens data view which updates my SubscribersMain Data extension with the latest email open dates, I have noticed that the query blanks out the last_open_date when it shouldn't but cant figure out why, I suspect it happens only when the Subscriber already has a null value in the last_opened_date field. I can see records that absolutely do have timestamps this month, become blank.

EDIT: It looks like none of the comparisons work, it always reverts to the ELSE field in the case statement.

EDIT 2: Actually it looks like even when there is a value in the column, and the aggregated EventDate has a value... the query will return a blank value.

Here is my query:

select sub.subscriberKey,
(CASE WHEN dateadd(hour,6,MAX(o.EventDate)) > sub.last_opened_date THEN dateadd(hour,6,MAX(o.EventDate)) 
   ELSE sub.last_opened_date
END) AS last_opened_date
FROM SubscribersMain sub INNER JOIN _Open o
     ON sub.SubscriberKey = o.SubscriberKey
GROUP BY sub.SubscriberKey, Sub.last_opened_date
HAVING MAX(o.EventDate) <> ''
  • This might not be the cause of your issue but I don't see you declaring your alias for SubscribersMain. Oct 3 '18 at 16:16
  • Sorry that was a typo Oct 3 '18 at 17:03

SFMC data extensions are based off SQL tables so the date/time field types have an auto-assigned default value. As an empty value, the system will default to this to (1/1/1900 12:00:00AM).

So try using using this data as validation to see if it delivers desired results.

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.