Define A Metric Precisely For The Whole Org
Turns a fuzzy KPI into a rigorous, unambiguous metric definition with SQL and edge-case rules.
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.
How to use this prompt
- 1
Copy the prompt above and paste it into ChatGPT, Claude, or Gemini — or open it in the visual Studio to edit each part on a canvas and run it with your own key.
- 2
Replace any bracketed placeholders with your specifics. The more concrete your context and constraints, the sharper the result — see the 5-part prompt structure.
- 3
Run it, then refine. Ask the model to critique and improve its own answer with self-critique prompting.
Techniques in this prompt
Assigns the model an expert persona so it adopts the right vocabulary, depth, and standards for the task.
Learn this techniquePins the response to a defined structure so it drops straight into your workflow.
Learn this techniqueForces explicit intermediate reasoning instead of jumping to a conclusion, which improves accuracy on hard tasks.
Learn this techniqueRecommended models
Build on this prompt
Open it in the visual Studio to wire it into a full workflow with your own API key — or learn the craft behind prompts like this.
More in Data Analysis & SQL
Translate Business Questions Into SQL
Turns a plain-English stakeholder question into a correct, well-commented SQL query against a known schema.
Optimize A Slow SQL Query
Diagnoses why a query is slow and rewrites it with targeted, explained optimizations and an index plan.
Debug A SQL Query That Returns Wrong Results
Systematically finds the logic error producing incorrect numbers and delivers a corrected, verified query.
Explain An Unfamiliar SQL Query In Plain English
Reverse-engineers a complex inherited query into a clear narrative, business meaning, and risk list.