What Should We Measure?

BUS220 - Business Intelligence and Analytics | Week 4

Oleh Omelchenko

2026-04-08

From clean data to decisions

“For three weeks you’ve been preparing data — cleaning, reshaping, connecting to Tableau, building charts. You can make bar charts and scatter plots. You know tidy data from messy data. But none of that answers the question your manager actually cares about: what should we measure, and what does it mean?”

Your VP sends you a Slack message

VP

VP of Sales 10:23 AM

How are we doing?

What do you show them?

Pause here. Let the question hang for 5-10 seconds. Ask students to think — or pair up for 30 seconds.

“This is not a trick question. But it’s also not a simple one. ‘How are we doing?’ could mean revenue, profit, customer growth, market share, satisfaction, retention. Your job as an analyst is to translate a vague question into something measurable.”

“When someone tells you revenue is $2.3M — what’s your first question?” Wait for answers. Expected: “Compared to what?”

Is this good?

$2,300,000

$2,300,000 (+15% YoY, 102% of target)

Now you can act on it. A metric needs a comparison — a trend, a benchmark, or a target.

Let the bare number sit for 5 seconds. Ask: “Is this good?” Wait for hands or responses.

“You can’t tell. If last year was $1.8M — great, you grew 28%. If the target was $3M — terrible. The number is correct either way. The difference is context.”

Click to reveal the second version. “Now you know: revenue is up 15% year-over-year and slightly above target. Same number, completely different reaction. A metric without comparison is just decoration.”

What does this dashboard tell you?

ARPDAU

$0.42

D7 Retention

18%

LTV/CAC

1.3

MAU

824K

K-factor

0.6

DAU/MAU

0.31

If your audience needs a glossary to read the dashboard, the dashboard has failed. A metric that people can’t discuss in a meeting won’t change anyone’s behavior.

“Raise your hand if you know what all six of these metrics mean.” Probably very few hands.

“ARPDAU is Average Revenue Per Daily Active User. D7 Retention is what percentage of users come back after 7 days. LTV/CAC is Lifetime Value divided by Customer Acquisition Cost. K-factor is a viral coefficient. DAU/MAU is the ratio of daily to monthly active users — a stickiness measure.”

“These are all real, useful metrics in mobile gaming and SaaS. But if you put this dashboard in front of a VP of Operations at a retail company, it’s meaningless. A metric that people can’t remember or discuss in a meeting won’t change anyone’s behavior — no matter how analytically correct it is.”

“This doesn’t mean simple metrics are always better. It means you need to know your audience. ARPDAU is perfectly clear to a mobile product team. Profit margin is perfectly clear to a retail VP. The metric itself isn’t good or bad — it’s good or bad for a specific audience.”

What makes a good metric?

The Lean Analytics framework gives four properties:

Comparative

Can be measured against time periods, segments, or benchmarks. “Conversion increased from 2% to 3%” is more useful than “conversion is 3%.”

Understandable

If people can’t remember it or discuss it, they won’t act on it. The best metrics fit in one sentence.

A ratio or rate

Ratios normalize for scale. Profit margin lets you compare a 10-person store with a 200-person store. Absolute profit doesn’t.

Behavior-changing

If the number moves, someone should do something different. If nobody’s behavior changes, why are you tracking it?

“These four come from Lean Analytics Chapter 2, which you read. The last one — behavior-changing — is the most important and the hardest to test. Ask yourself: if this metric goes up 10%, what do we do? If it goes down 10%, what do we do? If the answer is ‘nothing’ both times, it’s not a useful metric.”

“Ratios are especially important for this course. Profit margin, return rate, average shipping time — these are the building blocks of any business dashboard.”

Vanity metrics feel good but don’t help

A vanity metric makes you feel good but doesn’t change what you do.

Classic examples:

• Total registered users (includes everyone who signed up and left)
• Page views (one person hitting refresh looks like engagement)
• Number of followers (how many actually do something when you post?)

The actionable alternative is usually a ratio: not “total users” but “percent of users who are active this week.”

The car salesman test

A car dealer tells his customer: “You’ll get a satisfaction survey call. Can you give us a 5?”

The dealer spends more time asking for a good rating than providing a good experience. The satisfaction score becomes a vanity metric — it measures gaming, not satisfaction.

— Lean Analytics Ch 2, via Seth Godin

“The car salesman story is a real example from the book. Seth Godin calls this a ‘false metric’ — it measures the wrong thing because the people being measured learned to game it.”

“The antidote: always ask ‘what will I do differently based on this number?’ If you can’t answer that, you’re probably looking at a vanity metric.”

“Keep this in mind — you’re about to see a scenario where a VP panics over a number that turns out to be exactly this kind of misleading metric.”

KPIs: the metrics that run the business

A Key Performance Indicator is a metric an organization monitors regularly to evaluate performance against a goal.

Not every metric is a KPI. A dashboard with 30 KPIs has zero KPIs — if everything is key, nothing is.

Role	They probably watch
CEO	Revenue, margin, customer count, churn
VP Sales	Pipeline value, conversion rate, deal size
VP Operations	Delivery time, error rate, cost per unit
Product Manager	Active users, feature adoption, NPS

The VP of Sales and the VP of Operations need different dashboards because they make different decisions.

“KPIs aren’t a Tableau feature — they’re an organizational concept. Every industry, every role has a small set of numbers they check to know if things are on track.”

“A dashboard with 30 KPIs has zero KPIs — that line is worth remembering. The discipline is in choosing the few that matter.”

“Now let’s see what happens when a VP gets alarming KPIs and asks you to figure out what’s going on.”

Metrics come in pairs

Leading vs. lagging

A leading metric predicts the future. Number of qualified leads in the sales pipeline predicts next quarter’s revenue.

A lagging metric reports the past. Customer churn tells you people left — but by the time you measure it, they’re already gone.

Both are useful. Leading metrics give you time to act.

Correlated vs. causal

Ice cream sales and drowning deaths are correlated. Both go up in summer. Banning ice cream won’t save anyone.

Finding a correlation is useful — it helps you predict. Finding a cause is better — it lets you intervene. Causation requires controlled experiments, not just dashboards.

“Leading vs. lagging is one of the most practical frameworks from the Lean Analytics reading. When you build a dashboard, try to include at least one leading indicator. If you’re building a sales dashboard, show pipeline value alongside closed revenue.”

“The ice cream / drowning example illustrates why we can’t just look at two lines going up together and conclude causation. Both are caused by summer weather. Keep this in mind — you’re about to investigate a scenario where seasonal patterns create exactly this kind of confusion.”

Keep this to about 2 minutes. The concepts are covered in the reading; you’re reinforcing, not teaching from scratch.

BI Detective

Can you figure out what the numbers actually mean?

“We’ve been talking about metrics, comparisons, segmentation. Now let’s see how these ideas work when you’re under pressure. You’re going to play as a data analyst who gets an alarming message from their VP. Your job: explore the dashboard, answer questions, and figure out what’s actually happening — before the meeting.”

“Open the link I’m sharing now. Work through the tabs left to right. Each tab has a question — correct answers unlock observations on the left panel. You have about 15 minutes.”

Share the BI Detective link. Give students 12-15 minutes. Walk around, observe which tabs they explore and how they react to the dead-end breakdowns. Don’t give hints — let them experience the frustration of uniform drops before finding the real signal in the Rider Types tab.

BI Detective: Dashboard Investigation

OK

Olena Koval, VP Operations Divvy Bikeshare, Chicago

Hey! November numbers look bad — rides down 32%, average duration down 12%. Can you figure out what’s going on before our meeting today?

Open the game: Course website → Activities → Week 4 BI Detective. You have 15 minutes.

Work through the tabs left to right. Answer the questions to build your case.

The game uses real Divvy Bikeshare public data (Chicago, Nov 2022 – Nov 2023, ~5.7M trips). Students explore four dashboard tabs: Overview, Breakdowns, Rider Types, and Duration.

The game has two key findings students must unlock: 1. The ride drop is normal seasonality (Nov 2023 is actually +7.6% vs Nov 2022) 2. The duration drop is a composition effect — casual riders (who ride longer) left the mix

Several Breakdowns sub-tabs are intentional dead ends — day-of-week, hourly, bike types, stations all show uniform drops. These teach students to recognize when a dimension doesn’t explain the variance.

After 12-15 minutes, bring the class back. Some students will have finished and submitted; others may be stuck on the Breakdowns tab. Both are useful for the debrief.

Debrief: what did the investigation reveal?

The ride drop is seasonal, not a crisis

Nov 2023 has +7.6% more rides than Nov 2022. The Oct→Nov drop happens every year when it gets cold. The VP compared adjacent months in a seasonal business — the wrong baseline.

Casual riders drove the entire drop

Casual ridership fell -44%, members only -27%. The overall average duration also dropped — not because anyone rides shorter, but because the long-ride group (casuals ride ~2x longer) shrank in the mix.

Dead ends taught us where NOT to look

Hourly patterns, bike types, stations — all dropped uniformly. When every value in a dimension moves the same way, that dimension doesn’t explain the change.

The Thursday trap

Thursday barely dropped (-1%). Resilient day?

No — October had 4 Thursdays, November had 5. More days = more rides. Per-Thursday, the drop was ~30%, same as every other day.

Always check whether your comparison periods have the same calendar structure.

“Who went to the Breakdowns tab first? What did you find?” Let a few students share. Most will say they spent time on day-of-week or stations and found nothing useful.

“Recognizing dead ends quickly is a skill that separates experienced analysts from beginners. If every bar in the chart dropped by the same amount, that dimension isn’t your culprit. Move on.”

“The Thursday trap is worth remembering. Raw totals across months with different calendar structures are misleading — this comes up all the time in real reporting. Always normalize: rides per Thursday, not total Thursday rides.”

“The duration drop is worth discussing too. The VP said average ride duration dropped 12%. But if you segment by rider type, members barely changed. The overall moved because casual riders — who take 2x longer trips — became a smaller share of the mix. The average changed because of who’s riding, not because of how anyone rides. This is what happens with non-additive metrics when the composition shifts.”

“If you were building a Divvy dashboard for this VP, what would you change?” Collect a few answers: YoY comparisons, segment by rider type, seasonal baselines for alerts.

From what to how

You now know what makes a good metric. The rest of the lecture is about how to compute them.

“The Divvy investigation showed you what good analysis looks like: comparing year-over-year instead of month-to-month, segmenting by rider type instead of looking at totals, recognizing composition effects. Now the question becomes: how do you actually build these metrics in your tools? How do you get from raw data columns to profit margin, return rate, shipping delay?”

“The answer is the same across spreadsheets, Tableau, SQL, or any BI tool: you derive new values from existing ones. There are two fundamentally different ways to do that.”

Column-level calculations

A new value for every row — like adding a formula column in a spreadsheet.

Student	Course	Region	Modules Done	Total Modules	Completed?
Anna	Data 101	EU	8	8	1
Ben	Data 101	US	5	8	0
Clara	SQL Basics	EU	6	6	1
Dan	SQL Basics	US	3	6	0
Eva	SQL Basics	EU	6	6	1

IF [Modules Done] = [Total Modules] THEN 1 ELSE 0 END — each row gets its own result. The new column can then be summed, averaged, or counted in any grouping.

“This is the simplest kind of derived value. You look at each row independently and compute something new. Here, we flag whether a student completed all modules. The key property: this new column is additive — you can SUM it to count completions, or use it in a ratio to get completion rate. It works at any level of aggregation.”

“You’ve done this in spreadsheets — adding a helper column and dragging the formula down. Same idea, but in Tableau or SQL the formula applies to every row automatically.”

Dynamic calculations

The same formula, different results — depending on how the view is grouped.

Overall

Students	Completed	Rate
5	3	60%

Grouped by Course

Course	Students	Completed	Rate
Data 101	2	1	50%
SQL Basics	3	2	67%

Grouped by Region

Region	Students	Completed	Rate
EU	3	3	100%
US	2	0	0%

SUM([Completed]) / COUNT([Student]) — one formula, three different answers. The grouping determines what gets aggregated. This is how pivot tables, Tableau, and SQL all work.

“Same five students, same formula. But when you group by course, you see 50% and 67%. When you group by region, it’s 100% and 0% — the EU students all finished, the US students didn’t. The overall 60% hides both stories.”

“This is what Tableau does automatically. You write the formula once. Then you drag Course onto Rows — you get per-course rates. Drag Region instead — you get per-region rates. The formula adapts to whatever dimensions are in the view.”

“Notice: you can’t average the per-course rates (50% + 67%) / 2 = 58.5% to get the overall 60%. The groups have different sizes. This is the non-additive problem we saw in the Divvy game — and it’s what the next slide formalizes.”

Additive vs. non-additive metrics

Not every metric can be summed across groups. Compare COUNT vs. COUNT DISTINCT on the same data:

Enrollments

Student	Course
Anna	Data 101
Anna	SQL Basics
Ben	Data 101
Clara	SQL Basics

COUNT (enrollments)

Course	Count
Data 101	2
SQL Basics	2
Total	4 ✓

2 + 2 = 4. Correct.

COUNT DISTINCT (students)

Course	Unique
Data 101	2
SQL Basics	2
Total	3 ≠ 4

2 + 2 ≠ 3. Anna is in both.

COUNT is additive — the parts sum to the whole. COUNT DISTINCT is not — Anna appears in both courses but is one unique student overall. When you slice a dashboard by a dimension, additive metrics stay correct. Non-additive metrics need to be recalculated, not summed.

“This is one of the most common sources of dashboard errors. Someone exports your per-course student counts and sums them to get ‘total students.’ If any student is in multiple courses, that sum is wrong.”

“Same operation — counting — but COUNT counts rows and COUNT DISTINCT counts unique values. The first is additive, the second isn’t. Revenue (a SUM) is additive. Unique customers, unique sessions, unique anything — non-additive.”

“The Divvy game showed a related trap: the overall average duration dropped because casual riders shrank in the mix, not because anyone rode shorter. Averages are also non-additive — you can’t average the averages of different-sized groups.”

“You’ll encounter this in SQL next year. COUNT(*) vs COUNT(DISTINCT column) behave exactly the same way.”

Calculated fields in Tableau

Tableau’s implementation of the same principle: derive new values from existing columns.

“Now let’s get concrete. The column-level vs. dynamic distinction we just discussed — Tableau handles both. A formula like [Profit] / [Sales] is column-level. SUM([Profit]) / SUM([Sales]) is dynamic — it adapts to whatever grouping is in your view. Let’s look at the types of formulas you can write.”

Four types of calculated fields

Type	Example
Arithmetic	SUM([Profit]) / SUM([Sales])
String	LEFT([Product Name], 3)
Date	DATEDIFF('day', [Order Date], [Ship Date])
Conditional	IF [Profit] > 0 THEN "Profit" ELSE "Loss" END

Product	Order Date	Ship Date	Ship Days
Chair	Mar 1	Mar 4	3
Binder	Mar 1	Mar 2	1
Desk	Mar 3	Mar 10	7

Date calc: DATEDIFF(‘day’, [Order Date], [Ship Date])
Each row gets its own result — this is column-level.

“Arithmetic is by far the most common. You’ll spend most of your time building ratios: margin, rates, averages, per-unit costs. String calculations are useful for grouping — combining categories, extracting parts of product names. Date calculations unlock time-based analysis — shipping delay, days since last purchase, monthly trends. Conditional logic lets you create new dimensions: profitable vs. unprofitable, high discount vs. low discount, returned vs. not returned.”

Aggregation order matters

Order	Sales	Profit	Row Margin
A-001	$100	$10	10%
A-002	$10,000	$500	5%
Total	$10,100	$510	?

AVG( [Profit] / [Sales] ) → (10% + 5%) / 2 = 7.5%

Averages the row-level ratios. Both orders count equally.

SUM([Profit]) / SUM([Sales]) → $510 / $10,100 = 5.05%

Aggregates first, then divides. The $10K order dominates — as it should.

For business metrics, you almost always want the second version.

“This is the single most important concept in calculated fields. Both formulas are syntactically valid. Both produce a number. But they give different answers, and one of them is usually wrong for business purposes.”

“Look at the table. A $100 order with 10% margin and a $10,000 order with 5% margin. The row-level average is 7.5%. But the actual overall margin is 5.05% — the large order dominates. If your VP sees 7.5% on a dashboard and makes decisions based on that, they’re working with the wrong number.”

“Same idea as the Divvy duration: averaging across groups with different sizes gives you a misleading result. SUM/SUM avoids that problem.”

NULLs in calculations

After a left join, unmatched rows get NULL — and NULLs behave differently than you might expect:

Order	Sales	Returned	[Returned] = “Yes”
A-001	$500	Yes	TRUE
A-002	$200	NULL	NULL — not FALSE
A-003	$80	NULL	NULL — not FALSE

A formula using a NULL input produces NULL. [Returned] = "Yes" does not evaluate to FALSE when [Returned] is missing — it evaluates to NULL. Use IFNULL or ISNULL to handle this explicitly.

Aggregations skip NULLs silently. AVG([Returned Value]) ignores NULL rows — it doesn’t treat them as zero. Your average is computed over fewer rows than you think.

Filters exclude NULLs by default. If you filter on a field, NULL values disappear without warning.

“This will bite you if you don’t expect it. The table on screen shows what happens: after a left join to Returns, most orders have NULL in the Returned column. If you write [Returned] = ‘Yes’, the non-returned orders don’t evaluate to FALSE — they evaluate to NULL. That’s why you need ISNULL() or IFNULL() to handle it.”

“This is the same behavior you’ll see in SQL next year. AVG() in SQL also ignores NULLs. COUNT(*) counts all rows, COUNT(column) counts non-NULL values. Knowing this now saves you debugging time later.”

Keep this to 2 minutes. The point is awareness, not mastery. They’ll encounter it hands-on in practice.

Joins

Real business data lives in multiple tables. Joins bring them together.

“So far you’ve worked with single tables. But the Divvy game had rides and rider types as separate dimensions in the same table — imagine if member/casual status lived in a separate customers table. You’d need a join. Superstore has Orders, Returns, and People. Real company data is always split across tables — customers in one, orders in another, products in a third. To answer questions that span tables, you need joins.”

Why multiple tables?

“What’s our return rate by product category?” — the answer requires data from two tables:

Orders

Order ID	Category	Sales
A-001	Furniture	$500
A-002	Technology	$200
A-003	Furniture	$80

+

Returns

Order ID	Returned
A-001	Yes

Only returned orders have rows here.

Category lives in Orders, return status lives in Returns. A join on Order ID combines them — like XLOOKUP from week 1, but for the entire table at once.

“Joins are the formal version of XLOOKUP. Instead of pulling one field from a lookup sheet, you’re attaching an entire table based on matching keys. Tableau makes this visual — you drag tables onto a canvas and specify how they connect.”

“The key question is always: what field do these two tables share? Here it’s Order ID. That shared key is what makes the join possible.”

Four join types

Join	What you get
Inner	Only rows that match in both tables
Left	All rows from the left, matches from the right (NULL where no match)
Right	Mirror of left
Full outer	All rows from both tables

In analytics, you’ll use left join most of the time.

Left join: Orders + Returns

Order ID	Sales	Returned
A-001	$500	Yes
A-002	$200	NULL
A-003	$80	NULL

All orders kept. Return info attached where it exists. Non-returned orders get NULL — not dropped.

“Left join is the workhorse. ‘Give me all my orders. For each one, attach the return status if it exists. If an order wasn’t returned, leave those fields NULL.’ This keeps your full order history intact and adds context where available.”

“Inner join throws away any row that doesn’t match on both sides. If you inner join Orders to Returns, you’d only keep the ~300 returned orders and lose the other 97% of your data. That’s rarely what you want.”

“In Tableau, you’ll drag tables onto the data source pane and pick the join type from a visual menu. The join clause is usually a shared key — Order ID, Customer ID, Region.”

The most common join mistake: fan-out

If the right table has multiple rows per key, your join duplicates rows on the left:

Orders

Order ID	Sales
A-001	$500
A-002	$200

2 rows, $700 total

Returns

Order ID	Reason
A-001	Damaged
A-001	Wrong item

Two rows for one order!

After left join

Order ID	Sales	Reason
A-001	$500	Damaged
A-001	$500	Wrong item
A-002	$200	NULL

3 rows, SUM(Sales) = $1,200 — wrong!

Rule: always check row count and a key total (like SUM of Sales) before and after a join. If either changed, your join created duplicates.

“Fan-out is the single most common join mistake, and it’s easy to miss because Tableau won’t warn you. The numbers just silently inflate. In the example on screen, $500 becomes $1,000 because one order matched two return reasons.”

“The Superstore join you’ll do is safe — Returns has one row per Order ID, People has one row per Region. No duplication. But in assignments and the group project, you’ll work with messier data. Always check.”

Joins vs. unions

Operation	What it does	Analogy
Join	Adds columns — attaches new fields from a second table	Stapling two pages side by side
Union	Adds rows — stacks data from tables with the same columns	Stapling two pages top to bottom

You saw unions conceptually in week 3 (combining January + February + March files). Joins are new this week.

“Quick distinction. You probably won’t confuse them, but it’s worth naming: unions are for ‘more of the same data’ (monthly files, regional exports). Joins are for ‘different data about the same entities’ (orders + returns, customers + demographics).”

Keep this to 30 seconds. It’s a vocabulary clarification, not a new concept.

Next week: the power tools

Week 5: Advanced Calculations

• Table calculations — running totals, rankings, percent of total
• LOD expressions — calculating at a different level than the view shows
• Cohort analysis — grouping customers by when they first appeared

Reading for next week:

• PT Ch 13 — Introduction to Table Calculations
• PT Ch 16 — Introduction to LOD Expressions

“This week you’re writing basic calculated fields — arithmetic, dates, IF/THEN. Next week introduces two more types that are more powerful and harder to understand: table calculations (which compute across the rows in your view, like a running total) and LOD expressions (which compute at a different granularity than what’s on screen). Pre-reading is essential — LOD is the hardest Tableau concept, and seeing it once before class makes the lecture more productive.”

“Assignment 2 is due this week. Assignment 3 will be introduced — it builds on the Superstore dashboard you create in practice.”