Derived properties

You might want to run analyses based on properties that weren't sent to Amplitude. Amplitude Data's derived properties let you create new event and user properties retroactively, based on functions and operators that you can apply across multiple existing properties. Derived properties don't affect your raw data, and Amplitude computes them dynamically.

For example, you might 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

To create a derived property, follow these steps:

1. In Amplitude Data, go to Tracking Plan > Properties and select the Derived Properties tab.
2. Select Add Derived Property.
3. Give your derived property a name.
4. Add any relevant metadata to your property, including a description (optional, unless you want to use the Suggest feature) and the visibility of the property in charts within this project.
5. Enter your formula. Review the list of valid functions and operators below.
6. Select Save.

Preview your results

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

Derived property use cases

Using the referrer URL example, you can write a formula using string operators such as:

SPLIT(PROPERTY('referrer_url','event'), "/", 2)

This formula converts a value like "https://www.google.com/search?q=amplitude" into the value "www.google.com." To strip this down further to "google", wrap the result of a SPLIT function inside another SPLIT function. The resulting formula looks like this:

SPLIT(SPLIT(PROPERTY('referrer_url','event'), "/", 2), ".", 1)

Amplitude also supports math operators. For example, if you have events that contain subtotal and tip properties and want to run analyses based on the total amount, use this formula:

SUM(PROPERTY('subtotal','event'), PROPERTY('tip','event'))

To determine whether a particular order receives a discount when the total order size exceeds $50, use this formula:

IF(SUM(PROPERTY('subtotal','event'), PROPERTY('tip','event')) >= 50, 'true')

Functions and operators

String functions

• REGEXEXTRACT (text_property, regular_expression)
  • Description: Extracts substrings matching the regular expression.
  • Example: REGEXEXTRACT("shirt-150", "[0-9]+")
  • Result: 150
• REGEXREPLACE (text_property, regular_expression, replacement_text)
  • Description: Replaces the property's values with text matching the regular expression with replacement text.
  • Example: REGEXREPLACE("en-US", "-.*", "")
  • Result: en
• CONCAT(property1, property2)
  • Description: Concatenates a property with another property or text value.
  • Example: CONCAT("firstName", "lastName")
  • Result: firstName lastName
• LOWERCASE (text_property)
  • Description: Lower cases all characters in property's values.
  • Example: LOWERCASE("John")
  • Result: john
• UPPERCASE (text_property)
  • Description: Upper cases all characters in property's values.
  • Example: UPPERCASE("John")
  • Result: JOHN
• SPLIT (property, separator, [index])
  • Description: Split a property based on a delimiter and return an array of split elements.  Takes an optional index that returns the element at that index.
  • Example:
    • `SPLIT("a_b_c", "_")
    • SPLIT("john@example.com", "@", 0)`
  • Result:
    • `["a", "b", "c"]
    • "john"`
• REMOVE (property, text)
  • Description: Remove all occurrence of text in property.
  • Example: REMOVE("en-US", "en-")
  • Result: US
• EXTRACT_FROM_DICT (property, text)
  • Description: Extract a value from a dictionary string based on a specific key.
  • Example: EXTRACT_FROM_DICT("{'id': 1, 'name': 'John', 'country': 'US'}", "name")
  • Result: John

Math functions

Object functions

Date/ time functions

Amplitude requires all Unix timestamps to be 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. Amplitude only considers the first value of the other children, even when those other children 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]

Property functions

When you select a property from the Insert Property dropdown, Amplitude inserts a property function referencing that property directly into the editor. You can also manually insert this function wherever you want to reference a different Amplitude property.

These functions are available inside other functions.

Reference Event Time

To use an event's timestamp in a derived property formula, select Event Time from the Insert Property dropdown in the derived property builder.

Inside a derived property formula, Event Time is a long value in epoch milliseconds. To display it as a readable date, wrap it in LONG_TO_TIME or LONG_TO_DATE. Don't wrap Event Time in TIME_TO_LONG or DATE_TO_LONG, because the value is already a long and the conversion returns none. To compare Event Time against a date or datetime property, convert the other property to a long with TIME_TO_LONG or DATE_TO_LONG instead.

Conditional operators

String/numerical operators

Set operators

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

• ==
  • Example:
    • IF(product == ("apple","orange"), "true", "false")
    • product = "apple", Returns "true"
• !=
  • Example:
    • IF(product != ("apple","orange"), "true", "false")
    • product = "banana", Returns "true"

Parallel operators

Perform operations on arrays of data to help perform cart analysis.

Parallel operators require at least one property to be a child cart property, and both properties must be under the same parent property.

For example, products.price and products.quantity are compatible. products.price and shoppinglist.quantity aren't compatible because they have different parent properties.

When you use a parallel operator with two arrays, it adds, multiplies, or compares corresponding values at each position. For example, PARALLEL_SUM([1, 3, 5], [2, 4, 6]) returns [3, 7, 11].

When one input is an array and the other is a scalar (single number), the scalar broadcasts to match the array's length. For example, PARALLEL_SUM([1, 3, 5], 1) returns [2, 4, 6], and PARALLEL_PRODUCT(2, [4, 5, 6]) returns [8, 10, 12].

The derived property that a parallel operator creates becomes a child property of the parent cart property used in the formula. For example, creating a revenue derived property with PARALLEL_PRODUCT(PROPERTY('products.price', 'event'), PROPERTY('products.quantity', 'event')) makes revenue a child property of products.

Parallel operator example

You have a purchase event with the following cart property:

"Products": [{
    "brand": "Apple","categories": "Digital Content","department": "Electronics",
    "price": 24.99,"quantity": 1
}, {
    "brand": "Adidas","categories": "Newsletter","department": "Electronics",
    "price": 24.99,"quantity": 1
}, {
    "brand": "Fossil","categories": "Digital Content","department": "Women's Clothing",
    "price": 24.99,"quantity": 2
}]

Use PARALLEL_PRODUCT to create a Revenue derived property:

PARALLEL_PRODUCT(
    PROPERTY('Products.price', 'event'), 
    PROPERTY('Products.quantity','event')
)

If you add this property to a chart, group by Brand to view revenue by brand.

You can also use SUM_ARRAY on the Revenue derived property to calculate total cart value:

SUM_ARRAY(PROPERTY('Revenue', 'derived'))

This returns the sum of all revenue values across items in the cart, which is useful for filtering or grouping by total cart value.

Common derived properties formulas

This section describes several common use cases for derived properties formulas.

Calculate the age of a customer

CEIL(
    DIVIDE(
        MINUS(
            PROPERTY('server_upload_time', 'amplitude_user'),
            TIME_TO_LONG(PROPERTY('Created At', 'user'))
        ),
        86400000
    )
)

Use this formula when tracking a user property with a date-time data type, and you want to calculate the age of that user (how long that user has existed in your system) since the time of the event that triggered when this user property was set.

Calculate days since registration

Use Event Time with a date or datetime property to calculate how many days passed between an event and a fixed date, such as a user's registration date. In the derived property builder, select Event Time from the Insert Property dropdown, then build a formula like this:

DIVIDE(
    MINUS(
        PROPERTY('Event Time', 'event'),
        TIME_TO_LONG(PROPERTY('registration_date', 'user'))
    ),
    86400000
)

This example assumes registration_date is a datetime string (for example, "2023-08-18 17:37:39"). Inside a formula, Event Time acts as a long in epoch milliseconds. The formula converts the registration date to a long with TIME_TO_LONG, subtracts it from Event Time, and divides by 86400000, the number of milliseconds in one day.

Get the difference between two dates

DIVIDE(  
  MINUS(  
    DATE_TO_LONG(  
        PROPERTY(  
            'start_date', 'user'  
        )  
    ),  
    DATE_TO_LONG(  
        PROPERTY(  
            'end_date', 'user'  
        )  
    )  
  ),  
  86400000   
)

Sample output.

In the derived property above, Amplitude converts the properties end_date and start_date into UNIX timestamps to calculate the difference between them. Amplitude then divides that result by 86400000, the number of milliseconds in one day.

Output a standardized date format

IF(  
    DATE_TIME_FORMATTER(  
        PROPERTY(  
            'publishDate',  
            'event'  
        ),  
        "yyyy-MM-dd'T'HH:mm:ssX",  
        'yyyy-MM-dd'  
    ) 
    contains '-',  
    DATE_TIME_FORMATTER(  
        PROPERTY(  
            'publishDate',  
            'event'  
        ),  
        "yyyy-MM-dd'T'HH:mm:ssX",  
        'yyyy-MM-dd'  
    ),  
    IF(  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd HH:mm:ss",  
            'yyyy-MM-dd'  
        ) 
        contains '-',  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd HH:mm:ss",  
            'yyyy-MM-dd'  
        ),  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd",  
            'yyyy-MM-dd'  
        )  
    )  
)

Sample output.

One way to format dates to Standard Date Format is to use a series of IF statements. Make sure the higher specificity conditional comes first. Replace the $<number> here with the actual properties.

Get the month and year a customer signed up

CONCAT(  
    REGEXEXTRACT(  
        PROPERTY(  
            'start_date',  
            'user'  
        ),  
        'dddd-dd'  
    ), "-01"  
)

In this example, the derived property pulls the sign-up month and year from a property that contains a more detailed value, then appends "-01" to set it to the beginning of the month. Use REGEXEXTRACT() to pull the year and month from the value, and use CONCAT() to append "-01". Replace the $<number> here with the actual properties.

Replace existing property values

IF(
    OR(
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
                ),
            'Casual'
        ) =='Casual',
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
            ),
            '1 Job Posting'
        )=='1 Job Posting',
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
            ),
            '1 Basic'
        )=='1 Basic'
    ),
    'Casual',
    'False'
)

Sample output.

To replace multiple property values, use REGEXEXTRACT() to pull the string from the property, and use OR statements inside an IF statement to check whether the value pulled from the properties contains any of the values you want to replace. In this example, if the property value matches any of the specified values, the formula replaces the value with Casual. Otherwise, the formula replaces the property with False. Replace the $<number> here with the actual properties.

Count length of an array that was accidentally ingested as a string

Convert the string to an array format by using SPLIT:

SPLIT(  
PROPERTY('color', 'event'),  
','  
)

Sample output:

The property color was ingested as a string into Amplitude "Red, Green, Blue". After using SPLIT, the resulting value is [Red, Green, Blue].

Use the above derived property within a new derived property that uses ITEM_COUNT.

ITEM_COUNT(  
PROPERTY("Transform into Array", "derived")  
)

Sample output:

Now the underlying data is an array. ITEM_COUNT counts the number of items that make up the array.