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 should be ticked.
- 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 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))
Comments
0 comments
Article is closed for comments.