Exploratory Data Analysis

Introduction and context

Exploratory Data Analysis (EDA) is the systematic examination of data before formal modelling, reporting, or decision-making. In business intelligence (BI), EDA converts raw records into an initial understanding of performance, variation, relationships, exceptions, and possible explanations. It is not merely the production of charts. It combines descriptive statistics, filtering, segmentation, aggregation, trend analysis, and outlier investigation with professional judgement.

EDA serves two complementary purposes. First, it is descriptive: it answers questions such as what happened, where, when, and for whom. Second, it is diagnostic and investigative: it identifies patterns that require explanation and tests whether apparent insights are robust. A dashboard may show that sales increased, but EDA asks whether the increase resulted from higher prices, more units, a particular region, a promotional campaign, or a change in data capture.

EDA should precede strong causal claims. A relationship observed in a dataset is not automatically a cause-and-effect relationship. The analyst must also consider data quality, measurement definitions, sampling, time periods, and alternative explanations.

Learning objectives

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

1. Explain the purpose and workflow of EDA in business analytics.
2. Select and interpret suitable descriptive statistics.
3. Filter, segment, and aggregate data to answer business questions.
4. Analyse trends while distinguishing nominal, real, seasonal, and anomalous change.
5. Detect and investigate outliers using appropriate methods.
6. Validate insights through comparisons, reconciliation, and sensitivity checks.
7. Communicate findings responsibly in BI reports and dashboards.

The EDA mindset and workflow

A useful EDA workflow is iterative rather than strictly linear:

1. Define the decision problem. Identify the business decision, unit of analysis, population, time window, and key measures.
2. Inspect structure and quality. Review fields, data types, missing values, duplicates, invalid values, and the grain of each table.
3. Establish a baseline. Calculate totals, counts, rates, distributions, and time summaries.
4. Segment and compare. Examine differences by product, customer, channel, geography, cohort, or other relevant dimensions.
5. Investigate trends and relationships. Use time series, cross-tabulations, scatterplots, and carefully selected metrics.
6. Identify exceptions. Investigate outliers, sudden changes, gaps, and inconsistent records.
7. Validate. Reconcile totals to trusted sources, test alternative definitions, and check whether conclusions persist under reasonable changes.
8. Communicate and document. Present the finding, evidence, limitations, and recommended action.

The grain of a dataset is the level represented by one row. For example, one row may represent an order, an order line, a daily store total, or a customer-month. Confusing these grains can produce serious errors. Summing an order total after joining it to several order-line records may count the same order repeatedly.

Descriptive statistics for business data

Descriptive statistics summarise a dataset without claiming that it represents a wider population. Let observations be x₁, x₂, ..., x_n, where n is the number of observations.

Measures of centre

The arithmetic mean is:

Here, x̄ is the mean, x_i is observation i, and n is the number of valid observations. The mean is appropriate for numerical data when values are meaningfully additive, but it is sensitive to extreme values.

The median is the middle ordered observation. If n is odd, it is the value at position (n+1)/2. If n is even, it is commonly the average of the two middle observations. The median is often more representative than the mean for skewed measures such as income, delivery time, or transaction value.

The mode is the most frequently occurring value. It is useful for categorical variables, such as the most common payment method, and may be non-unique.

Measures of spread

The range is max(x_i) − min(x_i). It is simple but depends entirely on two observations.

The population variance is:

The sample variance is:

The population formula applies when the complete population of interest is available; the sample formula, with denominator n−1, is commonly used when estimating population variability from a sample. The corresponding standard deviation is σ = √σ² for a population or s = √s² for a sample. Standard deviation has the same units as the original measure.

Percentiles divide ordered data into relative positions. The interquartile range (IQR) is:

where Q₁ is the 25th percentile and Q₃ is the 75th percentile. Percentiles are valuable for service-level analysis: the 90th percentile delivery time describes a threshold that 90% of observed deliveries do not exceed, subject to the chosen percentile convention.

The coefficient of variation is:

It is dimensionless and can compare relative variability across positive measures with meaningful zero points. It is unsuitable when the mean is zero or close to zero, and comparisons are problematic when values can be negative.

Worked example: order values

Suppose five order values are $40, $45, $50, $55, and $110. The mean is:

x̄ = (40 + 45 + 50 + 55 + 110) / 5 = $60

The median is $50. The mean is higher because the $110 order pulls it upward. The range is $70. For this small, skewed dataset, the median better represents a typical order, while the high value deserves investigation rather than automatic removal.

Rates, ratios, and denominators

A percentage must have a clear denominator. A conversion rate may be defined as:

The denominator must match the business question and time period. Averaging daily conversion percentages can be misleading when daily visitor counts differ. A weighted overall rate is generally:

This distinction is essential in BI because a small segment can have a high percentage but little business impact.

Filtering, segmentation, and aggregation

Filtering selects records that satisfy conditions, such as orders from the United Kingdom, transactions during the current quarter, or customers with at least two purchases. Filters should be explicit about inclusivity, missing values, and time zones. A filter on “January” must specify the year and whether timestamps are converted to local business time.

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

• geography and store;
• customer type, tenure, or value band;
• product category and price tier;
• acquisition channel;
• order status and fulfilment method;
• cohort, such as customers acquired in the same month.

Segments should be defined before viewing outcomes where possible. Otherwise, analysts may search many possible segments until finding a favourable result, increasing the risk of false discoveries. Segments must also be large enough for stable interpretation and should be governed when they involve sensitive attributes.

Aggregation summarises records by a dimension. Typical functions include count, distinct count, sum, mean, median, minimum, maximum, and percentile. The correct aggregation depends on the measure:

A drill-down moves from a total to lower-level detail; a roll-up moves from detail to a higher-level summary. Both require consistent definitions and a known data grain.

Simpson’s paradox and misleading aggregation

An aggregate trend can contradict every segment trend. This is known as Simpson’s paradox. For example, total conversion may fall because traffic shifted from a high-converting channel to a low-converting channel, even if conversion improved within both channels. Analysts should compare both the overall result and its composition by relevant segments.

Trend analysis

Trend analysis studies how a measure changes over time. Time should be treated as an ordered variable, and the chosen time grain—hour, day, week, month, or quarter—should reflect the decision context.

A simple absolute change between periods is:

A percentage change, when the prior value is non-zero, is:

For positive business measures, the absolute-value notation is often unnecessary, but it makes the denominator convention explicit when negative values may occur. Percentage change can be unstable when the baseline is close to zero.

Year-over-year growth compares the same period in different years and can reduce seasonal distortion. Month-over-month growth is more responsive but may be affected by seasonality, different month lengths, promotions, or calendar effects. A moving average smooths short-term variation. For a simple k-period moving average:

This is descriptive smoothing, not proof of a causal trend. It also reduces the visibility of sudden changes and is less suitable near the beginning of a series.

When financial values span long periods, nominal growth may overstate real growth because of inflation. Analysts should state whether values are nominal or inflation-adjusted. Likewise, a sales trend can reflect changes in price, volume, product mix, store count, or reporting coverage.

A responsible trend review asks:

• Is the change statistically or commercially meaningful?
• Is it persistent or a single-period event?
• Is the comparison like-for-like?
• Are there missing periods or changes in data capture?
• Did the composition of customers, products, or channels change?
• Is the measure a count, rate, stock, or flow?

Outlier detection and investigation

An outlier is an observation unusually distant from the rest of a dataset. It may indicate a data error, a rare but valid event, fraud, a process change, or a previously unseen business opportunity. Detection is not the same as deletion.

IQR rule

A common robust rule identifies low and high fences:

Values outside these fences are potential outliers. The 1.5 multiplier is a convention, not a universal law. It is most useful for continuous distributions and should be applied with an appropriate percentile method.

Standard-score rule

For approximately symmetric data, a standard score is:

where x_i is an observation, x̄ is the sample mean, and s is the sample standard deviation. A large absolute value, often above 3 as a screening convention, may signal an unusual observation. This rule is less reliable for highly skewed data because the mean and standard deviation themselves can be distorted.

Worked outlier example

For order values 40, 45, 50, 55, and 110, using the median-of-halves convention, Q₁ = 42.5 and Q₃ = 82.5, so IQR = 40. The upper fence is 82.5 + 1.5 × 40 = 142.5. Therefore, $110 is not an outlier under this particular convention, although it remains a high-value observation. Different percentile algorithms may produce different results for very small datasets. This illustrates why an automated flag should initiate review rather than determine treatment.

Investigation protocol

For each flagged record, check:

1. whether the value is valid in the source system;
2. whether units, currency, decimal placement, and dates are correct;
3. whether the record is duplicated or joined incorrectly;
4. whether the event is a legitimate exceptional transaction;
5. whether the outlier is concentrated in a segment or time period;
6. whether excluding, winsorising, transforming, or retaining it changes the conclusion.

Winsorisation replaces extreme values with selected percentile limits; transformation changes the scale, for example with a logarithm for positive, heavily skewed values. These methods can support modelling but should not conceal operationally important events in management reporting. Any treatment must be documented.

Validating business insights

A credible insight contains a claim, evidence, scope, and limitation. Validation can include:

• Reconciliation: compare totals with the general ledger, source system, or approved KPI definition.
• Definition checks: verify numerator, denominator, time zone, currency, and inclusion rules.
• Triangulation: examine the result using another measure or data source.
• Segment consistency: determine whether the pattern holds across key groups.
• Sensitivity analysis: test reasonable alternative filters, outlier treatments, and time windows.
• Temporal validation: confirm that a pattern is not caused by a one-time event or incomplete period.
• Data-quality review: inspect missingness, duplicates, and changes in pipelines.

A useful distinction is between data validity and business validity. Data validity asks whether the records are technically accurate and complete. Business validity asks whether the metric represents the intended concept and supports the decision. A perfectly calculated metric can still be a poor KPI if its definition is inappropriate.

Case study: investigating declining online conversion

The following is an illustrative case.

Background

A retailer’s dashboard showed that online conversion fell from 3.2% in March to 2.6% in April. Management considered reducing digital advertising.

Problem

The headline rate did not explain whether customer demand had weakened, traffic quality had changed, or the checkout process had deteriorated. The analyst also needed to ensure that the rates used the same denominator.

Analysis

The analyst first confirmed that conversion meant completed orders divided by qualified sessions, excluded test traffic, and used the same time zone. The data was then segmented by device, acquisition channel, and new versus returning visitor.

The decline was concentrated among mobile and new visitors. A time series showed that the fall began immediately after a mobile checkout release. Error logs showed an increase in payment failures for one browser version. The analyst also calculated the overall rate from summed successes and sessions rather than averaging segment percentages.

Approach and solution

The retailer paused the planned advertising reduction and assigned the checkout team to reproduce the browser-specific failure. A temporary dashboard annotation identified the release date. After the payment error was corrected, the analyst monitored conversion by browser, device, and visitor type, while retaining the pre-release baseline.

Outcome

The evidence suggested a technical checkout problem rather than a general fall in demand. Conversion recovered over the following weeks. The company avoided cutting effective advertising and introduced release monitoring for critical funnel steps.

Learning points

The case demonstrates that aggregation can conceal operational causes, that a rate requires a stable denominator, and that temporal alignment between an event and a metric is useful evidence but not by itself proof of causation. Investigation should combine KPI analysis, segmentation, system logs, and validation.

BI tools, automation, and responsible use of AI

Spreadsheets, SQL, dashboard platforms, and statistical programming environments can all support EDA. Tool choice matters less than reproducible definitions and traceable transformations. A professional workflow preserves query logic, source versions, refresh dates, and metric documentation.

AI-assisted analytics can suggest segments, identify unusual values, generate summaries, or produce draft visualisations. However, AI output requires human verification because it may infer incorrect relationships, overlook missing data, expose confidential information, or present correlation as causation. Analysts should not upload personally identifiable or commercially sensitive data to an unapproved service. Automated anomaly detection should provide explanations, thresholds, and links to source records rather than unexplained alerts.

Ethical, governance, and practical limitations

EDA can create risks when analysts use sensitive attributes, infer personal characteristics, or compare groups without sufficient context. Important safeguards include data minimisation, access control, purpose limitation, de-identification where appropriate, and compliance with applicable privacy and sector regulations. Small segments can enable re-identification even when names are removed.

Other limitations include selection bias, survivorship bias, missing-not-at-random data, changing definitions, measurement error, and multiple comparisons. If an analyst examines hundreds of segments, some will appear unusual by chance. Findings should therefore be treated as hypotheses requiring confirmation, especially before high-impact decisions involving employment, credit, pricing, or access to services.

Best practices for professional EDA

1. Begin with a decision and a metric definition, not a chart.
2. Record dataset grain, filters, refresh date, and assumptions.
3. Use medians and percentiles for skewed operational measures.
4. Use weighted rates when denominators differ.
5. Compare totals with relevant segments to detect composition effects.
6. Label incomplete periods, estimates, and provisional data.
7. Investigate outliers before excluding them.
8. Separate descriptive evidence from causal interpretation.
9. Show uncertainty and limitations in the dashboard narrative.
10. Make analyses reproducible and obtain review for sensitive decisions.

Chapter Summary

Exploratory Data Analysis is an iterative process for understanding and validating business data before decisions or formal modelling. It combines data-quality inspection with descriptive statistics, filtering, segmentation, aggregation, trend analysis, and outlier investigation. Means, medians, percentiles, variance, standard deviation, rates, and percentage changes each answer different questions and have different assumptions. Correct denominators and dataset grain are essential to avoid misleading results. Trends must be interpreted in relation to seasonality, composition, price, volume, and data-collection changes. Outlier rules such as the IQR and standard-score methods identify candidates for investigation; they do not justify automatic deletion. Reliable BI insights are reconciled, segmented, sensitivity-tested, documented, and communicated with appropriate ethical and governance safeguards.

Key Terms

Exploratory Data Analysis: Systematic examination of data to discover patterns, anomalies, and relationships.

Dataset grain: The level of detail represented by one row.

Descriptive statistic: A numerical summary of observed data.

Mean: Arithmetic average of numerical observations.

Median: Middle value of ordered observations.

Percentile: Value below which a specified percentage of observations fall.

Variance: Average squared deviation, using a population or sample definition.

Standard deviation: Square root of variance, expressed in the original units.

IQR: Difference between the third and first quartiles.

Rate: A measure of events relative to a defined denominator.

Filtering: Selecting records that satisfy specified conditions.

Segmentation: Dividing data into meaningful groups for comparison.

Aggregation: Summarising records using functions such as sum or count.

Drill-down: Moving from a summary to more detailed data.

Trend analysis: Examination of change over an ordered time period.

Seasonality: Repeating variation associated with calendar or time cycles.

Outlier: An unusually distant observation.

Winsorisation: Replacing extreme values with selected boundary values.

Sensitivity analysis: Testing whether conclusions change under reasonable alternatives.

Simpson’s paradox: A reversal between aggregate and subgroup relationships caused by composition effects.

Review Questions

Short Answer Questions

1. What is the purpose of Exploratory Data Analysis in BI?
2. Why can the median be preferable to the mean for skewed data?
3. What is meant by the grain of a dataset?
4. Why must a conversion rate have a clearly defined denominator?
5. Why should an outlier not be deleted automatically?

Long Answer Questions

1. Explain an end-to-end EDA workflow, from defining a business question to communicating a validated insight.
2. Compare mean, median, percentiles, variance, and standard deviation, including situations in which each is useful.
3. Explain how filtering, segmentation, and aggregation can reveal or conceal business performance.
4. Discuss the differences between month-over-month and year-over-year trend analysis, including seasonal and data-quality risks.
5. Describe the IQR and standard-score approaches to outlier detection and explain their limitations.

Analytical / Application Questions

1. A company reports that average delivery time improved from 5.0 to 4.5 days, but customer complaints increased. Design an EDA investigation using distributions, segments, trends, and data-quality checks.
2. A marketing dashboard reports a 10% conversion rate for a small campaign and 6% for a large campaign. Explain what additional counts and calculations are needed before recommending more investment in the small campaign.
3. An analyst finds that sales rose after a pricing change. Propose a validation plan that separates price, volume, product mix, seasonality, customer composition, and possible data-pipeline effects.

Multiple Choice Questions with Answers

Question 1

Which measure is generally most resistant to a small number of extreme values?

A. Mean

B. Median

C. Range

D. Variance

Correct Answer: B. Median

Question 2

A conversion rate should usually be calculated as:

A. The average of all daily percentages

B. Total opportunities divided by total successes

C. Total successes divided by total opportunities

D. The largest segment percentage

Correct Answer: C. Total successes divided by total opportunities

Question 3

What does dataset grain describe?

A. The colour scheme of a dashboard

B. The level of detail represented by one row

C. The number of columns in a table

D. The refresh frequency of a report

Correct Answer: B. The level of detail represented by one row

Question 4

If Q₁ = 20 and Q₃ = 35, what is the IQR?

A. 15

B. 20

C. 35

D. 55

Correct Answer: A. 15

Question 5

Which action is most appropriate immediately after an outlier is detected?

A. Delete it automatically

B. Replace it with the mean without documentation

C. Investigate whether it is an error or a valid exceptional event

D. Ignore all other records

Correct Answer: C. Investigate whether it is an error or a valid exceptional event

Question 6

Why can averaging subgroup conversion percentages be misleading?

A. Percentages cannot be displayed in dashboards

B. Subgroups may have different denominator sizes

C. Conversion rates always have negative values

D. Aggregation removes all seasonality

Correct Answer: B. Subgroups may have different denominator sizes

Question 7

Which comparison is usually most useful for reducing ordinary annual seasonality?

A. Day-over-day

B. Hour-over-hour

C. Month-over-month only

D. Year-over-year

Correct Answer: D. Year-over-year

Question 8

A standard score measures an observation’s distance from the mean in units of:

A. Currency

B. The median

C. Standard deviation

D. The range

Correct Answer: C. Standard deviation

Question 9

Which issue can produce a misleading aggregate result even when subgroup results move in the opposite direction?

A. Simpson’s paradox

B. Data encryption

C. Currency formatting

D. A valid primary key

Correct Answer: A. Simpson’s paradox

Question 10

Which statement best describes responsible use of AI in EDA?

A. AI findings require no validation if the chart looks plausible

B. AI can replace metric definitions and governance

C. AI suggestions should be checked against data quality, privacy, and business context

D. Sensitive data should always be uploaded to public AI tools

Correct Answer: C. AI suggestions should be checked against data quality, privacy, and business context