This User Guide is for the Formulas feature of Countly 20.11. To see the Formulas User Guide for the latest version of Countly, i.e., Countly 22.03, please click here.
Countly offers a vast set of application metrics and visualization interfaces. Those individual indicators tell you a lot about your application, but you can benefit further by combining them. Our Formulas feature helps you with this by letting you create customizable formulas to better visualize all these metrics.
The Formulas plugin is available only in Countly Enterprise.
Benefitting from Formulas
You can build many different mathematical expressions depending on your data and business needs. Here are a few examples:
-
Number of sessions / Number of users: Average session count per user
-
Total purchase count / Number of users who purchased something: Average number of purchases per user
-
Event Return/Refund count / Event Purchase count: Percentage of purchases which are returned and refunded
-
Sum of Total Amount segment of Event Purchase / Event Purchase count: Average amount paid per purchase
-
Sum of Total Amount segment of Event Purchase / Number of users who purchased something: Average amount paid per user
Setting Up Formulas
First of all, make sure Formulas is enabled. To do so, in the main Countly Dashboard, go to Management > Plugins and enable the Formulas toggle.
After that, you will find Formulas in the Explore section of your Countly Dashboard.
Building a Formula
The Formulas screen has two main halves.
The upper one is the Formula Builder, which lets you:
- Build formulas
- Add, edit, or delete parameters
- Set the operators (plus, minus, multiply, divide) between parameters
- Group parameters using parentheses
- Load saved formulas (2)
- Configure the formula output (1)
- Save and execute formulas (5).
The lower half, with the Chart and Table tabs, displays the output of your formula.
In the Formula Builder, each of the boxes with dashed borders (A and B) represents a parameter. A parameter has two states: empty and configured. In the image above, A and B are currently empty parameters.
The Denominator is enabled by default. It helps you visualize some formulas which has divisions in it. Nonetheless, you can always click on Use denominator (3) to disable or enable it.
To add new parameters, click on the + buttons (4).
Parameters
A formula with an empty parameter will not be executed. Also, you cannot add a new parameter to a row when there is an empty parameter in that same row.
Parameters are the fundamental units of a formula. Each parameter yields a scalar value and your formula uses them to compute the final value of a time bucket. In other words, a formula is evaluated for each time bucket for the selected period. Buckets are independent, so they do not affect each other.
A formula may have up to 8 parameters. When you reach 8, the + buttons will be disabled automatically.
The table below shows the list of parameters you may select:
| Type | Description | Returns | Arguments |
|---|---|---|---|
| # of sessions | Number of sessions in a time bucket. | Integer | |
| # of users | Number of distinct users in a time bucket. | Integer | |
| # of new users | Number of distinct new users in a time bucket. | Integer | |
| Session duration | Total duration of sessions in a time bucket. | Time | |
| # of users who performed event | Number of distinct users who performed the specified event in a time bucket. | Integer | Event name |
| # of users in cohort | Number of users in the provided cohort in a time bucket. | Integer | Cohort name |
| Event count | Total number of the event occurrences in a time bucket. | Integer | Event name |
| Event sum | Full total of the event sum (if event has sum) in a time bucket. | Integer/Float | Event name |
| Event duration | Total duration of the specified event in a time bucket. | Time | Event name |
| Sum of event segment | See the description below | Integer/Float | Event name, Segment name |
| Numeric value | A fixed number you set. | Integer/Float | Number |
Sum of event segment is a relatively advanced parameter. It basically computes the full total of a segment, which is why it only works with numeric segments (Events that have no numeric fields will be hidden). For instance, imagine a Payment event with a segment called Paid Amount.
Sum of event segment(Payment, Paid Amount) would be:
| Paid Amount ($) segment | Count |
|---|---|
| 5.00 | 100 |
| 6.00 | 50 |
| 7.00 | 200 |
The total will be 5 * 100 + 6 * 50 + 7 * 200 = $2200.
Modifying a Parameter
To modify a parameter, just click on it.
A dialog will open to help you select the type of data to be used for the parameter you want.
Once you have selected the parameter type, some additional fields may appear. For instance Event count requires you to specify the event name. You can see all these requirements in the parameters table above.
Parameter Filters
The extensive list of parameter types helps you build many different formulas. In some cases, however, you might want to be more specific about those parameters. For instance, you might be interested in only a certain group of users which matches a query. This is exactly what parameter filters are for: they allow you to narrow down the search space for user, session, and event parameters.
In order to see the filter customization window, click on the + Add filter button on each parameter box. In the pop up, select the values and parameters that best match your need. Then, click on the Save filter button. From now on, the filter is applied to the parameter, which is # of users in the example below.
Operators
When you have more than one parameter in a row, you can change the operator between parameters.
Clicking on the operator icon between parameters will open the menu. You can select one of the operators using that menu.
Unless you group parameters, standard mathematical precedence rules apply.
Grouping
In Formulas, grouping is done using parentheses. Once you have more than one parameter per line, you can select the opening and closing parentheses to create a group. You can start from either the opening one or closing one.
To remove a group, just click on one of the parentheses icons in that group.
Let's say we want to change precedence so that first 20 and 10 are added together, then multiplied by 4. We would do this by wrapping 20 and 10 with parentheses:
20 + 10 * 4 → (20 + 10) * 4.
Based on this example, please note:
-
A group may have an arbitrary number of parameters (still globally limited by 8). All parameters between the first and last will be members of that group.
-
A group may have only one level of subgroups. In other words, a group may have a subgroup, but that subgroup cannot have one.
Executing a Formula
Once you have completed your formula, the last thing you need to do before executing it is to select the right configuration for its output.
Configuring the Output
From the Formula Builder, click on the overflow menu under the name of the formula. The dialog will let you configure the formula's output, and the corresponding number of decimal places and units.
A formula yields just an array of numbers. For instance, let's say you built a formula and your formula produced 3 results (60, 53, 186) like in the table below. Which type would be correct for your formula? Integer? Time?
| Bucket | Value | as Integer | as Time |
|---|---|---|---|
| Aug 8 | 60 | 60 | 00:01:00 |
| Aug 9 | 53 | 53 | 00:00:53 |
| Aug 10 | 186 | 186 | 00:03:06 |
You should select the correct type based on your formula:
-
To display decimal points you would need a Float type
-
If decimal points are not important for you, then you can just select the Integer type.
-
If your formula uses Event duration (e.g. Event duration / # of users who performed Event) you should probably use the Time type.
-
In some cases you just compare two similar values (e.g. # of new users / # of users). The Percentage type is a good fit for this comparison.
In addition to the type selection, you can specify the unit of the output. Using units is especially useful for Integer and Float types. That way, the interface will display numbers with the unit you provided. For example,
- Title: Average number of purchased items per user
- Type: Float
- Decimals: 1 decimal point
- Unit: item/user
- Title: Average amount paid per user
- Type: Float
- Decimals: 2 decimal points
- Unit: $/user
Executing
Now, our formula is ready to be executed. To do that, just click on the Execute formula button and wait for the chart and table to be updated. To switch between Chart and Table view, you can use the corresponding tabs.
Execution of your formula may take a while depending on complexity of your formula and size of your data. If it takes too long, execution will be dispatched to Report Manager. You can see the results there.
When the results are ready, you can see them on both chart and table tabs. Chart tab has a chart and some supplementary stats (total, avg, min, max). All these stats are calculated using time buckets. For instance, average is the average of values in time buckets. The ghost line in chart simply denotes the previous period.
Formulas plugin provides a time-series output by default. Each point (bucket) in the time series represents a certain day, week, etc. In some cases, howewer, you might prefer seeing an overall value of a formula for the date range you look at. "No buckets" option is designed to meet this need. When you enable it, your formula is executed for the date range you selected and it is not grouped by time buckets.
Table looks very similar to many other tables in Countly, date on the left hand side, value on the right hand side. In some cases, however, you may see values as N/A which basically mean that, for that bucket, there is a missing value (e.g. an event returning no value) or some arithmetic error has occurred (like division by zero).
Saving a Formula
Formulas can be saved and shared. So you don’t have to build a formula from scratch every time you need it. Just save it for later and view when you need it. It is also possible to create reports and custom dashboard widgets of your formulas.
Clicking on Save changes... opens a drawer. In this drawer, you can review the details of your formula.
Title and description are just self-descriptive. Formula key is the text identifier of your formula and it must be a unique (within the app). When you save your formula for the first time, it is automatically assigned a key. If it is a duplicate, drawer wants you to change it.
The lower half of the drawer is just another checkpoint for you to review output configuration. You can always go back and change things.
You can make your formula private or allow others to access it. The default level of visibility is global, which makes your formula accessible by everyone.
When your formula has any unsaved change (e.g. new parameter, removed parameter, etc.), a label becomes visible next to the title. Unless you save changes, the next time you load the Formula Builder, your formula will be opened in its last saved state.
To edit your formula, just load it, make your changes, and save it using the same steps.
Loading Saved Formulas
To load a formula previously saved, use the Formulas menu on the top-right corner. It contains a list of formulas which are accessible by you, including formulas created by others with a global level of visibility. From this menu you can also create anew formula using the + New formula button. Please note that this will not create a new formula immediately, but instead open an empty one for you to build from scratch.
