Build the Metrics

2
min read
Building Metrics

In the previous chapters, we filled out a metric spreadsheet. We took a vague ask from a Point Person and turned it into a well-defined list of metrics, calculations, and data sources. We will now use the completed metric architecture to create various SQL queries.

The columns of the metric architecture map to a SQL query.

Metric Spreadsheet to SQL query

Take a look at a couple of sample queries we could create from this spreadsheet for the Operations Cost metric:

Total Operations Cost

CODE:https://gist.github.com/fronofro/8ccd04452f36742ed7772a8bbb6ff690.js

Query result

Total Operations Cost by Department

(When we introduce a GROUP BY statement we must include any column there in the SELECT statement as well)

CODE:https://gist.github.com/fronofro/d526f2f345aa749017fdea3fcfe4491e.js

Query result

Total Operations Cost by Department by Month

CODE:https://gist.github.com/fronofro/3b3b3e52589cc95bade443de2dba59a9.js

Query result

One of the beauties of SQL is that it can do the logistical work of finding the columns in the data sources, and it can also compute mathematical equations. Most other methods require you to first access the unaggregated data via SQL and export the data into the tool so that you can create the calculations. Since SQL is tied to accessing the database when the underlying data changes, you can rerun the query and see the latest data. This is more efficient than exporting data into another tool.

SQL Resources

If you are new to SQL check out Chartio’s tutorial here:

If you are struggling with understanding how Aggregations or Subqueries work check out:

If you are running into errors or are getting 0 rows returned check out:

Checking your Queries

Do not assume your query is perfect. You should check it by looking at other peoples’ queries and/or by having the Data Gatekeeper review it.

Check other people’s Queries

Depending on the BI tool that you are using you can see other people’s SQL queries. This can be very insightful. You can take note of data sources they used that you were not aware of. You can also see if other people have complexity in their queries.

Complex Query example:

CODE:https://gist.github.com/fronofro/3dad56c33a2b6544c142cbf9c9396ec8.js

Complexity in a query typically suggests the data is nuanced, messy, or certain business logic needs to be adhered to. If you come across a complex query that is for the same or a similar metric as the one you are working on reaching out to the creator. You should try to understand what the extra parts are all about so you can incorporate what is relevant into your own query.

On the other hand, if other people have similar looking queries for similar metrics you are probably in the clear. However, you still will want to get someone else's eyes on it for verification.

Consult Data Gatekeeper

Getting a code review on your queries is a best practice. Reach back out to the Data Gatekeeper to validate your queries are calculating their metrics correctly. Having the metric spreadsheet facilitates this process since they can see your work and how you go to the query you wrote.

Build the Dashboard

Take the tables of data line them up with where they fit into your design.

Query results laid out like how it will be in the dashboard

Go through each table and create the corresponding data visualization in your BI tool. Put all the visualizations together into your final dashboard.

Summary

  • Build metrics in SQL by plugging in the columns to their relevant part of a SQL statement
  • SQL is required to get the data. Use it to calculate the metrics directly as well as to reflect any underlying changes
  • Check your queries by evaluating other people’s queries in your company and/or having the Data Gatekeeper review it

Give Feedback on our Google Doc

Chapter topics