Data Acquisition, Preparation and Feature Engineering

Introduction

Predictive models learn from the data presented to them. Consequently, model performance depends not only on the choice of algorithm but also on whether the underlying dataset is relevant, accurate, properly structured, and available at the time a prediction is made. Data acquisition, preparation, and feature engineering form the operational bridge between a business question and a reliable predictive system.

A raw dataset is rarely analysis-ready. It may contain duplicate records, inconsistent units, missing values, invalid dates, extreme observations, categorical labels, free text, and variables that were created after the event being predicted. A carefully prepared dataset addresses these issues while preserving the meaning of the original information. Poor preparation can produce biased estimates, misleading validation results, unfair decisions, and models that fail in production.

This chapter presents a practical workflow using SQL, spreadsheets, Python, or R. It emphasizes a central principle: every transformation must be justified by the prediction task, the information available at prediction time, and the intended use of the model.

Learning Objectives

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

1. Translate a business question into a suitable analytical dataset.
2. Acquire and combine data using relational queries, spreadsheets, and programming tools.
3. Diagnose and correct common data-quality problems.
4. Select appropriate strategies for missing values, outliers, categorical variables, and numerical scaling.
5. Detect target leakage and design time-aware data partitions.
6. Construct useful features from structured, temporal, text, and transactional data.
7. Address class imbalance and evaluate the consequences of preparation choices.
8. Document data lineage, assumptions, governance, and reproducibility.

1. From Business Question to Analytical Dataset

A predictive project begins with a precise prediction statement. It should specify:

• the unit of analysis, such as customer, transaction, account, or day;
• the target variable, such as churn within 30 days;
• the prediction time, when the forecast is generated;
• the outcome window, the period during which the outcome is observed; and
• the permitted information set, the data available at prediction time.

For example, “predict whether a customer will cancel within the next 30 days using information available at the end of today” is substantially clearer than “predict churn.” The first statement determines how records are assembled and prevents the use of future information.

A useful analytical table normally has one row per prediction unit and columns for the target, predictors, identifiers, timestamps, and metadata. Identifiers are useful for joining and auditing but are not automatically suitable predictors. A customer ID may cause memorization rather than generalization.

Data acquisition sources

Common sources include operational databases, data warehouses, application programming interfaces (APIs), spreadsheets, survey systems, sensors, web logs, and document repositories. Each source has different reliability and access characteristics.

Data acquisition should be reproducible. Record the source, extraction date, query or file version, filters, time zone, and responsible owner. A downloaded file without provenance is difficult to defend or refresh.

Relational preparation with SQL

Suppose a retailer wants one row per customer containing recent spending and a churn label. A conceptual SQL query might be:

SELECT
    c.customer_id,
    c.segment,
    COUNT(o.order_id) AS orders_90d,
    COALESCE(SUM(o.amount), 0) AS spend_90d,
    CASE
        WHEN EXISTS (
            SELECT 1 FROM orders o2
            WHERE o2.customer_id = c.customer_id
              AND o2.order_date > DATE '2025-03-31'
              AND o2.order_date <= DATE '2025-04-30'
        ) THEN 0 ELSE 1
    END AS churn_30d
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
   AND o.order_date > DATE '2025-01-01'
   AND o.order_date <= DATE '2025-03-31'
GROUP BY c.customer_id, c.segment;

The exact syntax varies by database. The important design decisions are the left join, the historical observation window, and the separation of predictor and outcome windows. A careless join can create multiple rows per customer and thereby violate the unit-of-analysis requirement.

2. Data Quality as Measurement and Governance

Data quality is multidimensional. A dataset may be complete but inaccurate, or accurate but too late for operational use.

Profiling and validation

Profiling should occur before transformation. Useful checks include row counts, distinct counts, minimum and maximum values, frequency tables, missingness rates, duplicate keys, date ranges, and referential integrity.

For a variable X with n records, its missingness rate is:

Here, m_X is dimensionless and ranges from 0 to 1. A high missingness rate does not automatically justify deletion; the meaning of missingness must be investigated. “Not applicable,” “not asked,” and “system failure” are different conditions.

Duplicates require a definition. Exact duplicate rows may be removed, but repeated transactions may be legitimate. A customer appearing twice in a customer master table may indicate a data-integrity problem, whereas two purchases by the same customer should not be collapsed.

Validation rules should be explicit. Examples include “quantity must be a non-negative integer,” “transaction date cannot precede account creation,” and “country code must belong to the approved reference list.” Invalid values should be quarantined or corrected using documented rules rather than silently discarded.

3. Missing Values

Missing data can arise through nonresponse, sensor failure, optional fields, system migration, or business processes. Three conceptual mechanisms are commonly distinguished:

• Missing completely at random (MCAR): missingness is unrelated to observed and unobserved values.
• Missing at random (MAR): missingness depends on observed variables after those variables are considered.
• Missing not at random (MNAR): missingness depends on the unobserved value or another unobserved process.

These categories are assumptions about a data-generating process, not labels that can always be proven from the dataset.

Strategies

1. Deletion. Remove rows or variables when missingness is small, the removal is defensible, and the remaining sample remains representative. Complete-case deletion can introduce bias.
2. Simple imputation. Replace numerical values with a training-set mean or median, and categorical values with the mode or an explicit “unknown” category. The median is less affected by extreme values.
3. Group-based imputation. Use a meaningful segment, such as region or product category, when group differences are substantively justified.
4. Model-based imputation. Predict missing values from other variables. This may preserve relationships but can introduce model dependence and false precision.
5. Missingness indicators. Add a binary variable M where M = 1 if the original value was missing and M = 0 otherwise, when the fact of missingness may itself be informative.

All imputation parameters must be learned from the training data only. If the full dataset mean is used before splitting, information from the validation or test set enters the training process. In Python, a pipeline should combine the imputer, encoder, scaler, and estimator so that fitting is controlled.

4. Outliers and Anomalous Observations

An outlier is an observation unusual relative to a chosen reference distribution or business context. It is not necessarily an error. A high-value purchase may be a legitimate transaction, while a negative age is almost certainly invalid.

The interquartile range is:

where Q₁ and Q₃ are the first and third quartiles. A conventional rule flags values below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a screening rule, not a universal deletion rule. It is most useful for continuous variables and can be misleading for skewed or multimodal distributions.

Other approaches include domain limits, robust statistics, logarithmic transformation, winsorization, separate anomaly flags, and models using robust loss functions. Before changing an extreme value, identify whether it is a data error, a rare but valid case, or a signal of a different population. Removing legitimate high-risk cases may reduce the model’s usefulness precisely where decisions matter most.

5. Encoding and Scaling Variables

Categorical variables

Nominal categories have no natural order. One-hot encoding represents a variable with k categories using indicator variables. For a category j:

With an intercept in a linear regression, one category is commonly omitted to avoid perfect multicollinearity. Tree-based methods can sometimes use ordinal or native categorical handling, but assigning arbitrary numbers to nominal categories can create false order.

Ordinal encoding is appropriate only when category order is meaningful, such as bronze, silver, and gold. High-cardinality variables require care. Alternatives include frequency encoding, carefully regularized target encoding, hashing, or grouping rare categories. Target encoding must be computed within training folds; otherwise the target leaks into the predictor.

Unseen categories at deployment should be handled explicitly, for example with an “unknown” category.

Numerical scaling

Scaling is important for distance-based, gradient-based, and regularized models, but often less important for ordinary decision trees. Standardization is:

where x_i is an observation, μ is the training-set mean, and σ is the training-set standard deviation. The result has no units. Standardization is appropriate when a variable is reasonably represented by its mean and standard deviation and σ > 0.

Min–max scaling to [0, 1] is:

where x_min and x_max are training-set bounds and x_max > x_min. It is sensitive to future values outside the observed range. Robust scaling uses the median and IQR and is often preferable when strong outliers remain.

Scaling must be fitted on training data only. Applying a test-set mean or maximum during preparation is a form of leakage.

6. Target Leakage and Data Splitting

Target leakage occurs when a predictor contains information that would not be available at the time the prediction is made, or is directly or indirectly derived from the outcome. Examples include a cancellation-reason field used to predict cancellation, a laboratory result recorded after diagnosis, and a “days past due” variable calculated after default.

Leakage can also arise through preprocessing. Imputing, selecting features, scaling, or encoding using all records before cross-validation allows information from held-out observations to influence the model.

Random splitting is reasonable when observations are independent and identically distributed with respect to time and entities. It is inappropriate when forecasting future outcomes, when repeated records from the same customer occur, or when temporal drift is important. Time-based splitting uses earlier observations for training and later observations for validation. Group-based splitting keeps all records from an entity in one partition.

A sound workflow is:

1. Define the prediction timestamp and outcome window.
2. Split data according to time, entity, or experimental design.
3. Fit preprocessing only on the training partition.
4. Apply the fitted transformations to validation and test partitions.
5. Evaluate on data that was not used for fitting or selecting the final model.

7. Class Imbalance

In classification, imbalance occurs when one class is much less frequent than another. Accuracy can be misleading. If 98% of transactions are legitimate, a classifier that always predicts “legitimate” has 98% accuracy but detects no fraud.

A confusion matrix contains true positives (TP), true negatives (TN), false positives (FP), and false negatives (FN). Important measures include:

Precision measures the proportion of predicted positives that are correct; recall measures the proportion of actual positives detected. The appropriate metric depends on the costs of errors. A fraud investigation may tolerate more false positives to improve recall, whereas an expensive medical intervention may prioritize precision.

Techniques include class-weighted learning, threshold adjustment, undersampling, oversampling, and synthetic methods such as SMOTE. Resampling must occur inside each training fold and never before the validation split. The original class distribution should be represented when reporting operational performance unless a different deployment prevalence is expected.

8. Feature Engineering

Feature engineering transforms raw observations into variables that express useful structure. It combines domain knowledge, statistical reasoning, and operational constraints.

Structured and temporal features

Examples include:

• ratios such as revenue per active customer;
• counts, sums, averages, and recency measures;
• tenure calculated from account start to prediction date;
• day of week, month, holiday indicator, and season;
• rolling averages and lagged values for time series;
• interaction terms such as price × promotion;
• frequency, recency, and monetary-value measures in customer analytics.

For a customer with transactions at times t₁, …, tₖ and prediction time T, recency can be defined as:

The units must be specified, such as days. Only transactions at or before T may be used. A rolling mean over the previous w observations is:

where w is the window length and the current or future observation is excluded when forecasting the current period. The initial periods may have insufficient history and require a documented rule.

Ratios can be informative but unstable when denominators approach zero. Retain denominator features and define behavior for zero denominators rather than silently producing infinite values.

Text features

Unstructured text can be represented using word counts, character counts, sentiment measures, domain dictionaries, or term-frequency inverse-document-frequency (TF–IDF) vectors. For term t in document d, a common TF–IDF representation is:

where TF(t,d) is the term frequency, N is the number of documents, and DF(t) is the number of documents containing t. Smoothing conventions vary. TF–IDF is useful for sparse linear models, while embeddings can represent semantic relationships but may increase computational, privacy, and governance concerns.

Text preprocessing should respect language, spelling, negation, domain terminology, and personally identifiable information. Removing words mechanically may remove meaning. Text vectorizers must be fitted on training documents only.

Feature selection and dimensionality

Feature selection may use domain rules, univariate screening, regularization, recursive methods, or model-based importance. Selection performed before cross-validation can leak information. Correlation is not sufficient evidence of usefulness, and a predictive feature need not be causally related to the target. However, causal interpretation requires additional design and should not be claimed from predictive association alone.

9. Tools and Reproducible Implementation

Spreadsheets are useful for inspection, small controlled transformations, and communication, but they are vulnerable to overwritten formulas, implicit type conversion, and undocumented manual edits. SQL is efficient for filtering, joining, aggregating, and creating governed data products. Python and R support statistical analysis, automated tests, pipelines, visualization, and version control.

A professional implementation separates:

1. raw immutable data;
2. validated and standardized data;
3. feature-generation code;
4. model-ready datasets; and
5. metadata, tests, and documentation.

A preprocessing pipeline should store category mappings, imputation values, scaling parameters, feature definitions, software versions, and random seeds where applicable. Tests should verify row uniqueness, expected ranges, no future timestamps, stable column names, and acceptable missingness. Reproducibility is not merely a technical convenience; it supports auditability and accountability.

Applied Case: Illustrative Retail Churn Project

Background and problem

A subscription retailer wants to identify customers likely to cancel during the 30 days following 31 March. The business can contact only a limited number of customers, so it needs a ranked risk score rather than an indiscriminate campaign.

Analysis

The unit of analysis is the customer at 31 March. The target equals 1 if the customer has no active subscription by 30 April. Permitted predictors include orders, support contacts, payment history, and engagement recorded on or before 31 March.

The team first discovered that the customer table contained duplicate IDs, order amounts were stored in both dollars and cents, and support tickets used inconsistent status labels. A reconciliation rule retained the mastered customer record, converted amounts to dollars using source metadata, and mapped statuses through a controlled reference table.

Missing income values were substantial and differed by acquisition channel. The team retained a missingness indicator and imputed the training-set median within channel. A negative order amount was investigated: some records represented refunds, so they were retained with a transaction-type indicator rather than treated as invalid sales.

Approach and solution

The team created order count, total spend, days since last order, number of support contacts, payment-failure count, and a seven-day engagement trend. All features were calculated using records no later than 31 March. The April outcome was kept separate.

Customers from January through February were used for training, March for validation, and April outcomes for final testing. Preprocessing was fitted on the training period. Because churn was relatively uncommon, the team compared class weighting with threshold adjustment and reported precision, recall, and a cost-based measure rather than accuracy alone.

Outcome and learning points

The resulting model produced a ranked contact list and was tested in a controlled campaign rather than assumed to create value. The most important learning was procedural: the initial model appeared excellent until a post-cancellation support code was removed. Performance then became more realistic but operationally defensible.

The case illustrates that feature engineering is inseparable from time, governance, and business process knowledge. A sophisticated algorithm cannot repair an incorrectly defined prediction window.

Limitations, Ethics, and Governance

Preparation choices can create or amplify unfairness. Missingness may be concentrated in disadvantaged groups; ZIP code may act as a proxy for protected characteristics; and historical labels may reflect unequal treatment. Examine performance and missingness across relevant groups, document legitimate business justification, and apply applicable privacy, consumer-protection, and discrimination laws.

Data minimization should guide acquisition: collect only information necessary for the stated purpose. Protect personal data through access controls, retention limits, encryption, pseudonymization where appropriate, and secure handling of text. Consent and lawful purpose depend on jurisdiction and context. Individuals should not be misled about significant automated decisions.

Feature importance does not establish fairness or causality. A model can be accurate overall while producing unequal error rates. Governance should include an owner, versioned documentation, approval criteria, monitoring for drift, and a process for correction and appeal.

Contemporary Developments and Best Practices

Modern data platforms increasingly support feature stores, data contracts, automated quality checks, and continuous monitoring. Large language models and embedding systems expand the use of unstructured text, but they introduce risks involving hallucinated attributes, sensitive information, model drift, and unclear provenance. Generated features should be validated like any other data product.

Best practice is to:

• define the prediction point before collecting features;
• preserve raw data and document transformations;
• use source-aware validation rules;
• fit all learned preprocessing on training data only;
• select splits that reflect deployment;
• test for leakage and subgroup performance;
• retain interpretable feature definitions where possible;
• monitor data distributions and missingness after deployment; and
• make the pipeline reproducible and reviewable.

Chapter Summary

Analysis-ready data are designed around a prediction unit, target, time boundary, and permitted information set. Acquisition requires reliable joins, documented provenance, and validation of accuracy, completeness, consistency, validity, uniqueness, timeliness, and integrity. Missing values should be investigated rather than mechanically deleted, and imputation must be learned from training data. Outliers require contextual diagnosis. Categorical variables need suitable encoding, while scaling is essential for some algorithms and unnecessary for others. Leakage can arise from future variables, target-derived fields, preprocessing, and inappropriate data splits. Imbalanced classification requires metrics and decision thresholds aligned with error costs. Feature engineering creates useful temporal, transactional, ratio, interaction, and text representations, but every feature must respect prediction-time availability. Ethical governance requires privacy protection, fairness analysis, documentation, monitoring, and accountability.

Key Terms

Data lineage: A record of where data originated and how it were transformed.

Unit of analysis: The entity represented by one analytical row.

Target variable: The outcome a predictive model is designed to estimate.

Prediction timestamp: The time at which a prediction is generated.

Missing completely at random: Missingness unrelated to observed or unobserved values.

Imputation: Replacing or estimating missing values.

Outlier: An observation unusual relative to a reference distribution or context.

One-hot encoding: Representing categories with binary indicator variables.

Standardization: Transforming a variable using its training-set mean and standard deviation.

Target leakage: Use of information unavailable at prediction time or derived from the outcome.

Class imbalance: Unequal representation of outcome classes.

Precision: The proportion of predicted positives that are true positives.

Recall: The proportion of actual positives that are detected.

Feature engineering: Creating predictive variables from raw data.

Recency: Time elapsed since the most recent relevant event.

Rolling feature: A statistic calculated over a moving historical window.

TF–IDF: A text weighting method combining term frequency and inverse document frequency.

Data drift: Change in data distributions or relationships after deployment.

Review Questions

Short Answer Questions

1. What information should a prediction statement specify?
2. Why must imputation parameters be fitted using training data only?
3. Distinguish a legitimate extreme value from a data error.
4. When is one-hot encoding appropriate?
5. Why can accuracy be misleading with imbalanced classes?

Long Answer Questions

1. Explain a complete workflow for converting raw operational data into a model-ready dataset.
2. Compare deletion, simple imputation, group-based imputation, and model-based imputation.
3. Explain target leakage and discuss at least four ways it can enter a predictive project.
4. Compare random, time-based, and group-based data splitting, giving an appropriate use case for each.
5. Discuss the ethical and governance risks of feature engineering in customer analytics.

Analytical / Application Questions

1. A bank predicts default using a “collection action” field recorded after a missed payment. Identify the problem and redesign the feature window.
2. A fraud dataset has 2% fraudulent transactions. Recommend an evaluation strategy, including metrics and treatment of class imbalance.
3. Design five leakage-safe features for predicting subscription churn at the end of a month, and specify the historical window for each.

Multiple Choice Questions with Answers

Question 1

Which statement best defines target leakage?

A. A predictor contains information unavailable at the time of prediction.

B. A dataset contains duplicate rows.

C. A numerical variable has a large standard deviation.

D. A category is represented using several binary columns.

Correct Answer: A. A predictor contains information unavailable at the time of prediction.

Question 2

Which statistic is generally more resistant to extreme values when imputing a skewed numerical variable?

A. Mean

B. Median

C. Variance

D. Maximum

Correct Answer: B. Median

Question 3

What is the main purpose of a time-based data split?

A. To guarantee equal class frequencies

B. To ensure future information is not used to predict the past

C. To remove all missing values

D. To convert categories into numbers

Correct Answer: B. To ensure future information is not used to predict the past

Question 4

For a binary classifier, recall is calculated as:

A. TP ÷ (TP + FP)

B. TP ÷ (TP + FN)

C. (TP + TN) ÷ (TP + TN + FP + FN)

D. FP ÷ (FP + TN)

Correct Answer: B. TP ÷ (TP + FN)

Question 5

Which method is usually suitable for a nominal categorical variable with no natural order?

A. One-hot encoding

B. Ordinal ranking based on alphabetical order

C. Standardization

D. Winsorization

Correct Answer: A. One-hot encoding

Question 6

A feature has Q₁ = 10 and Q₃ = 18. Under the conventional 1.5 × IQR rule, what is the upper screening fence?

A. 22

B. 26

C. 30

D. 36

Correct Answer: B. 26

Question 7

Why should oversampling be performed inside training folds rather than before cross-validation?

A. To prevent synthetic or duplicated information from influencing validation data

B. To guarantee that the minority class disappears

C. To make all features normally distributed

D. To avoid defining a target variable

Correct Answer: A. To prevent synthetic or duplicated information from influencing validation data

Question 8

Which feature is valid for predicting churn at 31 March?

A. A cancellation reason entered on 5 April

B. A support contact count recorded through 31 March

C. A refund issued on 10 April

D. The final churn label

Correct Answer: B. A support contact count recorded through 31 March

Question 9

Standardization using z = (x − μ) ÷ σ produces a variable that is generally:

A. Measured in the original units

B. Centered relative to the training mean and scaled by training standard deviation

C. Bounded between exactly 0 and 1

D. Guaranteed to contain no outliers

Correct Answer: B. Centered relative to the training mean and scaled by training standard deviation

Question 10

In TF–IDF, a term appearing in many documents generally receives a lower inverse-document-frequency contribution because:

A. Its document frequency is high

B. Its term frequency is always zero

C. The number of documents is reduced to one

D. It is necessarily misspelled

Correct Answer: A. Its document frequency is high