Automated Annual Scorecard with SQL/ DOMO - PART 1 : Metrics and Targets
In the old days, people start crunching all the spreadsheets and filling in monthly, quarterly numbers for various metrics for leadership team to review.
Nowadays, having most datasets available, most team would like to automate their scorecards where possible for various reasons.
Easy to audit and much more reliable with the raw data kept.
Significantly reduces manual labor.
So how do we leverage BI tools to build a scorecard?
The key pieces of data that are most likely not in our day to day operations dataset are the targets.
Targets are often calculated and approved by business owners and leadership team. But how do we set them up in the most efficient way that will allow us to easily merge the targets with our existing data?
We are going to start building a target dataset in a google sheet, and bring it into our BI platform (DOMO), in this case, DOMO has a connector with Google sheets that will allow live update.
PART 1 : Metrics and Definition
I created 7 sample metrics for this scorecard that will require different calculation and joining methods. For metrics like order cancellation rate, the number is the lower the better. As for metrics like Order SLA, the number is the higher the better. Later on, we will need to calculate our score Target/Actuals or Actuals/Target depends on the nature of the metric. We will cover this later on.
Once we have decided the weightings, let’s bring our data into the googlesheet.
PART 2: Target, Weightings, and Format
Now we are setting up our target google sheet that we will later bring into DOMO. Any scorecard requires a comparison between actuals and targets, and here comes our targets.
You may wonder, why are there so many rows in this google sheet? This is actually a pretty simple set up that will allow us the do our calculations precisely.
First of all, you will later appreciate the “Index” column as it will make our DOMO transforms very organized. Then of course we have our actual “Metric”
Next comes the “Partner” column. In my sample dataset, I set up two imaginary vendors: Partner A and Partner B. I not only want to see our overall performance but also the breakdown of two vendors, that is why we bring in the “Partner” Column, and we have three options: All, Partner A, and Partner B.
The “Weight” column is pretty self explanatory, set them up to add up to 100%.
Then we have the “Month” column, this is the main reason why we have hundreds of rows. To help organize our calculation, each metric and partner will be broken down into month January to December. This is critical when most of our metrics are based on completion date and deadline calculations.
The “Key” column is for us to join our actuals to targets later. This will be our unique identifier.
Last we have our “Targets”, I used some random numbers. In realty, these targets will be set up using predictive analytics and reviewing historical targets.
PART 3: Connect Google Sheet to DOMO
And Wallah!
We have our sample Scorecard Targets dataset. The next step is to gather all the other required datasets and start building our DOMO Transforms. This will be cover in the next post.
