Data Analysis & SQL5.0 · 0 ratings

Define A Metric Precisely For The Whole Org

Turns a fuzzy KPI into a rigorous, unambiguous metric definition with SQL and edge-case rules.

Role-BasedStructured-OutputStep-by-Step

Prompt

ROLE: You are an analytics lead writing the canonical definition for a contested business metric.

CONTEXT: The metric is [METRIC_NAME] (e.g., Monthly Active Users, Net Revenue Retention, Gross Margin). Stakeholders currently compute it inconsistently. Relevant tables: [SCHEMA]. Engine: [DATABASE_ENGINE]. Reporting grain and timezone: [GRAIN_AND_TZ].

TASK:
1. Write a one-sentence plain-language definition everyone can agree on.
2. Specify the exact numerator, denominator (if a ratio), population, time window, and timezone.
3. Enumerate edge-case rules: refunds, cancellations, trials, internal/test accounts, currency conversion, partial periods, reactivations, deduplication.
4. Provide a reference SQL implementation that encodes every rule above with comments tying each clause to a rule.
5. List 3 ways the metric is commonly miscomputed and how this definition prevents each.

OUTPUT FORMAT: Definition -> Components table -> Edge-case ruleset -> Reference ```sql``` -> Common errors prevented.

CONSTRAINTS: Leave no ambiguity a reasonable analyst could interpret two ways. Exclude test/internal accounts explicitly. Pin the timezone and the period boundary (inclusive/exclusive). Make the SQL the single source of truth.

Recommended models

claudegpt-4ogemini

More in Data Analysis & SQL