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’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 group 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.
NOTE: Derived properties are currently in Open Beta, and are only available to enterprise customers and customers who have purchased the Govern add-on (formerly known as the Taxonomy add-on).
Create a derived property
NOTE: You must be an Admin or Manager to create a derived property.
To create a derived property, follow these steps:
- In Govern, navigate to Derived Properties tab.
- Click Derive New Property at the top of the table.
- In the Derive New Property modal, enter a new property name (required) and description (optional).
- Enter your formula. See below for the list of valid functions and operators.
- Click Save. Derived properties will be denoted in the Govern properties tables with the 𝑓𝑥 symbol.
Preview your results
As long as the formula you entered was 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 do 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 do this:
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 formulas. There is also a limit of up to 10 property references per derived property.
Functions and operators
String functions
| Function | Description | Example | Result |
|---|---|---|---|
| REGEXEXTRACT (text_property, regular_expression) | Extracts substrings matching the regular_expression | REGEXEXTRACT("shirt-150", "[0-9]+") | "150" |
| REGEXREPLACE (text_property, regular_expression, replacement_text) | Replaces the property's values with text matching the regular_expression with replacement_text | REGEXREPLACE("en-US", "-.*", "") | "en" |
|
CONCAT(property1, property2) |
Concatenates a property with another property or text value. | CONCAT("firstName", "lastName") | "firstName lastName" |
| LOWERCASE (text_property) | Lowercases all characters in property's values | LOWERCASE("John") | "john" |
| UPPERCASE (text_property) | Uppercases all characters in property's values | UPPERCASE("John") | "JOHN" |
| SPLIT (property, separator, [index]) | 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. |
SPLIT("a_b_c", "_")
SPLIT("john@example.com", "@", 0) |
["a", "b", "c"]
"john" |
| REMOVE (property, text) | Remove all occurrence of text in property | REMOVE("en-US", "en-") | |
| EXTRACT_FROM_DICT (property, text) | Extract a value from a dictionary string based on a specific key | EXTRACT_FROM_DICT("{'id': 1, 'name': 'John', 'country': 'US'}", "name") | "John" |
Math functions
| Function | Description | Example | Result |
|---|---|---|---|
|
SUM(num_property1, num_property2) or ADDITION( |
Adds a property with other properties or with numbers. Equivalent to the `+` operator |
SUM(subtotal, tip) >>> SUM(10, 2) |
12 |
|
MINUS(num_property1, num_property2) or SUBTRACT( |
Subtracts a property with other properties or with numbers. Equivalent to the `-` operator. |
MINUS(total, tip) >>> MINUS(12, 2) |
10 |
|
MULTIPLY (num_property1, num_property2) |
Multiplies a property with other properties and/or with numbers. Equivalent to the `*` operator. |
MULTIPLY(price, quantity) >>> MULTIPLY(2.50, 4) |
10 |
|
DIVIDE(numerator, denominator) |
Divides a property by another property or number. Equivalent to the `/` operator. |
DIVIDE(calorie_intake, calorie_goal) >>> DIVID(1000, 2000) |
0.5 |
|
POWER(num_property, exponent) |
Takes the property's values to the exponent power |
POWER(property, 3) >>> POWER(2, 3) |
8 |
|
MIN(num_property1, num_property_2) |
Returns the minimum value between two numbers. |
MIN(5, 10) |
5 |
|
MAX(num_property1, num_property_2) |
Returns the maximum value between two numbers. |
MAX(5, 10) |
10 |
|
CEIL(num_property) |
Rounds up to the nearest integer. |
CEIL(3.8) |
4.0 |
|
FLOOR(num_property) |
Rounds down to the nearest integer. |
FLOOR(3.8) |
3.0 |
Object functions
| Function | Description | Example | Result |
|---|---|---|---|
| EXTRACT_FROM_DICT (property, text) | Extract a value from a dictionary string based on a specific key | EXTRACT_FROM_DICT("{'id': 1, 'name': 'John', 'country': 'US'}", "name") | "John" |
Date/ time functions
| Function | Description | Example | Result |
|---|---|---|---|
|
DATE_TO_LONG (date_property) |
Convert date into unix timestamp |
DATE_TO_LONG("2020-12-01") |
1606780800000 |
|
TIME_TO_LONG (time_property) |
Convert date time (YYYY-MM-dd[T]HH:mm:ss) into unix timestamp |
TIME_TO_LONG("2020-12-01 12:00:00") |
1606780800000 |
|
LONG_TO_TIME (number_property) |
Convert unix timestamp into date-time |
LONG_TO_TIME (1606780800000) |
"2020-12-01 12:00:00" |
|
LONG_TO_DATE (number_property) |
Convert unix timestamp into date |
LONG_TO_DATE (1606780800000) |
"2020-12-01" |
|
DATE_TIME_FORMATTER (datetime_property, old_format, new_format) |
Convert format of a datetime property to a new format. See Java SimpleDateFormat for more details. |
DATE_TIME_FORMATTER ("05.01.2021 12:00:00:000", "MM.dd.yyyy hh:mm:ss:SSS", "yyyy/MM/dd") |
"2021/05/01" |
|
TODAY() |
Current day represented as a long in epoch time in UTC. |
TODAY() - start_date_in_ms >>> 1609459200000 - 1577836800000 |
31622400000 |
Conditional operators
| Operator | Description | Example |
|---|---|---|
|
IF(logical_expression, value_if_true, value_if_false) |
Returns value_if_true if logical_expression is true, otherwise return value_if_false |
IF(price == 0, "true", "false") IF(property == "(none)", "Property was not set", "Property was set") IF(OR(region == "California", region == "New York"), "USA", "Other") |
|
AND(logical_expression_1, logical_expression_2) |
Returns True if both logical expressions are true, false otherwise |
AND(is_subscribed == "true", has_valid_promo == "true") |
|
OR(logical_expression_1, logical_expression_2) |
Returns True if any logical expression is true, false otherwise |
OR(has_email == "true", has_phone == "true") |
|
SWITCH(expression, case_1, value_1, [case_2, value_2 ...], [default]) |
Evaluates an expression and returns values based on defined cases. Returns a default value if no cases are met if defined, otherwise null. |
SWITCH(tier, "gold", 2, "silver", 2, "bronze", 1, 0) |