Metric Definitions And Business Logic

Why this matters

As a BI Analyst, you translate business questions into trusted metrics. In a semantic layer, one precise definition powers every dashboard, report, and alert. If definitions are unclear or inconsistent, teams make conflicting decisions. Strong metric definitions and business logic prevent that and speed up analytics work.

• Daily tasks this enables: converting stakeholder requests into exact formulas, avoiding double counting, setting default filters (e.g., exclude test users), and making time comparisons consistent.
• Outcome: one source of truth, fewer rework cycles, faster insights.

Who this is for

• BI Analysts and Analytics Engineers defining metrics in a semantic layer or BI tool.
• Data-savvy PMs who need consistent KPIs.

Prerequisites

• Comfort with dimensions, measures, joins, and data grain.
• Basic SQL knowledge (SELECT, GROUP BY, COUNT DISTINCT).
• Familiarity with time series metrics (daily, weekly, monthly).

Concept explained simply

A metric definition is a small contract: it states what the number means, how it is calculated, from which data, at what grain, with which filters, and how it should aggregate over time and categories. Business logic are the rules that keep this metric correct everywhere: default date ranges, currency conversion, deduplication, and edge-case handling.

Mental model

Think of a metric like a recipe card. Ingredients are source fields. The cooking steps are transformations and filters. The serving size is the grain (e.g., per day). If the recipe is clear, anyone can cook the same dish consistently.

Core components of a metric definition

• Name and short description
• Owner and use cases
• Formula with inputs (tables, fields)
• Filters and qualifiers (e.g., paid users only)
• Grain (row-level and reporting grain)
• Aggregation rules (sum, average, count distinct)
• Time logic (lookback, calendar vs rolling windows)
• Null/zero handling and deduplication
• Units, currency, and conversions
• Data quality checks and caveats

Name:
Short description:
Owner:
Use cases:
Formula (human):
Formula (pseudo-SQL):
Base tables and join keys:
Filters/qualifiers:
Grain (row-level):
Report grain (default):
Aggregation rule:
Time logic (window, calendar):
Null/zero and duplicates handling:
Units/currency:
Validation checks:
Caveats:

Worked examples

WITH sessions AS (
  SELECT session_id, user_id, DATE(event_time) AS event_date
  FROM events
  WHERE event_name = 'session_start' AND user_is_test = FALSE
), purchases AS (
  SELECT DISTINCT user_id, order_time
  FROM orders
)
SELECT s.event_date,
  COUNT(*) AS sessions,
  COUNT_IF(EXISTS(
    SELECT 1 FROM purchases p
    WHERE p.user_id = s.user_id
      AND p.order_time BETWEEN s.event_date AND s.event_date + INTERVAL 7 DAY
  )) AS sessions_with_purchase,
  SAFE_DIVIDE(COUNT_IF(...), COUNT(*)) AS conversion_rate
FROM sessions s
GROUP BY 1;

WITH net AS (
  SELECT ol.order_id, ol.line_id,
         ol.revenue_gross - COALESCE(r.revenue_refunded,0) AS revenue_net,
         ol.cost - COALESCE(r.returned_cost,0) AS cogs_net
  FROM order_lines ol
  LEFT JOIN returns r USING(order_id, line_id)
)
SELECT DATE(order_time) AS day,
       SUM(revenue_net) AS revenue_net,
       SUM(cogs_net) AS cogs_net,
       SAFE_DIVIDE(SUM(revenue_net) - SUM(cogs_net), SUM(revenue_net)) AS margin_pct
FROM net
GROUP BY 1;

WITH daily AS (
  SELECT DATE(event_time) AS day, COUNT(DISTINCT user_id) AS dau
  FROM events
  WHERE is_active_event = TRUE AND user_is_test = FALSE
  GROUP BY 1
), mau AS (
  SELECT d.day,
         (SELECT COUNT(DISTINCT e.user_id)
          FROM events e
          WHERE e.is_active_event = TRUE
            AND e.user_is_test = FALSE
            AND e.event_time BETWEEN d.day - INTERVAL 29 DAY AND d.day) AS mau
  FROM daily d
)
SELECT day, dau, mau, SAFE_DIVIDE(dau, mau) AS stickiness
FROM mau;

Business logic patterns you will reuse

• Filters and qualifiers: exclude internal traffic, spam, or out-of-scope regions.
• Time intelligence: rolling windows vs calendar periods; define one default and stick to it.
• Granularity and aggregation: define the lowest grain (e.g., line item, session) and how metrics roll up (sum vs average vs weighted average).
• Distinctness and deduplication: choose keys and windows to prevent double counting (e.g., first purchase per user per window).
• Semi-additive measures: inventory at end-of-day should average over day but pick last for end-of-month; document rules.
• Currency and units: specify conversion timing (transaction date FX) and rounding rules.
• Late arriving data: note if metrics are provisional for N days and backfill rules.

Practical projects

• Project 1: Build a metric catalog for your top 10 KPIs using the blueprint. Share with a stakeholder for feedback.
• Project 2: Reconcile two dashboards that show different numbers by writing explicit metric definitions and finding logic mismatches.
• Project 3: Implement time logic for a metric in both rolling and calendar variants; compare outcomes and document recommended default.

Exercises

These mirror the exercises below. Do them here, then submit answers in the Quick Test if you want to check understanding. Everyone can take the test; only logged-in users have saved progress.

Name: Session-to-Purchase Conversion Rate
Short description: Share of sessions that result in a purchase within 7 days.
Owner: Growth Analytics
Use cases: Campaign performance, landing page optimization
Formula (human): sessions_with_purchase_7d / total_sessions
Formula (pseudo-SQL): see Worked Example 1
Base tables and join keys: events.session_id, events.user_id; orders.user_id
Filters/qualifiers: user_is_test = FALSE; channel IN ('web','app')
Grain (row-level): session_id
Report grain (default): day (DATE(event_time))
Aggregation rule: weighted by denominator; do not average rates across days
Time logic: 7-day forward window from session timestamp
Null/zero and duplicates handling: treat missing orders as no purchase; de-duplicate sessions by session_id; count a session in numerator if ANY purchase occurs in window
Units/currency: percentage
Validation checks: numerator <= denominator; compare daily trend to prior period
Caveats: delayed orders arriving after 7 days are out of scope by design

Name: Gross Margin $ (Net)
Short description: Revenue net of refunds minus COGS net of returned cost.
Formula: SUM(revenue_gross - revenue_refunded) - SUM(cost - returned_cost)
Grain: line_id row-level; default daily by order_time
Aggregation: sum dollars first; do not average per-line margins
Partial returns: prorate revenue_refunded and returned_cost by returned quantity
Missing costs: exclude from numerator and flag a data quality warning
---
Name: Gross Margin % (Net)
Short description: Share of net revenue that remains after net COGS.
Formula: (SUM(revenue_net) - SUM(cogs_net)) / NULLIF(SUM(revenue_net),0)
Grain: daily by order_time; monthly roll-ups sum first then divide
Edge cases: when net revenue = 0, return NULL

Self-check checklist

• Did you specify a clear grain and aggregation rule?
• Are filters explicit and justified?
• Is time window (rolling vs calendar) documented?
• Did you define null/zero and deduplication rules?
• Can someone else reproduce your number from the spec?

Common mistakes and how to self-check

• Averaging percentages across groups. Fix: always aggregate numerator and denominator first, then divide.
• Undefined grain. Fix: state row-level and reporting grain explicitly.
• Double counting users/orders. Fix: define unique keys and dedup windows.
• Mixed windows (calendar vs rolling). Fix: document one default; provide variants if needed.
• Silently excluding data. Fix: list every filter with rationale; add validation checks.

Mini challenge

Write a one-page spec for Retention D30 (% of new users on day 0 who return on or after day 1 and within 30 days). Include exact cohorting rule, event filter, time window, and how to handle timezone and late events. Keep it reproducible by a teammate.

Learning path

• Start: Metric definitions and business logic (this page).
• Next: Data modeling for metrics (dimensions, conformed keys, SCDs).
• Then: Time intelligence and seasonality analysis.
• Finally: Metric governance and cataloging.

Next steps

• Turn your top 3 KPIs into formal metric specs using the blueprint.
• Validate each metric with a spot-check query and an independent reviewer.
• Add caveats and data quality checks; publish to your metric catalog.

Progress saving note: The quick test is available to everyone. If you are logged in, your progress and results will be saved automatically.