KNIME_​project_​Blankenhorn_​Grupp_​final

4.1 DATA MODELING: Building Flatfile & Feature Engineering

1. DATA ACCESS from files

2. DATA EXPLORATION - Plausibility check

City and country do not match (it was assumed that the country is wrong data, but the rest of the data is correct -> no other chance to validate)

Product name and category do not match

Single table view: No missing values & no negative values (check via statistic view) & no wrong data types (data type from "date" is converted from string to date at a later point)

3. DATA CLEANING

Check if discount min and max value lies between 0 and 1 -> YES (via statistic view)

Background Thoughts on Feature Engineering: (Potential Features)

Time Features

• Seasonality ("is_holiday"): Easter, Christmas, Valentine's Day, Halloween, Mother's Day/Father's Day → the lead-up period is relevant!

  • Easter and Mother's Day/Father's Day: not always on the same date!

  • Not all countries share the same dates!

    • 2023: 26.03. – 09.04. (2-week window prior to Easter)

    • 2024: 17.03. – 31.03. (2-week window prior to Easter)

  • Christmas: 01.12. – 24.12. (entire month due to Advent season)

  • Valentine's Day: 07.02. – 14.02. (1-week window prior)

  • Halloween: 24.10. – 31.10. (1-week window prior)

  • Mother's Day/Father's Day:

    • 2023: 07.05. – 18.05. (1-week window before Mother's Day → 14.05.)

    • 2024: 02.05. – 12.05. (1-week window before Father's Day → 09.05.)

  • → The same logic is applied accordingly for 2025 through 2027.

• Day of week: Is chocolate purchased more on weekdays or weekends? Which weekday is preferred?

  • Day of the week (Monday–Friday) → "day_of_week"

  • Weekend (yes/no) → "is_weekend"

Store Type Features

• Should school holiday periods be considered for Airport stores?

Product Category Features

• Gift category: Praline, Truffle → more likely purchased around holidays (peaks expected)

• Everyday category: Dark, White, Milk → purchased consistently throughout the year

Lag & Trend Features

• Same weekday last week (lag_7)

• Bi-weekly rhythm (lag_14)

• Three-weekly rhythm (lag_21)

• Monthly rhythm (lag_28)

Discount Features

• Has discount (yes/no)

Customer Features

• Is loyalty member (yes/no)

• "is_weekend" = all rows have the same value, so I choose the first

• "proximity_holiday" = different transactions on the same day could theoretically have different proximity values -> Setting the value to Maximum” ensures that the strongest holiday effect is retained

4.2 MODEL TRAINING: Partitioning data and setting up linear regression model

Background:

1. To be able to create lag columns efficiently, a history of transactions per day is required (through grouping some days are "lost" as no transactions happened -> this is prevented by this part of the workflow)

2. Getting transparency on transaction combinations that did not happen

5. EVALUATION: Testing linear regression model by forecasting customer demand (& recreating features)

Transformed data type because when it is a string, it does not interpret that 1 (monday) weighs less than 7 (sunday) which is important for the model when training with the data!

Lag features are not taken into account, since there must be no missing values in the final prediction.

Option 2 for feature creation ("is in_holiday timeframe")

Lag features can be accurately reconstructed until October 7th, where all lag values still reference real historical data. From October 8th onwards, lag_7 requires the demand value of October 1st, which is itself already a forecast. This means the model is predicting based on predictions, compounding uncertainty over time.

A recursive loop forecast would solve this by iteratively feeding each day's forecast as the lag input for the next prediction.

Option 2 does not define fixed holiday windows but instead uses only the exact holiday date as a reference point. A Rule Engine then calculates the number of days between each transaction date and the next upcoming holiday, assigning a proximity score: the closer the date is to the holiday, the higher the score. The underlying hypothesis is that demand increases gradually as a holiday approaches, with the strongest effect in the final days before the event.

However, this approach requires a Cross Join between the full transaction dataset and the holiday lookup table, generating an extremely large intermediate table of several million rows. This results in prohibitively long processing times, making the approach impractical within the given workflow. Consequently, Option 2 was not pursued further and is not considered in the subsequent analysis.

To ensure applicability to future data, all holiday reference dates were defined through 2027, allowing the workflow to be extended to new datasets beyond the original training period without requiring manual updates to the holiday lookup table.

VISUALIZATION: Plotting sales broken down by category and month

Validation of forecasting power of model by forecasting september data from 2024 and analyzing how big the delta is to actual data (output: see PowerBI visualization in PowerPoint presentation)

The mapping needs to be adjusted. When the record was created, it included the order ID, which has since been removed from the master record. Due to long loading times, it hasn't since been updated, but the logic of the workflow part should be clear.