Descriptive and Diagnostic Analytics

Introduction and Context

Business intelligence (BI) enables organizations to convert operational data into information for decision-making. Two of its most practical forms are descriptive analytics, which explains what has happened, and diagnostic analytics, which investigates why it happened. Together, they form the analytical foundation for dashboards, management reporting, performance reviews, and operational improvement.

Descriptive analytics summarizes historical and current data through aggregation, segmentation, statistical summaries, trend analysis, and visualization. Diagnostic analytics extends this work by comparing outcomes, drilling into detailed records, identifying unusual variation, testing plausible explanations, and locating root causes. Neither form automatically establishes causality. A decline in sales may coincide with a price increase, a supply shortage, a competitor campaign, or a change in data capture. Diagnostic analysis therefore requires disciplined reasoning, appropriate comparison groups, and awareness of data limitations.

For business analysts, the central task is not merely to produce a report. It is to construct a reliable chain from a business question to a measured pattern, from the pattern to a plausible explanation, and from the explanation to an actionable decision.

Learning Objectives

By the end of this chapter, students should be able to:

1. Distinguish descriptive analytics from diagnostic analytics and explain their roles in BI.
2. Apply aggregation, segmentation, drill-through, and statistical summaries to business data.
3. Calculate and interpret variance, percentage change, rates, averages, medians, percentiles, and dispersion.
4. Analyze trends while distinguishing genuine movement from seasonality, outliers, and data-quality problems.
5. Use structured methods to investigate symptoms and identify likely root causes.
6. Design analysis that is reproducible, appropriately governed, and useful for managerial action.

1. The Analytical Questions of Business Intelligence

A mature BI environment often supports a sequence of questions:

This chapter focuses on the first four questions. Prescriptive analytics may use the findings generated here, but it also requires decision models, constraints, forecasts, or optimization.

Descriptive analytics

Descriptive analytics transforms raw observations into interpretable summaries. Common outputs include:

• totals and subtotals;
• counts of customers, transactions, or cases;
• averages, medians, minimums, maximums, and percentiles;
• rates and ratios;
• actual-versus-target comparisons;
• time-series charts and geographic summaries;
• ranked lists and exception reports.

A descriptive result should specify the measure, population, time period, unit, and level of aggregation. “Sales were 2.4 million” is incomplete unless the currency, period, and inclusion rules are known.

Diagnostic analytics

Diagnostic analytics examines relationships and contrasts within data to explain an observed outcome. It commonly uses:

• comparison of actual performance with budgets, targets, or prior periods;
• decomposition of totals into products, locations, channels, or customer groups;
• drill-through from summary values to underlying transactions;
• analysis of correlations and associations;
• exception investigation;
• root-cause analysis;
• validation against operational context.

Diagnostic analysis produces evidence-based explanations, not guaranteed causal proof. A relationship may be coincidental, produced by a third factor, or caused by a measurement change.

2. Data Structure for Reliable BI Analysis

Before calculating a metric, analysts must understand the data model. A common analytical table is a fact table, containing measurable events such as orders, shipments, claims, or website sessions. Dimension tables describe those events through attributes such as date, product, customer, region, salesperson, and channel.

Grain

The grain is the level represented by one row. For example, a sales table may contain one row per order, one row per order line, or one row per daily product-location combination. Aggregating an order-line table without understanding its grain can double-count order-level values such as shipping cost.

Measures and dimensions

A measure is a numeric field that can be aggregated or analyzed, such as quantity, revenue, cost, or delivery time. A dimension is a descriptive field used to group or filter data, such as month, region, product category, or customer segment.

Measures may be:

• Additive: can be summed across all relevant dimensions, such as units sold.
• Semi-additive: can be summed across some dimensions but not time, such as account balance.
• Non-additive: should not be summed, such as percentages, ratios, and unit prices.

For a ratio, aggregate its numerator and denominator before dividing whenever possible. For example, the overall conversion rate should generally be calculated as total conversions divided by total eligible visits, not as an unweighted average of daily conversion rates.

Data-quality checks

Diagnostic conclusions are only as credible as the data supporting them. Analysts should check:

• missing or duplicated records;
• inconsistent definitions across reports;
• changes in coding or category labels;
• impossible values, such as negative quantities where they are not permitted;
• time-zone and date-boundary issues;
• late-arriving or cancelled transactions;
• changes in system coverage or data capture;
• joins that multiply rows.

A data-quality problem can resemble a business problem. For example, a sudden fall in customer complaints may reflect a broken case-intake integration rather than improved service.

3. Aggregation and Segmentation

Aggregation

Aggregation reduces detailed records to a higher-level summary. Typical operations include sum, count, distinct count, average, minimum, maximum, and percentile. The analyst should choose an aggregation that matches the business meaning of the measure.

Suppose a retailer records the following weekly sales:

Total monthly sales are:

The arithmetic mean weekly sales is:

Here, x̄ is the arithmetic mean and each weekly observation has equal weight. If weeks represent different numbers of operating days, a daily rate may be more appropriate.

Segmentation

Segmentation divides a population into meaningful groups. Common segmentation dimensions include:

• geography;
• product or service;
• customer value or lifecycle stage;
• channel;
• industry or account type;
• employee team;
• order size or service priority;
• risk category.

Useful segmentation has three characteristics. First, groups are defined consistently. Second, groups are sufficiently large for stable interpretation. Third, the segmentation supports a decision. A report with dozens of tiny categories may be detailed but not useful.

Segmentation can reveal mix effects. Total performance may improve because a high-value segment grew, even though performance deteriorated within most segments. Conversely, an overall decline may be caused by a shift toward lower-value products rather than lower performance within each product category.

4. Statistical Summaries for Business Data

Statistical summaries describe the center, spread, and shape of observations.

Measures of central tendency

For observations x₁, x₂, ..., x_n, the arithmetic mean is:

where n is the number of observations. The mean is appropriate when values are measured on a meaningful numeric scale and extreme observations do not dominate interpretation.

The median is the middle observation after sorting. It is often more representative than the mean for skewed measures such as order value, income, claim size, or response time. The mode is the most frequently occurring value and is especially useful for categorical data.

Weighted averages

When observations have different importance or exposure, a weighted mean is appropriate:

where xᵢ is an observation and wᵢ is its non-negative weight. For example, average selling price should normally be calculated as total revenue divided by total units, rather than as the simple average of product-level prices.

Measures of dispersion

The range is maximum minus minimum. Variance and standard deviation describe spread around the mean. For a population, variance is:

For a sample used to estimate a larger population, sample variance is:

The corresponding standard deviation is the square root of variance. μ and σ² describe a population mean and variance; x̄ and s² describe sample estimates. The n − 1 denominator provides the usual unbiased estimator of population variance under common random-sampling assumptions.

The interquartile range, IQR = Q₃ − Q₁, measures the spread of the middle 50% of observations and is less affected by extreme values. Percentiles are useful for service-level analysis: the 95th percentile of delivery time is the value at or below which approximately 95% of observations fall, subject to the chosen percentile method.

Worked example: delivery performance

Five delivery times are 2, 3, 3, 4, and 13 days. The mean is:

The median is 3 days. The mean is pulled upward by the 13-day delivery, while the median better represents a typical delivery. A manager should therefore report both the center and the tail: most deliveries are near three days, but a serious long-delay problem exists.

5. Variance Analysis

Variance analysis compares an observed value with a reference value. The reference may be a budget, target, forecast, prior period, benchmark, or control group.

Absolute and percentage variance

Let A be actual performance and B be the baseline. Absolute variance is:

Percentage variance, when B ≠ 0, is:

The sign must be interpreted in context. A positive variance in revenue may be favorable, whereas a positive variance in defects or costs may be unfavorable. For costs, organizations often define a favorable variance as actual cost below budget, so the report should state its convention explicitly.

If budgeted sales were $500,000 and actual sales were $460,000:

Sales were $40,000, or 8%, below budget. The next diagnostic step is decomposition by product, region, price, volume, and timing.

Rate and ratio interpretation

A rate should include its denominator. “Complaints increased by 20%” may be misleading if orders increased by 50%. A complaint rate can be expressed as:

The multiplier 1,000 is a reporting scale, not a change to the underlying rate. The numerator and denominator must refer to the same population and period.

6. Trend Analysis and Time Comparisons

Trend analysis examines movement over time. A basic period-over-period change is:

where xₜ is the current value and xₜ₋₁ is the immediately preceding comparable value. The baseline must not be zero, and comparison periods should have comparable definitions and exposure.

For annualized compound growth over k periods:

CAGR summarizes a constant equivalent growth rate; it does not imply that actual growth was constant in every period.

Trend interpretation principles

1. Use comparable periods. Compare weekdays with weekdays, equivalent promotional periods, or the same month in prior years when seasonality matters.
2. Separate level from rate. A sales total may rise because the number of stores or operating hours rose.
3. Inspect the baseline. A small absolute change can produce a large percentage change when the prior value is small.
4. Identify structural breaks. A new pricing system, acquisition, policy, or data source may change the series.
5. Check outliers. One unusual transaction can distort a short trend.
6. Avoid overinterpreting noise. Repeated movement across several periods is generally more informative than a single fluctuation.

A moving average can smooth short-term noise. A simple moving average of window length k is:

It is descriptive, not automatically predictive. Smoothing can also hide genuine turning points, especially when the window is long.

7. Drill-Through and Diagnostic Decomposition

A dashboard should support movement from a high-level indicator to the records and dimensions that explain it. This is commonly called drill-down or drill-through.

A practical diagnostic path is:

1. Confirm the headline metric. Verify definition, time period, filters, and data refresh status.
2. Decompose by major dimensions. Compare regions, products, channels, customers, or teams.
3. Rank contributions. Identify segments that account for most of the absolute variance.
4. Examine rates and denominators. Determine whether the issue is volume, conversion, price, cost, or exposure.
5. Inspect detailed records. Review transactions, cases, shipments, or system events.
6. Compare with operational evidence. Consult inventory logs, staffing records, campaign calendars, or incident reports.
7. Test alternative explanations. Look for evidence that could disconfirm the leading hypothesis.

A useful principle is to distinguish contribution to a total change from performance within a segment. If total revenue falls by $40,000, a region may contribute $30,000 of the fall because it is large, even if its percentage decline is modest. Another small region may have the worst rate decline but contribute little to the total.

8. Root-Cause Methods

Five Whys

The Five Whys method repeatedly asks why an observed problem occurred until the analysis reaches a controllable process condition. It is not a requirement to ask exactly five questions. The number is a prompt for deeper inquiry.

Example:

• Problem: online orders were shipped late.
• Why? Picking was completed after the carrier cut-off.
• Why? The picking queue accumulated in the afternoon.
• Why? A promotion generated an unexpected order spike.
• Why? Promotional demand was not incorporated into the labor plan.
• Why? The campaign calendar was not connected to workforce planning.

The final statement is more actionable than “the warehouse was slow,” but it should be validated with timestamps and staffing data.

Fishbone, or cause-and-effect, analysis

A fishbone analysis organizes possible causes into categories such as people, process, technology, materials, measurement, and environment. It is useful for collaborative hypothesis generation, but a listed cause is not evidence. Each proposed cause should be tested with data.

Pareto analysis

Pareto analysis ranks causes or categories by their contribution to a problem. A cumulative contribution can be calculated as:

where cᵢ is the contribution of category i, categories are ordered from largest to smallest, and m is the number of categories. The familiar “80/20” pattern is a heuristic, not a law. Some problems are concentrated in a few causes; others are broadly distributed.

Hypothesis testing through comparison

A diagnostic hypothesis becomes stronger when the affected group differs from a well-chosen comparison group. For example, compare delivery performance for the affected warehouse with similar warehouses, before and after a process change, or for promoted and non-promoted products. Analysts should consider confounding variables such as season, product mix, customer type, and order complexity.

Correlation can identify an association but does not establish causation. A causal conclusion requires stronger design, such as a credible natural experiment, controlled intervention, or longitudinal evidence supported by domain knowledge.

9. Illustrative Case: Declining Customer Retention

Background

This fictional case concerns Northstar Office Supplies, a business-to-business distributor. Its monthly dashboard shows that the 90-day repeat-purchase rate fell from 42% to 36% over two quarters. Senior management asks whether the fall reflects weaker customer service, pricing, product availability, or a reporting problem.

Problem definition

The analyst first defines the metric:

Only customers whose first purchase occurred at least 90 days before the measurement date are eligible. This avoids classifying recent customers as non-repeat customers merely because insufficient time has passed.

Analysis

The analyst validates the customer cohort logic and discovers that the CRM migration changed customer identifiers. After a matching correction, the rate remains below the prior level, although the decline is 4 percentage points rather than 6.

Segmentation produces the following results:

The enterprise segment accounts for most of the revenue impact because it has high order value. Drill-through shows that repeat purchasing declined sharply for customers whose core printer supplies were unavailable. Inventory records show that two high-volume products experienced stock-outs after a supplier change. The decline is also concentrated among customers assigned to the affected distribution center.

The analyst checks alternative explanations. Prices increased slightly, but customers with available products did not show a comparable retention decline. Service response times deteriorated modestly, but the timing and product concentration align more closely with availability.

Approach and solution

The organization classifies the likely root cause as a supply continuity problem, amplified by weak customer notification. It takes four actions:

1. establish safety-stock thresholds for critical products;
2. create an exception dashboard for projected stock-outs;
3. notify account managers when enterprise customers are affected;
4. monitor repeat-purchase rates by product availability, segment, and distribution center.

Outcome

In the following quarter, availability for the critical products improves and enterprise retention returns to 48%. The case does not prove that inventory was the sole cause, but the timing, concentration, operational records, and post-intervention improvement provide a coherent and testable explanation.

Learning points

• Define the metric before interpreting it.
• Validate identity and cohort logic after system changes.
• Separate percentage-point changes from percentage changes.
• Use segmentation to locate concentration.
• Combine BI evidence with operational records.
• Test competing explanations rather than accepting the first plausible story.
• Link diagnostic findings to an owner, action, and follow-up measure.

10. Dashboard Design and Communication

A diagnostic dashboard should support a decision, not merely display data. Effective design practices include:

• place the business outcome and its target prominently;
• show the current value, prior value, target, and variance;
• provide filters with visible selections;
• use consistent definitions and labels;
• show denominators for rates;
• enable drill-down while preserving context;
• display data-refresh time and coverage;
• highlight exceptions without excessive color;
• include notes about exclusions and calculation rules;
• distinguish confirmed facts from hypotheses.

A strong narrative might state: “Revenue was 8% below budget, primarily because two products in the West region were unavailable. The region contributed 75% of the absolute shortfall. The conclusion is supported by inventory records and order-level availability; price and traffic were broadly stable.” This is more useful than a chart without interpretation.

11. Governance, Ethics, and Responsible Use

Descriptive and diagnostic analytics can affect customers, employees, suppliers, and communities. Governance requirements include data access controls, audit trails, retention rules, documented metric definitions, and appropriate privacy protection.

Analysts should avoid exposing personally identifiable information when aggregate data is sufficient. Small groups may create re-identification risk, especially when several filters are combined. Sensitive attributes should not be used as informal proxies for protected characteristics without legal, ethical, and organizational review.

Fairness must also be considered. A service dashboard may show poorer outcomes for a customer group because of unequal access, biased data collection, or an operational rule. Treating the observed difference as evidence that the group is inherently less valuable is analytically and ethically unsound.

Automation and AI can assist with anomaly detection, natural-language summaries, and suggested drill paths. However, automated explanations may reproduce correlations, omit context, or present uncertain hypotheses as facts. Human review, source traceability, confidence communication, and documented approval remain necessary for consequential decisions.

12. Limitations and Best Practices

Descriptive and diagnostic analytics have important limitations:

• historical data may not represent future conditions;
• incomplete data can produce biased summaries;
• aggregation can conceal subgroup differences;
• averages can hide skew and outliers;
• correlation does not prove causation;
• multiple comparisons can produce coincidental patterns;
• changing definitions can create artificial trends;
• organizational incentives may encourage selective reporting;
• a root cause may be systemic and have several interacting contributors.

Best practice is to maintain a clear analytical record containing the question, metric definition, data sources, grain, filters, transformations, assumptions, comparison basis, findings, limitations, and recommended action. Reproducibility is not only a research virtue; it allows managers to challenge, update, and trust business analysis.

Chapter Summary

Descriptive analytics summarizes what has happened through aggregation, segmentation, statistical summaries, variance analysis, and trend analysis. Diagnostic analytics investigates why a pattern occurred by decomposing results, drilling into detailed records, comparing groups, testing hypotheses, and applying structured root-cause methods.

Reliable analysis begins with a clear metric definition and an understanding of data grain, measures, dimensions, additive properties, and quality limitations. Means, medians, weighted averages, dispersion measures, rates, variances, and growth measures each answer different questions and require appropriate denominators and assumptions. Trend analysis must account for seasonality, exposure, outliers, structural breaks, and changing data definitions.

Drill-through connects executive indicators to operational evidence. Five Whys, fishbone analysis, Pareto analysis, and comparison-based investigation help organize and test explanations, but none should be treated as proof without supporting evidence. The analyst’s professional responsibility is to distinguish observations from hypotheses, communicate uncertainty, protect privacy, assess fairness, and connect findings to measurable action.

Key Terms

Aggregation: Combining detailed records into a higher-level summary.

Analytics grain: The level of detail represented by one data row.

Descriptive analytics: Analysis that summarizes what has happened or is happening.

Diagnostic analytics: Analysis that investigates likely explanations for an observed result.

Dimension: A descriptive attribute used to group, filter, or classify data.

Drill-down: Moving from a summary view to progressively more detailed dimensions.

Drill-through: Opening underlying records or a detailed report supporting a summary value.

Fact table: A table containing measurable business events or observations.

Interquartile range: The difference between the third and first quartiles.

Mean: The arithmetic average of numeric observations.

Median: The middle value in an ordered set of observations.

Outlier: An observation unusually distant from the main pattern of data.

Pareto analysis: Ranking contributors to identify the categories responsible for most of an outcome.

Percentile: A value below which a specified percentage of observations falls.

Root cause: An underlying condition whose correction can prevent or materially reduce a problem.

Segmentation: Dividing a population into meaningful groups for analysis.

Variance: The difference between an observed value and a reference value, or a statistical measure of spread depending on context.

Weighted average: An average in which observations contribute according to specified weights.

Review Questions

Short Answer Questions

1. What is the difference between descriptive and diagnostic analytics?
2. Why is data grain important when aggregating a fact table?
3. When might the median be more useful than the mean?
4. What is the difference between absolute variance and percentage variance?
5. Why does correlation alone not establish causation?

Long Answer Questions

1. Explain how a BI dashboard can support the progression from “what happened?” to “why did it happen?”
2. Discuss the distinction between additive, semi-additive, and non-additive measures, using business examples.
3. Explain how seasonality, outliers, and structural breaks can mislead trend analysis.
4. Compare Five Whys, fishbone analysis, and Pareto analysis as root-cause methods.
5. Describe the governance and ethical controls required for responsible diagnostic analytics.

Analytical / Application Questions

1. A company’s customer complaints increased from 800 to 960 while orders increased from 40,000 to 60,000. Calculate the complaint rate per 1,000 orders for both periods and determine whether complaint incidence improved or worsened.
2. A region is $30,000 below budget, but it contains three product categories with different sales volumes. Design a diagnostic decomposition that would distinguish price, volume, product mix, and availability effects.
3. A dashboard shows a sudden 25% fall in service tickets after a new software release. Propose a step-by-step investigation that tests whether the fall represents improved service, reduced demand, a classification change, or a data-capture failure.

Multiple Choice Questions with Answers

Question 1

Which question is most directly addressed by descriptive analytics?

A. What happened to revenue last quarter?

B. Which intervention will maximize profit?

C. What is the causal effect of a price change?

D. Which staffing schedule is optimal?

Correct Answer: A. What happened to revenue last quarter?

Question 2

A sales table contains one row for each order line. Which risk is most important when calculating an order-level shipping cost?

A. Underestimating the number of product categories

B. Double-counting shipping cost across order lines

C. Losing the currency symbol

D. Converting a median into a percentile

Correct Answer: B. Double-counting shipping cost across order lines

Question 3

Actual cost is $92,000 and budgeted cost is $100,000. What is the percentage variance calculated as (Actual − Budget) ÷ Budget × 100?

A. −8%

B. 8%

C. −12.5%

D. 12.5%

Correct Answer: A. −8%

Question 4

Which measure is generally least affected by a single extremely large observation?

A. Arithmetic mean

B. Median

C. Range

D. Maximum

Correct Answer: B. Median

Question 5

Why should the numerator and denominator of a business rate refer to the same population and period?

A. To make the rate dimensionless and comparable

B. To guarantee that the rate is above zero

C. To eliminate all sampling error

D. To ensure that the mean equals the median

Correct Answer: A. To make the rate dimensionless and comparable

Question 6

A weighted average is most appropriate when:

A. All observations have identical importance

B. Observations have different exposure or importance

C. The data contains no numeric values

D. The analyst wants to remove all outliers

Correct Answer: B. Observations have different exposure or importance

Question 7

Which statement about a moving average is correct?

A. It proves that a trend is causal

B. It smooths short-term variation but may hide turning points

C. It can only be used with categorical data

D. It always predicts the next period accurately

Correct Answer: B. It smooths short-term variation but may hide turning points

Question 8

What is the primary purpose of drill-through in a BI report?

A. To replace data governance

B. To move from a summary result to supporting detailed records

C. To convert all measures into percentages

D. To remove the need for stakeholder consultation

Correct Answer: B. To move from a summary result to supporting detailed records

Question 9

In root-cause analysis, a cause listed in a fishbone diagram should be treated as:

A. A confirmed fact

B. A hypothesis requiring evidence

C. A statistically significant result

D. A substitute for operational data

Correct Answer: B. A hypothesis requiring evidence

Question 10

Which conclusion is most defensible from a correlation between stock-outs and lower repeat purchases?

A. Stock-outs are certainly the only cause of lower repeat purchases

B. The variables are associated, and further evidence is needed to assess causation

C. Repeat purchases cause stock-outs in every situation

D. The relationship is invalid because both variables are numeric

Correct Answer: B. The variables are associated, and further evidence is needed to assess causation