| name | metrics-definition |
| description | Defines business metrics with precision — formula, grain, calculation window, data source, and edge cases. Creates a metrics registry with unambiguous specifications to prevent dashboard drift and calculation mismatches. |
| allowed-tools | Read, Write |
| effort | medium |
When to activate
When building a metrics layer, creating KPI definitions for stakeholders, or reconciling dashboard metric discrepancies. Run before implementing metrics in dbt, a semantic layer, or dashboard tool. Use whenever a business metric needs a single source of truth.
When NOT to use
Not for ad-hoc metric exploration — this skill is for canonical, production metrics. Not without stakeholder alignment on what the metric should measure. Not for exploratory analysis without clear business context.
Metrics Definition Checklist
- Identify the metric — What business outcome does it measure (revenue, churn, DAU, NPS)?
- Define the formula — Explicit SQL or calculation steps; no ambiguity
- Set the grain — Atomic level (daily, user-level, transaction-level)
- Specify the window — MTD, YTD, rolling 30 days, point-in-time?
- Document the source — Which fact table, dimension, or raw table?
- List edge cases — Nulls, outliers, data quality issues, cancellations, refunds
- Specify SLA — Latency requirement (real-time, hourly, daily, weekly)
- Owner & stakeholders — Who owns the metric; who consumes it
Metric Types
Fact metric — Counts or sums directly from raw events (transactions, page views, sign-ups). Example: total_revenue, order_count.
Ratio metric — Quotient of two other metrics (conversion_rate = conversions / visitors). Risk: division by zero edge cases.
Derived metric — Calculated from other metrics or dimensions (net_revenue = gross_revenue - refunds - discounts).
Cohort metric — Grouped by cohort dimension, tracked over time (cohort_retention = active_users_this_month / users_in_cohort).
Derived time-series metric — Change over time (month_over_month_growth = (current_month - prior_month) / prior_month).
Metrics Registry Template
# Metrics Registry
**Team:** [Team name]
**Last Updated:** [date]
**Owner:** [Name, role]
---
## Metric: [Metric Name]
**Business Definition:** [One sentence — what does this metric measure and why?]
**Formula:**
\`\`\`sql
SELECT
SUM(transaction_amount) AS metric_value
FROM fact_transactions
WHERE DATE(transaction_date) >= '2026-01-01'
AND DATE(transaction_date) < '2026-02-01'
AND transaction_status = 'completed'
\`\`\`
**Grain:** [Monthly / Daily / User-level / Transaction-level]
**Calculation Window:** [Calendar month / Rolling 30 days / Fiscal quarter]
**Data Source:** [fact_transactions] from [Data warehouse name]
**Latency:** [Real-time / Hourly / Daily / Weekly]
**Owner:** [Name]
**Stakeholders:** [Teams/roles that consume this metric]
---
## Edge Cases & Handling
| Case | Value | Treatment |
|------|-------|-----------|
| Refunded transactions | Negative amount | Included in sum (net revenue) |
| Cancelled orders | NULL status | Excluded (WHERE status = 'completed') |
| Multi-currency | EUR, GBP, JPY | Converted to USD at transaction_date rate |
| Free trial users | $0 | Excluded (WHERE subscription_type != 'trial') |
---
## Calculation Example
**Period:** January 2026
**Expected Output:** ~$2.4M
**Debug Query:**
\`\`\`sql
SELECT
transaction_date,
COUNT(*) AS tx_count,
SUM(transaction_amount) AS daily_revenue
FROM fact_transactions
WHERE DATE(transaction_date) >= '2026-01-01'
AND DATE(transaction_date) < '2026-02-01'
AND transaction_status = 'completed'
GROUP BY transaction_date
ORDER BY transaction_date;
\`\`\`
---
## Implementation
**In dbt:** Create a metric node in `schema.yml`:
\`\`\`yaml
metrics:
- name: total_revenue
label: Total Revenue
model: fact_transactions
description: "Sum of all completed transactions in USD"
calculation_method: sum
expression: transaction_amount
filters:
- field: transaction_status
operator: '='
value: 'completed'
time_grains: [day, month, quarter, year]
\`\`\`
**In semantic layer:** Register metric in [Tool name] with refresh cadence and permissions.
---
## Reconciliation
**Dashboard vs. Report mismatch?** Check:
1. Calculation window (MTD vs. calendar month vs. rolling)
2. Data freshness (real-time vs. 1-day lag)
3. Filter differences (active users vs. all users)
4. Dimension slice (country filter, subscription type, etc.)
---
### [Metric 2 Name]
**Business Definition:** [definition]
**Formula:** [SQL]
**Grain:** [grain]
... (repeat template)
---
Example
Metrics Registry
Team: Analytics
Last Updated: June 12, 2026
Owner: Alice Chen, Senior Analytics Engineer
Metric: Monthly Recurring Revenue (MRR)
Business Definition: Total predictable revenue from active subscriptions at month-end, excluding one-time purchases and trials.
Formula:
SELECT
DATE_TRUNC('month', DATE(snapshot_date))::DATE AS month_end,
SUM(monthly_subscription_amount) AS mrr
FROM dim_subscriptions
WHERE snapshot_date = DATE_TRUNC('month', DATE(snapshot_date)) + INTERVAL '1 month' - INTERVAL '1 day'
AND subscription_status = 'active'
AND subscription_type NOT IN ('trial', 'free')
GROUP BY DATE_TRUNC('month', DATE(snapshot_date));
Grain: Monthly (aggregated across all active subscriptions at month-end)
Calculation Window: Calendar month (measured at last day of month)
Data Source: dim_subscriptions from Data warehouse
Latency: Daily (snapshot taken nightly)
Owner: Alice Chen
Stakeholders: Finance, Product, Executive Leadership
Edge Cases & Handling
| Case | Value | Treatment |
|---|
| Trial subscriptions | $0 or small | Excluded (subscription_type != 'trial') |
| Cancelled mid-month | Prorated | Counted if active on month-end snapshot |
| Annual plans | $X/12 | Stored in monthly_subscription_amount column |
| Paused subscriptions | $0 | Excluded (subscription_status = 'active') |
Calculation Example
Period: May 2026
Expected Output: ~$1.8M
Debug Query:
SELECT
subscription_id,
customer_id,
monthly_subscription_amount,
subscription_status,
subscription_type
FROM dim_subscriptions
WHERE snapshot_date = '2026-05-31'
AND subscription_status = 'active'
AND subscription_type NOT IN ('trial', 'free')
LIMIT 100;
Implementation
In dbt:
metrics:
- name: monthly_recurring_revenue
label: MRR
model: dim_subscriptions
description: "Total monthly recurring revenue from active subscriptions"
calculation_method: sum
expression: monthly_subscription_amount
filters:
- field: subscription_status
operator: '='
value: 'active'
- field: subscription_type
operator: not_in
value: ['trial', 'free']
time_grains: [month, quarter, year]