Automated Annual Scorecard with SQL/ DOMO - PART 3: Calculate the Actuals
Just a recap, in the two previous posts, we have gathered all the necessary input datasets to create our automated scorecard:
Targets datasets (with index, metrics, weights, and keys)
Static datasets for Actuals (derived from monthly snapshot of system data)
Now we are finally ready to create our DOMO transform.. In this post, we are going to calculate all of the metrics and union the ‘Actuals’ that will be used in score calculation later.
PART 1: Load the Input Datasets
PART 2 : Metrics, Calculation, and Format
So now that we have our targets and static monthly system data, we will eventually need to make a comparison to assign a score. However, static monthly system data needs to get turned into ‘Actuals’ first. Thus, our first step is to generate our actuals for each metric!
Other than generating actuals, we need to keep in mind that our end goal is to create dataset in a table format, so that it will make it easier for us to build a card.
Format matters!
For every metric, we want an output with
Key
Index
Month
Partner
Numerator
Denominator
Actual
These will later be joined with the target datasets for score calculation.
1.1 Team Pulsecheck Survey
Since we did not have the metric 1.1 Team Pulsecheck Survey in any of our input datasets, we will have to create a simple table in the format we wanted. This kind of stand alone metric is not uncommon in most company’s scorecard, there is no massive dataset to back up something like employee engagement survey score, we simply need to input a number. And this is why we are demonstrating this technique here.
Since this metric does not have a split in vendor performance, we put ‘All’ in the Partner column. Note that for the Numerator, Denominator, and Actual we input ‘NULL’ as we don’t have to calculate anything. All we need is the score that we already have, and we will input that when joining with the target dataset later.
2.1a Order SLA
Order SLA calculates orders completed within due dates over all orders due in that month. Remember, results need to be split by different vendors(partners) as well. Since we already have the targets table, we will just have to generate a table for the ‘Actuals’.
2.1b Order Cancellation
Order cancellation metric calculates the orders cancelled throughout that month over all of the orders due in that month.
2.2a Escalation Opened
Escalations opened that month over all orders opened that month.
2.2b Escalation SLA
Escalation SLA is calculating Escalations completed within due dates over all escalations due in that month.
PART 3 : Union the Actuals
Now that we have our actuals for the metrics that require calculations. We have to union them. We will have to union the ‘Partner Actuals’ , ‘Overall Actuals’ and ‘Manual Input Actuals’. (Team Pulscheck Survey)
Now we have the three type of actuals, we are going to union them.
