Converting Business Questions Into Metrics

Why this matters

BI Developers are constantly asked things like "Are our emails working?" or "Which channel brings the best customers?" Turning those into clear, trustworthy metrics is the foundation for dashboards, alerts, and decisions. When this step is sloppy, teams chase noisy charts and make poor calls.

• Real tasks you will do: frame ambiguous questions, define KPIs and guardrails, specify metric logic, align stakeholders, and hand over precise requirements to data modeling and dashboarding.
• Outcomes: fewer reworks, consistent definitions across teams, and dashboards people actually trust.

Concept explained simply

Converting business questions into metrics means expressing a goal or concern as a measurable, reproducible definition. You specify what to count, who to include, when to measure, and how to slice it. The result is a metric spec that any analyst or engineer can implement identically.

Mental model: Metric Canvas

Think of a metric like a recipe card. If two cooks follow the same card, the dish tastes the same. Your metric card includes:

• Purpose: the business question in one line
• Entity & Event: what and when to count (e.g., orders placed)
• Population: who is in or out (e.g., paid customers only)
• Time: grain (daily/weekly) and window (last 7 days, MTD)
• Aggregation: sum, count, average, ratio, percentile
• Filters: what is included/excluded
• Dimensions: how it can be broken down (channel, region)
• Freshness: how often it updates
• Source of truth: tables and fields, if known
• Owner & Notes: who decides if something changes

Framework: from question to metric in 6 steps

Worked examples

E-commerce: "Are our emails working?"

• Purpose: Evaluate email campaign effectiveness on revenue without harming list health.
• Primary KPI: Email-driven revenue per 1,000 sends (RPS)
• Entity & Event: order; event = order placed within attribution window
• Attribution: orders within 7 days of an email click from the same user
• Population: non-internal users; exclude refunded orders
• Time: daily grain; 7-day rolling window
• Formula: RPS = (Sum revenue from attributed orders / total sends) * 1000
• Filters: email_type in [campaign, automation]; order_status = completed
• Dimensions: campaign_id, template, segment, device, region
• Guardrails: unsubscribe rate, spam complaint rate
• Freshness: daily by 09:00

SaaS: "How healthy is onboarding?"

• Purpose: Assess activation effectiveness for new sign-ups.
• Primary KPI: Activation rate (within 14 days of sign-up)
• Entity & Event: user; event = achieved activation action (e.g., completed setup checklist)
• Population: new sign-ups excluding internal and test accounts
• Time: cohort by sign-up date; report weekly
• Formula: Activation rate = activated_users_14d / new_signups
• Dimensions: plan_tier, acquisition_channel, region
• Guardrails: time to first value (median mins), support ticket rate in first 14 days
• Freshness: daily by 08:00

Marketplace: "Is supply balanced with demand?"

• Purpose: Ensure buyers can find providers promptly.
• Primary KPI: Fill rate = fulfilled_requests / total_eligible_requests
• Entity & Event: request; event = request fulfilled
• Population: exclude cancelled-by-user within 5 minutes; exclude test
• Time: daily grain; weekly trend
• Dimensions: city, category, time_of_day
• Guardrails: median time to first response; cancellation rate after acceptance
• Freshness: near real-time not required; hourly refresh is adequate

Exercises you will do

Do these in your notes or a doc, then compare with the solutions below. These mirror the graded exercises.

1. Exercise 1 (ex1): Translate "Are discounts hurting margin?" Produce a metric spec with purpose, entity/event, population, time, formula, filters, dimensions, guardrails, freshness.
2. Exercise 2 (ex2): Translate "Which channels bring high-LTV customers?" Define the KPI and supporting metrics, including cohort/time window and minimum data requirements.

Common mistakes and how to self-check

• Ambiguous population (e.g., not excluding tests/internal). Fix: explicitly list exclusions.
• Mixing time grain and window (daily metric with monthly-only logic). Fix: state both.
• Denominator drift (changing eligible set across days). Fix: define eligibility once and keep consistent.
• Ratio without guardrails (optimizes one thing but harms another). Fix: add guardrail metrics.
• Undefined attribution window. Fix: specify window, precedence, and tie-breaking rules.

Practical projects

• Project 1: Take a recent product question, write two metric specs (KPI + guardrail), and mock a dashboard layout with 3 dimensions.
• Project 2: Choose a churn question, define cohort-based KPIs (e.g., 90-day churn rate), and prepare a QA plan to validate the metric against raw events.
• Project 3: For a marketing funnel, define a conversion KPI and a leading indicator. Add attribution rules and stress-test with edge cases.

Who this is for

BI Developers, Analytics Engineers, and Data Analysts who translate stakeholder questions into dashboards and semantic models.

Prerequisites

• Basic SQL and understanding of joins and distinct counts
• Familiarity with business domains (marketing, product, sales) helps

Learning path

1. Start here: learn the Metric Canvas and do the exercises
2. Next: model entities and events in your warehouse
3. Then: define KPIs and guardrails for your core domains
4. Finally: implement in your BI tool and set validation checks

Mini challenge

Rewrite "Are we growing fast enough?" into a metric spec with a primary KPI and two guardrails. Keep it under 10 lines. Bonus: add two dimensions that help explain changes.

Next steps

• Complete the exercises and compare with the solutions
• Take the Quick Test to check your understanding
• Apply the framework to one live request this week