0

How can I create a create an Contact Entry Filter that only selects records that were created yesterday using a "DateCreated" date field in a Data Extension?

I tried:

DateCreated AtOrAfter today minus 1 day

But this appears to include records from today too, which is incorrect.

I also tried the following with no results:

DateCreated AtOrAfter today minus 1 day AND DateCreated Before today
DateCreated After today minus 1 day AND DateCreated Before today    
DateCreated Equal today minus 1 day
  • Try this: "datecreated After today minus 2 days AND datecreated Before today". The "after today minus 1 day" in your filter would find records created today (today minus 1 day would be yesterday, so records created "after" yesterday would be created today or after) , so combined with the "And DateCreated Before today" you wouldn't get anything. – Mark P Oct 1 '16 at 23:49
  • I figured out how to get it to work using a Filtered Data Extension using your filter exactly, but not using Journey Builder. I have to tick the "Account Time Zone" box for Filtered Data Extensions. This option does not exist in Journey Builder. Does this mean that Journey Builder is in US CST Time Zone? – Christopher Carswell Oct 2 '16 at 22:10
1

Try using a Date Based Event instead of a Contact Data Event. In your scenario, I would select the DateCreated attribute and set it to 1 day after CreatedDate. So for example, if the CreatedDate is October 1, then Contacts will enter the journey on October 2.

Date Based Event

  • This isn't working either, I suspect that there is a time zone complication as when creating a Filtered Data Extension using "datecreated After today minus 2 days AND datecreated Before today" and ticking "Account Time Zone" works. When it's unticked, the filter doesn't work. Journey Builder doesn't seem to take into account the time zone of the account when querying the record. – Christopher Carswell Oct 5 '16 at 1:45
1

From Support:

The entry event comparisons are being made on the server side, which is running on US Central Standard Time (without daylight savings) which would be about 19 hours behind your timezone.

This is a known limitation. The date/time displays will follow your account timezone settings but any date-based entry event will be based on the server time.

Currently, there is no option to configure the timezone to be used for a date-based entry event and the recommended solution would be to adjust the filter settings to match US Central Standard Time. ie instead of DateCreated after today minus 2 use DateCreated equal Today

Support has suggested an inefficient solution. My solution will be to either:

  1. SQL queries converting the data file date/time to CST, then use that field for the Entry Criteria
  2. Use a Data Filter Activity to output the subscribers eligible for mailing into another Data Extension, then use that Data Extension as the source Data Extension for the Journey

Update on 27/10/2016

My final solution was to create a Query which outputs a CST version of the Date field using below:

DATEADD(HH,-19,CONVERT(datetime, LEFT(CONVERT(nvarchar(20),DateCreated, 20),10) + ' 12:00:00', 120)) as DateCreatedCST
  • It's important to hardcode the time (12:00:00) to account for fluctuations of DST between NZ Timezone and server Timezone.
  • To adjust for another timezone, simply change the -19 value to whatever the offset between CST and your timezone is.

In Journey Builder, I used this filter criteria

DateCreatedCST Equal today minus 1 day

In order to target 5 days ago I would use

DateCreatedCST Equal today minus 5 days

There is no need to set a more complex filter.

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.