Skill: Metric Spec

Purpose

Define any metric clearly and completely using a standardized template so there is no ambiguity about what is being measured, how it's calculated, or how to interpret it.

When to Use

Apply this skill when defining a new metric, when a metric is referenced without a clear definition, or when different people are using the same metric name to mean different things. Every metric used in an analysis should have a spec.

Instructions

Metric Spec Template

## Metric: [Name]

### Definition
**Plain English:** [One sentence a non-technical person can understand]
**Formula:** [Exact calculation]
**Owner:** [team or person accountable for this metric]
**Granularity:** [the grain it is reported at: per user / per order / daily / weekly ...]

### Components
| Component | Definition | Source |
|-----------|-----------|--------|
| **Numerator** | [What's being counted/summed in the top] | [Table.column] |
| **Denominator** | [What's being counted in the bottom (if ratio)] | [Table.column] |
| **Unit of analysis** | [What does one row represent?] | [e.g., per user, per session, per order] |

### Segmentation Dimensions
| Dimension | Values | Why |
|-----------|--------|-----|
| [e.g., Device type] | [mobile, desktop, tablet] | [Different UX → different conversion] |
| [e.g., Acquisition channel] | [organic, paid, referral] | [Different intent → different behavior] |
| [e.g., Geography] | [US, EU, APAC] | [Different markets → different baselines] |

### Data Source
- **Primary table:** [schema.table_name]
- **Key columns:** [list]
- **Refresh cadence:** [real-time / hourly / daily / weekly]
- **Latency:** [how delayed is the data?]
- **Reference query:** [SQL query that computes this metric — the canonical implementation]

### Guardrails
| Condition | Value | Action |
|-----------|-------|--------|
| **Healthy** | [e.g., >3.5%] | No action needed |
| **Watch** | [e.g., 2.5-3.5%] | Monitor weekly, investigate if persists >2 weeks |
| **Investigate** | [e.g., <2.5%] | Root cause analysis within 48 hours |
| **Alert** | [e.g., <1.5%] | Escalate to leadership, immediate investigation |

**Typical range:** [the band the metric normally lives in, with its basis: "3.4-4.2% over the last 6 months"]

### Known Limitations
- [Limitation 1: e.g., "Does not include guest checkouts — only registered users"]
- [Limitation 2: e.g., "Affected by bot traffic; filter using is_bot flag"]
- [Limitation 3: e.g., "Denominator changes when new markets launch — compare like-for-like"]

### Related Metrics
- [Upstream: what drives this metric?]
- [Downstream: what does this metric drive?]
- [Alternative: other ways to measure the same concept]

### Driver Decomposition (Optional)
If this is a key business metric, decompose it into its drivers to enable faster diagnosis when the metric changes.

**Decomposition type:** [Multiplicative / Additive]

| Driver | Formula | Relationship | Data Source |
|--------|---------|-------------|-------------|
| [driver 1] | [formula] | [× / +] | [table.column] |
| [driver 2] | [formula] | [× / +] | [table.column] |
| [driver 3] | [formula] | [× / +] | [table.column] |

**Diagnostic rule:** If [parent metric] drops, check these drivers in order:
1. [driver 1] — [why this is the most likely cause / highest leverage]
2. [driver 2] — [what changes in this driver would look like]
3. [driver 3] — [least common but possible]

**Verification:** [parent metric] = [driver 1] × [driver 2] × [driver 3] (for multiplicative)
or [parent metric] = [driver 1] + [driver 2] + [driver 3] (for additive)

Writing Rules

1. Definition must be unambiguous — two different analysts reading the spec should write the same SQL
2. Always specify the denominator — "conversion rate" is meaningless without knowing what's in the denominator (visitors? sessions? users?)
3. Always specify the time window — "DAU" measured daily is different from "DAU" measured as a 7-day average
4. Always specify exclusions — which users/events are filtered out? (test accounts, internal users, bots)
5. Guardrails should be based on historical data — not gut feel. State the basis: "Based on 6-month average of 3.8% ± 0.4%"

Workflow: Write Spec → Register to Knowledge System

After writing the spec, register it to the knowledge system so it is discoverable and reusable (steps below).

Registration Steps (execute these immediately after completing the metric spec):

1. Read active dataset ID: Read .knowledge/active.yaml to get the active dataset name

2. Check metrics directory: Verify .knowledge/datasets/{active}/metrics/ directory exists. If not, create it.

3. Generate metric ID: Convert metric name to ID format: lowercase, hyphens, no spaces

   • Example: "Checkout Conversion Rate" → checkout-conversion-rate

4. Check for existing entry: Read .knowledge/datasets/{active}/metrics/index.yaml. If the metric ID exists, you're updating. If not, you're creating new.

5. Write metric YAML: Create .knowledge/datasets/{active}/metrics/{id}.yaml with exactly the fields the metrics skill displays:

   • name: The metric's display name
   • owner: Copy from the Owner field (null if not stated)
   • definition.plain_english: Copy from Plain English field
   • definition.formula: Copy from Formula field
   • definition.unit: Infer from formula (%, count, currency, ratio)
   • definition.direction: Infer from guardrails (higher_is_better / lower_is_better)
   • definition.granularity: Copy from the Granularity field (fall back to Unit of analysis)
   • source.tables: List primary table(s) from Data Source section
   • source.sql: Copy reference query if provided
   • dimensions: Array of dimension column names from Segmentation Dimensions
   • guardrails: Map guardrail conditions to values (the field is named guardrails, not thresholds)
   • typical_range: Copy from the Typical range line (null if unknown)
   • validation_status: draft on first registration; set to validated once the reference query has been run against the data and its result checked
   • last_validated: the date the reference query was last verified against the data (null until then)
   • limitations: Array of strings from Known Limitations

6. Update index: Update .knowledge/datasets/{active}/metrics/index.yaml with an entry:

   - id: checkout-conversion-rate
     name: Checkout Conversion Rate
     category: conversion  # infer: conversion, engagement, revenue, retention, etc.
     direction: higher_is_better
     validation_status: draft
     created: YYYY-MM-DD
     updated: YYYY-MM-DD
   

   (direction and validation_status are in the index because the metrics skill's list view displays them.)

7. Add a compile: block when the metric is a single aggregate (recommended). If the metric is one measure over one table with named dimensions and filters (not a join, a window function, or multi-metric arithmetic), add a compile: block so the metric compiler can compute it deterministically (Tier A). This is what turns a defined metric from "the model writes SQL from the definition" into "the same number every run." Shape:

   compile:
     measure: "AVG(Volume)"          # aggregate expression over columns of `table`
     table: sp500_daily
     time_column: Date               # optional; enables date filters
     grain: day                      # documentation of what one input row represents
     grain_key: [Date]               # columns that uniquely identify a row; the fan-out guard
                                     # halts if the table has more rows than distinct grain keys
     dimensions:                     # public name -> column/expression; whitelisted group-bys
       year: "extract(year from Date)"
     filters:                        # public name -> WHERE fragment with :params (bound, not interpolated)
       year: "extract(year from Date) = :year"
     denominator: "SUM(SUM(Volume)) OVER ()"   # set only for a ratio
     value_bounds: [0, 1]            # valid range for a ratio; the guard halts outside it (default [0,1])
     requires_columns: [Volume, Date]
   

   Set the index entry's compilable: true. If the metric needs a join, a window over rows, or more than one measure, do NOT add a compile: block; it stays on the generate-and-validate path, which is correct. Full spec and guards: helpers/data/metric_compiler.py.

Why this matters: Registering metrics to the knowledge system enables:

• Future analyses can reference the canonical definition
• Other analysts can discover what metrics already exist
• The system can warn when metrics are being redefined differently
• Metric lineage and usage can be tracked

Examples

Example 1: Conversion Rate

## Metric: Checkout Conversion Rate

### Definition
**Plain English:** The percentage of users who visit the checkout page and complete a purchase.
**Formula:** (Users who completed purchase) / (Users who viewed checkout page) × 100

### Components
| Component | Definition | Source |
|-----------|-----------|--------|
| **Numerator** | Distinct users with a `purchase_completed` event within 24h of checkout view | events.event_type = 'purchase_completed' |
| **Denominator** | Distinct users with a `checkout_viewed` event | events.event_type = 'checkout_viewed' |
| **Unit of analysis** | Per user per day (deduplicated — a user counts once even with multiple checkout views) |

### Segmentation Dimensions
| Dimension | Values | Why |
|-----------|--------|-----|
| Device type | mobile, desktop, tablet | Mobile checkout has different UX friction |
| Payment method | credit card, PayPal, Apple Pay | Different failure rates by method |
| New vs returning | first purchase, repeat | Different conversion baselines |

### Data Source
- **Primary table:** analytics.events
- **Key columns:** user_id, event_type, event_timestamp, device_type, properties.payment_method
- **Refresh cadence:** Hourly
- **Latency:** ~2 hours from event to availability

### Guardrails
| Condition | Value | Action |
|-----------|-------|--------|
| **Healthy** | >3.5% | No action |
| **Watch** | 2.5-3.5% | Monitor; check if specific segment is dragging |
| **Investigate** | <2.5% | Root cause within 48h; check payment processor, page load times |
| **Alert** | <1.5% | Immediate escalation; likely a bug or outage |

### Known Limitations
- Does not include guest checkouts (only logged-in users)
- 24h attribution window means some slow purchasers are excluded
- Bot filtering depends on `is_bot` flag accuracy (~95% reliable)

Example 2: Revenue Metric

## Metric: Monthly Recurring Revenue (MRR)

### Definition
**Plain English:** The total monthly revenue from all active subscriptions, normalized to a monthly rate.
**Formula:** SUM(active_subscriptions × monthly_equivalent_price) as of the last day of the month

### Components
| Component | Definition | Source |
|-----------|-----------|--------|
| **Numerator** | Sum of monthly-equivalent price for all subscriptions with status='active' on the measurement date | subscriptions.price / (billing_interval_months) |
| **Denominator** | N/A (absolute metric, not a ratio) | — |
| **Unit of analysis** | Per month, measured on last calendar day |

### Segmentation Dimensions
| Dimension | Values | Why |
|-----------|--------|-----|
| Plan tier | free, starter, pro, enterprise | Different ARPU and churn dynamics |
| Billing interval | monthly, annual | Annual has lower churn but deferred revenue |
| Cohort month | signup month | Tracks retention and expansion by cohort |

### Guardrails
| Condition | Value | Action |
|-----------|-------|--------|
| **Healthy** | MoM growth >3% | On track for annual targets |
| **Watch** | MoM growth 0-3% | Dig into new vs expansion vs churn components |
| **Investigate** | MoM growth <0% | Net churn exceeding new business — root cause urgently |

### Known Limitations
- Annual subscriptions are divided by 12 for monthly equivalent; actual cash flow differs
- Does not include one-time fees, implementation fees, or overages
- Enterprise custom pricing may lag in system — verify against finance for board reporting

Example 3: Engagement Metric

## Metric: DAU/MAU Ratio (Stickiness)

### Definition
**Plain English:** The percentage of monthly users who use the product on any given day. Higher = more habitual usage.
**Formula:** (Average daily active users in the month) / (Monthly active users) × 100

### Components
| Component | Definition | Source |
|-----------|-----------|--------|
| **Numerator** | Average of daily distinct users with ≥1 meaningful action, averaged across all days in the month | AVG(daily_active_users) where action ∈ meaningful_actions |
| **Denominator** | Distinct users with ≥1 meaningful action in the entire month | COUNT(DISTINCT user_id) for the month |
| **Unit of analysis** | Per month |

### Segmentation Dimensions
| Dimension | Values | Why |
|-----------|--------|-----|
| User tenure | <30d, 30-90d, 90-365d, >365d | New users have different patterns |
| Plan tier | free, paid | Paid users should be stickier |
| Platform | web, iOS, Android | Mobile tends to be stickier |

### Guardrails
| Condition | Value | Action |
|-----------|-------|--------|
| **Healthy** | >25% | Strong daily habit (comparable to social apps) |
| **Watch** | 15-25% | Typical for B2B SaaS; look for improvement opportunities |
| **Investigate** | <15% | Weak daily habit; investigate activation and feature adoption |

### Known Limitations
- "Meaningful action" definition matters enormously — login alone should NOT count
- Weekday/weekend patterns affect daily averages; consider business-day-only variant for B2B
- Bots and automated API calls must be excluded or this metric is inflated

Example 4: Metric with Driver Decomposition

## Metric: Revenue

### Definition
**Plain English:** Total revenue from completed orders in a period.
**Formula:** COUNT(orders) × AVG(order_value)

### Components
| Component | Definition | Source |
|-----------|-----------|--------|
| **Numerator** | Sum of total_amount for orders with status='completed' | orders.total_amount WHERE status='completed' |
| **Denominator** | N/A (absolute metric) | — |
| **Unit of analysis** | Per month |

### Driver Decomposition
**Decomposition type:** Multiplicative

Revenue = Active Users × Orders per User × Average Order Value

| Driver | Formula | Relationship | Data Source |
|--------|---------|-------------|-------------|
| Active Users | COUNT(DISTINCT user_id) with ≥1 order in period | × | orders.user_id |
| Orders per User | COUNT(orders) / COUNT(DISTINCT user_id) | × | orders |
| Average Order Value | SUM(total_amount) / COUNT(orders) | × | orders.total_amount |

**Diagnostic rule:** If Revenue drops, check these drivers in order:
1. Active Users — did fewer users place orders? (acquisition or retention problem)
2. Orders per User — did users buy less frequently? (engagement or value problem)
3. Average Order Value — did users spend less per order? (pricing, mix shift, or promo problem)

**Verification:** Revenue = Active Users × Orders per User × AOV

Anti-Patterns

1. Never define a metric without specifying the denominator — "conversion rate" is meaningless without context
2. Never use a metric name that means different things to different teams — if marketing's "conversion" ≠ product's "conversion," create two separate specs
3. Never set guardrails without historical data — arbitrary thresholds lead to false alarms or missed problems
4. Never skip the "known limitations" section — every metric has caveats, and hiding them doesn't make them go away
5. Never use a ratio without understanding what moves the numerator vs. denominator independently — a "improving" conversion rate could mean you lost low-intent traffic, not that you improved the product

Reference Queries for Common Metrics

Use these canonical SQL patterns when computing standard metrics. Replace {schema} with the active dataset schema (e.g., your_dataset).

Conversion Rate (Event-Based)

-- Conversion rate: % of users who performed action B after action A
SELECT
    COUNT(DISTINCT CASE WHEN b.user_id IS NOT NULL THEN a.user_id END) * 1.0
    / NULLIF(COUNT(DISTINCT a.user_id), 0) AS conversion_rate
FROM {schema}.events a
LEFT JOIN {schema}.events b
    ON a.user_id = b.user_id
    AND b.event_type = '{{TARGET_EVENT}}'
    AND b.timestamp >= a.timestamp
    AND b.timestamp <= a.timestamp + INTERVAL '{{WINDOW}}'
WHERE a.event_type = '{{SOURCE_EVENT}}'
    AND a.timestamp BETWEEN '{{START_DATE}}' AND '{{END_DATE}}';

Revenue (Order-Based)

-- Total revenue and order count for a period
SELECT
    COUNT(DISTINCT order_id) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value,
    COUNT(DISTINCT user_id) AS purchasing_users
FROM {schema}.orders
WHERE status = 'completed'
    AND order_date BETWEEN '{{START_DATE}}' AND '{{END_DATE}}';

Active Users (DAU / WAU / MAU)

-- Daily/Weekly/Monthly active users
SELECT
    DATE_TRUNC('{{GRANULARITY}}', timestamp) AS period,
    COUNT(DISTINCT user_id) AS active_users
FROM {schema}.events
WHERE event_type IN ({{QUALIFYING_EVENTS}})
    AND timestamp BETWEEN '{{START_DATE}}' AND '{{END_DATE}}'
GROUP BY 1
ORDER BY 1;

Retention Rate (Cohort-Based)

-- Cohort retention: % of users active in period N after signup
WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('{{GRANULARITY}}', signup_date) AS cohort
    FROM {schema}.users
),
activity AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('{{GRANULARITY}}', timestamp) AS active_period
    FROM {schema}.events
)
SELECT
    c.cohort,
    DATE_DIFF('{{GRANULARITY}}', c.cohort, a.active_period) AS period_number,
    COUNT(DISTINCT a.user_id) * 1.0
    / NULLIF(COUNT(DISTINCT c.user_id), 0) AS retention_rate
FROM cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

NPS (Net Promoter Score)

-- Net Promoter Score: % promoters - % detractors
SELECT
    COUNT(CASE WHEN score >= 9 THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0)
    - COUNT(CASE WHEN score <= 6 THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0) AS nps,
    COUNT(CASE WHEN score >= 9 THEN 1 END) AS promoters,
    COUNT(CASE WHEN score BETWEEN 7 AND 8 THEN 1 END) AS passives,
    COUNT(CASE WHEN score <= 6 THEN 1 END) AS detractors,
    COUNT(*) AS total_responses
FROM {schema}.nps_responses
WHERE submitted_at BETWEEN '{{START_DATE}}' AND '{{END_DATE}}';

Usage notes:

• Always replace {schema} with the active dataset's schema prefix
• Replace {{VARIABLE}} placeholders with actual values for the analysis
• These are starting patterns — adapt WHERE clauses and JOINs for your specific data model
• Always validate output with the Data Quality Check skill before drawing conclusions