Using calculated KPIs and adding them to widgets

This guide explains what calculated KPIs are, how to manage your calculated KPIs, and how to add them to widgets in the Explore page.

Introduction

Calculated KPIs are metrics created based on a combination of other metrics, such as a sum or a ratio. Calculated KPIs can be used in Explore and Present to analyze and visualize your data. Adverity displays calculated KPIs in green with the sign.

Calculated KPIs can be loaded into Explore & Present but not external destinations.

Creating calculated KPIs

Create calculated KPIs to perform calculations on existing metrics and add the results of the calculations to a widget. The example below explains how to create a calculated KPI that expresses cost per click (CPC) values. The calculated KPI created in this example is identical to the default CPC calculated KPI offered by Adverity.

Only users with the required permissions can create calculated KPIs. For more information, see Managing user permissions.

To create a calculated KPI from the administration, follow these steps:

1. Go to the Explore page.

2. In the top left corner, click Select workspace, then Administration.

3. In the Explore section of the administration menu, click KPIs and dimensions.

4. In the top right corner, click Create calculated KPI.

Or, to create a calculated KPI from Explore, follow these steps:

1. Go to the Explore page.

2. Next to the View tabs at the top of the page, click Create new view.

3. In the Metrics section of the view, click + Create new calculated KPI. This opens the Create a new calculated KPI screen.

To configure the new calculated KPI, follow these steps:

1. In the Define KPI formula section, configure the following fields:

   Select metrics to add to formula below

   In the drop-down list, select a metric to use in your formula, then click + Add metric. Repeat this step for all metrics you want to use in the formula.

   Formula

   Use mathematical operators to specify the calculation to perform using the metrics or previously created calculated KPIs you have selected. For example, use / to add a division sign between two metrics: (costs)/(clicks).

   Use Ctrl+Space to see suggestions for the formula.

   Calculated formula

   This field shows the full formula you have created and whether or not it is valid. If the formula is not valid, this field tells you where the syntax error is.

2. In the Define calculated KPI section, configure the following fields:

   Calculated KPI name

   Enter a name for the new calculated KPI to help you identify it. This is the name that is used for this calculated KPI in the Adverity platform.

   Format

   If your calculated KPI represents a percentage value, currency, or duration, select the corresponding format option.

   Visibilities

   Select the workspaces that can access the new calculated KPI.

3. Click Create calculated KPI.

As a result, you have created a new calculated KPI. You can now add this calculated KPI to widgets.

Adding a calculated KPI to a widget

To add a calculated KPI to a widget in the Explore page, follow these steps:

1. Go to the Explore page.

2. Next to the View tabs at the top of the page, click Create new view.

3. From the Dimensions & Metrics list on the left, select the calculated KPI that you want to add to your widget. Calculated KPIs are displayed in green with the sign.

As a result, you have added the selected calculated KPI to your widget. You can now continue adding other dimensions and metrics to your widget as normal.

Editing and deleting a calculated KPI

To edit or delete an existing calculated KPI, follow these steps:

1. Go to the Explore page.

2. In the top left corner, click Select workspace, then Administration.

3. In the Explore section of the administration menu, click KPIs and dimensions.

4. In the top right corner, click Create calculated KPI.

5. At the bottom of the screen, click Open calculated KPI.

6. Select the calculated KPI you want to edit and click Apply.

7. Perform any of the following actions:

   • To edit the selected calculated KPI, make any changes to the fields in the Define KPI formula and Define calculated KPI sections. Then click Update calculated KPI.

   • To delete the selected calculated KPI, at the bottom of the screen, click Delete.

As a result, you have edited or deleted the selected calculated KPI.

Viewing your calculated KPIs

To view your calculated KPIs, follow these steps:

1. Go to the Explore page.

2. In the top left corner, click Select workspace, then Administration.

3. In the Explore section of the administration menu, click KPIs and dimensions.

4. At the top of the page, select the Calculated KPIs tab.

For more information, see Viewing and configuring dimensions, metrics and calculated KPIs.

Creating calculated KPIs with complex formulae

The example above explained how to create a calculated KPI where the Formula field only contains the following basic mathematical operators:

• addition +

• subtraction -

• multiplication *

• division /

It is possible to use more complex formulae, such as conditional statements and average calculations.

Using conditional statements in formulae

Specify the condition in the Formula field in the following way:

CASE WHEN {condition} THEN {calculation1} ELSE {calculation2} END

In the code above, replace the following:

• Replace {condition} with a conditional statement. Use basic mathematical operators and the following: OR, AND, IS NULL, NULLIF.

• Replace {calculation1} with the value of the calculated KPI if the condition is satisfied.

• Replace {calculation2} with the value of the calculated KPI if the condition is not satisfied.

For example, if you use the formula CASE WHEN (clicks)>100 THEN (costs)/(clicks) ELSE (costs)*0.01 END in the example above, Adverity computes the calculated KPI in the following way:

• If the number of clicks for a campaign is greater than 100, the CPC value is the ratio of costs and clicks.

• If the number of clicks is less or equal to 100, the CPC value is 1% of the costs.

Using dimensions in formulae

Conditions used to compute calculated KPIs can include dimensions as well as metrics.

For example, if you use the formula CASE WHEN week IS NULL AND (clicks)<100 THEN 0 ELSE 1 END in the example above, Adverity computes the calculated KPI in the following way:

• If the week dimension is null and the number of clicks for a campaign is less than 100, the calculated KPI value is set to 0.

• If the week dimension is not null and/or the number of clicks is equal to or greater than 100, the calculated KPI value is set to 1.

Generally, you cannot perform calculations on the values of a dimension. One exception is that you can calculate the unique values in a dimension. To do so, use the following code: COUNT(DISTINCT({dimension})), replacing {dimension} with the name of the dimension whose unique values you want to count.

For example, the code COUNT(DISTINCT(campaign_name)) calculates the number of unique campaign names.

Performing calculations on data in a table in Explore

To use the Formula field for a calculated KPI to perform a calculation on data that is shown in a table in Explore, use the following expressions:

• To calculate the average of a metric's values, use AVG({metric-name}). Replace {metric-name} with the metric's name.

• To calculate the sum of a metric's values, use SUM({metric-name}). Replace {metric-name} with the metric's name.

• To compute a function differently for all distinct combinations of dimension values, use OVER (PARTITION BY 0) after the function.

• To compute a function differently for all distinct combinations of dimension values, excluding the last dimension, use OVER (PARTITION BY 1) after the function.

• To compute a function differently for all distinct combinations of dimension values, excluding a number of dimensions at the end of the list, use OVER (PARTITION BY {number-of-dimensions-to-exclude}) after the function. Replace {number-of-dimensions-to-exclude} with the number of dimensions to exclude.

• To compute a function for all rows without splitting data for any dimensions, use OVER() after the function.