Forecasting Data and Data Preparation

Introduction

Demand forecasting is often presented as a modelling problem, but the quality of a forecast is usually determined earlier—by how demand data are defined, assembled, cleaned and interpreted. In logistics, an apparently simple observation such as “daily demand for product P at location L” may conceal several distinct realities: customer orders, shipments, invoices, replenishment requests, returns, cancelled orders, stockouts and promotional purchases. These events do not measure the same construct.

Forecasting data preparation therefore has two purposes. First, it creates a reliable analytical representation of the demand process. Second, it preserves information about exceptional events rather than silently deleting them. A forecast should estimate the demand that would be observed under a specified operating and market context; it should not merely reproduce errors in the transaction system.

This chapter develops a structured approach to acquiring, cleaning and exploring transactional, master, market and external data. Particular attention is given to missing values, outliers, stockouts, returns, calendar effects and data leakage.

Learning objectives

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

1. Distinguish transactional, master, market and external data used in demand forecasting.
2. Define the forecasting grain, target variable, time index and demand window.
3. Build a reproducible data-preparation pipeline.
4. Diagnose missing values, outliers, stockouts, returns and calendar effects.
5. Prevent temporal and target leakage.
6. Conduct exploratory demand analysis using appropriate measures and visualisations.
7. Evaluate data quality and document assumptions for governance and managerial use.

1. Demand as an analytical construct

A demand observation is not simply a number. It is a quantity associated with a product, location, time interval, customer segment and business context. A useful observation schema is:

The forecasting grain is the smallest combination of dimensions for which a forecast is required. For example, a retailer may forecast SKU–store–day, whereas a manufacturer may forecast product family–distribution centre–week. A mismatch between the data grain and the decision grain produces misleading results. Aggregating too early can hide stockouts and local seasonality; retaining an unnecessarily fine grain can create sparse, noisy series.

Let [[M:y_{i,t}]] denote the observed quantity for item-location combination [[M:i]] during period [[M:t]]. The target must be defined explicitly. Possible targets include:

• customer orders received;
• units shipped;
• units invoiced;
• point-of-sale consumption;
• delivered units net of returns; or
• estimated unconstrained demand.

These are not interchangeable. If a warehouse ships only 60 units because inventory was 60 but customers requested 100, shipments measure fulfilment, not necessarily demand.

2. Data sources and the data model

2.1 Transactional data

Transactional data record events such as orders, shipment lines, receipts, invoices, cancellations and returns. They generally contain event timestamps, quantities, prices, customer or channel identifiers, product identifiers and location identifiers. Transactional records are often the primary source for historical demand, but they can contain duplicate lines, late postings, amendments and inconsistent status codes.

A transaction table should preserve the event-level record. A separate aggregation process can produce the forecasting table. This separation supports auditability: analysts can trace an aggregate observation back to its source transactions.

2.2 Master data

Master data describe relatively stable entities:

• SKU attributes: category, pack size, unit of measure, lifecycle status and substitution group;
• location attributes: region, capacity, opening date and service territory;
• customer attributes: channel, segment and contractual terms;
• calendar attributes: fiscal period, holiday, trading day and week number.

Master-data changes are time-dependent. A product category or pack size recorded today may not have applied historically. Therefore, forecasting systems should use effective dates where possible. A historical transaction should be joined to the master-data version valid on its transaction date, rather than automatically receiving the current description.

2.3 Market and external data

Market data may include competitor prices, market share, industry volume, promotions and search indicators. External data may include weather, public holidays, school calendars, economic indicators, sporting events, port disruptions and fuel prices. Such variables are useful only when their timing, geographic coverage and causal relevance are understood.

A variable available after the forecast origin cannot be used as a predictor for that forecast, even if it explains the outcome very well retrospectively. This principle is central to leakage prevention.

2.4 A practical analytical structure

A robust forecasting dataset commonly contains one row per item-location-period and columns such as:

A complete panel includes periods with zero recorded demand where the item was available. Omitting those rows confuses “no transaction recorded” with “no demand.” However, a zero should not be inserted when the item was not listed, the location was closed or the system was unavailable.

Figure: A governed workflow for preparing demand data.

3. Data-quality dimensions and validation

Data quality is multidimensional. Accuracy asks whether a value represents reality; completeness asks whether required records exist; consistency asks whether values agree across systems; timeliness asks whether data arrive soon enough; validity asks whether values conform to permitted rules; and uniqueness asks whether events are duplicated.

Validation should occur at three levels:

1. Schema validation: data types, required fields, permitted codes and units of measure.
2. Relational validation: uniqueness of keys, valid item-location combinations and referential integrity with master data.
3. Business validation: non-negative shipped quantities, returns linked to prior sales where appropriate, shipment dates not preceding order dates, and realistic lead times.

Useful control measures include the missingness rate:

Here, “expected values” refers to required cells in the defined analytical population. The measure is meaningful only after the population and time window are specified. Missingness should also be reported by item, location, period and field; an overall percentage can conceal a serious problem in a particular region.

Duplicate detection should use the true business key, not an arbitrary row number. For an order-line table, the key might be order_id–line_id. Repeated extracts can create exact duplicates, while amended orders can create records that are similar but legitimately distinct. These cases require different treatment.

4. Missing values and zero demand

Missingness has different mechanisms and therefore different remedies.

• Structural missingness: a value does not apply, such as weather for a distant location.
• System missingness: a feed failed or a field was not captured.
• Operational missingness: an event was not recorded during a process disruption.
• Informative missingness: absence itself is related to demand, as when stock availability is missing during a disruption.

Missing quantity is not automatically zero demand. A period with no order record may indicate zero sales, stockout, store closure, assortment inactivity or system failure. The correct treatment depends on availability and process evidence.

Common treatments include deletion, constant imputation, interpolation, model-based imputation and explicit missing indicators. Interpolation may be acceptable for a slowly changing explanatory variable, but it is usually inappropriate for demand during a stockout. Imputation must be performed within the information available at the forecast origin; using future observations can create leakage.

For intermittent demand, the proportion of zero observations is informative:

This statistic describes observed demand, not necessarily customer need. If stockouts produce artificial zeros, [[M:p_0]] overstates intermittency.

5. Outliers, anomalies and exceptional demand

An outlier is an observation unusually distant from the prevailing pattern. It is not necessarily an error. A large order may be a valid project sale; a sharp fall may reflect a stockout; a sudden increase may be caused by a promotion or pre-buying before a price rise.

Detection methods include:

• rule-based limits, such as negative quantities or impossible dates;
• robust distributional methods, such as the interquartile range;
• time-series residual checks after accounting for trend and seasonality;
• comparisons with related products, locations or operational events.

The interquartile range is:

A conventional upper screening rule is [[M:x > Q_3 + 1.5 × IQR]], and the corresponding lower rule is [[M:x < Q_1 − 1.5 × IQR]]. Here [[M:Q_1]] and [[M:Q_3]] are the first and third quartiles. This is a screening convention, not proof of error; it is less sensitive to extreme values than a mean-and-standard-deviation rule but may flag legitimate seasonal peaks.

Appropriate actions are to retain and flag, correct using source evidence, winsorise, transform, or exclude with documented justification. Blind deletion is dangerous because it removes precisely the events that may matter for capacity and inventory planning.

6. Stockouts, lost sales and censored demand

Observed sales during limited availability are a censored measure of demand. Let [[M:d_t]] be customer demand and [[M:a_t]] be available inventory or fulfilment capacity. A simplified relationship is:

Here [[M:y_t]] is observed fulfilled quantity. This relationship assumes that all available units are allocated to the relevant demand and that availability is measured consistently. In practice, substitutions, backorders and allocation rules complicate the relationship.

Stockout identification should combine inventory balance, availability flags, replenishment records, lost-sales estimates, order cancellations and unusually short selling windows. A stockout flag should be retained as a feature of data quality and as an explanatory variable.

Possible analytical treatments are:

1. Exclude affected observations when estimating unconstrained demand, if sufficient unaffected data exist.
2. Treat demand as censored and estimate latent demand using an appropriate statistical model.
3. Use lost-sales or backorder records where available.
4. Model availability explicitly, distinguishing demand from the probability of fulfilment.

The selected approach must match the decision. A replenishment forecast needs expected customer demand, whereas a warehouse labour forecast may need expected shipped volume.

7. Returns, cancellations and negative quantities

Returns create negative transactions but do not necessarily represent negative demand. A return can relate to an earlier sale, a damaged item, a quality failure or a customer’s changed preference. Analysts should distinguish gross sales, returns and net fulfilled units:

This identity is valid when gross and returned units use the same item definition, unit of measure and accounting window. If returns occur weeks after sale, period netting can distort the timing of demand. A cohort view linking returns to the original sale is often more informative.

Cancellations should be classified by timing. A cancellation before fulfilment may indicate that the original order overstated realised shipments but still provides evidence of customer intent. A cancellation caused by stockout is different from a customer-initiated change of mind. These distinctions should be retained in status fields rather than collapsed into one negative adjustment.

8. Calendar, trend and commercial effects

Calendar effects include day-of-week patterns, month length, public holidays, moving holidays, school terms, pay cycles, fiscal periods and leap years. A raw monthly total is partly determined by the number of trading days. For a rate-based comparison, a simple trading-day adjustment is:

This measure assumes demand is reasonably comparable across the included days. It should not be used uncritically when weekends, holidays or promotions have structurally different demand.

Calendar variables should be generated from a controlled calendar table, with fields such as date, weekday, fiscal week, holiday name, holiday lead/lag indicators and open/closed status. Moving holidays require date-specific treatment. A holiday effect may occur before the holiday through pre-buying and after it through replenishment or delayed consumption.

Promotions and price changes also alter both the level and timing of demand. A promotion may create cannibalisation among related SKUs, forward buying, post-promotion dips and stockouts. The promotion flag should therefore be accompanied, where possible, by discount depth, display, media support, duration and affected substitute products.

9. Exploratory demand analysis

Exploratory analysis is not a decorative step. It tests whether the proposed target is plausible and identifies structures that should influence model design.

9.1 Time-series examination

Plot demand over time at several aggregation levels. Examine raw units, logarithmic or other variance-stabilising views where justified, and rates per trading day. Useful questions include:

• Is there a trend, level shift or product lifecycle pattern?
• Are there repeated weekly, monthly or annual cycles?
• Are peaks aligned with promotions or holidays?
• Are zeros genuine or availability-related?
• Do variance and mean increase together?

For a series [[M:y_1, y_2, …, y_T]], the sample mean is:

and the sample variance is:

The denominator [[M:T − 1]] applies when estimating population variability from a sample and when the mean is estimated from the same observations. Units of [[M:ȳ]] are demand units; units of [[M:s²]] are demand-units squared.

9.2 Segmentation and aggregation

Demand characteristics differ by ABC value, volume, intermittency, lifecycle, channel and geography. A high-value, low-volume spare part should not be assessed using the same diagnostics as a fast-moving grocery SKU. Aggregation can reveal stable patterns, but it can also hide substitution and local availability problems. Analyse both the decision level and relevant higher levels, then reconcile interpretations.

9.3 Dependence and seasonality

Autocorrelation measures association between observations separated by a lag. The sample autocorrelation at lag [[M:k]] is commonly expressed as:

This statistic is descriptive and assumes a consistently spaced series. It can be misleading when trend, changing variance, stockouts or calendar omissions are untreated. Seasonal interpretation should therefore combine plots, calendar knowledge and operational evidence.

9.4 Cross-sectional diagnostics

Use heat maps and grouped summaries by item, location, category and channel. Compare mean, median, standard deviation, zero proportion, number of active periods and stockout frequency. Relationships between variables should not be interpreted as causal solely because they correlate. For example, high sales and high inventory may both result from a promotion rather than inventory causing sales.

10. Data leakage and temporal integrity

Data leakage occurs when information unavailable at the forecast origin influences model training or evaluation. It produces optimistic performance and poor deployment results.

Common examples include:

• calculating an imputation using the full dataset, including future periods;
• using final monthly totals to predict an earlier day in that month;
• joining current product classifications to historical records;
• using a promotion outcome or post-event sales adjustment as a predictor;
• randomly splitting time-series observations so that future patterns enter training;
• scaling or selecting variables using the complete dataset before validation.

Define the forecast origin [[M:t_0]], horizon [[M:h]] and information set [[M:I_{t_0}]]. A valid predictor must be measurable or known within [[M:I_{t_0}]]. The target lies after the origin, for example [[M:y_{t_0+h}]]. Time-based validation uses earlier origins for training and later origins for testing. Rolling-origin evaluation is especially useful when demand regimes change.

A data-leakage audit should record each feature, its source timestamp, its publication delay, its transformation window and whether the transformation is refitted separately within each training fold. Feature lineage is both a technical control and a governance requirement.

11. An end-to-end preparation procedure

A professional workflow can be organised as follows:

1. Specify the decision: forecast purpose, horizon, frequency, service objective and required grain.
2. Define the target: orders, shipments, consumption, net units or estimated unconstrained demand.
3. Inventory sources: owners, systems, refresh rates, historical coverage and legal constraints.
4. Preserve raw data: create immutable extracts and record retrieval timestamps.
5. Standardise identifiers and units: resolve product aliases, pack sizes, currencies and calendars.
6. Validate structure: test keys, dates, duplicates, referential integrity and permitted values.
7. Construct the time panel: create expected item-location-period rows subject to assortment and opening rules.
8. Classify exceptions: missingness, stockouts, promotions, returns, cancellations and system outages.
9. Transform carefully: aggregate transactions, join time-valid master data and create lagged features without future information.
10. Explore: inspect distributions, time plots, seasonality, segments and anomalies.
11. Split by time: create leakage-resistant training, validation and test periods.
12. Document and monitor: retain data-quality metrics, decisions, version numbers and unresolved limitations.

Automation is valuable, but automated deletion or imputation without exception reporting is not governance. Every transformation should be reproducible and explainable.

Illustrative case: forecasting replenishment demand for a regional retailer

Background

A fictional retailer, Northstar Home, operates 40 stores and two distribution centres. It wants weekly forecasts for 8,000 active SKU–store combinations to set replenishment quantities.

Problem

The initial analysis showed a large number of zero-sales weeks and apparently erratic demand. A model trained on shipment data produced good historical fit but systematically underforecasted popular products during periods of low inventory.

Analysis

The team defined the decision grain as SKU–store–week and audited three years of order, shipment, inventory and promotion records. It found that:

• some stores were closed on selected dates but retained in the panel;
• product pack-size changes created apparent jumps in quantity;
• returns were posted in the week received rather than the week sold;
• 14% of high-volume SKU–store weeks had an end-of-week stockout flag;
• a current product hierarchy had been joined to older transactions;
• the original random train–test split allowed later promotional patterns into training.

The team distinguished shipped units from estimated customer demand. It also created fields for open days, stockout exposure, promotion status, pack size and product-hierarchy validity dates.

Approach and solution

Raw transactions were retained, duplicates were resolved using event keys, and quantities were converted to a base unit. The weekly panel included zero demand only when the store was open, the SKU was ranged and the system feed was complete. Stockout weeks were flagged rather than automatically treated as ordinary zeros. Returns were linked to original sales where possible and reported separately.

The evaluation used rolling time splits. Calendar variables were generated from a maintained calendar table, and all imputations and scaling parameters were fitted within each training window. Exploratory plots were reviewed jointly by analysts, store operations and inventory planners.

Outcome

The revised dataset revealed that much of the apparent intermittency was caused by assortment and availability changes. Forecast bias for high-volume items declined in back-testing, and planners gained a separate view of expected demand versus expected shipments. The organisation also introduced a data-quality dashboard reporting feed completeness, stockout coverage, unresolved product mappings and late-arriving transactions.

Learning points

The central improvement did not come from selecting a more complex algorithm. It came from defining the target correctly, preserving operational context, using time-valid master data and preventing leakage. Data preparation changed both forecast accuracy and the credibility of the planning process.

Challenges, governance and contemporary practice

Data preparation involves ethical and governance responsibilities. Customer-level data should be minimised, access-controlled and retained only as long as justified. Location and customer attributes may create unfair allocation outcomes if used uncritically. Forecasts should not become a mechanism for systematically reducing service to particular regions without review.

Important controls include a data dictionary, ownership matrix, lineage records, access permissions, versioned transformations, exception logs and approval of material changes. Organisations should distinguish an operational forecast from an experimental estimate and communicate uncertainty and known blind spots.

Modern data platforms increasingly support automated quality tests, metadata catalogues, feature stores and real-time event processing. Machine-learning systems can detect anomalies and infer missing values, but they can also encode leakage or learn operational artefacts. Human review remains necessary for stockouts, product substitutions, structural breaks and unusual events. Generative AI may assist documentation and query construction, but its outputs require validation against governed source data.

Best-practice checklist

• Define demand and the forecast grain before extracting data.
• Keep raw, cleaned and analytical layers separate.
• Treat zero, missing, cancelled, returned and stockout quantities as distinct states.
• Use effective dates for changing master data.
• Validate units of measure and pack-size conversions.
• Flag unusual observations before deciding whether to alter them.
• Build calendar and availability variables from authoritative sources.
• Fit imputations, transformations and feature-selection steps only on training data.
• Use time-based validation and record publication delays.
• Report data-quality limitations alongside forecast results.
• Make every major transformation reproducible and auditable.

Chapter Summary

Forecasting data preparation converts heterogeneous operational records into a time-valid representation of demand. The process begins with a clear decision grain and target definition, because orders, shipments, consumption, returns and unconstrained demand answer different managerial questions. Transactional data provide event evidence; master data provide context; market and external data provide potential drivers, subject to timing and relevance.

Reliable preparation requires schema, relational and business validation; careful treatment of missing values and zeros; diagnosis rather than automatic deletion of outliers; explicit recognition of stockouts as censored demand; separation of returns and cancellations; and calendar-aware analysis. Exploratory demand analysis should examine distributions, trends, intermittency, seasonality, cross-sectional differences and operational explanations.

Temporal integrity is essential. Leakage arises whenever future or post-outcome information enters training, feature construction or validation. Time-based splits, source timestamps, effective-dated joins and transformation lineage are therefore fundamental controls. Good data preparation improves not only forecast accuracy but also interpretability, governance, inventory decisions and confidence in the planning system.

Key Terms

Demand: Customer requirement for a product or service during a specified period.

Forecasting grain: The product, location, time and other dimensions at which a forecast is produced.

Transactional data: Records of operational events such as orders, shipments, invoices and returns.

Master data: Descriptive and reference information about products, locations, customers and calendars.

External data: Information originating outside the core transaction system, such as weather or economic indicators.

Complete panel: A dataset containing expected item-location-period rows under defined assortment and operating rules.

Missingness: Absence of a recorded value where a value may be expected.

Structural missingness: Missingness caused by a value being inapplicable.

Outlier: An observation unusually distant from the prevailing pattern.

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

Stockout: A condition in which requested demand cannot be fulfilled because inventory or capacity is unavailable.

Censored demand: Demand observed only up to a limit imposed by availability or capacity.

Return: Product received back after an earlier sale or shipment.

Calendar effect: Systematic demand variation associated with dates, holidays or trading days.

Intermittent demand: Demand containing frequent zero observations.

Data leakage: Use of information unavailable at the forecast origin.

Forecast origin: The time at which available information is frozen to generate a forecast.

Rolling-origin evaluation: Repeated time-based training and testing using successive forecast origins.

Data lineage: A record of where data came from and how it was transformed.

Unconstrained demand: Demand estimated as if inventory or fulfilment limitations had not restricted sales.

Review Questions

Short Answer Questions

1. Why must the forecasting grain be defined before aggregating transactions?
2. Distinguish a genuine zero-demand period from a missing observation.
3. Why is a stockout observation not necessarily a valid measure of demand?
4. State two reasons why returns should be analysed separately from sales.
5. What is temporal data leakage?

Long Answer Questions

1. Explain how transactional, master, market and external data contribute to a logistics forecasting system.
2. Discuss alternative treatments for missing values and the conditions under which each may be appropriate.
3. Explain how stockouts, cancellations and returns can distort a demand target.
4. Describe a complete data-quality and exploratory-analysis workflow for SKU–location–week data.
5. Discuss the governance requirements for using machine learning in demand-data preparation.

Analytical / Application Questions

1. A store reports no sales for a product for three weeks. The store was open during week 1, closed during week 2, and had zero inventory during week 3. Explain how each week should be represented in an analytical panel and why.
2. A product’s weekly units are 100, 105, 98, 102 and 500. The 500-unit observation was caused by a documented promotion. Should it be deleted, winsorised or retained? Justify your answer and identify additional features that should be recorded.
3. A forecasting team randomly splits three years of data into training and test sets and uses a promotion variable recorded after each campaign ends. Identify the leakage risks and propose a corrected evaluation design.

Multiple Choice Questions with Answers

Question 1

Which observation best represents unconstrained customer demand?

A. Units shipped when inventory was insufficient

B. Units requested, adjusted using reliable stockout information

C. Units returned during the month

D. Units invoiced after cancellations

Correct Answer: B. Units requested, adjusted using reliable stockout information

Question 2

What is the main purpose of a complete item-location-period panel?

A. To remove all zero-demand periods

B. To ensure every row contains a promotion

C. To distinguish no transaction from an omitted period

D. To replace transactional records permanently

Correct Answer: C. To distinguish no transaction from an omitted period

Question 3

Which is an example of structural missingness?

A. A weather value missing because the feed failed

B. A product category missing from a valid master record

C. A weather variable not applicable to a location outside its coverage

D. A quantity omitted during a system outage

Correct Answer: C. A weather variable not applicable to a location outside its coverage

Question 4

For values 10, 12, 13, 15 and 40, what is the conventional upper outlier screening limit using quartiles defined by the median-excluded method?

A. 18

B. 20

C. 22

D. 25

Correct Answer: C. 22

Question 5

Why should a documented promotional peak usually be retained and flagged?

A. It is always a data-entry error

B. It may contain information relevant to future promotional demand

C. It proves that all future demand will be identical

D. It eliminates the need for calendar variables

Correct Answer: B. It may contain information relevant to future promotional demand

Question 6

Which quantity identity is appropriate when gross and returned units use the same basis and period?

A. net_units = returned_units − gross_units

B. net_units = gross_units + returned_units

C. net_units = gross_units − returned_units

D. net_units = gross_units ÷ returned_units

Correct Answer: C. net_units = gross_units − returned_units

Question 7

Which action most directly prevents temporal leakage during imputation?

A. Using the full dataset to calculate the replacement value

B. Fitting the imputation using only information available in each training window

C. Randomly shuffling all observations before imputation

D. Replacing every missing value with zero

Correct Answer: B. Fitting the imputation using only information available in each training window

Question 8

What does a stockout most directly imply about observed shipments?

A. They necessarily equal latent customer demand

B. They may be a censored measure of demand

C. They are always negative

D. They cannot be used for any operational purpose

Correct Answer: B. They may be a censored measure of demand

Question 9

Which validation approach is most appropriate for evaluating a time-series forecasting model?

A. Random row-level splitting

B. Splitting by product name alphabetically

C. Time-based or rolling-origin evaluation

D. Testing only on observations used for training

Correct Answer: C. Time-based or rolling-origin evaluation

Question 10

Why are effective dates important when joining master data?

A. They ensure historical transactions receive the historically valid attributes

B. They remove the need for product identifiers

C. They convert all quantities into monetary values

D. They guarantee that forecasts have no uncertainty

Correct Answer: A. They ensure historical transactions receive the historically valid attributes