Types of Data and Measurement Scales

When the math is legal but the conclusion is wrong

A consumer packaged goods company reported "average customer satisfaction of 4.2" on a five-star scale and set a company-wide objective of 4.5. The chart looked precise. The problem was conceptual: star ratings are ordered categories, not dollar amounts. The distance between one star and two may not equal the distance between four and five. Averaging stars treats them like interval data and can mis-rank stores. Worse, one unhappy wholesale buyer representing 18% of revenue sat hidden inside thousands of happy retail shoppers. The dashboard was not lying on purpose. It was applying the wrong operations to the wrong measurement scale.

From Lesson 1, you learned to translate business questions into testable data questions. This lesson teaches what kind of evidence those questions produce. Measurement scale determines which summaries are legitimate (counts, medians, averages, ratios) and which joins and aggregations corrupt meaning. Managers who skip this lesson approve targets that cannot be defended and fund models built on fields that should never enter a regression.

The cost shows up in meetings, not textbooks. A private equity diligence team once walked away from a acquisition because the target's "customer health score" could not be reconciled to churn in the enterprise segment. The score averaged ordinal inputs and masked two large accounts at risk. Scale literacy would have forced segment-level median and revenue-weighted views earlier. The deal might still have failed, but the conversation would have been honest weeks sooner.

Your toolkit in this lesson is conceptual, not computational. You will classify fields, choose legitimate summaries, name data structures (cross-section, time series, panel), and spot encoding traps. Analysts implement; managers challenge whether the implementation respects the type of evidence.

Four measurement scales and why order matters

Nominal data are categories with no inherent order. Country, payment method, product family, and campaign channel are nominal. You can count how many customers chose each category and report the mode (most common). You cannot say "email is greater than paid search." Averaging encoded categories (treating email = 1, paid search = 2) imports fake math.

Ordinal data are ordered categories where rank matters but spacing is unknown. Survey Likert items ("strongly disagree" to "strongly agree"), support priority P1–P4, and star ratings are ordinal. You can sort and take medians or percentiles. Averaging ordinals is common in business and often defensible as a rough index, but it is not interval truth. Decisions that hinge on small mean differences (0.1 star) are fragile.

Interval data have equal gaps between values but no true zero. Temperature in Celsius and calendar years are classic examples. You can add and subtract (the gap from 10°C to 20°C is the same size as 20°C to 30°C) but ratios are meaningless (20°C is not "twice as hot" as 10°C in a physical sense). Many business "indices" behave like intervals only if you prove equal weighting.

Ratio data have a true zero where "none" means none. Revenue, units sold, minutes to resolve a ticket, and defect counts are ratio. All arithmetic including ratios is meaningful: one store sold twice the units of another. Most operational KPIs (key performance indicators, tracked metrics tied to goals) that drive dollars are ratio or derived from ratio (rates).

The managerial stake is governance: finance and product must agree whether a field is ordinal or ratio before it becomes an OKR (objectives and key results, goal-setting framework). Mislabeling ordinals as intervals produces false precision in board decks.

Consider a hiring panel comparing two candidates on "culture fit" scores of 4.2 vs 4.3 on a five-point rubric. The difference may be noise within ordinal judgment, not a ratio gap justifying an offer. Yet spreadsheets will happily subtract 4.3 minus 4.2 and declare a winner. Your job is to ask whether the decision should instead use structured behavioral evidence, calibrated interviews, and role-specific work samples. Scale literacy prevents false tie-breakers.

Another frequent confusion is treating coded categories as interval because the software stored them as numbers. A CRM may store tier as 1, 2, 3 for bronze, silver, gold. That encoding is for storage convenience, not proof that silver is exactly halfway between bronze and gold in lifetime value. For modeling, you may one-hot encode nominal tiers (separate yes/no columns per tier) rather than regress on a single number.

Variables by role in business tables

Beyond scale, each column plays a role in analysis. Confusing roles breaks joins and summaries.

Categorical variables segment populations: region, channel, customer tier, industry vertical. They are usually nominal or ordinal. Use them in filters, group-bys, and stratified comparisons.

Numerical continuous variables take many values on a continuum: revenue per order, session length in minutes, temperature in a cold chain log. They are often ratio or interval. Means and distributions matter.

Numerical discrete variables count indivisible units: items in cart, seats on a flight, number of support contacts. They behave like ratio for sums but may need different models for forecasting if small counts.

Identifiers (user ID, invoice number, device ID) are not measurements. They exist to uniquely tag rows and join tables. Never average IDs. Never chart IDs. A pivot table "average customer_id" is nonsense that still happens when exports are rushed.

Timestamps enable sequencing, cohorts, seasonality, and service-level windows. They are not outcome metrics by themselves but unlock time-based ratio metrics (orders per day, tickets per week).

In a spreadsheet, role mistakes show up as wrong pivot field choices. In SQL, they show up as AVG(customer_id) in a careless SELECT. Managers should ask: "Is this column a label, a measurement, or a key?"

Structured, semi-structured, and unstructured data

Structured data fit rows and columns with stable types: transactions, CRM contacts, inventory snapshots. This is the default terrain for managerial analytics in Excel, BI (business intelligence, reporting tools like Tableau or Power BI), and warehouses.

Semi-structured data have predictable fields embedded in flexible containers: JSON event logs, web clickstreams, API payloads. They often flatten into structured tables for analysis (one row per event).

Unstructured data lack a fixed schema: emails, call transcripts, images, PDF contracts. They require coding (manual tags, NLP (natural language processing, text analysis methods)) before measurement scales apply. Sentiment scores derived from text become ordinal or interval-like only after you define and validate the scoring rule.

Most decisions in this course begin structured. Unstructured sources matter for customer insight but rarely should drive payroll or credit decisions without human review and documented error rates.

Cross-sectional, time series, and panel structures

How data is organized across units and time determines valid comparisons and model choice.

Cross-sectional data observe many units at one point: a customer survey this week, a snapshot of store margins in March. You compare across units at a single time slice. Risk: confounding (stores with remodels also have higher traffic).

Time series follow one unit over time: monthly corporate revenue 2020–2025, daily site visits for a single product. You compare a unit to its own past. Risk: mistaking seasonality for trend.

Panel data track many units over time: weekly sales for each of 200 stores across two years. Panels support richer questions (store fixed effects, learning curves) but demand consistent grain. Pooling panel data without acknowledging unit-specific traits can hide that only a few stores drive "average" lift.

State structure explicitly in every brief. From Lesson 1, population and time window must match structure. A cross-sectional survey cannot answer a month-over-month operations question without merging to a time series of behavior.

Rates, indexes, and derived metrics

Many dashboards show rates (churn rate, conversion rate, defect rate) rather than raw counts. A rate is typically a ratio of two ratio-scale counts with aligned denominators: churned customers in March divided by active customers at March start. Misaligned denominators are a silent scale error (using churned in March divided by signups in March).

Indexes combine multiple fields into one number (customer health score, supplier risk index). Indexes are managerially convenient and statistically dangerous unless weights are documented and validated. Treat them as ordinal or interval-like for decision thresholds, not as ratio truth.

When a metric is derived, trace it to base fields and scales. NPS (Net Promoter Score, percent promoters minus percent detractors on a 0–10 recommend scale) is built from ordinal buckets (0–6 detractor, 7–8 passive, 9–10 promoter). Report promoter and detractor proportions alongside any index.

Spreadsheet and SQL implications (prose)

In Excel, put raw fields in a source tab at the finest grain. Build derived metrics in a second tab with documented formulas. If satisfaction_score is 1–5 stars, create top_box = IF(score>=4,1,0) for proportion analysis instead of assuming interval means. For revenue by country, never sum yen and dollars in one cell; convert to a single currency column first (ratio integrity).

In SQL, cast types deliberately: dates as dates, IDs as strings (not floats), amounts as decimal. Join on keys, aggregate on measurements. A pattern for weekly store panel metrics: SELECT store_id, week_start, SUM(revenue) AS revenue, COUNT(DISTINCT order_id) AS orders FROM fact_orders GROUP BY store_id, week_start. Check row grain: one row per store-week ✓. If COUNT(*) explodes, a many-to-many join duplicated rows.

Managers reviewing work should ask for a one-line grain statement and a scale/role table for every non-obvious column used in a decision metric.

Encoding, transformation, and binning traps

Real pipelines transform raw fields into "helpful" numbers that erase scale information. A product team bins session length into low, medium, high then encodes high = 3 for a "engagement score." The score is ordinal at best. Marketing then averages engagement scores across regions and sets quotas. Executives interpret movement from 2.1 to 2.2 as continuous improvement. The right documentation would say: "percent of sessions in high bin," a ratio-scale proportion built from ordinal bins.

Binning (grouping continuous values into buckets) always sacrifices information. It can help reporting (income brackets for privacy) but changes scale. Brackets are ordinal; analyzing them with ratio tools misleads. If you must bin revenue, report bracket shares and median bracket, not mean bracket code.

Log transforms apply to positive ratio data (revenue, counts) when distributions are skewed. Logs do not turn ordinal stars into ratio data. After a log transform, explain conclusions in original units (dollars, counts) for finance stakeholders.

Standardization (z-scores) centers ratio or interval-like features for modeling. A z-score on store traffic is not permission to add z-scores of star ratings and call it a composite health metric without weights and validation.

Spreadsheet discipline: keep a data_dictionary tab listing column, scale, role, source_system, allowed_operations. SQL discipline: create views with explicit names like v_customer_month_ratio_metrics vs v_survey_ordinal_responses so consumers know which aggregates are legal.

Who cares about scale: stakeholder lenses

Investors may prefer a single headline number. Operators need distributions and segments. Legal cares how features enter models. When stakeholders conflict, expose both views: mean with median and top-box; total revenue with enterprise share. Lesson 1's translation step should name which stakeholder view is primary for the decision.

Comparing units and currencies on ratio scales

Ratio data still require common units. Summing yen and dollars, meters and feet, or seconds and minutes without conversion produces confident nonsense. Finance teams maintain exchange rate tables with effective dates; operations should do the same for any cross-region operational dashboard.

Rates built on ratio counts need aligned denominators (Lesson 2 preview): customer-month churn divides churned customers by customers active at period start, not by signups unless the question is explicitly about new-logo churn. Label rates on slides.

When a vendor changes currency reporting mid-year, restate prior periods or break the series with a footnote. Consistency dimension from Lesson 3 overlaps here: a beautiful chart with a silent unit change is a policy failure.

BrightCart, an online grocer, averages 1–5 star delivery ratings to 4.1 and celebrates improvement from 3.9. Enterprise buyer FoodWorks represents 22% of revenue and recently scored three consecutive twos.

Part A: Setup

Business question: Is customer satisfaction strong enough to renew enterprise contracts?

Fields:

Population: deliveries with ratings in Q1 2026.

Part B: Wrong vs right summaries

Wrong path: single mean 4.1 for all segments. Enterprise mean = 2.3; consumer mean = 4.3. Blended mean hides enterprise pain.

Right path:

Check: revenue shares sum to 100% ✓. Rating counts sum to total rated deliveries ✓.

Spreadsheet logic: pivot star_rating to median and top_box_pct by customer_segment; join revenue shares from a separate account table on segment.

Part C: Interpretation

Ordinal integrity: report median and top-box, not only mean. Enterprise deterioration is severe and revenue-weighted. Consumer strength does not offset contract risk.

Part D: Managerial read

BrightCart should pause marketing boasting about 4.1. Operations should open an enterprise service review with FoodWorks this month. Product should trace failed deliveries for enterprise routes separately. The board question: "What percent of ARR is exposed to accounts with median rating below 3?" Finance should model renewal probability separately by segment instead of one company-wide satisfaction target.

LumenTel sells phones through 85 company-owned stores and tracks weekly units_sold, promo_spend, and foot_traffic_index (vendor index centered at 100). Leadership compares "average lift during promo weeks."

Part A: Structure

Data are panel: 85 stores × 104 weeks. foot_traffic_index is interval-like (vendor-defined). units_sold is ratio. promo_spend is ratio.

Part B: Pooling error

Analyst pools all store-weeks and computes correlation between promo_spend and units_sold at r = 0.42. High-traffic flagship stores spend more promos and sell more units always. Pooling confounds store size with promo effect.

Better approach: for each store, compute deviation from that store's own 52-week average units_sold during promo weeks vs non-promo weeks. Then average deviations across stores. Result: average lift +6 units per store-week during promo, but flagship stores drive 70% of the pooled correlation.

Check: sum of store-level promo weeks across stores = 312 store-week promos ✓, matching promo flag table ✓.

SQL plain English: create store_week grain; join promos; compute units_sold - AVG(units_sold) OVER (PARTITION BY store_id) as demeaned sales; compare mean demeaned sales when promo_flag=1 vs 0.

Part C: Managerial read

Marketing should not set national promo budgets from pooled correlations. Allocate promos where store-specific lift is positive and foot traffic index is stable. Close the analytic loop in Lesson 1 terms: the data question is "Within each store, do promo weeks beat that store's baseline?" not "Do promos correlate with sales nationally?"

Part D: Scale check on foot traffic index

Because foot_traffic_index is vendor-defined with base 100, treat comparisons as interval-like, not ratio. Saying "traffic doubled" because index moved 100 to 200 may be wrong if the vendor rescales annually. Request vendor documentation before ratio language enters OKRs. Combine index with ratio ground truth where possible (door counter sessions).

RiverBank uses a 1–7 Likert agreement scale on employee engagement and reports "engagement rose from 5.1 to 5.3." Separately, employee_id appears in a pivot averaging "responsiveness score" alongside numeric IDs.

Tasks:

1. Classify likert_agreement and employee_id by scale and role.
2. Propose two better summary statistics for engagement than the mean alone.
3. A regional manager wants to rank offices by engagement. Why might mean ranking differ from median ranking? Give a numeric mini-example with three offices.
4. Write plain-English SQL or spreadsheet logic to compute top-box (6–7) rate by office for Q2 2026.

Solution

1. Classification: likert_agreement is ordinal outcome. employee_id is an identifier key, not a measurement. Responsiveness score (if 1–7) is ordinal; never average IDs.

2. Better summaries: median agreement per office; percent of responses in categories 6–7 (top-box); percent in categories 1–3 (bottom-box) to detect tail risk.

3. Median vs mean ranking: Office A responses: six sevens and four ones. Mean = 4.6, median = 7. Office B responses: all fives. Mean = 5.0, median = 5. Mean ranks B above A; median ranks A above B. Means are sensitive to polarized ordinals; medians reflect typical rank.

4. Logic: Spreadsheet: filter Q2 responses; pivot with office on rows, response_id count as n, and top_box = IF(likert>=6,1,0) averaged as top-box rate. SQL: SELECT office, COUNT(*) AS n, AVG(CASE WHEN likert>=6 THEN 1.0 ELSE 0 END) AS top_box_rate FROM engagement_responses WHERE quarter='2026-Q2' GROUP BY office. Check: sum of n across offices equals total Q2 responses ✓.

Explain why: HR decisions about manager intervention should trigger on bottom-box concentration, not a 0.2 mean twitch.

A logistics dashboard sums package_weight_kg and package_id after a bad export places IDs in a numeric column. Total weight is correct; "average package_id" is 4,812,233.

Tasks:

1. Identify two role/scale errors in the dashboard.
2. Describe how duplicated package_id rows would distort weight totals and what check to run.
3. State the correct grain for on-time delivery rate.

Solution

1. Errors: package_id is an identifier, not a ratio variable; averaging it is meaningless. If package_id was treated as numeric measurement, scale is wrong. Weight is ratio and valid to sum only at a defined grain.

2. Duplicates: If joins duplicate rows, SUM(weight) double-counts packages. Check: COUNT(DISTINCT package_id) should equal logical package count; compare to COUNT(*); investigate if COUNT(*) > COUNT(DISTINCT package_id).

3. Grain: one row per package (or per shipment if multi-package shipments are the unit of service). On-time rate = packages delivered on time divided by packages due in period, same population.

Explain why in prose: Operations leaders need defensible service metrics. Mixing identifier columns with measurements signals an export pipeline that will keep breaking until roles are enforced at the source.

Your marketing team proposes a "brand health index" averaging NPS (ordinal-derived), Instagram followers (ratio), and "share of voice" percent (ratio). They target a single index OKR.

Tasks:

1. Which components are ratio vs ordinal-derived?
2. Why is an unweighted mean problematic?
3. Suggest a reporting pack that respects scales.

Solution

1. NPS is derived from ordinal 0–10 recommend responses; followers and share of voice are ratio-scale counts or percentages.

2. Unweighted mean treats a 2-point NPS move like a 2% share-of-voice move, which have different business meaning and volatility. Ordinal and ratio components should not be averaged without explicit weights and validation.

3. Report separately: NPS with promoter/detractor mix; follower growth rate month over month; share of voice trend. If leadership insists on one slide, use a traffic-light dashboard per component rather than a faux-precise composite mean.

Key takeaways

• Measurement scale dictates legitimate math; ordinal data are not interval data without justification.
• Classify columns by role (category, measurement, identifier, time) before pivoting or modeling.
• Know whether data are cross-sectional, time series, or panel; structure must match the question from Lesson 1.
• Document derived metrics and indexes to their base fields; trace rates to aligned numerators and denominators.
• Demand median/top-box and segment views when ordinal outcomes drive revenue risk.

1. List five metrics your organization tracks; label each by scale and role; note one misuse you have seen.
2. Find a report that averages an ordinal score; how would median and top-box change the story?
3. Continue to Lesson 3: Data Quality, Missingness, and Bias.