Data Preparation / Predictive Modeling / Data Quality
Why Data Preparation is More Important Than Algorithm Complexity
The development of machine learning tools is gradually lowering the technological entry barrier to predictive analytics.
Companies can use Python, R, Apache Spark MLlib, and cloud analytics platforms to build models without needing to develop all the mathematical algorithms themselves. However, the availability of software tools does not guarantee a successful outcome.
In many corporate projects, the main difficulties arise even before the model training begins. ERP, CRM, and DWH systems can contain a large volume of historical information, but this data was created for operational tasks, not specifically for machine learning.
For example, a CRM stores the current customer status, whereas a predictive model needs to know what that status was on a specific date in the past. An ERP might contain actual sales data but does not always allow for directly identifying unmet demand due to stockouts.
An analytical data warehouse may consolidate financial metrics but lack sufficient detail to evaluate individual customer transactions.
Therefore, the main stage of creating a predictive model is Data Preparation — the preparation of an information set that correctly reflects the business process under study and can be used for mathematical training and independent verification.
This task requires knowledge in the areas of Data Quality, ETL, MDM, business semantics, and statistical analysis.
Without such preparation, even a technologically complex model can learn to reproduce errors, random dependencies, or the consequences of incorrect data organization.
Defining the business goal and target variable
Before retrieving information from corporate databases, it is necessary to formulate a specific forecasting task. For example, the formulation “we want to use Machine Learning for sales” is insufficient.
It is necessary to determine exactly which indicator needs to be predicted, for what period, and how the result will affect the management decision.
Example of demand forecasting
An enterprise may set a task to forecast the number of units of a certain product that will be sold next week. The target variable will be the actual sales volume for the corresponding future period.
To train the model, historical examples need to be formed, where the characteristics at the beginning of each week are known, along with the actual result after its completion.
Overdue Payment Prediction Example
Another task is to predict the risk of late invoice payment. The target variable can have two values: on-time payment or overdue.
However, the prediction moment must be clearly defined.
For example, the model can assess the risk when the invoice is issued. In this case, it should not use information that becomes known only after the payment due date.
This is a fundamental requirement for correctly building the training set.
Data Profiling Before Model Creation
After defining the task, available sources need to be explored. This is achieved using Data Profiling.
Profiling allows assessment of record counts, value distributions, missing value proportions, and potential integrity violations. For example, a delinquency risk model might use historical invoices from an ERP.
Before training, it is necessary to verify that all invoices have a correct customer ID, invoice date, due date, and final status.
Missing Value Analysis
Not all missing values are equally significant. For instance, the absence of a phone number might be acceptable for a certain customer type.
Conversely, the absence of an invoice date can make it impossible to correctly determine its payment history.
Therefore, it is necessary to establish the reason for missing values.
Distribution Analysis
Data may contain values that differ from what is expected. For example, the average order amount can vary significantly between different customer categories.
If all records are processed identically, the model may not adequately account for these differences.
Analysis of Historical Changes
Particular attention should be paid to changes in information structure. For instance, a company might have changed its order registration rules in 2014.
Data before and after this change may have different semantics.
Therefore, mechanically combining several years of history does not always ensure a homogeneous training set.
Data Cleansing: cleaning without distorting business information
After profiling, cleansing rules are defined. Typical operations include format normalization, correction of obvious errors, handling missing values, and deduplication.
However, it is important not to alter information solely for the sake of a more convenient mathematical representation. For example, a large order might appear statistically anomalous but be a completely real transaction.
If all large values are automatically removed, the model may begin to systematically underestimate the demand from corporate clients.
Handling Missing Values
Various approaches can be used. For certain numerical characteristics, imputation with the median is acceptable.
For categorical attributes, use a separate category for unknown values. For other fields, it may be necessary to obtain information from an authoritative corporate source.
It is important that the imputation method does not create hidden dependencies on test data.
For example, the median for preparing the training set must be calculated only on the training part and then applied to the validation set.
Deduplication
If one client has multiple independent cards, the training set may disproportionately reflect their behavior. MDM rules must be used to eliminate such problems.
However, records should not be automatically merged solely based on name similarity.
Legal identifiers, transaction history, and business rules must be considered.
Feature Engineering: Transforming Corporate Records into Model Features
After cleaning, a set of features must be formed. Features represent characteristics of an object or situation that the model uses for prediction.
For example, for a client, one can calculate the average number of orders per month, the purchase volume in the last 90 days, and the proportion of overdue payments.
These characteristics can be significantly more useful than individual ERP initial fields.
Aggregated Metrics
Summary characteristics can be formed from operational tables.
For example:
SELECT
customer_id,
COUNT(*) AS orders_count,
SUM(order_amount) AS total_amount,
AVG(order_amount) AS average_order
FROM Orders
WHERE order_date >= '2016-01-01'
AND order_date < '2016-10-01'
GROUP BY customer_id;
This is a conditional example of feature engineering for a fixed historical period. For a complete predictive model, it is necessary to generate such indicators relative to the date of each training example.
Otherwise, the model may receive information from the future.
Categorical Features
These include region, product category, customer type, or sales channel. For many mathematical algorithms, categories need to be converted into a numerical representation.
One of the approaches is One-Hot Encoding.
For example, three customer categories can be represented by three separate binary features.
However, if there are too many categories, this can significantly increase the dimensionality of the dataset.
Temporal Features
For sales forecasting, the day of the week, month, holiday periods, and seasonality can be important. However, calendar features should align with the actual business context.
For example, holidays that affect sales in Ukraine may differ from the calendar of other countries.
Similarly, historical changes in the company’s work schedule must be considered.
Temporal Correctness and the Data Leakage Problem
One of the most dangerous sources of errors is Data Leakage—the inclusion of information in the model that should not be available at the time of prediction.
Let’s consider an example.
A company wants to determine the risk of payment delay when issuing an invoice.
The initial ERP contains a field for the actual payment date.
If this field is included as a feature, the model will determine delays with almost no errors on historical data.
However, during actual operation, the actual payment date is unknown.
Therefore, a high model score will be illusory.
Historical State of Information
For each training example, it is necessary to determine the moment at which the features are formed.
For example, if the prediction is made on March 1st, only information that was available up to that moment can be included in the training set.
Slowly Changing Dimensions
In a corporate DWH, mechanisms for preserving the history of reference data, particularly Slowly Changing Dimensions Type 2, may be used. They allow the state of a certain attribute to be reproduced at a historical date.
For example, a client might have changed their service category.
For training, it is necessary to use the category that was active at the corresponding moment, not just its current value.
This is one of the reasons why a well-designed DWH is a valuable source for Machine Learning.
Training, Validation, and Test Sets
After feature engineering, the information needs to be split into parts. The Training Set is used to train the algorithm.
The Validation Set is for parameter selection and model comparison. The Test Set is for independent evaluation of the final result.
For cases where records are independent and identically distributed, random splitting can be used.
However, in corporate analytics, this is not always appropriate.
Time Series
For sales forecasting, historical periods cannot be arbitrarily shuffled. If the model is trained on events from future months and tested on past ones, the result may not accurately reflect real-world operation.
Therefore, it is advisable to use a temporal split.
For example, the model is trained on data from 2014-2015, parameters are validated at the beginning of 2016, and final testing is performed on a later, separate period.
Duplicate Customer Records
For customer forecasts, it is necessary to consider that a single customer may have multiple training examples. If similar records accidentally end up in both the training and testing sets simultaneously, the results may be overestimated.
In some tasks, it is advisable to perform a split by customers or groups of entities.
The choice depends on what the company wants to predict: the behavior of already known customers or new ones.
Quality Metrics for Predictive Models
For evaluation, it is necessary to use metrics that align with the business objective.
Classification
For classification, Precision, Recall, F1-score, and ROC AUC can be used. For example, in a default risk prediction task, Recall characterizes what proportion of actual problematic cases the model detected.
Precision shows what proportion of predicted problematic cases were actually problematic. These metrics have different economic implications.
If the model frequently misclassifies customers as risky, the company may spend resources on unnecessary checks. If it misses a significant portion of risky customers, financial losses may increase.
Regression
For numerical prediction, Mean Absolute Error (MAE) and Root Mean Squared Error (RMSE) can be used. MAE reflects the average absolute magnitude of the error.
RMSE is more sensitive to large deviations. The choice of metric should consider the economic impact of errors.
For example, for some products, underestimating demand might be more costly than overestimating it. Therefore, a single symmetric statistical metric may not always fully reflect business needs.
Comparison with Baseline Model
One of the most important conditions is to compare a complex algorithm with a simple forecast. For instance, for sales, a baseline option could be the average of the previous four weeks.
For customer risk, a simple rule based on the number of previous defaults. If Random Forest or another complex model shows only a minor improvement, it’s necessary to assess whether it justifies the implementation costs.
In some cases, a simple model might be a better corporate solution due to its transparency, stability, and ease of maintenance. This is especially important for companies that are just starting to use Machine Learning.
Practical Example with scikit-learn 0.18
Let’s consider a simplified example of customer risk classification.
As of November 2016, scikit-learn 0.18 is available, which includes the sklearn.model_selection module.
from sklearn.model_selection import train_test_splitfrom sklearn.preprocessing import StandardScalerfrom sklearn.linear_model import LogisticRegressionfrom sklearn.pipeline import Pipelinefrom sklearn.metrics import classification_report# X - prepared numerical features# y - historically confirmed outcomeX_train, X_test, y_train, y_test = train_test_split( X, y, test_size=0.25, random_state=42)model = Pipeline([ ("scaler", StandardScaler()), ("classifier", LogisticRegression())])model.fit(X_train, y_train)predictions = model.predict(X_test)print(classification_report(y_test, predictions))
This example demonstrates the technical sequence of training. A feature is the use of a Pipeline, which combines feature scaling and logistic regression.
Thanks to this, scaling parameters are determined during training and then applied to test data. However, for a real corporate task, random splitting may be unsuitable.
If the data has a temporal structure or recurring customers, an appropriate validation scheme must be applied. Also, before training, it is necessary to perform missing value imputation, prepare categorical features, and check their historical availability.
Apache Spark ML Pipelines for Large Datasets
Apache Spark MLlib can be used for distributed computing. On November 14, 2016, Spark 2.0.2 was released, containing stability fixes for the 2.0 branch. As of November 24, it is already available for use.
The spark.ml API allows forming sequences of transformations and training based on DataFrames. For example, a Pipeline can include components like StringIndexer, OneHotEncoder, VectorAssembler, and LogisticRegression.
There are corresponding tools available for parameter selection, including CrossValidator and ParamGridBuilder. However, even in a distributed environment, it is necessary to control information dependencies.
If future data is used during feature preparation, increasing the number of servers will not fix the errors. It is also necessary to evaluate the processing cost.
For a small CRM database, deploying a separate Spark cluster might be an excessive solution.
How to Organize a Production Data Preparation Process
For regular use of a predictive model, a reproducible information process must be created.
- ERP / CRM / DWH
- Data Profiling and Source Verification
- ETL, MDM, and Data Cleansing
- Feature Engineering
- Training and Independent Validation
- Approved Model
- Forecast → CRM / ERP / BPM
- Actual Quality Control
Conceptual scheme for preparation and use of a corporate predictive model. After controlling the actual results, a new cycle of verification and training may be initiated. For each stage, it is necessary to define the responsible person and the criteria for successful completion.
For example, the ETL process must verify the number of loaded records and the presence of critical fields. The training procedure must store information about the data version, software code, and algorithm parameters.
After approval, the model can be published as an application service or used in a batch process. In this case, the company must ensure the ability to re-generate the forecast with the corresponding historical data.
This is important for error analysis and result verification.
Monitoring Quality After Implementation
Model results can degrade even without changes to the software code. For example, the company changes its pricing policy, opens new regional divisions, or switches to a different CRM.
In such situations, data characteristics may change.
Distribution Control
It is necessary to monitor the main statistical characteristics of the input data. For example, the proportion of customers in a certain category may significantly increase.
This can affect the quality of the model.
Monitoring Actual Results
When real results appear, they need to be compared with previous forecasts.
For example, after payment deadlines have passed, it can be determined how correctly the model assessed the risk of default.
Checking the Need for Retraining
If the quality deteriorates, it is necessary to determine the cause. It is not always the correct decision to immediately train a new model.
First, it is necessary to check the information sources, changes in business processes, and the correctness of the target variable. The issue might stem from a new data management rule in the CRM, rather than the algorithm itself.
Data Governance and Forecast Responsibility
The predictive model is part of the corporate information system, thus requiring defined management. The Data Owner should be responsible for the business value of the information and its permissible uses.
The Data Steward oversees the quality of relevant datasets. Analysts and machine learning specialists are responsible for the methodology of model construction and evaluation.
The IT department ensures its technical operation. The business process owner determines which management actions can be performed based on the forecast.
For example, if the model assesses customer risk, it must be separately approved whether such a forecast can trigger an additional check, change a manager’s work priority, or affect other operations. It is also important to document the model’s limitations.
For example, a forecast might be sufficiently reliable for large corporate clients but lack the necessary quality for new clients with a short history of cooperation.
Intecracy Group Competencies in Data Preparation for ML
Building predictive analytics requires a comprehensive approach to information resources. Data Management IG specializes in ETL, MDM, Data Quality, DWH, and corporate analytics. These areas are directly related to preparing training datasets, controlling historical information, harmonizing reference data, and forming features for models.
IQusion IT operates in the areas of system integration, BPM, and electronic document management. Relevant competencies are important for using forecasting results in corporate applications and decision-making processes. Softline IT specializes in server and network infrastructure, storage systems, and corporate infrastructure projects. These competencies are necessary for creating a reliable data processing environment and operating analytical platforms.
If computation spans local and cloud environments, decide where each processing stage will run.
Furthermore, the correctness of the mathematical model must be confirmed by independent testing and actual business results.
Practical Guidelines for Implementation
Preparing corporate data is one of the most critical stages in building predictive analytics.
Before training, it is necessary to define the business task, target variable, prediction time, and relevant information sources.
Data Profiling allows for the assessment of the actual state of records.
Data Cleansing and MDM ensure cleaning and reconciliation.
Feature Engineering transforms operational data into features suitable for mathematical algorithms.
Special attention must be paid to the historical correctness of information and the prevention of Data Leakage.
To validate models, independent datasets and metrics that correspond to the economic consequences of prediction errors must be used.
After implementation, it is necessary to regularly monitor the quality of results and the state of information sources.
A reliable enterprise machine learning model begins not with the choice of algorithm, but with correctly prepared data, a clearly defined business objective, and a procedure for independent forecast verification.
Author