How to Create and Implement a Metric Comparison Grid Report

Marek Stepniowski
May 10 · 4 min read

One of the primary goals for the Data Team at Ro is facilitating data-driven conversations across the company. One way we do that is by creating dashboards in Looker and Metabase. However, accessing data stored in these dashboards requires our colleagues to go out of their way, learn a new tool, and remember to check in on the dashboards often.

What if we could instead meet our colleagues where they are?

Everyone at Ro uses Slack and email for their daily tasks. If we made it easier to share metric reports there, it would help our peers to include KPI metric data in their day-to-day decision making.

In order to do that, our team built a framework for analysts to easily create and embed performance metric comparison grid reports. Now, we’re providing that tool open-source for others to use. Below we will describe what a metric comparison grid is, tell you how to use the framework to create your own reports, and share some on implementation.

What is a metric comparison grid?

A metric comparison grid is a quick way to track business metrics and to measure how close are they to reference numbers.

It’s a grid where each row describes a single metric, while columns show the value of the metric aggregated over a period of time or a change to that metric compared to another period.

Usage

First you’ll need to tell the comparison grid framework what credentials to use to connect to Snowflake and Slack. To set them up please follow the readme.

A comparison grid report is described by a single YAML config file:

name: customer-supportslack: customer-support-metrics # slack channels to send report to
email: customer-support@company.com # email-addresses to send to
columns: # columns of the grid are defined by time series calculation
- name: yesterday
type: number # show the result of the query as number
value: yesterday # value to show
- name: vs 7 days ago
type: pctchange # show % change over time
value: yesterday # value to compare
base: daysago(7) # base value
- name: vs a month ago
type: pctchange
value: yesterday
base: monthsago(1)
- name: vs trailing 7-day average
type: pctchange
value: yesterday
base: trailingavg(7)
- name: last 7 days
type: number
value: trailingsum(7)
- name: vs prior 7 days
type: pctchange
value: trailingsum(7)
base: trailingsum(14, 7)
rows: # rows of the grid are defined by SQL queries
- name: Total Zendesk Tickets
query: sql/zendesk_tickets.sql
type: number
- name: Customer Satisfaction Score
query: sql/csat.sql
type: percent
goal: 90
# all other settings are passed directly into Jinja template
header: “Customer support metrics for {yesterday_date} :mail:”
footer: “Take care! :wave:”

Each row of a metric comparison grid is defined by a SQL query. The query needs to return a time series of date and total values, one entry per day:

select date, count(distinct ticket_id) as total
from zendesk_tickets
group by date

Each column of a metric comparison grid is defined by time series calculation. We support two types of columns, number that returns metric value aggregated in time, and percent change that compares the metric over two periods of time or against a goal:

- name: yesterday
type: number
value: yesterday
- name: week over week
type: pctchange
value: lastweek
base: week(2)

We came up with our own simple format to describe time periods for aggregation, with a variety of options supported:

After defining rows and columns, you need to select the Slack and Email recipients:

slack: customer-support-metrics # slack channels to send report to
email: customer-support@company.com # email-addresses to send to

All done! Time to run the framework to send your first report:

python -m compgrid slackimg CONFIG_FILE_PATH
python -m compgrid email CONFIG_FILE_PATH

Implementation Notes

Our needs called for automating metric comparison grid reports on a regular schedule. We use Airflow’s built-in Python operator to do that, but a simple cron setup would also work.

The comparison grid framework supports Jinja2 templating to support console output and formatting for Slack and email updates based on the same config. This also allows users of the framework to override templates for custom styling of each comparison grid if needed.

Ro.Codes

Ro’s Tech Org Blog