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.

How to use this prompt

  1. 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. 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. 3

    Run it, then refine. Ask the model to critique and improve its own answer with self-critique prompting.

Techniques in this prompt

Role-Based

Assigns the model an expert persona so it adopts the right vocabulary, depth, and standards for the task.

Learn this technique
Structured Output

Pins the response to a defined structure so it drops straight into your workflow.

Learn this technique
Step-by-Step

Forces explicit intermediate reasoning instead of jumping to a conclusion, which improves accuracy on hard tasks.

Learn this technique

Recommended models

claudegpt-4ogemini

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