How to create metrics
What are metrics?
Metrics are used to perform calculations on your Tables.
If dimensions segment your data into groups, metrics calculate interesting statistics for those groups. You can define metrics in your dbt project .yml files along with your dimensions and dbt model properties.
For example, if we have a dimension, status, to split orders by their status, we may want to know the "Total number of orders" or the "Total sales" of the orders. These calculations are metrics:
# schema.yml
version: 2
models:
- name: "orders"
description: "A table of all orders."
columns:
- name: "status"
description: "Status of an order: ordered/processed/complete"
- name: "order_id"
meta:
metrics:
total_order_count:
type: count_distinct
- name: "order_value"
meta:
metrics:
total_sales:
type: sum
You can see the full list of metric types that you can use in your Lightdash project.
We support metrics defined using either Lightdash or dbt syntax! You can read more about the two methods in our reference docs here.
Once you've added your metrics, you can use them in Lightdash to build charts and filter results. Metrics appear in the Explore view, above dimensions and, if selected, pop us as brownish-yellow fields in your results table.
Let's try adding a metric to our dbt project
This tutorial assumes you've set up the Lightdash CLI
If you haven't installed the Lightdash CLI yet, then follow this guide to installing and setting it up.
We're going to try adding a new metric to our dbt project, then syncing it with Lightdash. We've made this really easy to do using our CLI tool.
Add a metric to one of your dbt models
Head to your dbt project, checkout a new branch (or just work on main if that's your style 🤠) and add a metric to one of your dbt models.
We'd suggest starting out simple, like a count for a primary key in your table.
For example:
# schema.yml
version: 2
models:
- name: "orders"
columns:
- name: "status"
- name: "order_id"
meta:
metrics:
total_order_count:
type: count
Preview your changes using lightdash preview
Once you've added a metric to your dbt model, you might want to check to make sure that it's working the way you'd expect. This is where lightdash preview comes in handy.
Developer previews are temporary Lightdash projects where you can safely experiment with your metrics, dimensions and charts without affecting your production project.
So, let's spin up a developer preview and check out our changes. In your terminal, run the commands:
lightdash preview
Then cmd + click to open the preview link from your terminal. Once you're in Lightdash go to Explore --> Tables, then click on the model you just updated to see your new metric and play around with it.
Configure your metric (optional)
You can jazz up your metrics by configuring them in your .yml files. These metric configurations live under the meta tag of your columns, under metrics:
version: 2
models:
- name: "orders"
description: "A table of all orders."
columns:
- name: "status"
description: "Status of an order: ordered/processed/complete"
- name: "order_value"
meta:
metrics:
total_sales:
type: sum
label: "Total sales (USD)"
group_label: "Sales metrics"
round: 2
...etc.
Things like the format, the label that people see in Lightdash, rounding, etc. - these are all configurations that you can apply to your metrics.
You can see all of the metric configurations in our metrics reference doc here.
If you're happy with your new metric, you can deploy it to production.
If you're working with a version controlled project, you'll just want to make sure to merge your changes into the branch you've connected to your Lightdash project (e.g. main or master).
Once they've been merged or if you're just working off of main (rebel 😏), you can deploy your changes.
Once you've merged your changes, you'll want to deploy them to production. To do this, just run these commands in your terminal from your dbt project:
git checkout main # checkout main or master - or whatever your production branch name is
git pull
lightdash deploy # --target prod. If you use developer profiles in your dbt project, you might need this flag. See below.
This will deploy the changes in your dbt project to the Lightdash project you set up on your CLI tool earlier.
Note: Lightdash's deploy command will deploy using your default dbt target unless you specify to use a different target. For example, if you've set up a developer profile where it targets a dev dataset (like dbt_khindson.my_model_names), then you'll need to pass the production target in your lightdash deploy command. Something like: lightdash deploy --target prod.
And voilà! Your new metric is available to explore in Lightdash.
Adding custom metrics in the Explore view
The fields that you see in your dimensions and metrics are created by the people maintaining your Lightdash project.
But, if there's something missing from this list of metrics, you can use custom metrics to add some on-the-fly calculations while you're exploring.
So, we're going to break it down for you: what are custom metrics? and how can I use them? 🪄
Creating a custom metric from a dimension
To create a custom metric from a dimension, you just need to:
- Click on the dimension's three-dot
optionsmenu - Click on one of the options available (e.g.
Count Distinct) - Confirm your changes
Then, your new metric will be added to your results table automatically and will appear in the custom metrics space in your sidebar.
If you want to delete the custom metric, you can just click on the three-dot options menu and hit Remove custom metric
Setting filters to your custom metric
These filters are based on the dimensions set in your project, allowing for more precise metrics. You can add filters when you're creating a new custom metric or editing an existing one.
You can add filters to your custom metric in 2 ways:
By creating a custom metric:
By editing an existing custom metric:
Custom metrics are different from the metrics defined in your YAML
Like we mentioned above, custom metrics are on-the-fly calculations in the Explore view. They're a bit different to the regular metrics you define in your Table's YAML, but some things are the same.
Here are some things about custom metrics we thought would be worth mentioning:
- It's harder to manage custom metrics in your project than regular metrics. We recommend adding the metrics that everyone is going to use regularly to your dbt project. This is because custom metrics are missing out on things like formatting, custom descriptions, and any other configuration options that you have available to you for metrics defined in your dbt project. So, for metrics that are going to have a lot of use, we recommend taking the next step in your data "relationships" and making them a bit more permanent 😉
- You can't persist your custom metrics in the Table. If you add some custom metrics in the Explore view, then these custom metrics won't pop up for the next person that opens the Table.
- But, you can use custom metrics in a saved chart. If you've added custom metrics to your saved chart, and someone else opens the saved chart, they'll see your custom metrics. But, if they open a new, fresh Table in the Explore view, your custom metrics won't appear there.
- You can't use custom SQL in your custom metrics. If you need to write some more complicated SQL to define your metric, then you'll need to define it as a metric in your dbt project.