Metric Definition Template & Hygiene Checklist

Purpose

This template helps teams create clear, authoritative metric definitions that guide day-to-day decisions and reduce confusion across dashboards and reports. Use it whenever you introduce, change, or document a metric so everyone relies on the same source of truth.

How to use

Fill every section below. Keep the definition short and precise so a technical person can implement it and a non-technical consumer can understand what the metric means and how to act on it. Store the completed definition where dashboards, reports, and teams can reference it (for example: a central metric catalog or the related domain collection).

Metric definition fields

1. Metric name (canonical):

   Short, consistent name used across systems (example: "Orders Shipped On-Time Rate").

2. Short description / intent:

   One-sentence explanation of what decision or behavior the metric should inform. Example: "Tracks the percent of customer orders shipped by the committed ship date — used to prioritize fulfillment issues and measure logistics performance."

3. Owner / steward:

   Name, role, and contact (primary owner responsible for accuracy and issues; secondary owner for escalation).

4. Consumers / stakeholders:

   Who uses this metric and why (teams, dashboards, SLAs, reports).

5. Business goal / target:

   What good looks like, tolerance bands, and the cadence for evaluation (e.g., target 95% monthly, yellow 90–95%, red <90%).

6. Owner-level SLA / response expectation:

   When the owner must respond to a breach (e.g., investigate within 2 business days, apply corrective action within 10 business days).

7. Precise calculation (SQL / pseudocode):

   Provide SQL, pseudocode, or an expression that a data engineer can implement verbatim. Include windowing rules, aggregation level, joins, filters, handling of nulls, and time-zone treatment. Example:

   -- Example: Orders Shipped On-Time Rate (monthly)
   SELECT
     date_trunc('month', delivered_at) AS month,
     SUM(CASE WHEN delivered_at <= promised_ship_date THEN 1 ELSE 0 END)::decimal
       / NULLIF(COUNT(order_id),0) AS on_time_rate
   FROM shipments
   WHERE status = 'delivered'
     AND delivered_at BETWEEN :start_date AND :end_date;
           

8. Reporting grain / aggregation:

   Define the natural grain (per order, per SKU, per facility), and supported rollups (daily, weekly, monthly). State whether metric is a ratio, count, rate, or index.

9. Windowing and alignment rules:

   How events are assigned to periods (ship date vs. delivered_at), lookback windows, and late-arrival corrections.

10. Filters / exclusions and caveats:

    List valid/invalid data subsets (e.g., exclude test orders, cancellations, returns, or special-handling shipments). Note known observational caveats (e.g., incomplete tracking in region X before date Y).

11. Upstream data sources & owners:

    Tables, event streams, files, or APIs used and the teams or systems that own them. Include column-level mapping when relevant.

12. Data quality checks / monitoring suggestions:

    Concrete automated tests (see checklist below). Include acceptable ranges for counts and ratios, schema change detection, and freshness checks.

13. Version (date) and change log:

    Record who edited the definition, why, and when. Reference a deprecation policy for obsolete metrics.

14. Related artifacts & dashboards:

    Links to dashboards, notebooks, and reports that use this metric. State canonical dataset or view name to be used by reports.

15. Action guidance:

    When the metric signals an issue, what are the next steps the owner and consumers should take? Include examples of actionable thresholds and playbooks if available.

Hygiene checklist (use before publishing or changing a metric)

• Is there a single canonical SQL/pseudocode statement that implements the metric?
• Is the metric name consistent across dashboards and reports?
• Has the owner reviewed and accepted the calculation and caveats?
• Do automated tests exist for: schema drift, row counts, null rates, acceptable ranges, and freshness?
• Are tolerances and targets documented and approved by stakeholders?
• Are exclusions and special cases explicitly documented?
• Are upstream data source owners identified and informed of downstream dependencies?
• Is there a version history and a published deprecation policy?
• Are consumers linked to dashboards and playbooks for typical actions?
• Has the metric been sanity-checked against a small sample of known cases or hand-calculated examples?

Automated data-quality (DQ) tests — recommended examples

• Freshness: confirm latest event timestamp within expected latency (e.g., last 2 hours).
• Volume sanity: row counts within historical mean ± 3σ for the period.
• Null/invalid values: critical columns < threshold (e.g., <1%).
• Distribution checks: compare current ratio to rolling baseline; alert on significant drift.
• Schema change detection: fail when new columns appear or expected columns are missing.

Example: filled metric

Metric name: Orders Shipped On-Time Rate

Intent: Measure percent of orders delivered by promised ship date to prioritize fulfillment fixes.

Owner: Logistics Analytics Lead (logistics-analytics@example.com)

Calculation (pseudocode): See SQL block above. Aggregation: monthly per region. Filters: exclude test orders and orders with cancellation > 0.

Target: >= 95% monthly. SLA: investigate if monthly < 90%.

Common pitfalls and how to avoid them

• Avoid ambiguous time fields — explicitly state which timestamp determines period assignment.
• Don’t implicitly rely on dashboard filters — embed required business filters into the canonical calculation.
• Prevent multiple canonical implementations — publish the canonical SQL and reference it in dashboards or materialized views.
• Beware of small-sample volatility — prefer smoothing or minimum-sample rules before alerting on percentages.

Naming conventions (suggested)

• Use clear nouns with context: [BusinessArea] — [Metric] — [Measure]. Example: "Logistics — On-Time Ship Rate — %"
• Include unit or rate indicator where helpful (%, count, days).

Monitoring and lifecycle

Publish metrics to a central catalog. Configure automated DQ tests and alerting. Maintain version history and a deprecation process: a metric may be marked deprecated but remain available for 90 days with a recommended replacement.

Where this fits in a reusable domain

Keep metric definitions as part of your Metrics & KPI Library so teams can copy, tailor, and inherit canonical definitions across business units. When appropriate, bundle related metrics and hygiene checks into a small toolkit (for example: Fulfillment Metrics Toolkit) that teams can adopt.