As of May 2022, you can reference calculated metrics inside a calculated metric!
This sounds trivial, but unlocks some really powerful use cases:
Analyse interpolated data, e.g., when you have non-daily / spotty data (if that sounds theoretical, drop down here to see what it means š¤ )
Time-series analysis of numeric fields (fields, not metrics!), like looking at week-over-week trends, find the min/max or average value over a given period of time for any numeric field in your customer database
Your metric library can become more dynamic, which means updating in one place updates everywhere related (note: with great power comes great responsibility here, be mindful of cascading effects of updates and circular references)
Content below:
How to build a calculated metric referencing a calculated metric
Use case #3: a more dynamic metrics library
What it means
How calculated metrics are processed (š hint: you can now decide the order of metric processing!)
Before we dive into use cases, let's quickly explain how to do it
š Read more here about how to build calculated metrics in general.
To reference another calculated metric inside a calculated metric:
Copy the calculated metric ID, which you can find under the calculated metric title and will look like this
calculated.625577d94c890d12cbfe6bfd
Insert the ID as the property value in a calculated metric operation, like below where the references calculated metric is another time series that you can sum up, take an average over defined number of days, etc (more on that below)
{"type": "metricOverTime", "days": 7, "op": "SUM", "prop": "calculated.625577d94c890d12cbfe6bfd"}
Use case #1: Analyse interpolated data
Sometimes when working with metrics you want to perform multiple operations on an entire time-series. This is most often relevant when you have gaps in your data (i.e., it's sent in non-daily) which leads to you using the "LAST" operator to smooth it over time, and then you want to analyse that smoothed time-series. Examples:
You send in a time series with a few "gaps" in the data
You only care about the latest available data point, which might not be daily
Let's imagine the scenario below where you are pushing in 3 different metrics (Log-ins, Temperature, NPS) that all have 1 day of null
value. You want to build a calculated metric to show what the 5-day rolling average is.
Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | |
Log-ins | 7 | 5 | 9 | 5 | - |
Temp (CĀ°) | 22 | 15 | 15 | - | 17 |
NPS | 5 | - | - | - | - |
Depending on the metric, the null
value could be interpreted differently. In the case of Log-ins, it probably means no one logged in that day, and we can assume that null
equals 0. In the case of temperature, looking at the other days' values it seems that null
means "no value sent" rather than 0 (maybe the thermometer was broken, there was something wrong with the internet connection, etc). In the NPS case, a data point is only sent in when updated, but we should still treat Day 2-5 like they have that value.
š Side note: use this setting to choose how Planhat interprets zeros, as 0 or as null.
How does this relate to nested calculated metrics? In the case of Temperature and NPS, you probably want to "smooth" the data by assuming some replacement figure for the null
value (since assuming 0 will mess up the 5-day rolling average).
To solve this, you can build a calculated metric which looks at the Temperature or NPS and saves the latest available data point. So for Day 4, that means taking 15CĀ°. For NPS, that means looking further back and taking the latest available (5). In the Temperature case, the calculated metric would look like this:
// Temperature example: calculated.123456789
{"type": "metricOverTime", "days": 3, "op": "LAST", "prop": "custom.temperature"}
š Calculated Metric extra class: the number of "days
" in the "LAST" function can be anything from 1 to infinity, depending on how far back you want to fetch data. If you say days = 1, that means Planhat will only look back 1 day for a data point - meaning if you have 2 days in a row with null
value then you will get a null
value. This makes sense if the data is volatile and data farther back than the day before won't correlate to today. 2-5 is perhaps a reasonable value for Temperature if you live in volatile Sweden - 60 if you live in sunny California!
For NPS, maybe we'll always take the last value available!
This will generate a new time-series as a calculated metric:
Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | |
Log-ins | 7 | 5 | 9 | 5 | 0 |
Temp (CĀ°) | 22 | 15 | 15 | 15 | 17 |
NPS | 5 | 5 | 5 | 5 | 5 |
Great - all smoooooth. But now you want to "sum up" these 5 days in order to average them - but this doesn't work. Previously, you couldn't use the "average over 5 days" on this, since that operation couldn't be performed on calculated metrics. Now, you can!
Again, using the calculated metric example:
// 5-day average of temperature: calculated.987654321
{"type": "metricOverTime", "days": 5, "op": "AVG", "prop": "calculated.123456789"}
Use case #2: Time-series analysis of numeric fields
In calculated metrics, you can reference a numeric field (i.e., point-in-time data) which turns it into a time-series.
This was possible before, but then you couldn't do any "analytics" on it, like:
Define change over time (e.g., week-over-week change)
Find the min/max value over a given period of time
See the average over a period of time
With calculated metrics inside calculated metrics, you can:
The "first" calculated metric which turns field data into a metric could look like:
// Field to metric: calculated.121212123
{"type": "propertyValue", "prop": "company.custom.employee count"}
Now, you can:
// Average over last 7 days
{"type": "metricOverTime", "days": 7, "op": "AVG", "prop": "calculated.121212123"}
// Maximum value in last year
{"type": "metricOverTime", "days": 365, "op": "MAX", "prop": "calculated.121212123"}
...and so on!
Note that this does not work retroactively, i.e., Planhat only starts saving down the daily value from the day you set up the metric.
Use case #3: Make your metric library more efficient and dynamic
What it means
When you build up a library of metrics to understand your customer operations, you often end up having some depend on others.
Let's assume you have two metrics called "This week's logins" and "Last week's logins" which are based on summing up a custom metric being pushed in from your back-end. Now you want a "Week-over-Week change in logins" metric which does a week-by-week comparison. Without being able to reference other calculated metrics, this would have looked like:
// Metric 1: THIS WEEK'S LOGINS
{"type": "metricOverTime", "days": 7, "op": "SUM", "prop": "custom.logins_per_day"}
// Metric 2: LAST WEEK'S LOGINS
["SUBTRACTION",
{"type": "metricOverTime", "days": 14, "op": "SUM", "prop": "custom.logins_per_day"},
{"type": "metricOverTime", "days": 7, "op": "SUM", "prop": "custom.logins_per_day"}]]
// Metric 2: WEEK-OVER-WEEK CHANGE IN LOGINS
["DIVISION",
{"type": "metricOverTime", "days": 7, "op": "SUM", "prop": "custom.logins_per_day"},
["SUBTRACTION",
{"type": "metricOverTime", "days": 14, "op": "SUM", "prop": "custom.logins_per_day"},
{"type": "metricOverTime", "days": 7, "op": "SUM", "prop": "custom.logins_per_day"}]]
Now, with the ability to reference other calculated metrics, it both looks cleaner and is more dynamic. Change one calculation, and it updates across. See below:
// Metric 1: THIS WEEK'S LOGINS
{"type": "metricOverTime", "days": 7, "op": "SUM", "prop": "custom.logins_per_day"}
// Metric 2: LAST WEEK'S LOGINS
["SUBTRACTION",
{"type": "metricOverTime", "days": 14, "op": "SUM", "prop": "custom.logins_per_day"},
{"type": "metricOverTime", "days": 7, "op": "SUM", "prop": "custom.logins_per_day"}]]
// Metric 2: WEEK-OVER-WEEK CHANGE IN LOGINS
["DIVISION",
{"type": "metricOverTime", "days": 1, "op": "SUM", "prop": "calculated.626a62ecea8fbb3c3ed1086b"},
{"type": "metricOverTime", "days": 1, "op": "SUM", "prop": "calculated.625577d94c890d12cbfe6bfd"}]
š Processing
When calculated metrics can reference each other, it means the order of processing becomes important. This is how Planhat does it:
First, process all non-dependent calculated metrics (i.e., those that do not reference another calculated metric)
Second, process all dependent calculated metrics based on the order they are found in the calculated metrics UI! (see where in screenshot below)
This will allow you to sequence the metric processing when you have multiple dependent steps (e.g., Metric 1 is dependent on Metric 2, which is dependent on Metric 4, etc). Re-order by hovering over a metric and drag-and-drop. The ability to re-order is available to any user with the permissions to see and update calculated metrics.
š Quick tip: to see which calculated metrics are dependent (need to be ordered carefully), spot the arrow icon next to the metric name and hover over it to see which metric(s) they reference.