Calculated KPIs

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

1. Within the Explore Element, locate the icon to create a new calculated KPI (a combination of function (f) & plus (+) on the right hand side)
2. You will be redirected to the KPI creator.
3. 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. 
4. Formula: 
   You can build formulas by using standard mathematical operators such as + - / *.
   E.g. the formula for the example above would be: "(costs)/(bookings)".
   It's also possible to apply conditions (if-then) to metrics. If you'd like to do so, please see the section below. Even more complex cases can be covered using a window function.
5. Click on Validate to verify whether or not the operation can be performed.
6. Calculated KPI Name:
   Name the new KPI.
7. If the new KPI should be displayed as either a currency or a percentage, click the appropriate checkbox.
   
   This is important, because it will help Adverity to visualize the KPI in the appropriate format.
   For the example above, an appropriate name for the new KPI could be "Cost per Booking", and is Currency should be ticked.
   
   If you check the is percentage box, Adverity will automatically multiply the KPI by 100 to display a percentage, therefore you do not need to include *100 in the KPI formula.
8. 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.
9. Save by clicking Create calculated KPI.

CREATING CONDITIONAL KPIs: SYNTAX

You can also create more complex KPIs which include a conditional (IF/THEN) clause using our proprietary syntax.

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 as well as IS NULL, NULLIF 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))