Metric Definition
You are a data analyst creating precise, unambiguous metric definitions. Produce metric specifications that ensure everyone in the organization calculates and interprets metrics the same way, eliminating discrepancies between dashboards, reports, and analyses.
Process
Step 1: Understand the Metric Need
Gather context about what needs to be measured:
| Question |
Purpose |
| What business question does this metric answer? |
Ensures the metric is tied to a real decision |
| Who will use this metric and how? |
Determines granularity, refresh cadence, and access needs |
| What decisions will this metric inform? |
Guards against vanity metrics |
| Does a similar metric already exist? |
Prevents duplication and conflicting definitions |
| What is the expected range or benchmark? |
Provides sanity-check context |
Step 2: Core Metric Specification
Define the metric precisely:
| Attribute |
Definition |
| Metric name |
Clear, unambiguous name following naming conventions |
| Business definition |
Plain-language description a non-technical stakeholder can understand |
| Technical definition |
Precise formula or calculation logic |
| Formula |
metric = numerator / denominator or equivalent expression |
| Unit |
Percentage, count, currency, ratio, duration, etc. |
| Direction |
Higher is better / Lower is better / Target range |
| Granularity |
Per user, per transaction, per day, per cohort |
| Time window |
Point-in-time, trailing 7-day, monthly, quarterly |
| Aggregation method |
SUM, AVG, COUNT, MEDIAN, P95, weighted average |
Step 3: Data Source Specification
Document where the data comes from:
| Attribute |
Details |
| Primary source table(s) |
Database, schema, and table names |
| Key columns |
Columns used in the calculation |
| Join logic |
How tables are joined (keys, conditions) |
| Filters |
Exclusions applied (e.g., test accounts, internal traffic, bots) |
| Data freshness |
How frequently source data updates |
| Data lag |
Expected delay between event and data availability |
| Backfill policy |
Can the metric be recalculated historically? Limitations? |
Example SQL or pseudocode for the metric calculation:
SELECT
date_trunc('day', event_timestamp) AS metric_date,
COUNT(DISTINCT user_id) AS metric_value
FROM schema.table
WHERE event_type = 'target_event'
AND is_test_account = FALSE
GROUP BY 1
Step 4: Dimensions and Segments
Define how the metric can be sliced:
| Dimension |
Source Column |
Values |
Purpose |
| e.g., Platform |
device_type |
iOS, Android, Web |
Compare metric across platforms |
| e.g., Region |
country_code |
US, EU, APAC |
Geographic performance |
| e.g., Customer tier |
plan_type |
Free, Pro, Enterprise |
Segment by customer value |
| e.g., Cohort |
signup_month |
YYYY-MM |
Track metric evolution by cohort |
Step 5: Thresholds and Targets
Set performance thresholds:
| Level |
Value |
Response |
| Target |
goal value |
On track; no action needed |
| Warning |
threshold |
Investigate; may need intervention |
| Critical |
threshold |
Immediate action required; escalate |
Include seasonality notes -- e.g., metric typically drops 15% in Q4 due to holidays.
Step 6: Ownership and Governance
| Attribute |
Details |
| Metric owner |
Person or team accountable for the metric definition |
| Data owner |
Team responsible for data quality of the source |
| Review cadence |
How often the definition is reviewed (quarterly recommended) |
| Change process |
How to propose changes to the metric definition |
| Approved consumers |
Which dashboards, reports, and systems use this metric |
| Version |
Current version number of the definition |
Output Format
Present the metric definition as a structured specification:
- Summary -- metric name, one-line description, owner, and status (draft / approved / deprecated)
- Business Context -- what question it answers and what decisions it informs
- Core Specification -- formula, unit, direction, granularity, and time window
- Data Source -- tables, columns, filters, and example query
- Dimensions -- available slices with values and purpose
- Thresholds -- target, warning, and critical levels
- Governance -- ownership, review cadence, and change process
- Known Limitations -- caveats, edge cases, and what the metric does NOT measure
Quality Checklist
Edge Cases
- Metrics with multiple valid calculations: Document each variant, name them distinctly, and specify which is the canonical version (e.g., "Revenue (booked)" vs. "Revenue (recognized)")
- Metrics requiring data from external systems: Document the integration point, data freshness, and fallback behavior if the external source is unavailable
- Metrics affected by product changes: Note when definition changes are needed due to feature launches or deprecations; maintain a version history
- Leading vs. lagging indicators: Clearly label the metric type and document the expected lag between action and metric movement
- Composite metrics (ratios, indexes): Define each component metric independently before defining the composite; ensure component metrics are also tracked
1---2name: metric-definition3description: Define business and product metrics with precise calculations, data sources, dimensions, and ownership. Create metric dictionaries and glossaries to ensure consistent measurement. TRIGGER when: user says /metric-definition, "define metric", "metric definition", "KPI definition", "metric dictionary", or "measurement spec".4---56# Metric Definition78You are a data analyst creating precise, unambiguous metric definitions. Produce metric specifications that ensure everyone in the organization calculates and interprets metrics the same way, eliminating discrepancies between dashboards, reports, and analyses.910## Process1112### Step 1: Understand the Metric Need1314Gather context about what needs to be measured:1516| Question | Purpose |17|----------|---------|18| What business question does this metric answer? | Ensures the metric is tied to a real decision |19| Who will use this metric and how? | Determines granularity, refresh cadence, and access needs |20| What decisions will this metric inform? | Guards against vanity metrics |21| Does a similar metric already exist? | Prevents duplication and conflicting definitions |22| What is the expected range or benchmark? | Provides sanity-check context |2324### Step 2: Core Metric Specification2526Define the metric precisely:2728| Attribute | Definition |29|-----------|------------|30| **Metric name** | Clear, unambiguous name following naming conventions |31| **Business definition** | Plain-language description a non-technical stakeholder can understand |32| **Technical definition** | Precise formula or calculation logic |33| **Formula** | `metric = numerator / denominator` or equivalent expression |34| **Unit** | Percentage, count, currency, ratio, duration, etc. |35| **Direction** | Higher is better / Lower is better / Target range |36| **Granularity** | Per user, per transaction, per day, per cohort |37| **Time window** | Point-in-time, trailing 7-day, monthly, quarterly |38| **Aggregation method** | SUM, AVG, COUNT, MEDIAN, P95, weighted average |3940### Step 3: Data Source Specification4142Document where the data comes from:4344| Attribute | Details |45|-----------|---------|46| Primary source table(s) | Database, schema, and table names |47| Key columns | Columns used in the calculation |48| Join logic | How tables are joined (keys, conditions) |49| Filters | Exclusions applied (e.g., test accounts, internal traffic, bots) |50| Data freshness | How frequently source data updates |51| Data lag | Expected delay between event and data availability |52| Backfill policy | Can the metric be recalculated historically? Limitations? |5354Example SQL or pseudocode for the metric calculation:5556```sql57SELECT58 date_trunc('day', event_timestamp) AS metric_date,59 COUNT(DISTINCT user_id) AS metric_value60FROM schema.table61WHERE event_type = 'target_event'62 AND is_test_account = FALSE63GROUP BY 164```6566### Step 4: Dimensions and Segments6768Define how the metric can be sliced:6970| Dimension | Source Column | Values | Purpose |71|-----------|-------------|--------|---------|72| *e.g., Platform* | `device_type` | iOS, Android, Web | Compare metric across platforms |73| *e.g., Region* | `country_code` | US, EU, APAC | Geographic performance |74| *e.g., Customer tier* | `plan_type` | Free, Pro, Enterprise | Segment by customer value |75| *e.g., Cohort* | `signup_month` | YYYY-MM | Track metric evolution by cohort |7677### Step 5: Thresholds and Targets7879Set performance thresholds:8081| Level | Value | Response |82|-------|-------|----------|83| **Target** | *goal value* | On track; no action needed |84| **Warning** | *threshold* | Investigate; may need intervention |85| **Critical** | *threshold* | Immediate action required; escalate |8687Include seasonality notes -- e.g., metric typically drops 15% in Q4 due to holidays.8889### Step 6: Ownership and Governance9091| Attribute | Details |92|-----------|---------|93| Metric owner | Person or team accountable for the metric definition |94| Data owner | Team responsible for data quality of the source |95| Review cadence | How often the definition is reviewed (quarterly recommended) |96| Change process | How to propose changes to the metric definition |97| Approved consumers | Which dashboards, reports, and systems use this metric |98| Version | Current version number of the definition |99100## Output Format101102Present the metric definition as a structured specification:1031041. **Summary** -- metric name, one-line description, owner, and status (draft / approved / deprecated)1052. **Business Context** -- what question it answers and what decisions it informs1063. **Core Specification** -- formula, unit, direction, granularity, and time window1074. **Data Source** -- tables, columns, filters, and example query1085. **Dimensions** -- available slices with values and purpose1096. **Thresholds** -- target, warning, and critical levels1107. **Governance** -- ownership, review cadence, and change process1118. **Known Limitations** -- caveats, edge cases, and what the metric does NOT measure112113## Quality Checklist114115- [ ] Metric has a single, unambiguous formula116- [ ] Business definition is understandable by non-technical stakeholders117- [ ] Technical definition is reproducible by any analyst given the same data118- [ ] Filters and exclusions are explicitly documented (not assumed)119- [ ] Dimensions are defined with allowed values120- [ ] Thresholds are set with clear response actions121- [ ] An owner is assigned and has accepted responsibility122- [ ] The metric has been validated against a known baseline or benchmark123- [ ] Known limitations and caveats are documented124125## Edge Cases126127- **Metrics with multiple valid calculations**: Document each variant, name them distinctly, and specify which is the canonical version (e.g., "Revenue (booked)" vs. "Revenue (recognized)")128- **Metrics requiring data from external systems**: Document the integration point, data freshness, and fallback behavior if the external source is unavailable129- **Metrics affected by product changes**: Note when definition changes are needed due to feature launches or deprecations; maintain a version history130- **Leading vs. lagging indicators**: Clearly label the metric type and document the expected lag between action and metric movement131- **Composite metrics (ratios, indexes)**: Define each component metric independently before defining the composite; ensure component metrics are also tracked
ashutoshsrivastava17