dashboard-metric-label-vs-sql-definition

name	dashboard-metric-label-vs-sql-definition
description	Write precise client-facing definitions of dashboard KPIs / metrics by reading the backing SQL, not the display label. Use when: (1) drafting an "explain this dashboard to a client" doc, tooltip, onboarding email, or page-by-page narration; (2) the user pushes back on a metric definition you wrote ("that's not what X means"); (3) you're about to describe what a KPI "means" using its rendered label, the variable name in Python, or the column alias in SQL; (4) writing release notes, ADRs, or methodology notes for a metric. Covers the two traps caught repeatedly: (a) display label diverges from underlying SQL (e.g. "Active Students" labels a query that is literally `COUNT(*) FROM predictions WHERE scoring_date = MAX(scoring_date) AND <term>_suppressed IS NULL` — i.e. "rows in today's scoring run, post-suppression"); (b) UI toggles (term, lookback, segment) silently re-parametrize the count, so the same number means different things depending on which pill is active.
author	wan-huiyan + Claude Code
version	1.0.0
date	"2026-05-07T00:00:00.000Z"

Dashboard Metric: Label vs. SQL Definition

Problem

When writing client-facing copy about a dashboard, it's tempting to use the rendered KPI label, the section header, or the Python variable name as the definition of the metric. These are display strings, not semantic definitions. The actual quantity is whatever the SQL SELECT clause computes after the WHERE and GROUP BY resolve. They often disagree:

• The label "Active Students" sounds like "students with recent activity"
• The variable name active_students reinforces that read
• But the SQL is literally: COUNT(*) FROM predictions WHERE scoring_date = MAX(scoring_date) AND <term>_suppressed IS NULL
• Which actually means: "rows in today's scoring run, after this term's suppression filter"

The miss matters in two compounding ways:

1. Semantic drift — the client-facing definition is wrong, even if the number is right.
2. Toggle dependence — UI controls (term toggle, lookback selector, segment filter) silently re-parametrize the same metric. A precise definition has to acknowledge that the value changes when those toggles change.

Context / Trigger Conditions

Use this skill when:

• Drafting a "what each page means" tooltip, onboarding doc, intro deck, or client email
• Writing methodology / definitions / release notes for a metric or KPI
• The user corrects or pushes back on a metric definition you proposed (especially "that's not what X means" or "but it's actually the count of Y")
• You catch yourself describing a KPI from the rendered label, the Python attribute name, or the SQL alias without having opened the query
• You're explaining a dashboard you've worked on before from memory (familiarity is a trap; labels and definitions evolve at different rates)

Solution

Two-step protocol before writing any user-facing definition of a metric:

Step 1 — Find and read the backing query

Grep for the variable/column name from inside-out:

# 1. Find where the value is rendered
grep -rn "{{ kpi.value }}\|kpi.label\|metric_name" templates/

# 2. Find the function that produces the dict
grep -rn "def get_kpis\|def get_<metric>" .

# 3. Read the SQL inside that function — quote the SELECT and the WHERE

Quote the SQL aggregation (COUNT(*), SUM, COUNT(DISTINCT)) and the complete WHERE clause. Both shape the meaning.

Step 2 — Identify silent re-parametrization

For every UI toggle on the page (term pills, lookback buttons, segment filters), check:

• Does the toggle pass into this query as a parameter?
• Does the toggle change a column reference (e.g. _suppressed_col(suffix), _score_col(suffix))?
• Does the toggle affect the WHERE predicate or the date arithmetic?

If yes to any, the definition must say: "This number changes when you toggle X." Otherwise readers will assume the count is global.

Step 3 — Write the definition

State, in order:

1. The aggregation (count of what?)
2. The universe (from what table / cohort, with what filters?)
3. The toggle dependencies (what re-parametrizes it?)
4. Then mention the rendered label — and flag it explicitly if the label diverges from the definition.

Format:

The number labelled "Active Students" is the count of rows in today's scoring run for the selected term, after the per-term suppression filter. Toggle the term and the number changes, because students already enrolled in that term get suppressed. Despite the label, this is not "students with recent activity" — recent activity is one of four cohort-entry criteria upstream, not the metric itself.

Verification

Before sending the copy:

• You have the SQL open in your editor (or pasted into the draft)
• Your definition cites the actual aggregation (COUNT(*), etc.)
• Your definition mentions every UI toggle that changes the value
• If the rendered label diverges from the SQL definition, you have either (a) flagged the divergence in the copy, or (b) filed an issue to rename the label
• You did NOT describe the metric using only the variable name (active_students), the column alias, or the section header

Example

Bad (drafted from label / memory):

"Active Students — how many students are currently active in the applicant pool."

Better (after reading the SQL):

"Active Students — students we scored today for the selected term. Specifically: COUNT(*) of rows in propensity_predictions for the latest scoring_date, with the per-term suppression filter applied. The count changes when you toggle the term."

Best (with cohort-entry criteria documented separately):

Top of doc: "Every metric on the dashboard is computed over rows we scored today. A row enters today's scoring run if it meets four criteria: visited in the last 60 days, has a valid identifier, isn't already in the terminal positive state, and latest source-system status is not in the terminal-negative set (e.g. withdrawn / closed / rejected codes)."

Per metric: "Active Students = count of today's scored rows for the selected segment. (Rendered label is 'Active Students' — consider renaming to 'Students Scored Today' for client deliverables.)"

Notes

• Variable names lie too. active_students is just the SQL alias the engineer picked; it is not load-bearing on meaning. Don't use it as evidence of what the metric is.
• Display labels evolve at a different rate than queries. A query refactor that broadens the cohort (or narrows it) can ship without a label change because the engineer didn't think the label needed updating. The label becomes a fossil.
• For dashboards with many KPIs, build a one-pager mapping each rendered label to its SQL aggregation + filters + toggle dependencies. Reuse it for every new client deliverable.
• Sister skills in this toolkit:
  • whole-dashboard-factcheck-via-parallel-cluster-agents — when you need a definition pass across MANY KPIs at once, dispatch this skill per cluster and synthesise.
  • template-hardcoded-literal-vs-existing-payload — once the definition is right but the rendered number is wrong, this is the most common fix-shape.
• Related skills outside this toolkit: artifact-sql-number-mismatch-reconcile (chart number disagrees with claimed source SQL), client-readiness-pass-on-rendered-html (sanitizing internal language out of HTML reports). This skill is upstream of both — get the definition right before reconciling numbers or polishing prose.
• Memory caveat. When asked to describe a familiar dashboard, do not rely on prior session memory or MEMORY.md summaries — those summarize changes, not definitions. Open the code.
• Mid-migration caveat. Even reading the code can mislead when a feature is mid-migration (sections being moved, IA consolidation in progress, deprecation warnings on a still-rendering block). The committed template may show a section that is intended to be removed/relocated. Before drafting client copy for any dashboard known to be in active redesign, ask the user explicitly: "Should I describe the dashboard as it currently renders, or the post-migration state?" — and surface any divergence between code and intent in your draft so the user can confirm.