Countly offers a rich set of application metrics and visualization interfaces. Even though those individual indicators tell you a lot about your application, you may need some additional metrics that are calculated from the existing metrics. This is exactly what “Formulas” does, letting you build your own formulas that combines different metrics.
What can you do with 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
Building a formula
Formulas UI has two main parts. First one, the builder, lets you;
- Build formulas
- Add/edit/delete parameters
- Set the operators (plus, minus, multiply, divide) between parameters
- Group parameters using parentheses.
- Load saved formulas (2)
- Configure formula output (1)
- Save and execute formulas (5).
The other half, chart and table, displays output of your formula.
Each of the boxes with dashed borders (A and B) represents a parameter. A parameter has two states: empty and configured. A and B are currently empty parameters.
Denominator line is on by default. It helps you to 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 button with plus sign (4).
A formula with an empty parameter won't be executed. Also, you cannot add a new parameter to a row if there is an empty parameter in that row.
Parameters are fundamental units of formulas. Each parameter yields a scalar value and your formula uses them to compute final value of a time bucket. In other words, A formula is evaluated for each time bucket for selected period. Buckets are independent, so they don't affect each other.
Maximum number of parameters
A formula may have up to 8 parameters. When you reach 8, add buttons will be disabled automatically.
The table below shows the list of parameters you may select:
|# 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, that's why it only works with numeric segments (Events which 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|
The total will be 5 * 100 + 6 * 50 + 7 * 200 = $2200.
Modifying a parameter
To modify a parameter, just click on it.
The opened dialog will help you to select the type of data to be used for the parameter you want.
Once you selected type, some additional fields may appear. For instance Event count requires you to specify event name. You can see all these requirements in the parameters table above.
Rich set of parameter types helps you to 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 parameter boxes. The window will greet you with the standard query builder, which assists you to input a query.
Once you're done with the query, click on "Save filter" button. From now on, the filter is applied to the parameter, which is # of users in the example below.
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 precedence rules apply.
In Formulas, grouping is done using parentheses. You select opening and closing parameters 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 with 4. We would do this wrapping 20 and 10 with parentheses:
20 + 10 * 4 → (20 + 10) * 4.
Here are some other notes about grouping:
A group may have 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.
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 table. Which type would be correct for your formula? Integer? Time?
|Bucket||Value||as Integer||as Time|
You should select the correct type based on your formula:
To display decimal points you would need Float type
If decimal points are not important for you, then you can just select Integer.
If your formula uses Event duration (e.g. Event duration / # of users who performed Event) you should probably use Time type.
In some cases you just compare two similar values (e.g. # of new users / # of users). Percentage is a good fit for this comparison.
In addition to type selection, you can specify the unit of the output. Using unit is especially useful for Integer and Float types. So the interface displays numbers with the correct suffix: unit you provided. For example,
- Title: Average number of purchased items per user Type: Float, 1 decimal point, Unit: “item/user”
- Title: Average amount paid per user Type: Float, 2 decimal points, Unit: “$/user”
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, you can use 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 some others to access it. The default level of visibility is global, which makes your formula accessible by everyone.
When your formula has some unsaved changes (e.g. new parameter, removed parameter, etc.), a label becomes visible next to the title. If you don't save them, your formula will be opened in its last saved state, when you load it again
To edit your formula, just load it, do your modifications and save it using the same steps.
Loading a saved formula
To load a formula saved before, you can use top-right Formulas menu. It contains a list of formulas which are accessible by you. Also, you can open a new formula using + New formula button. Please note that this wouldn't create a new formula immediately, but it would open an empty one (just like an empty document).