Calculated KPIs are metrics that represent a ratio/sum/formula that is based on other metrics. Common examples are Cost-per-Mille (CPM), Cost-per-Click (CPC) or Conversion Rate.

- Calculated KPIs should not be imported. Instead, only their components are imported, e.g. Costs and Clicks in order to produce a CPC. This is necessary when, e.g. the total CPC of several data sets does not equal the sum of the CPCs.
- Example: You have three data sets with a CPC of €1. The total CPC is still €1 and not €3.

**CREATING CALCULATED KPIs**

You can create KPIs with the KPI Creator.

E.g. Cost per Booking = Costs / Bookings

- Within the
**Explore**Element, locate the icon to create a new calculated KPI (a combination of**function (f)**&**plus (+)**on the right hand side) - You will be redirected to the KPI creator.
**Select Metrics to add to formula below:**

In the drop-down menu, select the required metric(s) and click**Add metric**. Repeat for each Metric you want to include, and each will then appear in the formula field below.**Formula:**You can build formulas by using standard mathematical operators such as + - / *.

E.g. the formula for the example above would be: "(costs)/(bookings)".- Click on
**Validate**to verify whether or not the operation can be performed. **Calculated KPI Name:**Name the new KPI.- If the new KPI should be displayed as either a
**currency**or a**percentage**,**click**the appropriate checkbox.

For the example above, an appropriate name for the new KPI could be "Cost per Booking", and**is Currency**

**Visibilities:**

Select the Workspaces which should have access to the new Calculated KPI. If the root Workspace or a Workspace Group is chosen, then all of its child Workspaces will also have access.- Save by clicking
**Create calculated KPI.**

**CREATING CONDITIONAL KPIs: SYNTAX**

You can also create more complex KPIs which include a conditional (IF/THEN) clause. For those of you somewhat familiar with SQL syntax, this can be very easily constructed, as it follows the same logic.

Use Case: You want to make sure that the cost per booking KPI you created above is only calculated when a sufficient number of bookings exist. Otherwise, you would like to simply apply a general discount factor to the cost until the sufficient number of bookings has been reached.

Cost per Booking = Costs / Bookings BUT only calculate this when bookings are higher than X (e.g. 10), otherwise simply discount the cost by Y (e.g. 30% or 0.3). This could be calculated using the formula below:

`CASE WHEN (bookings)>10 THEN (costs)/(bookings) ELSE (costs)*0.3 END`

Please note:

- CASE WHEN and END must wrap around the entire calculation
- OR, AND and IS NULL are supported

Special Case DISTINCT: Usually, calculated KPIs only support metrics within the formula. However, there is one exception: the DISTINCT(COUNT) function, which will count the distinct (unique) values for a given dimension. For example, if you would like to know how many distinct (unique) values are contained in the dimension channel, you can use the formula below:

`COUNT(DISTINCT(channel))`

## Comments

0 comments

Article is closed for comments.