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
Translate Business Questions Into SQL
Turns a plain-English stakeholder question into a correct, well-commented SQL query against a known schema.
Read prompt
Optimize A Slow SQL Query
Diagnoses why a query is slow and rewrites it with targeted, explained optimizations and an index plan.
Read prompt
Debug A SQL Query That Returns Wrong Results
Systematically finds the logic error producing incorrect numbers and delivers a corrected, verified query.
Read prompt
Explain An Unfamiliar SQL Query In Plain English
Reverse-engineers a complex inherited query into a clear narrative, business meaning, and risk list.
Read prompt