Metrics
Query the metric
A metric is an aggregation over a table. In general, it consists
- The model to aggregate. (e.g.
Events
) - The column to aggregate (e.g.
user_id
) - The aggregation method (e.g. distinct count)
- The time column to group (e.g.
event_time
) - The time granularity for grouping (e.g.
group by date_trunc('DAY', event_time)
- The dimension for grouping (e.g.
group by country
) - The filters
So if we can define a daily active users metric as
The daily active user is the distinct count of theuser_id
in theEvents
table, and group the result by the date of theevent_time
The dbt metrics provide a way to define the metrics, and PipeRider implements the query of the metrics according to the dbt metrics definition.
There are three steps to
- define metrics in your dbt project
- update the dbt manfests
- run piperider
To define a metric, please see the dbt metric document to see how to define a metric in your dbt project
models/marts/metrics.yml
metrics:
- name: active_users
label: Active Users
model: ref('stg_events')
description: "The active user"
calculation_method: count_distinct
expression: user_id
timestamp: event_time
time_grains: [day, week, month, year]
+ tags: ['piperider']
To tell PipeRider to query your metrics, you must add the
piperider
tag. The tag name can be changed in the config.yml
The following command will list the metrics that you have just modified.
dbt list -s tag:piperider --resource-type metric
Now, you can try if the metric is successfully queried
piperider run
View metrics in your PipeRider Report. Metric query results can is shown in the
Metrics
page
PipeRider queries each
time_grain
except all_time.
This results in a maximum of 5 queries per metric. For instance, given the following metric, PipeRider would perform four queries: daily
, weekly
, monthly
, and yearly
active users.metrics:
- name: active_users
label: Active Users
model: ref('stg_events')
description: "The active user"
calculation_method: count_distinct
expression: user_id
timestamp: event_time
time_grains: [day, week, month, year]
tags: ['piperider']
For each time grains, PipeRider only queries the last n periods of the table data
time_grain | metric query |
---|---|
day | daily result for last 30 days |
week | weekly result for last 12 week |
month | monthly result for last 12 months |
quarter | quarterly result for last 10 quarters |
year | yearly result for last 10 years |
PipeRider currently supports only the default queries. Queries for specific dimensions and other query customization will be added in the future. Tell us if you consider it is super useful for us. It will help us to prioritize them earlier.
When developing a new metric, it is time-consuming to run through all the piperider run to test the metric result. A tip is that use the
dbt list
to shorten the development cycle- 1.Update the dbt yaml file
- 2.Update the dbt manifest by
dbt compile
- 3.Run piperider by dbt list output
dbt list -s metric:active_users | piperider run --dbt-list
With PipeRider's compare feature, you're also able to compare metrics between reports, which is particularly useful when analyzing the impact of data model or metric definition changes.
.png?alt=media)
PipeRider supports the follow dbt metric properties.
Field | Support |
---|---|
name | yes |
model | yes |
label | yes |
description | yes |
calculation_method | count, count_distinct, sum, average, min, max, derived |
expression | yes |
timestamp | yes |
time_grains | yes |
dimensions | Dimensions are currently not shown on metric charts |
window | No. Support for window is coming soon |
Metrics that use the 'window' property are currently not supported and will not be queried. Support for 'window' is will be added in an upcoming release.
- ratios
- subtractions
- any arbitrary calculation
models/marts/<derived-metric>.yml
metrics:
- name: average_revenue_per_customer
label: Average Revenue Per Customer
description: "The average revenue received per customer"
calculation_method: derived
expression: "{{metric('total_revenue')}} / {{metric('count_of_customers')}}"
tags: ['piperider']
Last modified 10d ago