Note: The quick test is available to everyone. If you log in, your progress will be saved automatically.
Why this matters
Analytics Engineers turn raw data into trustworthy metrics used by product, finance, and leadership. Clear metric definitions and governance prevent misaligned decisions, duplicated work, and painful last-minute debates before launches or board meetings.
- Real tasks you will do:
- Create a single definition for metrics like Monthly Active Users, Revenue, Churn Rate, and Conversion.
- Document formula, grain, filters, time window, freshness, and owners.
- Review change requests and communicate breaking changes to stakeholders.
- Add tests (not null, unique, accepted range) to guard metric quality.
- Version and deprecate metrics safely across dashboards and reports.
Concept explained simply
A metric is a contract: a clear promise about what a number means and how to compute it. It includes its name, formula, inputs, grain (unique keys), time window, filters, and owner. Governance is how you keep this contract consistent, testable, and change-managed over time.
Mental model: The Metric Contract
- Name and purpose: what decision this number supports.
- Grain: the unique row identity (e.g., user_id by month).
- Formula: numerator, denominator, filters, units, timezone.
- Dimensions: allowed breakdowns (e.g., country, plan).
- Time: window (e.g., last 28 days), calendar vs rolling, UTC vs local.
- Sources and lineage: tables, events, joins.
- Tests: expectations to keep it healthy.
- Owner and reviewers: who changes/approves.
- Versioning and deprecation policy.
Worked examples
Example 1: Monthly Active Users (MAU)
- Purpose: Track product engagement.
- Grain: user_id by calendar month (UTC).
- Definition: Count distinct user_id with at least one qualifying event in the month.
- Qualifying events: app_open, page_view, or api_call; exclude internal/test users.
- Formula (conceptual): MAU = count(distinct user_id) where event_date is in month and event_type in allowed set and is_internal = false.
- Allowed dimensions: platform, country, plan_tier.
- Tests: event_date not null; user_id not null; internal users excluded; duplicates not allowed.
- Owner: Analytics Engineering; Reviewers: Product Analytics.
Example 2: Gross Revenue vs. Net Revenue
- Purpose: Finance and growth reporting.
- Grain: order_id.
- Gross Revenue: sum(order_amount) before discounts, refunds, taxes, shipping.
- Net Revenue: sum(order_amount - discounts - refunds - taxes - shipping).
- Filters: currency converted to USD using month-end FX; exclude fraudulent orders.
- Units: USD; Timezone: UTC.
- Tests: Non-negative values; FX rate present; order_id unique.
- Owner: Finance Analytics; Reviewers: Accounting.
Example 3: Signup-to-Purchase Conversion Rate
- Purpose: Evaluate funnel performance.
- Grain: cohort by signup_date (UTC) with 30-day window.
- Numerator: users with a purchase within 30 days of signup.
- Denominator: users who signed up in the cohort period (exclude test users and internal domains).
- Formula: Conversion = numerator / denominator.
- Edge cases: Multiple purchases still counted once in numerator.
- Tests: numerator ≤ denominator; signup_date not null; join keys validated.
- Owner: Growth Analytics; Reviewer: Product Manager.
Metric definition template
Copyable template
{
"name": "",
"purpose": "",
"owner": "",
"reviewers": [""],
"grain": "",
"time": {
"window": "",
"calendar_or_rolling": "calendar|rolling",
"timezone": "UTC"
},
"formula": {
"numerator": "",
"denominator": "(optional)",
"filters": [""],
"units": "",
"notes": "edge cases, exclusions"
},
"dimensions": [""],
"sources": [
{"table": "", "keys": [""], "freshness_sla": ""}
],
"tests": [
"not_null(field)",
"unique(key)",
"accepted_range(field, min, max)",
"numerator_le_denominator"
],
"lineage": "upstream models / events",
"version": "v1",
"change_log": [],
"deprecation": {
"status": "active|deprecated",
"replacement": "(if any)",
"sunset_date": "(if any)"
}
}
Governance workflow
- Propose: Open a change request with impact analysis (who uses it, dashboards affected, backward compatibility).
- Review: Owner and reviewers sign off; verify tests and sample outputs.
- Version: If breaking, increment major version (e.g., v1 → v2); run both in parallel if needed.
- Communicate: Announce changes, migration steps, and sunset date.
- Monitor: Add alerts for freshness, range, and drift.
- Deprecate: Hide from catalog, remove dashboards after sunset.
Naming and style guidelines
- Use clear, business-friendly names: "Monthly Active Users" (display) and snake_case for backend: monthly_active_users.
- Metric names should not embed filters; use dimensions for breakdowns.
- Document timezone and units explicitly.
- Avoid ambiguous words like "users" without defining eligibility.
Common mistakes and self-checks
- Mistake: Unclear grain → double counting. Self-check: State unique keys and aggregation path.
- Mistake: Numerator not subset of denominator. Self-check: Assert numerator ≤ denominator per cohort.
- Mistake: Silent definition drift (new events included). Self-check: Lock allowed event list and test it.
- Mistake: Timezone mismatches. Self-check: Normalize to UTC and document exceptions.
- Mistake: Unit confusion (gross vs net). Self-check: Include units and exclusions in formula.
- Mistake: Hidden filters. Self-check: List all filters in the definition; no implicit filters.
Exercises
Try these. You can compare with the solutions below.
- Exercise 1 (matches ex1): Draft a complete metric spec for Monthly Churn Rate for a subscription product. Include purpose, grain, time window, formula, eligibility rules, data sources, tests, and owner.
- Exercise 2 (matches ex2): A PM asks to redefine Active Users to only include users with a purchase or add_to_cart event. Perform a change impact analysis and write the governance steps you would follow.
- I wrote an explicit numerator and denominator.
- I defined grain and time window.
- I listed all filters, units, and timezone.
- I added at least three tests and a freshness SLA.
- I identified the owner, reviewers, and version.
Practical projects
- Create a small metric catalog (5–8 metrics) for a sample ecommerce dataset. Include owners, tests, and versions.
- Implement drift monitoring: track a metric and alert when it changes more than 20% week-over-week due to definition or source changes.
- Run a breaking-change simulation: publish v2 of a metric and plan the deprecation of v1 across three dashboards.
Who this is for, prerequisites, learning path
Who this is for
- Analytics Engineers and BI Developers owning metric layers and dashboards.
- Product/Finance analysts who need consistent numbers across reports.
Prerequisites
- Basic SQL (joins, aggregates, window functions).
- Understanding of your company events/tables and data freshness.
Learning path
- Understand metric anatomy: grain, formula, time, filters, dimensions.
- Write specs using the template and validate with stakeholders.
- Implement in your modeling layer (SQL/semantic layer) with tests.
- Govern: set review, versioning, and communication rules.
- Monitor: freshness, range, and drift alerts.
Mini challenge
Your sales team reports Net Revenue that is 8% lower than Finance. Draft a short plan (max 6 bullets) to align the definition and prevent recurrence. Think about owners, units, exclusions, and tests.
When you feel ready, take the quick test below. Everyone can take it; log in if you want your progress saved.