Derived properties: Add new event and user properties retroactively – Amplitude

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.

Screen_Shot_2021-08-03_at_2.35.25_PM.png

NOTE: Derived properties is 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.

Screen_Shot_2021-08-03_at_12.46.09_PM.png

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

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

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
EVENT_HOUR_OF_DAY()	Get hour of day from the event's timestamp. (0-23)	EVENT_HOUR_OF_DAY()	10
EVENT_DAY_OF_WEEK()	Get day of week from the event's timestamp as string. i.e. Monday	EVENT_DAY_OF_WEEK()	Monday

Array functions

Function	Description	Example	Result
ITEM_COUNT (property)	Length of array property; defaults to 1 for non-arrayed properties	ITEM_COUNT ([product1, product2, product3])	3 (products)

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)