# AI-Driven Predictive Maintenance for Workforce and Service Optimization in the Automotive Sector
## 3 Preliminaries
### Data Collection  
The dataset used for this study was compiled from multiple enterprise systems of an automotive company, combining structured records from CRM databases, vehicle information systems, and authorized service center logs. The overall data collection process was both historical and automated, spanning the period between January 2012 and December 2024.  
The data was organized into four main categories:  
\(\bigcirc\)**Customer Data:** This includes static demographic attributes generated by leveraging customer demographics, as well as vehicle specifications like Brand Code, Base Model Code, Engine Type, and registration province, retrieved from the internal vehicle catalog system.  
\(\bigcirc\)**Maintenance Data:** Extracted from work orders issued by authorized service centers, covering service dates, part replacements, discounts applied, and service fees.  
\(\bigcirc\)**Derived Data:** These features were engineered using deterministic rules and statistical transformations across the above three tables. For instance,* Days Between Service Visits is computed as the difference between the Service Visits Date and the Previous Service Visits Date.
* Warranty Remaining Days is obtained by subtracting Guarantee End Date from the forecast date.
* Mileage per Day and Service Visit per Year are computed as normalized ratios over time.  
To ensure the dataset reflects realistic maintenance behavior, data related to previous vehicle owners was included in the training phase, while the test set was constructed solely based on the final owner of each vehicle to maintain behavioral consistency. All data extraction, transformation, and loading (ETL) procedures were automated using in-house Python (3.11 version) pipelines. The raw data was extracted from Oracle Database tables using DBeaver, an internal data access and visualization platform. Three primary tables--customer, vehicle, and maintenance--served as the foundational data sources. These tables were linked using T-SQL queries, with foreign key (FK) and primary key (PK) constraints to establish relational integrity. The entity relationship and the joining mechanism between these tables are visualized in Figure 1.  
The preprocessing logic, including filtering and joining, was partially implemented in SQL and partially in Python, following a modular pipeline architecture. In the SQL layer, data joining operations between customer, vehicle, and maintenance tables were executed using T-SQL. Additionally, domain-specific filtering rules--such as restricting the data to certain vehicle brands, filtering records within a defined service date range, and excluding invalid entries--were applied directly within the database environment for efficiency and scalability.  
In the Python layer, advanced data preprocessing tasks were programmatically applied to the extracted datasets, including  
* Imputation of missing values (using median for numerical and mode for categorical features);
* Outlier detection and treatment (via Z-score and domain-specific thresholds);
* Feature scaling, including both standardization and normalization where appropriate;
* Encoding of categorical variables through one-hot encoding and frequency encoding;
* Creation of derived and interaction-based features to enhance model interpretability;  
Figure 1: Entity relationship diagram of the data sources used for feature construction.  
Application of class imbalance strategies, such as class_weight = 'balanced'.  
This hybrid preprocessing strategy ensured the balance between efficiency (SQL operations close to source) and flexibility (Python for modeling-specific transformations). It also improved reproducibility and modularity across iterative model development phases. A comprehensive description of all 60 attributes, their sources, and transformations is available in Table A1 of Appendix A. An illustration of the overall data acquisition and enrichment flow is provided in Figure 2.