In an Event Segmentation chart, the Formula tab in the bottom module of the chart control panel allows you to write formulas to perform or calculate specific analyses and metrics on events with greater flexibility. Custom formulas are also useful if you want to perform comparisons between various different analyses on the same Event Segmentation chart.
This document will walk through the details of this feature as well as examples of formulas you can use. Custom formulas serve two major purposes:
- You can plot up to six formulas on the same chart, separated by semicolons (;).
- Choose from 20 custom formulas to plot the metrics you wish to see on a chart.
The text box will autocomplete with suggestions for formulas to use and each formula is color coded depending on the type of formula it is.
Table of Contents
Syntax
In your formulas, you will have to refer to events you have selected in the left module of the chart control panel by the letter that corresponds to the event. Note: The functions and the parameters are not 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) below refers to the event 'Play Song or Video' while the letter B in the formula UNIQUES(B) below refers to the event 'Download Song or Video'. This setup will display the ratio of users who played a song or a video to users who downloaded a song or video.
In addition, you can write a formula that consists of events with each event being grouped by a property or properties. However, in order 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', then 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, as shown here. 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 discover how many more times users in Cohort One fire a particular event than do users in Cohort B.
To compare a metric between two different cohorts or user segments, simply 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.
In addition to arithmetic operations, you are also able to add the following prefixes to view your metrics in percentages or dollars:
- Percentage (%:)
- Dollars ($:)
Metrics Formulas
Metrics formulas allow you to 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 that corresponds to the event you are interested in as a parameter.
ACTIVE
Syntax: ACTIVE(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
The ACTIVE formula returns the percent (decimal fractions) of active users who performed the event. This is the same as the "Active %" metric in the bottom module except in decimal fraction form. The below setup will display the percentage of active users who have triggered the 'Play Song or Video' event.
We can see that on September 25th, 82.21% of active users played a song or a video.
AVG
Syntax: AVG(event)
- Event: Refers to the event you are interested in. 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 the event was performed. This function is equivalent to TOTALS(event)/UNIQUES(event). The below setup will display the ratio of number of times 'Play Song or Video' was triggered to the number of times 'Download Song or Video' was performed, the average number of times 'Play Song or Video' was performed, as well as the average number of times 'Download Song or Video' was performed on the same chart.
We can see that the average number of times people play songs or videos is higher than the average number of times people download songs or videos.
TOTALS
Syntax: TOTALS(event)
- Event: Refers to the event you are interested in. 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 the event was performed. The below setup will show the total number of times a song or video was purchased plus the total number of times a ticket was purchased.
We can see that on September 17th, there were 250,023 purchase events triggered. We can also see that the total number of purchase events is increasing for our product!
UNIQUES
Syntax: UNIQUES(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
Returns the number of unique users who performed the event. For example, the following setup shows the ratio of users who played a song or a video to the users who downloaded a song or video.
On September 21st, the ratio of users who fired the 'Play Song or Video' event to the users who fired the 'Download Song or Video' event was 1.07.
HIST
Syntax: HIST(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
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 'Play Song or Video' event.
We can see that in the last 30 days, 137,927 users played a song or a video between 21-50 times.
FREQPERCENTILE
Syntax: FREQPERCENTILE(event, percentage)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
- Percentage: Refers to the percentile you are interested in. This must be a value that is 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 'Play Song or Video' event.
We can see here that 90% of users play 6 or fewer songs or videos on average across the last 30 days. This means that our top 10% of users play more than 6 songs or videos a day.
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.
PERCENTILE
Syntax: PERCENTILE(event, percentage)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
- Percentage: Refers to the percentile you are interested in. This must be a value that is less than or equal to 1.
Important Note:
- 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 the 'Duration' of all 'Play Song or Video' events.
This chart shows that on September 24th, 90% of all songs or videos played were 6,300.2 seconds long or less. 
Another example where the PERCENTILE formula can be useful is if you are tracking load times for your product and you want to make sure that a certain percentage of load times is below a certain threshold.
PROPSUM
Syntax: PROPSUM(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel. The event must be grouped by the property you'd like to sum.
Important Note:
- This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula will perform 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 average total seconds each user played a song or video per day over the last 30 days.
On September 28th, the chart is showing us that the average length users played songs or videos was 7,882.07 seconds. Note that this metric is different from the Property Average metric, which shows the average length of videos or songs played per event per day, e.g. PROPSUM(A)/TOTALS(A) if you were to do it with custom formulas.
PROPAVG
Syntax: PROPAVG(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
Important Note:
- This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula will perform the calculation with the first group by clause.
Returns the average of the property values you are grouping by. This function is equivalent to PROPSUM(event)/TOTALS(event). The following setup will show you the 90th percentile for the song or video length as well as the property average of all songs or videos played on a given day.
The chart shows us that on July 31st, the average duration of songs or videos played was 3,506.24 seconds. In comparison, 90% of all songs or videos played in the last 30 days were no longer than 6,500 seconds.
PROPHIST
Syntax: PROPHIST(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
Important Note:
- This function will only work if you are grouping by a numerical property on the event. If grouping by multiple properties, the formula will perform 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 will display the distribution of song or video length over the last 30 days.
We can see that there is a pretty even distribution of 'Duration'. In the last 30 days, 145,011 songs and videos played were between 4100 and 4200 seconds long.
PROPCOUNT
Syntax: PROPCOUNT(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel. If grouping by multiple properties, the formula will perform the calculation with the first group by clause.
Returns the number of distinct property values for the property the event is grouped by. For example, let's say you care about not the total number of tickets purchased or the total number of users who purchased a ticket but instead want to know the unique concerts users purchased tickets for. If each concert has a unique 'Content_ID', then you can use the PROPCOUNT formula to count the unique number of concerts users purchased tickets to on a given day.
This data point is telling us that on September 23rd, there were 9,996 unique 'Content_ID' event property values, meaning that users purchased tickets to 9,996 unique concerts.
PROPCOUNTAVG
Syntax: PROPCOUNTAVG(event)
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel. If grouping by multiple properties, the formula will perform the calculation with the first group by clause.
Returns the average number of distinct values each user has for a specified property. For example, let's say 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 fire; each fired song also captures a "Genre_Type" event property. 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.
The data point is telling us that on November 22nd, each user who fired "Play Song or Video" on average played songs belonging to 2.38 unique genre types.
REVENUETOTAL
Syntax: $:REVENUETOTAL(event)
- Event: Refers to the revenue event. This must be a letter that corresponds to an event in the left module of the chart control panel.
Important Note:
- This function will only work if you are grouping by a numerical property on the event. Also, the "$:" prefix is optional, and simply ensures that the output format will be as a currency.
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:
ARPAU
Syntax: $:ARPAU(event)
- Event: Refers to the revenue event. This must be a letter that corresponds to an event in the left module of the chart control panel.
Important Note:
- This function will only work if you are grouping by a numerical property on the event. Also, the "$:" prefix is optional, and simply ensures that the output format will be as a currency.
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:
Aggregation Formulas
Aggregation formulas allow you to 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 the metric you are aggregating, the event you are interested in, and the interval to aggregate by.
ROLLAVG
Syntax: ROLLAVG(metric, event, # of intervals)
- Metric: The metric you wish to aggregate. This will be one of the metrics formulas listed above.
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
- Number of intervals: The number of five-minute intervals, hours, days, weeks, or months to perform a rolling average over. For example, if you are looking at a daily chart, then you can only perform a rolling average over daily intervals. Note: The maximum range you can select for a rolling average is 36 five-minute intervals (3 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 segment shows your daily active users and the green segment below shows the weekly rolling average. This is useful to see if your daily active user count is higher or lower than the rolling average. We can see that on August 2nd, we had 134,249 active users, which was higher than the rolling average on that day.
ROLLWIN
Syntax: ROLLWIN(metric, event, # of five-minute intervals/hours/days/weeks/months)
- Metric: The metric you wish to aggregate. This will be one of the metrics formulas listed above.
- Event: Refers to the event you are interested in. This must be a letter that corresponds to an event in the left module of the chart control panel.
- Number of intervals: The number of five-minute intervals, hours, days, weeks, or months to perform a rolling window over. For example, if you are looking at a daily chart, then you can only perform a rolling window over daily intervals. Note: The maximum range you can select for a rolling window is 36 five-minute intervals (3 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. For example, this chart would show you your daily active user to your monthly active user ratio.
CUMSUM
Syntax: CUMSUM(metric, event)
- Metric: The metric you wish to aggregate. This will be 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 timeframe. For example, the chart below shows you a daily cumulative sum of revenue from "Complete Purchase" events in the last 30 days. The data point for April 19th will be a sum of revenue generated on April 17th, April 18th, and April 19th.
For CUMSUM(UNIQUES,A), a deduplicated count of unique users will be returned in each data point.
- On 4/17, User A fired "Complete Purchase".
- On 4/18, User A and User B fired "Complete Purchase".
- On 4/19, User C and User D fired"Complete Purchase".
On the data point for 4/19, a total count of 4 will be returned because 4 unique users fired this event from 4/17 - 4/19.
Function Formulas
Function formulas allow you to query on a mathematical function for a particular event and metric you are interested in. These formulas will be color coded in blue. Each function formula requires a value that can be a constant or another formula containing an event.
TRENDLINE
Syntax: TRENDLINE(value)
- Value: The value can be a constant or another function (e.g. the value you pass in could be 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 that you can perform a comparison. Otherwise, the TRENDLINE function will simply give you a straight line with no context on a chart. An example of this function is to see the trendline of number of users who purchase songs or videos and compare it to the unique number of users.
We can see that the trendline shows us this metric is steadily increasing over time, which means any product changes that are being made to increase purchases of songs or videos is most likely making an impact.
EXP
Syntax: EXP(value)
- Value: The value can be a constant or another function (e.g. the value you pass in could be UNIQUES of an event). Note: The maximum value accepted is EXP(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.
LOG
Syntax: LOG(value, base)
- Value: The value can be a constant or another function (e.g. the value you pass in could be TOTALS of an event).
- Base: A constant. Note: The base must be a constant and cannot contain another function.
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.
LOG10
Syntax: LOG10(value)
- Value: The value can be a constant or another function (e.g. 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 will return the logarithm of the average number of times 'Purchase Song or Video' was performed to base 10.
LN
Syntax: LN(value)
- Value: The value can be a constant or another function (e.g. 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, the following formula would calculate the natural logarithm of the number of unique users who shared a song or video.
POWER
Syntax: POWER(value, exponent)
- Value: The value can be a constant or another function (e.g. the value you pass in could be TOTALS of an event).
- Exponent: A constant. Note: The exponent must be a constant and cannot contain another function.
Returns the inputted value to the power of the exponent specified. For example, the following formula returns the number of unique users who searched a song or video squared.
SQRT
Syntax: SQRT(value)
- Value: The value can be a constant or another function (e.g. the value you pass in could be AVG of an event).
Returns the square root of the value. For example, the following formula would return the square root of the total number of times users skipped ads.
