Analysis limitations on date fields

Are you tracking a property where data type = date?

There are a few ways you can filter on date fields in Amplitude today. You can build a cohort or chart segment using the following operator conditions

• = specific date

• ≠ specific date

• ∋ portion of the date string

• ∌ portion of the date string

• > or ≥ specific date (all days after that single point in time)

• < or ≤ specific date (all days before that single point in time)

However, these are all relatively static filters. If you need to set a dynamic filter on a date field relative to today, keep reading below.

Use cases to expand analysis and targeting on date fields relative to TODAY()

But there’s even more capability that you can unlock by building a derived property that identifies how many days until a future date will happen, with respect to today’s relative date. There are a few use cases where this concept of “days until x date from today’s relative date” becomes a critical signal in analyses and cohorts…

• Use case a: Exclude users and accounts from upsell targeting if they have a future contract start date after an opportunity has been closed won.

  • Oftentimes when sales reps close a deal with a new customer, the customer will ask for a future contract start date, starting next month when they have the resources and bandwidth to begin the project with the vendor. Growth Marketing teams need to be able to suppress these “future customers” with a future contract start date from their digital programs in order to avoid double-upselling product features that have already been purchased. So within the target cohort, it’s important to specify the target user group does not have a contract start date in the next n days from now. 

• Use case b: Exclude users and accounts from digital nurture programs that are approaching a contract end date.

  • For recurring marketing campaigns, it’s a strategic tactic to exclude users associated with contracts that are approaching a contract end date within the next n days. 

• Use case c: Target users and accounts to collect feedback n days prior to the contract expiration date.

  • Contrary to use case b, there might be an intentional program seeking feedback only from users and accounts that are not renewing their contracts. In this case, it’s important to have this “days until x date” field readily available for cohort targeting.

There are additional scenarios to target or analyze users “n days since x date” to narrow down the audience to users and accounts who are at least n days past their contract start date. 

• Use case d: Target users within accounts that are at least n days past their contract start date.
  • It’s important to consider which stage in the account lifecycle is ready to receive specific content. Whether you want to set a tenure maturity level requirement before sending specific content to an account, or if you just wish to avoid overlapping programs with content sent during the onboarding and activation stages, a property that indicates “days since x date” is critical to leverage. 

At Amplitude, the Growth Marketing team factors this “days until x date” criteria in many of the cohorts that target active paid contracts. In order to target users on an active Salesforce contract who are at least n days into their contract AND more than n days out from their contract end date, we need to extract 2 new derived properties from existing group properties.

Extract this data and stamp it onto a newly derived property with retroactive data 

In order to set up this “date relative to today” field in Amplitude Data, navigate to the derived properties tab and create a new derived property.

Below is the logic we used for this specific derived property setup.

How to interpret this logic:

• First convert the date field with DATE_TO_LONG function. If you’re referencing a datetime field, use the TIME_TO_LONG function.
• Next subtract the current date with MINUS () and TODAY () functions
• Then divide by 86,400,000 milliseconds to convert the output into days
• The final values will yield negative for past dates and positive for future dates
• Test out your new derived property with the preview outcome feature
• Finally, plug in your new derived property into your cohort. Our original example is the following:  
  • at least 90 days into the contract:  days since contract initial start date ≤  -90.0
  • more than 30 days out from their contract end date: days until future subscription end > 30.0

CEIL(
	DIVIDE(
		MINUS(
			DATE_TO_LONG(
				<<insert original date property here>>
			),
			TODAY()
		),
		86400000
	)
)

Build analyses and cohorts with newly derived properties

Now that you’ve configured a newly derived property, you can build out advanced logic that is dynamic, relative to today’s date. Make sure to set a clear property definition for others to benefit from using this data as well. 

This newly derived property will always be prefixed with fx to easily differentiate it from other properties. 

This new data enables you to dynamically target the right audience based on days since key dates in a contract happened, and days until key dates in a contract occur. 

Comment below if you have any questions.