The Formulas feature enables you to combine the different individual indicators and insights that other features of Countly provide. Through customization of formulas, you can better visualize all the metrics that you are tracking, identifying key relationships between them to follow a large number of trends within your application.

Benefits of Formulas

Formulas enable you to track even more metrics than those that individual Countly features provide. You can use the metrics attained through other features and apply them, with detailed customization, to get figures and trends that tell you even more about your application. By saving and executing formulas, each team and user can track the metric relationships that matter the most to you.

Getting Started

To start using Formulas, you need to first enable the feature. To do so, simply go to Management > Feature Management and enable the Formulas toggle.

You can use a variety of metrics in the formulas you design. However, you need to ensure that the metric you are including in your formula is already being tracked. For example, if you are using a particular Event in your formula, that Event needs to be set up as part of the events you are tracking.

To start using the Formulas feature, simply click on Formulas in the Main Menu in Countly.

Formulas Overview

Examples of Formulas

You can build many different mathematical expressions depending on your data and business needs. Here are a few examples of some calculations you can undertake using Formulas:

• 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 OR Refund count / Event Purchase count: Percentage of purchases which are returned or 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

Understanding Formulas

Key Elements

The Formulas View has some key elements that are responsible for specific functions. Let's delve into these a little further, using the below screenshot for reference.

1. Save Formula button: It is used to save new formulas, as well as changes on existing saved formulas.
2. Last Queries button: This button allows you to load a list of the last run formula. If you see an orange dot on Last Queries, it means that you're running a query on Formulas features.
3. New Formula button: It opens an empty formula page for users to start building a new formula from scratch.
4. Open Formula button: It is used to browse any saved formula.
5. Use Denominator check box: It is used to enable/disable the denominator on formulas. The denominator is included by default and can be disabled if not required.
6. Execute Formula: Clicking this button starts the calculation of the formula. 
7. Configure format settings: Before or after the formula is created, the output format can be configured to suit your requirements. Please check the details of the output format below.
8. Date Range dropdown: Use the date range dropdown to customize the period for the result of the formula to be displayed on the table and chart.
9. Query results: Displayed as a Chart and a Table.

Layout

The Formulas View is divided into two parts: the upper part (which is the Formula Builder) and the lower part (which contains the Chart and Table with the results of the formula).

Formula Builder

The Formula builder lets you:

• Build formulas
• Add, edit, or delete parameters (see Parameters section below for more detail)
• Set the operators (plus, minus, multiply, divide) between parameters
• Group parameters using parentheses
• Configure the formula output
• Load saved formulas
• Save and execute formulas

Chart and Table

The Chart and Table display the output of your formula. Results can be filtered by two different criteria, including:

• Bucket
• No Bucket

Bucket

When you opt for Bucket-based results, the data within the chart display results of the formula for the selected date range and for daily, weekly, and monthly buckets. You also see the Total, Average, Minimum, and Maximum values in the widgets above the chart, and the selected bucket-based results in the table below. Green-colored percentages next to the numbers in the widgets show the changes compared to the previous period. Each time you change the Bucket by clicking on the relevant bucket option (Daily, Monthly, and so on), the calculation process is executed again. 

For example, if the Daily bucket is selected, the table shows dates in DD/MM/YY format for each day, and shows the value that is calculated for each day. The same applies to the weekly bucket.

No Bucket

When you opt for No Bucket-based results, you simply see results for the entire selected period (without any time buckets applied) on the chart, and the values for the previous period. The table gives you the results for the time period selected in the Date Range.

Parameters

In the Formula Builder, each of the boxes with dashed borders (A and B) represents a parameter. 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 parameter has two states: empty and configured.

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. Hence, it is important to configure the parameters in your formula.

Types of Parameters

The table below shows the list of parameters you may select:

Sum of Event Segment is a relatively advanced parameter. It 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:

The total will be 5 * 100 + 6 * 50 + 7 * 200 = $2200.

Maximum number of Parameters

A formula may have up to 8 parameters. When you reach 8, the + button will be disabled automatically.

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.

Operators

The Operator determines the relationship/calculation between two parts of any formula. The Operators available include addition, subtraction, multiplication, and division, indicated by the symbols: +, -, x, and /.

Output Format

Output is the result of the formula that has been designed. Output can be selected by the user (using the Configure Output dropdown) and can be one of four types:

• Integer (with the unit that is an optional field)
• Float (with customizable decimal places and unit that is an optional field)
• Percentage (with customizable decimal places)
• Time (with the unit that is an optional field)

Using Formulas

Creating a New Formula

When you click on Formulas in the Main Menu, you will land on the default View of the Formulas feature. This will show you an unnamed and unsaved formula, with a default description, that you can customize to your requirements prior to saving it. Users need to create their formula at this stage by setting the desired output format and building the formula from properties.

To create a formula, you need to undertake the following steps:

Step 1: Select Output Format and Unit

Select the output format from the available options by using the dropdown menu on the top left of the page. Please see the Output Format section above for more details of each format.

Below is a list of criteria that can help you determine the output format best suited to your formula:

• To display decimal points you would need a Float type.

• If decimal points are not important, then you can 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 may simply want to 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 format, 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

If you are unsure of the output format and unit at this stage, you can follow the next steps and come back to set this once your formula has been built.

Step 2: Enable/Disable Denominator

The denominator field is enabled by default for every new formula. If you do not want a denominator for the formula you are building, you can disable the denominator by unchecking the box against the term 'Use Denominator' on the top left corner of the formula builder section.

Step 3: Determine Parameters (and Parameter Filters)

See the Types of Parameters section above to know more about the parameters you can set at this step. Click on any box within the formula to set your parameters for that box. After setting a parameter, click on + Add filter button to add filters and properties to the selected parameter. In the pop-up that opens, select the values and parameters that best match your need. Then, click on the Save filter button. From here on, the filter is applied to the parameter. See the Parameter Filters section above for more information on how they work.

You can follow these steps for every parameter that you want to include in your formula. To delete any parameter, click the parameter box and then click on the Delete Parameter button. To add more parameters, click on the + next to the latest parameter. You can determine the operators in the next step as per the mathematical function you wish to carry out.

Step 4: Determining Operators

Once you have more than one parameter, you can determine the mathematical relationship between the two parameters. Click on the + to see the operator options available. See the Operators section above to know more about the available operators. Select any operator that you wish to apply. You can change operators after adding other parameters.

Step 5: Grouping

Standard mathematical precedence rules apply to parameters. However, you can alter this by grouping certain parameters. 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 of any of these parameters. You can start from either the opening one or the closing one.

Two important points must be noted about Grouping:

• 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 another subgroup.

To remove a group, just click on one of the parentheses icons in that group.

Step 6: Select Date Range

Use the Date Range dropdown to select the date period to which you want to apply the formula.

Step 7: Execute the Formula

At this stage, you can execute the formula to see the result. If you are not satisfied with the result, you can make more changes to the formula built, repeating this step until you have the calculation you want.

To execute the formula, click on the Execute Formula button. The chart and table will then get updated with the results. For more details on reading the results, please see the Reading Data section below.

Execution of a formula may take a while depending on complexity of the formula and size of the data. If it takes too long, execution will be dispatched to Report Manager, and you can see the results there.

Step 8: Save the Formula

Once you are satisfied with the formula you have built, you can save the formula for easy access at a later stage. To do so, click on the Save Formula button at the top right corner of the formula builder. You will be prompted to fill in a few details for the formula, including:

• Formula name: A name for the formula.
• Description: A brief description of the formula and what it is meant to showcase.
• Formula key: Formula key is the text identifier of your formula and it must be unique (within the app). When you save your formula for the first time, it is automatically assigned a key. If it is a duplicate, you will be prompted to change it at this step.
• Visibility: All formulas have a visibility setting that indicates who can see this formula and its results. You can choose to make the visibility Global (making the formula visible to all users) or Private (making the formula visible only to you as the creator). By default, the visibility is set to Global. If you choose to make the visibility Private, you will get the option to add e-mail addresses of specific users with whom you want to share the formula. Adding e-mail addresses of shared users is optional.

Loading a Saved Formula

To load a formula previously saved, click on the Open Formula button at the top right corner of the Formulas View. This will open up a list of formulas which are accessible by you, including formulas created by others with a global level of visibility. You can then execute the formula to see results or make any necessary edits.

Editing a Formula

You can edit any formula once it has been saved. If you make any changes to a formula that has already been saved, you will unlock the option to save these changes. Unless you save changes, the next time you load the Formula Builder, your formula will be opened in its last saved state.

To edit any formula, simply load it using the above step, make your changes, and save it by using the steps in the Step 8: Save the Formula section above. You will be able to save the formula using the Save Changes button (which becomes clickable for edited formulas). You can also choose to save it as a new formula so as not to overwrite the existing formula. This is most useful when you are editing a formula so as to expand upon an existing one and create another one.

Reading Data

When you click on the Execute Formula button, the results of the formula are displayed in the Chart and Table in the bottom half of the Formulas View. As discussed above, you can choose to see results by applying Buckets or opting for No Buckets (where no time buckets are applied). Use the button next to the term 'Results Based On' to select between the two options.

Four widgets (2) under this selection provide the below data, when you choose the Bucket option (1):

• Total value: Sum of the elements in the array.
• Average value: Average of the elements in the array.
• Minimum value: Minimum value seen within the array.
• Maximum value: Maximum value seen within the array.

A percentage displayed in green next to each of these numbers (3) highlights changes from the previous period in these values. You can also select the date range for which you want to see results in the date picker to the right top of the chart (4).

If you have selected the Bucket option, you will see buttons for Weekly and Monthly data at the top of the chart. Click on any of these to see the results for those time buckets. When you opt for the No buckets option, you will see the data only for the entire period selected, without the option of applying time buckets, and the data for the period previous to the selected period.

You will also see the data through a tabular view (5) in the table under the chart. This data table can be downloaded by clicking on the download arrow at the top right side of the table. You can also search within the data table using the search bar at the top right corner of the table.