Anatomy of a Business Metric

ECON250 - Big Data Analytics | Week 3

Oleh Omelchenko

2026-02-17

Metrics Are Definitions

Block 1: ~6 minutes

This block establishes the central theme: the SQL is the easy part; choosing what to compute is the hard part. Open with a concrete scenario where three correct queries produce three different answers.

“What was our revenue last month?”

Most commonly, the questions that sound simple may be not so easy to answer.

What do you need to know to answer the question correctly?

Hook opening — 3 minutes

Read the question aloud, then pause. “This seems like a simple question, right? One number. But here’s what actually happened at a company…”

Let students think for a moment before revealing the next slide.

Same question, different definitions

Analyst	Definition	Result
A	All order_items.sale_price summed	$1,240,000
B	Excluding returned items	$1,105,000
C	After discounts and shipping	$980,000

The SQL was correct in all three cases — the disagreement was about what “revenue” means.

All three are valid definitions depending on context. Finance uses net revenue. Marketing uses gross. The product team might use yet another definition.

In real companies, metric definitions are documented and versioned. An entire category of tooling — the “semantic layer” — exists to solve this problem (e.g., dbt’s MetricFlow lets teams define metrics in code so every dashboard computes them the same way).

Transition: “So how do you avoid being one of these three analysts who can’t agree? Let’s look at the workflow.”

Before you write any SQL

1. Clarify the definition — what exactly do we mean by this metric?

2. Identify the grain — one row per what?

3. Choose the aggregation type — SUM, COUNT, AVG, ratio? ← rest of today

4. Write the query

5. Validate — does the result make sense?

3 minutes

Steps 1–3 happen before you write any SQL. Step 5 happens after. The query itself (step 4) is often the shortest part.

Students tend to jump straight to step 4. The rest of this lecture is about why steps 1–3 and 5 matter — and what goes wrong when you skip them.

Transition: “Let’s put this into practice. We’ll work through several types of metrics — starting with the simplest — and see what goes wrong at each step.”

Additive Metrics

Block 2: ~10 minutes

Additive metrics are the most common on dashboards: revenue, order count, items sold. You can slice them by any dimension and the parts always sum to the whole.

Aggregation toolkit recap

SELECT
  COUNT(*) AS total_items,
  ROUND(SUM(sale_price), 2) AS total_revenue,
  COUNTIF(returned_at IS NOT NULL) AS returned_items
FROM `bigquery-public-data.thelook_ecommerce.order_items`
WHERE created_at >= '2025-01-01'

• COUNTIF(condition) — BigQuery shorthand for SUM(CASE WHEN ... THEN 1 ELSE 0 END)
• These are all additive: slice by any dimension and the parts sum to the whole

2 minutes

Quick review — students know these functions. The key property to emphasize: additive metrics can be summed across any dimension. If you GROUP BY category, the per-category numbers will sum to the totals.

COUNTIF is BigQuery-specific. DATE_TRUNC rounds a date down to MONTH, WEEK, QUARTER, YEAR. DATE(created_at) converts TIMESTAMP to DATE.

Transition: “Additive metrics are friendly — the parts always sum to the whole. But that friendliness breaks when you JOIN carelessly.”

Caveat: JOIN fan-out

orders

order_id	revenue
1	$50
2	$30
3	$20

Total revenue: $100

order_items (multiple items per order)

order_id	item
1	Shirt
1	Pants
2	Shoes
3	Hat
3	Scarf
3	Gloves

SELECT SUM(o.revenue) AS total_revenue
FROM orders o
JOIN order_items oi USING (order_id)

The total should be $100. What will this query return?

Error Spotting — 4 minutes

Show the two tables. Walk through the query aloud. Give students 60–90 seconds to think. Take 2–3 guesses before advancing to the next slide.

The key question: what happens to an order row when it matches multiple items?

Fan-out: one-to-many JOINs duplicate rows

After the JOIN, each order row gets duplicated for every matching item:

order_id	revenue	item
1	$50	Shirt
1	$50	Pants
2	$30	Shoes
3	$20	Hat
3	$20	Scarf
3	$20	Gloves

SUM(revenue) = $50 + $50 + $30 + $20 + $20 + $20 = $190 (wrong!)

Defensive pattern: compare COUNT(*) before and after the JOIN. If it increases, you have fan-out.

Order 1 appears twice (two items), order 3 appears three times (three items). Each copy carries the full revenue, so SUM counts it multiple times.

This happens silently — the query runs without errors. The only clue is that the number is too high.

In thelook_ecommerce, joining order_items to inventory_items on product_id causes exactly this problem — multiple inventory rows per product inflate the SUM. Students will encounter this in practice.

Caveat: COUNT(*) vs COUNT(column)

Consider these 5 rows from order_items:

order_id	sale_price	shipped_at	returned_at
1001	$45	2025-01-03	NULL
1002	$30	2025-01-04	2025-01-15
1003	$60	NULL	NULL
1004	$25	2025-01-06	2025-01-20
1005	$80	2025-01-05	NULL

Predict: will these three numbers be the same or different?

SELECT
  COUNT(*) AS total_rows,
  COUNT(returned_at) AS count_returned_at,
  COUNT(shipped_at) AS count_shipped_at
FROM `bigquery-public-data.thelook_ecommerce.order_items`

COUNT(*) = 5  |  COUNT(shipped_at) = 4  |  COUNT(returned_at) = 2 — all different, because COUNT(column) skips NULL values.

Prediction — 3 minutes

Pause. Let students look at the sample data and predict. Ask for a few answers.

Reveal: all three numbers are different. - COUNT(*) = 5 (all rows) - COUNT(shipped_at) = 4 (skips the NULL in row 1003) - COUNT(returned_at) = 2 (only rows 1002 and 1004 have a value)

This matters in ratio calculations (Block 4) — using the wrong COUNT in a denominator silently changes the result.

Rule of thumb: COUNT(*) for “how many rows?”, COUNT(column) for “how many non-NULL values?”

Transition: “Additive metrics are straightforward because the parts sum to the whole. What happens when that property breaks?”

Distinct Counts

Block 3: 12 minutes

COUNT(DISTINCT) looks like a simple aggregate, but unlike additive metrics, the parts don’t sum to the whole. This causes one of the most common analytics mistakes in production dashboards.

Summing daily uniques ≠ monthly uniques

I calculated daily unique customers for January and summed them up. Will this equal the monthly unique customer count?

Raw orders (January)

day	user_id
Jan 5	Alice
Jan 5	Bob
Jan 12	Carol
Jan 20	Alice
Jan 20	Carol

Daily uniques

day	unique_users
Jan 5	2 (Alice, Bob)
Jan 12	1 (Carol)
Jan 20	2 (Alice, Carol)

Sum of daily: 5

Monthly unique users: 3 (Alice, Bob, Carol)

Alice and Carol each ordered on two different days — they get counted once per day but once for the whole month.

5 minutes total for the reveal and explanation

Show the visual first, then optionally run the full query on thelook data where the gap is even more dramatic.

This is not a SQL bug — it’s a property of set cardinality: |A ∪ B| ≤ |A| + |B|. In Kimball’s framework, distinct counts are semi-additive facts: they can be summed across some dimensions but not across time.

In practice: you cannot roll up distinct counts by summing.

The query that proves it

WITH daily AS (
  SELECT
    DATE(created_at) AS day,
    COUNT(DISTINCT user_id) AS unique_users
  FROM `bigquery-public-data.thelook_ecommerce.order_items`
  WHERE created_at BETWEEN '2025-01-01' AND '2025-02-01'
  GROUP BY day
)
SELECT
  SUM(unique_users) AS summed_daily_uniques,
  (SELECT COUNT(DISTINCT user_id)
   FROM `bigquery-public-data.thelook_ecommerce.order_items`
   WHERE created_at BETWEEN '2025-01-01' AND '2025-02-01'
  ) AS monthly_uniques
FROM daily

Run this on BigQuery. The gap between the two numbers is substantial — typically 2x or more, because many customers order multiple times per month.

The visual on the previous slide explains why. This query lets students see the scale of the problem on real data.

Why this matters in production

• A dashboard that sums daily active users to get monthly active users will overcount

• “Unique visitors by region” columns won’t sum to “total unique visitors” (same visitor can appear in multiple regions)

• This is why platforms report total views (additive — easy to sum) but are careful about unique reach (requires deduplication at each grain)

Rule: always compute distinct counts at the grain you need — don’t try to aggregate from a finer grain.

3 minutes

Good real-world example: Instagram can sum daily video views to get monthly views (additive). But “unique accounts reached” requires re-running COUNT(DISTINCT) at the monthly grain — summing daily reach overcounts because the same user sees content on multiple days.

If you need both daily and monthly uniques, compute them separately with separate queries.

APPROX_COUNT_DISTINCT exists in BigQuery for performance at scale — it’s faster but returns approximate results (~1% error). Fine for dashboards, not for precise reporting.

What defines “distinct”?

“Unique customers” depends on the identifier you choose:

Identifier	Counts
COUNT(DISTINCT user_id)	Unique accounts
COUNT(DISTINCT email)	Unique emails (some users have multiple accounts)
COUNT(DISTINCT session_id)	Unique visits (one user = many sessions)

Different identifiers produce different numbers. The metric definition must specify which.

2 minutes

Back to Block 1’s point: the definition determines the result more than the SQL does. “Unique customers” is ambiguous without specifying the identifier.

Transition: “Additive metrics can be summed. Distinct counts can’t. Now let’s look at metrics that are derived from dividing two other metrics — ratios.”

Ratios and Rates

Block 4: 18 minutes

Ratios are common on dashboards and prone to subtle errors, because both the numerator and denominator can introduce problems.

Ratios are everywhere

Think of 2–3 business metrics that are calculated as ratios or averages.

• AOV (average order value) — revenue / orders
• Conversion rate — purchases / visits
• Return rate — returned items / total items
• GDP per capita — GDP / population

Every average is also a ratio: AVG(x) = SUM(x) / COUNT(x). Ratios have their own category of pitfalls.

2 minutes

Ask the room. Take 3–4 answers. Students will naturally mention conversion rates, AOV, return rates, etc.

Then reveal: every one of these is a fraction — a numerator divided by a denominator. The rest of this block is about what can go wrong with that denominator.

Percent of total

“What share of revenue does each product category contribute?”

-- Step 1: Revenue per category
WITH category_revenue AS (
  SELECT category, SUM(sale_price) AS revenue
  FROM order_items
  JOIN products USING (product_id)
  GROUP BY category
),
-- Step 2: Compute the grand total
total AS (
  SELECT SUM(revenue) AS grand_total FROM category_revenue
)
-- Step 3: Divide each category by the total
SELECT category, revenue,
  ROUND(revenue * 100.0 / grand_total, 2) AS pct_of_total
FROM category_revenue CROSS JOIN total
ORDER BY revenue DESC

The CROSS JOIN attaches the single grand total to every row, so each row can compute its percentage.

5 minutes

Walk through the three steps using the code line highlights. The CROSS JOIN pattern is the same one students used in Assignment 2.

In practice, students will use the full table names: bigquery-public-data.thelook_ecommerce.order_items etc. The pattern is the same — table name length doesn’t change the logic.

Mention briefly: SUM(revenue) OVER () (Week 4) will provide a more concise alternative. For now, the CTE approach makes the logic explicit.

Caveat: the denominator changes the answer

Consider this data — two orders, five items total, one item returned:

order_id	item	returned_at
101	Shirt	NULL
101	Pants	NULL
101	Belt	2025-01-20
102	Jacket	NULL
102	Shoes	NULL

What’s the return rate? Can you find more than one way to calculate it?

Prediction — 4 minutes

Let students study the data. Ask them to predict both numbers.

Item-level: 1 returned / 5 total = 20%. Order-level: 1 order with return / 2 orders = 50%.

The same data produces 20% or 50% depending on whether you count by items or orders. Ask students to predict before showing the queries.

Two definitions of “return rate”

Approach A: item-level

-- Returned items / total items
SELECT ROUND(
  COUNTIF(returned_at IS NOT NULL)
  * 100.0 / COUNT(*), 2
) AS return_rate
FROM `bigquery-public-data
  .thelook_ecommerce.order_items`
WHERE created_at >= '2025-01-01'

Approach B: order-level

-- Orders with returns / total orders
WITH order_returns AS (
  SELECT order_id,
    MAX(CASE WHEN returned_at
      IS NOT NULL THEN 1 ELSE 0
      END) AS has_return
  FROM `bigquery-public-data
    .thelook_ecommerce.order_items`
  WHERE created_at >= '2025-01-01'
  GROUP BY order_id
)
SELECT ROUND(
  SUM(has_return) * 100.0
  / COUNT(*), 2
) AS return_rate
FROM order_returns

Different denominators (items vs orders), different numbers. Neither is wrong — they answer different questions.

Approach A asks: “What fraction of items get returned?” Approach B asks: “What fraction of orders have at least one return?”

The numbers will differ because an order with 3 items where 1 is returned counts as 1/3 in Approach A but 1/1 in Approach B.

This is the denominator trap: the same metric name (“return rate”) can mean different things depending on the denominator.

You can’t average ratios across groups

Category	Returned	Total	Return rate
Accessories	1	2	50%
Electronics	99	1,000	9.9%

Overall: 100 returned out of 1,002 total

Naive average of rates: (50% + 9.9%) / 2 = 30%

Actual overall rate: 100 / 1,002 = 10%

4 minutes

The small category with a high rate distorts the average because each category gets equal weight regardless of size.

This is a variant of Simpson’s paradox: a trend visible in subgroups reverses or disappears when the groups are combined, because small groups get equal weight with large ones.

The fix: recompute the ratio from summed numerators and summed denominators. Never average pre-computed ratios.

Naming it “Simpson’s paradox” gives students a searchable term.

Recompute from totals, don’t average rates

Wrong

-- Average of per-category rates
WITH rates AS (
  SELECT category,
    COUNTIF(...) / COUNT(*) AS rate
  FROM ...
  GROUP BY category
)
SELECT AVG(rate)   -- wrong!
FROM rates

Correct

-- Overall rate from raw counts
SELECT
  COUNTIF(returned_at IS NOT NULL)
    * 100.0 / COUNT(*) AS rate
FROM order_items
WHERE created_at >= '2025-01-01'

Kimball’s design rule: store the additive components (returned count, total count), compute the ratio at query time at the grain you need.

Same pattern applies to conversion rates, retention rates, completion rates — any ratio computed from subgroups.

If you need per-category rates and an overall rate, compute the overall rate separately from the raw data, not by averaging the per-category rates.

Caveat: NULL in comparisons silently drops rows

This query counts items that are NOT in “Complete” status. The analyst says the count seems too low. What will each row evaluate to?

SELECT COUNT(*) AS not_complete
FROM order_items
WHERE status != 'Complete'

order_id	status	status != 'Complete'	included?
1001	Complete	???	???
1002	Shipped	???	???
1003	Returned	???	???
1004	NULL	???	???
1005	Processing	???	???

Error Spotting — 5 minutes

Show the query and the table. Give students 60–90 seconds to fill in each row mentally. Ask for volunteers to share their answers, especially for row 1004.

NULL comparison: the reveal

SELECT COUNT(*) AS not_complete
FROM order_items
WHERE status != 'Complete'

order_id	status	status != 'Complete'	included?
1001	Complete	FALSE	no
1002	Shipped	TRUE	yes
1003	Returned	TRUE	yes
1004	NULL	NULL	no
1005	Processing	TRUE	yes

Expected: 4 rows. Actual: 3 rows — row 1004 silently dropped because NULL != 'Complete' evaluates to NULL, not TRUE.

The problem: NULL != 'Complete' evaluates to NULL (not TRUE), so row 1004 is silently excluded. WHERE only keeps rows where the condition is TRUE — NULL is not TRUE.

This is SQL’s three-valued logic. Every comparison with NULL returns NULL, not TRUE or FALSE.

NULL comparisons return NULL, not FALSE

-- Fix option 1: explicit NULL check
WHERE status != 'Complete' OR status IS NULL

-- Fix option 2: IS DISTINCT FROM (treats NULLs as a value)
WHERE status IS DISTINCT FROM 'Complete'

The same trap appears with NOT IN:

WHERE x NOT IN (1, 2, NULL) returns zero rows — any comparison with NULL produces NULL, and the entire expression evaluates to NULL.

IS DISTINCT FROM is the cleanest fix — it’s a NULL-safe comparison that treats NULL as a comparable value. Available in BigQuery (standard SQL:2003).

The NOT IN trap is worth memorizing: if a subquery used in NOT IN can return NULL values, the entire NOT IN evaluates to NULL for every row, and no rows pass the filter.

Transition: “We’ve seen how denominators and NULLs can trip up ratios. Now let’s look at a function that hides both of these issues — AVG.”

Averages

Block 5: 12 minutes

AVG deserves its own block because it’s a ratio in disguise — SUM(x) / COUNT(x) — but it hides the denominator. This makes its pitfalls less obvious than explicit ratios, which is exactly why they’re more dangerous.

How AVG works

AVG(x) = SUM(x) / COUNT(x)

COUNT(x) excludes NULLs — so AVG only considers rows where the value exists.

2 minutes

AVG has a built-in NULL filter that most people don’t think about. Often that’s what you want, but sometimes it isn’t — you need to recognize which case you’re in.

AVG ignores NULLs silently

Five orders, two with discounts:

order_id	discount
1001	10
1002	NULL
1003	NULL
1004	30
1005	NULL

Will these two queries return the same number?

-- Query A
SELECT AVG(discount)
FROM orders

(10 + 30) / 2 = 20

-- Query B
SELECT SUM(discount) / COUNT(*)
FROM orders

(10 + 30) / 5 = 8

Prediction — 5 minutes

Show the sample data. Pause. Let students compute both by hand before revealing.

Query A: AVG skips NULLs — divides by 2 (the two non-NULL rows). Query B: SUM ignores NULLs in the numerator, but COUNT(*) counts all 5 rows.

Both are valid — it depends on the question: - “What’s the average discount when we offer one?” → AVG(discount) = 20 - “What’s the average discount across all orders?” → SUM(discount)/COUNT(*) = 8, or equivalently AVG(COALESCE(discount, 0))

COALESCE converts NULLs to 0 before averaging, which changes both the numerator and the denominator.

Which average do you want?

Question	SQL
Average discount when offered	AVG(discount)
Average discount across all orders	AVG(COALESCE(discount, 0))

COALESCE(x, fallback) returns the first non-NULL value — use it inside the aggregate when NULLs should be treated as zeros.

Neither approach is wrong — the right choice depends on what business question you’re answering. The point is to choose deliberately, not accidentally.

Average of averages ≠ overall average

I computed AOV per category, then averaged those. Will this match the overall AOV?

Category	Revenue	Orders	AOV
Clothing	$10,000	500	$20
Electronics	$50,000	200	$250

Average of the two AOVs: ($20 + $250) / 2 = $135

Actual overall AOV: $60,000 / 700 = $85.71

Prediction — 3 minutes

This is the same principle as “you can’t average ratios” from Block 4, reinforced here deliberately. Students need to see it twice because AVG hides the ratio, making this mistake even more common in practice. Unequal group sizes make naive averaging misleading.

Electronics has fewer orders but much higher AOV. When you average the two AOVs, Electronics gets 50% weight despite representing only 29% of orders.

The correct overall AOV must be computed from total revenue / total orders, not averaged from per-group AOVs.

Same applies to average shipping time per region, average review score per category, average salary per department.

If you need a weighted average: SUM(aov * orders) / SUM(orders).

Transition: Let’s do a quick recap of the NULL-handling tools you have.

NULL-handling functions

Function	Purpose	When to use
COALESCE(x, 0)	First non-NULL value	NULLs should be treated as zeros in AVG/SUM
IFNULL(x, 0)	Two-argument shorthand	Same as COALESCE with one fallback
NULLIF(x, 'Unknown')	Return NULL if x = value	Convert sentinel values to proper NULLs

COALESCE checks multiple columns in order — useful beyond simple fallbacks:

SELECT COALESCE(mobile_phone, work_phone, email, 'no contact') AS best_contact

2 minutes

Quick reference slide. COALESCE is the most common — use it when you want to replace NULL with a default value inside an aggregate.

NULLIF goes the other direction: converts a known placeholder (like ‘Unknown’ or 0) into NULL so that AVG and COUNT behave correctly.

Transition: “We’ve now seen all the major metric pitfalls. Let’s organize what we’ve covered.”

Wrap-up

Wrap-up: ~7 minutes

Four types of metrics — reference card

Type	Examples	Key property	Watch out for
Additive	Revenue, order count	Parts sum to the whole	Fan-out, COUNT(*) vs COUNT(col)
Distinct counts	Unique customers, sessions	Cannot sum across time	Non-additivity
Ratios / rates	Return rate, conversion	Derived from two metrics	Denominator choice, can’t average ratios
Averages	AOV, avg shipping time	Sensitive to inclusions	NULLs silently excluded, can’t average

This classification is adapted from Kimball’s dimensional modeling framework, which distinguishes additive, semi-additive, and non-additive facts. Kimball’s design rule: store the fully additive components (numerator, denominator) and compute the non-additive metric at query time.

Students can use this as a reference card. Growth metrics (period-over-period comparisons using LAG) will be covered in Week 4.

Three sources of metric bugs

1. Definitional ambiguity — “revenue” means different things to different people

2. NULL behavior — aggregations handle NULLs differently, and comparisons with NULL are neither true nor false

3. Wrong granularity — computing at one grain and aggregating to another (fan-out, non-additivity, averaging ratios)

Most metric bugs are silent: the query runs fine, but the number it returns is wrong.

Emphasize “silent” — no error messages. The only defense is understanding what each aggregation actually computes and validating your results.

Transition: Preview the practice sessions.

What’s next

Practice 1: Calculate business KPIs from thelook_ecommerce, encountering these pitfalls firsthand

Practice 2: Diagnose and fix queries with metric bugs, then build a defensive metrics summary

Practice 1 is organized by metric type to reinforce the taxonomy from this lecture. The non-additivity test (computing daily vs monthly uniques) is the most important exercise — make sure students actually run both queries and see the difference.

Practice 2 flips things around: instead of building metrics from scratch, students debug queries where these pitfalls produce wrong results. The “bug hunt” format works well as pair work.

Mention: read SQL DA Ch 3’s second half for a preview of window functions (rolling windows, cumulative values, LAG). We’ll cover those properly in Week 4, along with period-over-period growth comparisons.