Issue

This article explains how to use timerange filters in the query editor.

Product

• Mobile Marketing Cloud
• Customer Data Platform

General

Filtering data using timerange filters can often be challenging to comprehend and configure accurately. One crucial aspect to understand is that the CDP uniformly stores all date fields in UTC within its database.

On the frontend, we display dates in the local timezone and/or according to your selection on the profile insights page. This ensures that all date properties are presented in the chosen timezone, including the profile details page. Additionally, we have implemented a hover feature that normalizes the dates into a more user-friendly and easily readable format. This is a default format and cannot be adjusted.

ISO dates are displayed with time, birthdays are displayed without time.

If you are doubting if you have the correct filter, you can always check the summary of what your filter will include.

This summary offers a clear overview of what data will be included based on your chosen filter settings. It serves as a helpful reference to ensure that your filter encompasses the desired criteria and aligns with your analysis goals.

Why use timerange filters?

A date filter allows you segment profiles based on having profile property values or event property values that fall within a specific date range.

The terms "fixed date filter" and "rolling date filter" typically refer to different ways of specifying date ranges when querying or segmenting customer data. Here's the difference between the two:

1. Fixed Date Filters: An absolute date filter allows you to specify a fixed or specific range of dates. You define the start and end dates explicitly, such as selecting a particular month or a specific date range. For example, you might set the filter to include customer data from January 1, 2023, to December 31, 2023. This filter provides precise control over the exact period you want to analyze or segment.
   
2. Rolling Date Filters: A relative date filter, on the other hand, allows you to define a date range based on a relative time frame in relation to the current date or a specific reference point. Instead of specifying fixed dates, you use relative terms like "today," "yesterday," "last 7 days," "last month," "next year," and so on. The date range dynamically adjusts based on the current date or reference point. For instance, if you set the filter to "last 30 days," it will always include data from the most recent 30-day period ending on the current day. So when checking your rolling date filter on another day, your results might have changed.

The selection between absolute and relative date filters relies on your specific analysis or segmentation requirements. If you aim to analyze or segment data for specific periods, such as a fiscal quarter or a holiday season, absolute date filters are the recommended choice. They allow you to precisely focus on designated time ranges.

On the other hand, if your objective is to track recent or ongoing trends, or compare data across various time frames relative to the current date, relative date filters offer a more adaptable and dynamic approach. They provide flexibility by enabling comparisons and trend monitoring based on time intervals relative to the present moment.

Fixed date filters

For the fixed date filters we have the following options:

• Is at
• Before
• After
• Between

The 'Is at' filter has the option to also filter on Day of week/Day of month and Month.

See the filter summary of what data will be included in your filter to make sure you have setup the correct filter

Rolling date filters

The rolling date filters have the following options:

• Exactly
• Between now and XX
• Before
• After

Each of these options have the ability to filter down on hours (not available for the anniversary property), days, months and years in either the past or future.

This filtering is always done on UTC timezone. So keep that in mind when working with time.

See the filter summary of what data will be included in your filter to make sure you have setup the correct filter.

Anniversary date filter

Anniversary properties do not include time or a timezone selection. The tradition of celebrating birthdays is generally based on the date alone, without specific regard to the time of birth. Most people celebrate their birthdays on the specific day, regardless of whether it's at midnight or later in the day. Also in many legal contexts, such as age verification for online services, only the date of birth is required. Adding a time or timezone might complicate these processes without adding significant value.

Therefore these type of properties will not have a timezone or time selection option in the filters.

The anniversary property filters have the same options as the date/time properties, so they also have fixed date filters and rolling date filters. Only difference is that they do not include time or timezone selection.

There is one addition to an anniversary property, namely the age filters. You can use them to filter on an exact age or a between age range.

These filters do have a timezone selection, age filters typically have a timezone selection when they are used in situations where time and timezone are relevant factors for determining a person's age. This is often the case in scenarios related to online services, events, or activities that depend on a user's age within a specific timezone.

In situations where age eligibility is determined by the current date and time in a specific timezone, having a timezone selection becomes crucial. For example, if an online sale is only available to users who have reached a certain age in a specific timezone, the platform needs to accurately calculate the user's age based on their selected timezone.

For events or activities that take place in different time zones, organizers may need to apply age restrictions based on the local time of each event.

Here the summary is also of great help to show what is included and what not.

Legacy format vs. new format

The above timerange format is our latest version of timerange filters. The old legacy timerange filter is still visible if you already have setup a timerange filter in the past. It will keep working, but any new timerange filters or editing an existing timerange filter will be setup using the new timerage format.

Preferably all old legacy timerange filters are switched to the new format as the new format also includes bug fixes where timeranges are now correctly applied using the right date/time settings.

So when you encounter the below, it means the timerange filter was setup using the old legacy format. If you want to adjust it, you need to re-create the filter. You can still see the summary of your filter so you know what to setup using the new format.

In the below example, simply click on the 'empty' condition dropdown and setup your filter using the new format.

When applying, the filter is saved in the new format.