Custom formulas: Syntax and definitions

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 ten formulas on the same chart, separated by semicolons. 

This article explains the mechanics of custom formulas, with examples of formulas you can use right now.

Feature availability

This feature is available to users on Plus, Growth, and Enterprise plans only. See our pricing page for more details.

Formula syntax

In your formulas, refer to events selected in the Events Module by their corresponding letter. The functions and the parameters aren't case sensitive. You can also perform the following arithmetic operations:

• Parenthesis ()
• Addition (+)
• Subtraction (-)
• Multiplication (*)
• Division (/)

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 displays 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, there is 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 displays 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:

• Percentage (%:)
• Dollars ($:)

List of available formulas

This section lists available formulas by type: Metric, Aggregation, and Function. Click on a formula name to review its syntax.

Metrics formulas

With metrics formulas, you can query on a metric for a particular event that interests you. These formulas are color-coded in green. Each metrics formula requires a letter corresponding to the event you're interested in as a parameter.

Aggregation formulas

Aggregation formulas let you query on a rolling average or rolling window for the metric and event that interests you. These formulas are color-coded in purple. Each aggregation formula requires three components: the metric you are aggregating, the event that interests you, and the interval to aggregate by.

Function formulas

Function formulas let you query on a mathematical function for a particular event and metric you're interested in. These formulas are color coded in blue. Each function formula requires a value that can be either a constant, or another formula containing an event.

Metrics formulas

ACTIVE

Syntax: ACTIVE(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.

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 displays in decimal fraction form. This setup displays the percentage of active users who have triggered the View Item Details event.

ARPAU

Syntax: $:ARPAU(event)

• Event: Refers to the revenue event. This must be a letter that corresponds to an event in the Events card.
• This function will only work if you are grouping by a numerical property on the 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's the same as 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 is a currency.

AVG

Syntax: AVG(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the left module of the chart control panel.

Returns the average number of times users triggered the event. This function is the same as TOTALS(event)/UNIQUES(event). This setup displays 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.

FREQPERCENTILE

Syntax: FREQPERCENTILE(event, percentage)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.
• Percentage: Refers to the percentile that interests you. This value must be less than or equal to 1.

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 aren't in the cohort.

HIST

Syntax: HIST(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.

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)

• Session: Refers to the session that interests you. This must be a letter that corresponds to a session in the Session card.

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.

PERCENTILE

Syntax: PERCENTILE(event, percentage)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.
• Percentage: Refers to the percentile that interests you. This value must be less than or equal to 1.

Returns inputted percentile of the property being grouped by. For example, the following formula returns 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.

PROPAVG

Syntax: PROPAVG(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.
• This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula runs the calculation with the first group-by clause.

Returns the average of the property values you are grouping by. This function is the same as PROPSUM(event)/TOTALS(event).

The following setup displays the average of the revenue generated by completed purchases on a given day.

PROPCOUNT

Syntax: PROPCOUNT(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card. If grouping by multiple properties, the formula runs the calculation with the first group by clause.

Returns the number of distinct property values for the property the event is grouped by. In this setup, the formula retrieves 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 comes from 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. 

PROPCOUNTAVG

Syntax: PROPCOUNTAVG(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card. If grouping by multiple properties, the formula runs the calculation with the first group-by clause.

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 user plays a song, a Play Song or Video event triggers; each played song also captures a Genre_Type event property. Running PROPCOUNTAVG on Play Song or Video grouped by Genre_Type gives you the average number of unique Genre_Type values users who fire PlaySong or Video have.

PROPHIST

Syntax: PROPHIST(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.
• This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula runs the calculation with the first group by clause.

Returns the distribution of the property values you are grouping by over the selected time period. The following setup displays the distribution of revenue over the last 30 days.

PROPMAX

Syntax: PROPMAX(event)

• Event: returns the maximum value of the property you are grouping the specified event by. The property must be numeric. If grouping by multiple properties, the calculation runs using the first group-by clause.

PROPMIN

Syntax: PROPMIN(event)

• Event: returns the minimum value of the property you are grouping the specified event by. The property must be numeric. If grouping by multiple properties, the calculation runs using the first group-by clause.

PROPSUM

Syntax: PROPSUM(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card. The event must be grouped by the property you'd like to sum.
• This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula runs the calculation with the first group-by clause.

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)

• Session: Refers to the session that interests you. This must be a letter that corresponds to a session in the Session card.

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.

REVENUETOTAL

Syntax: $:REVENUETOTAL(event)

• Event: Refers to the revenue event. This must be a letter that corresponds to an event in the Event card.
• This function will only work if you are grouping by a numerical property on the event. Also,

Returns the aggregate sum of the property, formatted as a currency. It's the same as 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 is a currency.

TOTALS

Syntax: TOTALS(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the left module of the chart control panel.

Returns the total number of times a user triggered the event. This setup shows the total number of times users viewed an item's details, plus the total number of times users added an item to a cart.

UNIQUES

Syntax: UNIQUES(event)

• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.

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)

• Session: Refers to the session that interests you. This must be a letter that corresponds to a session in the Session card.

Returns the number of unique users who engaged in sessions 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.

EVENTTOTALS

Syntax: EVENTTOTALS(session)

• Session: Refers to the session that interests you. This must be a letter that corresponds to a session in the Sessions card.

This formula metric is only available in the User Sessions chart. Returns the total number of events triggered during each session.

For example, the following setup shows the number of Page Viewed events across all sessions.

SESSIONTOTALS

Syntax: SESSIONTOTALS(session)

• Session: Refers to the session that interests you. This must be a letter that corresponds to a session in the Sessions card.

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. 

Aggregation formulas

CUMSUM

Syntax: CUMSUM(metric, event)

• Metric: The metric you wish to aggregate. This is one of the metrics formulas listed above.

Returns the metric for selected event with a running total of days/weeks/months over the chart's time frame. 

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 is a sum of revenue generated on February 20th, 21st, and 22nd.

CUMSUM(UNIQUES,A) returns a deduplicated count of unique users for each data point. 

ROLLAVG

Syntax: ROLLAVG(metric, event, # of intervals)

• Metric: The metric you wish to aggregate. This is one of the metrics formulas listed above.
• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.
• Number of intervals: The number of five-minute intervals, hours, days, weeks, or months to include in the rolling average. For example, a daily chart allows rolling averages over daily intervals only. The maximum ranges for a rolling average are 36 five-minute intervals (this works out to three hours), 72 hours, 90 days, 12 weeks, or 12 months.

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.

ROLLWIN

Syntax: ROLLWIN(metric, event, # of five-minute intervals/hours/days/weeks/months)

• Metric: The metric you wish to aggregate. This is one of the metrics formulas listed above.
• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.
• Number of intervals: The number of five-minute intervals, hours, days, weeks, or months to include in the rolling average. For example, a daily chart allows rolling averages over daily intervals only. The maximum ranges for a rolling average are 36 five-minute intervals (this works out to three hours), 72 hours, 90 days, 12 weeks, or 12 months.

Returns the metric for the event selected with a rolling window of the number of days, weeks, or months you specify, where a rolling window aggregation applies 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.

The day (or week, or month) the chart displays is the last day of the window.

ROLLWINBEFORE

Syntax: ROLLWINBEFORE(metric, event, # of five-minute intervals/hours/days/weeks/months)

• Metric: The metric you wish to aggregate. This is one of the metrics formulas listed above.
• Event: Refers to the event that interests you. This must be a letter that corresponds to an event in the Events card.
• Number of intervals: The number of five-minute intervals, hours, days, weeks, or months to include in the rolling average. For example, a daily chart allows rolling averages over daily intervals only. The maximum ranges for a rolling average are 36 five-minute intervals (this works out to three hours), 72 hours, 90 days, 12 weeks, or 12 months.

Returns the metric for the event selected with a rolling window of however many days/weeks/months inputted, where a rolling window aggregation applies 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.

Function formulas

EXP

Syntax: EXP(value)

• Value: The value can be a constant or another function (for example, the value you pass in could be UNIQUES of an event). The maximum value accepted is 700.

Returns e to the power of value you have specified. For example, this formula computes e to the power of the average number of times users purchase tickets.

LN

Syntax: LN(value)

• Value: The value can be a constant or another function (for example the value you pass in could be 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.

LOG

Syntax: LOG(value, base)

• Value: The value can be a constant or another function (for example the value you pass in could be TOTALS of an event).
• Base: A constant. The base must be a constant and can't contain another function.

Returns the logarithm of the value to the base. For example, the following formula returns the logarithm of the count of unique active users to base 3.

LOG10

Syntax: LOG10(value)

• Value: The value can be a constant or another function (for example the value you pass in could be AVG of an event).

Returns the logarithm of the value to base 10. For example, the following formula returns the logarithm of the average number of times users triggered Complete Purchase to base 10.

POWER

Syntax: POWER(value, exponent)

• Value: The value can be a constant or another function (for example the value you pass in could be TOTALS of an event).
• Exponent: A constant. The exponent must be a constant and can't contain another function.

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.

SQRT

Syntax: SQRT(value)

• Value: The value can be a constant or another function (for example the value you pass in could be AVG of an event).

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.

TRENDLINE

Syntax: TRENDLINE(value)

• Value: The value can be a constant or another function (for example the value you pass in could be UNIQUES of an event).

Returns the ordinary least-squares linear regression trendline of the value. You should strongly consider plotting another custom formula alongside this one, so you can compare them. Otherwise, the TRENDLINE function simply gives 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.