Derived properties: Add new event and user properties retroactively

Updated

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:

  1. In Govern, navigate to Derived Properties tab.
  2. Click Derive New Property at the top of the table.
  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. 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 the formula. There is also a limit of up to 10 property references per derived property.

Using derived properties to calculate age

Many Amplitude customers want to calculate the ages of their customers. You can do this using derived properties:

  1. Subtract the Unix timestamp of the customer's date of birth from today's date
  2. Divide by the number of milliseconds in a year (31,536,000,000)
  3. Round down with floor
FLOOR( 
  DIVIDE( 
    MINUS( 
      TODAY(),
      DATE_TO_LONG(birthday)
    ),
    31536000000 
  )
)

Functions and operators

String functions

Function Description Example Result
REGEXEXTRACT (string_property, regular_expression)
Extracts the first substring from the property matching the regular_expression.
 REGEXEXTRACT("shirt-150", "[0-9]+") "150"
REGEXREPLACE (string_property, regular_expression, replacement_text) Replaces all substrings in the property that match regular_expression with replacement_text. REGEXREPLACE("en-US", "-.*", "") "en"

CONCAT (string_property1, string_property2)
Concatenates two string properties or text values. Can take 2 or more inputs.
 CONCAT("firstName", " ", "lastName") "firstName lastName"
LOWERCASE (string_property) Lowercases all characters in the property. LOWERCASE("John") "john"
UPPERCASE (string_property) Uppercases all characters in the property. UPPERCASE("John") "JOHN"
SPLIT(string_property, delimiter[, 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 (string_property, text)
Remove all occurrences of specified text in the property.
 REMOVE("en-US", "en-") "US"
EXTRACT_FROM_DICT (string_property, key)
Interpret the property as a serialized JSON object and extract the value at the specified 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 (num_property1, num_property2)

Adds a property with other properties or with numbers. Equivalent to the `+` operator. Can take 2 or more inputs.

SUM(subtotal, tip) >>>  SUM(10, 2)

12

MINUS(num_property1, num_property2) or SUBTRACTION (num_property1, num_property2)

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 or with numbers. Equivalent to the `*` operator. Can take 2 or more inputs.

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)

Returns the property to the power of the exponent.

POWER(property, 3) >>> POWER(2, 3)

8

MIN(num_property1, num_property_2)

Returns the minimum value among two or more properties or numbers.

MIN(5, 10)

5

MAX(num_property1, num_property_2)

Returns the maximum value among two or more properties or numbers.

MAX(5, 10)

10

CEIL(num_property)

Rounds property up to the nearest integer.

CEIL(3.8)

4.0

FLOOR(num_property)

Rounds property down to the nearest integer.

FLOOR(3.8)

3.0

ROUND(num_property)

Rounds the property to the nearest integer.

ROUND(3.8)

4.0

 

Date/time functions

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

Function Description Example Result

DATE_TO_LONG (string_property)
Converts a date string (YYYY-MM-dd) into a Unix timestamp.

DATE_TO_LONG("2020-12-01")

1606780800000

TIME_TO_LONG (string_property)

Converts a datetime string (YYYY-MM-dd[T]HH:mm:ss) into a Unix timestamp.

TIME_TO_LONG("2020-12-01 00:00:00")

1606780800000

LONG_TO_DATE (number_property)
Converts a Unix timestamp to a date string (YYYY-MM-dd).

LONG_TO_DATE (1606780800000)

"2020-12-01"

LONG_TO_TIME (number_property)

Converts a Unix timestamp into a datetime string (YYYY-MM-ddTHH:mm:ss).

LONG_TO_TIME (1606780800000)

"2020-12-01 00:00:00"

DATE_TIME_FORMATTER (string_property, old_format, new_format)

Converts a datetime property from one format to another.

See Java SimpleDateFormat for more details.

DATE_TIME_FORMATTER ("05.01.2021 00:00:00.000", "MM.dd.yyyy HH:mm:ss.SSS", "yyyy/MM/dd")

 "2021/05/01"

TODAY()

Returns the start of the current UTC day as a Unix timestamp.

TODAY() - start_date_in_ms >>> 1609459200000 - 1577836800000 

31622400000

EVENT_DAY_OF_WEEK()

Returns the day of the week of the event time based on the project timezone.

EVENT_DAY_OF_WEEK()

"Monday"

EVENT_HOUR_OF_DAY()

Returns the hour of the day of the event time based on the project timezone (0-23).

EVENT_HOUR_OF_DAY() 

10

 

Array functions

Function Description Example Result

ITEM_COUNT(property)

Returns the length of an array property, or 1 for non-array properties.

ITEM_COUNT(products*)

 

*products is an array property (e.g. ["apple", "orange", "banana"])

3

GREATEST(property)

Returns the maximum value within an array property.

GREATEST(prices*)

 

*prices is an array property (e.g. [3.5, 10, 2])

10

LEAST(property)

Returns the minimum value within an array property.

LEAST(prices*)

 

*prices is an array property (e.g. [3.5, 10, 2])

2

COALESCE(property)

Returns the first non-null value within an array property.

COALESCE(locations*)

 

*locations is an array property (e.g. [null, "California", "New York"])

"California"

 

Conditional functions

Operator Description Example

IF(boolean_property, value_if_true, value_if_false)

Given a property or expression, returns one of two values depending on whether the property or expression evaluates to true or 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(boolean_property1, boolean_property2)

Returns true if ALL of the properties or expressions provided evaluate to true.

AND(is_subscribed == "true", has_valid_promo == "true")

OR(boolean_property1, boolean_property2)

Returns true if ANY of the properties or expressions provided evaluate to true.

OR(has_email == "true", has_phone == "true")

SWITCH(property, case_1, value_1, [case_2, value_2 ...], [default])

Evaluates a property or expression and returns values based on defined cases. Returns a default value if no cases are met (null if not defined).

SWITCH(tier, "gold", 2, "silver", 2, "bronze", 1, 0)

 

Property functions

Function Description Example Result
PROPERTY (property_name, property_type)

Returns a property of a specified type ("event", "user", or "group") and name from the event.

If the type is "group," a third argument containing the group_type must be provided.

 PROPERTY('email', 'event')

"datamonster@ amplitude.com"

 

Boolean expression operators

Unlike the functions listed in the tables above, these operators are infix rather than prefix (see examples).

Operator

Example

==

action == “purchase”

!=

item_count != 0

contains

email contains “@gmail.com”

does not contain

title does not contain “officer”

<, <=, >, >=

duration >= 60

glob match

url glob match “https://www.google.*/*”

glob does not match

query glob does not match “*/query=*“

has prefix

title has prefix “sir”

 



Was this article helpful?
19 out of 25 found this helpful
Have more questions? Check out the Amplitude Community.
Return to top

Related articles