Derived properties

This article will help you:

• Understand how derived properties can benefit your analysis
• Review the functions and operators that can be used when creating your derived properties

In some cases, you may want to run analyses based on properties that were not sent to Amplitude but can be derived from existing properties. Amplitude Data’s derived properties allow you to create new event and user properties retroactively, based on functions and operators that you can apply across multiple existing properties. These do not affect your raw data and will be computed on the fly.

For example, you may want to create a chart that groups by whether an item added to a shopping cart is eligible for a discount. In that case, you could create a derived property whose value is a boolean based on whether the price exceeds a certain amount.

Create a derived property

NOTE: You must be in your project's main branch to create a derived property.

To create a derived property, follow these steps:

1. In Amplitude Data, navigate to Properties and click the Derived tab.
2. Click + Add Derived Property.
3. In the Derive New Property modal, enter a new property name (required) and description (optional).
4. Enter your formula. See below for the list of valid functions and operators.
5. Click Save. 

Preview your results

As long as the formula you entered is valid, you can preview the results in the space below the formula editor. Do this by selecting existing values for properties used in the formula, or test out any free-form values. You can do this from either the Create/Edit modal or from the side panel for a saved derived property.

Derived property use cases

Taking our previous referrer URL example, you can write a formula using string operators that looks like this:

SPLIT(referrer_url, "/", 2)

This formula will convert a value like "https://www.google.com/search?q=amplitude" into the value "www.google.com." But what if you want to strip this down even further, to just "google"? You can achieve this by wrapping the result of a SPLIT function inside another SPLIT function. The resulting formula would look like this:

SPLIT(SPLIT(referrer_url, "/", 2), ".", 1)

Amplitude also supports math operators. Let’s say you have events that contain subtotal and tip properties, and you want to run some analyses based on the total amount. You can use this formula:

SUM(subtotal, tip)

Maybe you're also interested in knowing how many orders you would have given discounts to if the total order size was over $50. This formula will tell you whether a particular order would receive a discount:

IF(SUM(subtotal, tip) >= 50, true)

NOTE: Queries using derived properties may experience longer query times depending on the complexity of the formulas. There is also a limit of up to 10 property references per derived property.

Functions and operators

String functions

Math functions

Object functions

Date/ time functions

NOTE: Amplitude requires all Unix timestamps to be expressed in milliseconds.

Array functions

When performing computations on derived properties created from array properties, Amplitude assumes that only the first child is an array property, and only considers the first value of the other children, even if they are also array properties. 

Here are some illustrative examples:

Example 1
     propA = [1,2,3], propB = [a,b,c]
     CONCAT(propA, propB) = [1a, 2a, 3a]
Example 2
     propA = [1, 2, 3], propB = [a]
     CONCAT(propA, propB) = [1a, 2a, 3a]
Example 3
     propA = [1], propB = [a, b, c]
     CONCAT(propA, propB) = [1a]

Conditional operators

String/numerical operators

Set operators

Set literals (e.g. ("apple", "orange")) must appear on the right hand side of the operator

Derived Properties Formulas

Get the difference between two dates

DIVIDE( 
SUBTRACTION( 
DATE_TO_LONG( 
$1 ), 
DATE_TO_LONG( 
$2 ) 
), 
86400000 
)

Sample Output:

In the derived property above, the properties end_date and start_date are converted into UNIX timestamps and then we find the difference between them. Finally, we divide that number by 86400000 which is the number of milliseconds in 1 day. Please note that the output will be a double type (e.g. 2.0). At the bottom, you can preview some of the results of the derived property. Replace the $<number> here with the actual properties

Standardized Date Format

IF(DATE_TIME_FORMATTER(
 $60 ,
 "yyyy-MM-dd'T'HH:mm:ssX",
 'yyyy-MM-dd'
) contains '-', DATE_TIME_FORMATTER(
 $61  ,
 "yyyy-MM-dd'T'HH:mm:ssX",
 'yyyy-MM-dd'
), IF(DATE_TIME_FORMATTER(
 $72 ,
 "yyyy-MM-dd HH:mm:ss",
 'yyyy-MM-dd'
) contains '-', DATE_TIME_FORMATTER(
 $73 ,
 "yyyy-MM-dd HH:mm:ss",
 'yyyy-MM-dd'
), DATE_TIME_FORMATTER(
 $76 ,
 "yyyy-MM-dd",
 'yyyy-MM-dd'
)))

Sample Output:

One possible way to format dates to Standard Date Format would be to use a series of IF statements, but you need to make sure the higher specificity conditional comes first. Replace the $<number> here with the actual properties

Get Sign Up Month and Year

CONCAT(
 REGEXEXTRACT($107,
 '\d\d\d\d\-\d\d'
 ),
 "-01"
)

In the example above, the derived property will pull the sign-up month and year from a property that contains a more detailed value and append the "-01" to set it to the beginning of the month. You can use the REGEXEXTRACT() to pull the Year and Month from the value and use CONCAT() to append the "-01" to the sign-up month and year. Replace the $<number> here with the actual properties.

Replace Property Values

IF(
 OR(
 REGEXEXTRACT($47,'Casual) =='Casual',
 REGEXEXTRACT($61,'1 Job Posting')=='1 Job Posting',
 REGEXEXTRACT($67,'1 Basic')=='1 Basic'
 ), 'Casual', 'False')