This article helps you:
Understand and use custom formulas in Amplitude to create exactly the analysis you need
In an Event Segmentation or Data Table chart, the Formula option in the Measured As module's Advanced drop down offers you greater flexibility when performing analyses. Custom formulas are also useful for comparing various analyses on the same chart.
Choose from more than 20 custom formulas to plot the metrics you need. You can plot up to six formulas on the same chart, separated by semicolons.
This article will describe the mechanics of custom formulas, with examples of formulas you can use right now.
While the Experiment Results chart also uses formula metrics, it does so in a different way than either the Event Segmentation or Data Table charts. To learn more about those differences, see this Help Center article on using formula metrics in Amplitude's Experiment Results chart.
This feature is available to users on Plus, Growth, and Enterprise plans only. See our pricing page for more details.
In your formulas, refer to events selected in the Events Module by their corresponding letter. The functions and the parameters are not case sensitive. You can also perform the following arithmetic operations:
For example, the letter A in the formula UNIQUES(A)
refers to the event View Item Details
while the letter B in the formula UNIQUES(B)
refers to the event Add Item to Cart
. This setup will display the ratio of users who viewed an item's details to users who placed an item in their cart.
You can also write a formula that consists of events, grouping each event by a property or properties. However, for the formula to be valid, the properties must have matching values across all events you are segmenting.
For example, if you have an event called Page Name
, the following property values would not match:
Tutorial
and TUTORIAL
(the matching is case sensitive)1
and 1.0
(non-matching characters)The order in which you are grouping properties by matters as well. Both events must have the grouped by values in the same order; otherwise, you will see a warning that events have no matching group by values.
You can also use custom formulas to uncover how many more times users in one cohort trigger a particular event than do users in another cohort.
To compare a metric between two different cohorts or user segments, add the number of the segment to the letter designating the event: UNIQUES(A1)/UNIQUES(A2)
. This will display a ratio of the performance of your cohorts on the same event as a single plotted line on your graph.
You can also view your metrics in percentages or dollars by adding the following prefixes to your formula:
This section lists available formulas by type: Metric, Aggregation, and Function. Click on a formula name to review its syntax.
With metrics formulas, you can query on a metric for a particular event you are interested in. These formulas will be color-coded in green. Each metrics formula requires a letter corresponding to the event you're interested in as a parameter.
ACTIVE | ARPAU | AVG | FREQPERCENTILE |
HIST | PERCENTILE | PROPAVG | PROPCOUNT |
PROPCOUNTAVG | PROPHIST | PROPMAX | PROPMIN |
PROPSUM | REVENUETOTAL | TOTALS | UNIQUES |
EVENTTOTALS | SESSIONTOTALS |
Aggregation formulas let you query on a rolling average or rolling window for the metric and event you are interested in. These formulas will be color-coded in purple. Each aggregation formula requires three components: the metric you are aggregating, the event you are interested in, and the interval to aggregate by.
CUMSUM | ROLLAVG | ROLLWIN | ROLLWINBEFORE |
Function formulas let you query on a mathematical function for a particular event and metric you're interested in. These formulas will be color coded in blue. Each function formula requires a value that can be either a constant, or another formula containing an event.
EXP | LN | LOG | LOG10 |
POWER | SQRT | TRENDLINE |
Syntax: ACTIVE(event)
The ACTIVE
formula returns the percent of active users who triggered the event. This is the same as the Active %
metric in the Measured card, but here it is displayed in decimal fraction form. This setup will display the percentage of active users who have triggered the View Item Details
event.
Syntax: $:ARPAU(event)
Returns the aggregate sum of the revenue event property formatted as a currency, divided by the number of unique active users in that same time period. It is equivalent to PROPSUM(event) / UNIQUES(any active event)
.
For example, the following setup shows the average revenue per active user of a generic e-commerce company:
As you can see in the screenshot above, the $:
prefix is optional. Its presence simply ensures the output format will be as a currency.
ARPAU cannot be used in conjunction with aggregation formulas.
Syntax: AVG(event)
Returns the average number of times the event was triggered. This function is equivalent to TOTALS(event)/UNIQUES(event)
. This setup will display the ratio of number of times View Item Details
was triggered to the number of times Add Item to Cart
was triggered, the average number of times View Item Details
was triggered, as well as the average number of times Add Item to Cart
was triggered on the same chart.
Syntax: FREQPERCENTILE(event, percentage)
Returns the inputted percentile event frequency across all users. A percentile is a measure that indicates the value below which a given percentage of values fall. For example, the following formula shows the 90th percentile of users who triggered the View Item Details
event.
You can then take this information and create a behavioral cohort of your power users and further analyze them to see what distinguishes them from users who are not in the cohort.
Syntax: HIST(event)
Returns the distribution of the event frequency per unique user over the selected time period. The following setup displays the distribution of event frequency for the Complete Purchase
event.
We can see that in the last 30 days, 22,075 users completed purchases five times.
The syntax for HIST varies slightly for the User Sessions chart as sessions are the focus of the metrics.
Syntax: HIST(session)
Returns the distribution of session durations (in seconds) over the selected time period. The following setup displays the distribution of the durations of all sessions.
Syntax: PERCENTILE(event, percentage)
This function will only work if you are grouping by a numerical property on the event.
Returns inputted percentile of the property being grouped by. For example, the following formula will return the 90th percentile for revenue of all Complete Purchase
events.
Another example where the PERCENTILE formula can be useful is if you're tracking load times for your product, trying to ensure that a particular percentage of load times is below a certain threshold.
Syntax: PROPAVG(event)
Returns the average of the property values you are grouping by. This function is equivalent to PROPSUM(event)/TOTALS(event)
.
The PROPAVG formula ignores events where the selected property value is (none)
.
The following setup will show the average of the revenue generated by completed purchases on a given day.
Syntax: PROPCOUNT(event)
Returns the number of distinct property values for the property the event is grouped by. In this setup, the formula will retrieve the number of different departments covering all the items for which details were viewed:
Note that PROPCOUNT
is an estimate of distinct property values. This estimate is generated by a HyperLogLog algorithm, and its accuracy depends on amount of data it has to work with. Expect a relative error in the range of 0.1% for less than 12,000 unique values, and up to 0.5% for more than 12,000 unique property values, depending on the cardinality of the property.
Syntax: PROPCOUNTAVG(event)
Returns the average number of distinct values each user has for a specified property.
For example, imagine you're interested in the average number of song genres your music app subscribers listen to. Every time a song is played, a Play Song or Video
event will trigger; each played song also captures a Genre_Type
event property. Running PROPCOUNTAVG
on Play Song or Video
grouped by Genre_Type
will give you the average number of unique Genre_Type
values users who fire PlaySong or Video
have.
Syntax: PROPHIST(event)
Returns the distribution of the property values you are grouping by over the selected time period. The following setup will display the distribution of revenue over the last 30 days.
Syntax: PROPMAX(event)
Syntax: PROPMIN(event)
Syntax: PROPSUM(event)
Returns the sum of the property values you are grouping the specified event by. For example, this visualization shows the total revenue generated by the Complete Purchase
event.
The syntax for PROPSUM varies slightly for the User Sessions chart as sessions are the focus of the metrics.
Syntax: PROPSUM(session)
Returns the total time (sum of the duration in seconds) of the specified session. For example, the following chart shows the total time (in seconds) summed across all sessions.
Syntax: $:REVENUETOTAL(event)
Returns the aggregate sum of the property, formatted as a currency. It is equivalent to PROPSUM(event)
. For example, the following setup shows the total revenue by day generated by purchases:
As you can see in the screenshot above, the $:
prefix is optional. Its presence simply ensures the output format will be as a currency.
Syntax: TOTALS(event)
Returns the total number of times the event was triggered. This setup will show the total number of times an item's details were viewed, plus the total number of times an item was added to a cart.
Syntax: UNIQUES(event)
Returns the number of unique users who triggered the event. For example, the following setup shows the ratio of users who viewed an item's details to the users who added an item to their cart.
The syntax for UNIQUES varies slightly for the User Sessions chart as sessions are the focus of the metrics.
Syntax: UNIQUES(session)
Returns the number of unique users who engaged in session(s) defined by the specified session. For example, the following setup shows the ratio of users who engaged in sessions longer than one minute to the users who engaged in sessions that contained at least one Search Items
event.
Syntax: EVENTTOTALS(session)
This formula metric is only available in the User Sessions chart. Returns the total number of events that were triggered during each session.
For example, the following setup shows the number of Page Viewed
events across all sessions.
Syntax: SESSIONTOTALS(session)
This formula metric is only available in the User Sessions chart. It returns the number of sessions defined by the specified session.
For example, the following setup shows the total number of sessions by day over the last 30 days for all users in the United Kingdom who completed at least one Add to Cart
event during each session.
Syntax: CUMSUM(metric, event)
Returns the metric for selected event with a running total of days/weeks/months over the chart's timeframe.
For example, this chart shows a daily cumulative sum of revenue from Complete Purchase
events in the last 30 days. The data point for February 22nd will be a sum of revenue generated on February 20th, 21st, and 22nd.
For CUMSUM(UNIQUES,A)
, a deduplicated count of unique users will be returned for each data point.
Syntax: ROLLAVG(metric, event, # of intervals)
Returns the metric for the event selected with a rolling average over the interval selected. For example, the following chart shows you your weekly rolling average superimposed on top of your daily active users.
The blue line in this chart shows daily active users and the green line shows the weekly rolling average. This is useful to see if your daily active user count is higher or lower than the rolling average.
Syntax: ROLLWIN(metric, event, # of five-minute intervals/hours/days/weeks/months)
Returns the metric for the event selected with a rolling window of however many days/weeks/months inputted, where a rolling window aggregation is applied after a cohort filter, if one is in use.
For example, this chart first calculates the new users for each time interval and then performs the rolling window aggregation on top of that.
Syntax: ROLLWINBEFORE(metric, event, # of five-minute intervals/hours/days/weeks/months)
Returns the metric for the event selected with a rolling window of however many days/weeks/months inputted, where a rolling window aggregation is applied before a cohort filter, if one is in use.
For example, this chart first calculates the rolling active users for each time interval and then applies the new user cohort filter on top of that.
Syntax: EXP(value)
UNIQUES
of an event). The maximum value accepted is 700.Returns e to the power of value you have specified. For example, here we are computing e to the power of the average number of times users purchase tickets.
Syntax: LN(value)
UNIQUES
of an event).Returns the natural logarithm of the value. This is logarithm to the base of mathematical constant e. For example, LN(UNIQUES(A))
would calculate the natural logarithm of the number of unique users who triggered event A.
Syntax: LOG(value, base)
TOTALS
of an event).Returns the logarithm of the value to the base. For example, the following formula will return the logarithm of the count of unique active users to base 3.
Syntax: LOG10(value)
AVG
of an event).Returns the logarithm of the value to base 10. For example, the following formula will return the logarithm of the average number of times Complete Purchase
was triggered to base 10.
Syntax: POWER(value, exponent)
TOTALS
of an event).Returns the inputted value to the power of the exponent specified. For example, POWER(UNIQUES(A), 2)
returns the number of unique users who triggered event A.
Syntax: SQRT(value)
Returns the square root of the value. For example, SQRT(TOTALS(A))
would return the square root of the total number of times users triggered event A.
Syntax: TRENDLINE(value)
UNIQUES
of an event).Returns the trendline of the value. This is calculated with ordinary least-squares linear regression. It is highly recommended that you plot another custom formula alongside this one, so you can compare them Otherwise, the TRENDLINE
function will simply give you a straight line with no context on a chart.
For example, use this function to see the trendline of number of users who purchase a song or video and compare it to the unique number of users.
Thanks for your feedback!
June 10th, 2024
Need help? Contact Support
Visit Amplitude.com
Have a look at the Amplitude Blog
Learn more at Amplitude Academy
© 2024 Amplitude, Inc. All rights reserved. Amplitude is a registered trademark of Amplitude, Inc.