Modelling Exercise

Comparative demand forecasting across 5 strategies × multiple model types.

# Strategy Granularity Models

1 Supermarket-Level 3 groups XGBoost, Prophet,
Improved Prophet
2 SKU-Level 3 groups XGBoost, Prophet,
Improved Prophet
3 SM × SKU 9 groups XGBoost, Prophet,
Improved Prophet
4 Global XGBoost 1 model XGBoost (all series
stacked)
5 Global LightGBM 1 model LightGBM (all series
stacked)

1. Load Data
%matplotlib inline
import pandas as pd, numpy as np, [Link] as plt, os
[Link]('/Users/[Link]/Downloads/Technical Assignment Heineken
- Data Science')

import logging, sys

[Link]([Link])

from modelling_utils import *

df = pd.read_csv('Implementation/data_with_features.csv', index_col=0,
parse_dates=True)
print(f'Shape: {[Link]} | Date: {[Link]().date()} →
{[Link]().date()}')
print(f'Supermarkets: {list(df["supermarket"].unique())}')
print(f'SKUs: {list(df["sku"].unique())}')
[Link](3)

Shape: (9855, 45) | Date: 2019-01-01 → 2021-12-30

Supermarkets: ['albert-heijn', 'dirk', 'jumbo']
SKUs: ['desperados', 'heineken 0.0', 'heineken regular']

/Users/[Link]/.pyenv/versions/3.11.0/lib/python3.11/site-
packages/tqdm/[Link]: TqdmWarning: IProgress not found. Please
update jupyter and ipywidgets. See
[Link]
from .autonotebook import tqdm as notebook_tqdm
 demand sku supermarket promotion year month
quarter \
date

2019-01-01 93.0 desperados albert-heijn 0 2019 1

1
2019-01-02 93.0 desperados albert-heijn 0 2019 1
1
2019-01-03 94.0 desperados albert-heijn 0 2019 1
1

day_of_week day_of_month week_of_year ...

demand_roll_std_56 \
date ...

2019-01-01 1 1 1 ...
NaN
2019-01-02 2 2 1 ...
NaN
2019-01-03 3 3 1 ...
0.0

demand_roll_min_56 demand_roll_max_56
days_since_promo \
date

2019-01-01 NaN NaN 999

2019-01-02 93.0 93.0 999

2019-01-03 93.0 93.0 999

days_until_promo promo_count_past_7d
promo_count_past_14d \
date

2019-01-01 999 NaN

NaN
2019-01-02 999 0.0
0.0
2019-01-03 999 0.0
0.0

promo_count_past_28d promo_count_past_56d target

date
2019-01-01 NaN NaN 92.0
2019-01-02 0.0 0.0 94.0
2019-01-03 0.0 0.0 93.0
[3 rows x 45 columns]

2. Strategy 1: Supermarket-Level (3 groups × 3 models)

df_sm = [Link]([[Link], 'supermarket']).agg(
demand=('demand','sum'), promotion=('promotion','max')
).reset_index().rename(columns={'level_0':'date'}).set_index('date')

sm_groups = [(sm, df_sm[df_sm['supermarket']==sm]

[['demand','promotion']])
for sm in sorted(df_sm['supermarket'].unique())]

s1_results, s1_preds = run_strategy(sm_groups, 'strategy1',

'Supermarket')
s1_df = [Link](s1_results)
print(f'\n✅ {len(s1_results)} models | Avg MAPE:
{s1_df["mape"].mean():.2f}%')

🔨 albert-heijn (1095 samples)

XGBoost → MAPE: 28.78%
Prophet → MAPE: 27.50%
Improved Prophet → MAPE: 27.49%

🔨 dirk (1095 samples)

XGBoost → MAPE: 28.71%
Prophet → MAPE: 23.62%
Improved Prophet → MAPE: 23.53%

🔨 jumbo (1095 samples)

XGBoost → MAPE: 21.50%
Prophet → MAPE: 18.29%
Improved Prophet → MAPE: 18.76%

✅ 9 models | Avg MAPE: 24.24%

3. Strategy 2: SKU-Level (3 groups × 3 models)

df_sku = [Link]([[Link], 'sku']).agg(
demand=('demand','sum'), promotion=('promotion','max')
).reset_index().rename(columns={'level_0':'date'}).set_index('date')

sku_groups = [(sku, df_sku[df_sku['sku']==sku]

[['demand','promotion']])
for sku in sorted(df_sku['sku'].unique())]

s2_results, s2_preds = run_strategy(sku_groups, 'strategy2', 'SKU')

s2_df = [Link](s2_results)
print(f'\n✅ {len(s2_results)} models | Avg MAPE:
{s2_df["mape"].mean():.2f}%')

🔨 desperados (1095 samples)

XGBoost → MAPE: 28.68%
Prophet → MAPE: 22.53%
Improved Prophet → MAPE: 22.93%

🔨 heineken 0.0 (1095 samples)

XGBoost → MAPE: 22.17%
Prophet → MAPE: 18.46%
Improved Prophet → MAPE: 18.38%

🔨 heineken regular (1095 samples)

XGBoost → MAPE: 32.99%
Prophet → MAPE: 30.14%
Improved Prophet → MAPE: 29.17%

✅ 9 models | Avg MAPE: 25.05%

4. Strategy 3: Supermarket × SKU (9 groups × 3 models)

smsku_groups = []
for (sm, sku) in
sorted([Link](['supermarket','sku']).[Link]()):
mask = (df['supermarket'] == sm) & (df['sku'] == sku)
smsku_groups.append((f'{sm}_{sku}', df[mask].copy()))

s3_results, s3_preds = run_strategy(smsku_groups, 'strategy3',

'SM×SKU', precomputed=True)
s3_df = [Link](s3_results)

# Add supermarket/sku columns for detailed analysis

s3_df['supermarket'] = s3_df['group'].[Link]('_').str[0]
s3_df['sku'] = s3_df['group'].[Link]('_', n=1).str[1]
print(f'\n✅ {len(s3_results)} models | Avg MAPE:
{s3_df["mape"].mean():.2f}%')

🔨 albert-heijn_desperados (1095 samples)

XGBoost → MAPE: 53.14%
Prophet → MAPE: 5.78%
Improved Prophet → MAPE: 6.72%

🔨 albert-heijn_heineken 0.0 (1095 samples)

XGBoost → MAPE: 5.58%
Prophet → MAPE: 8.23%
Improved Prophet → MAPE: 6.36%
🔨 albert-heijn_heineken regular (1095 samples)
XGBoost → MAPE: 40.96%
Prophet → MAPE: 12.98%
Improved Prophet → MAPE: 8.24%

🔨 dirk_desperados (1095 samples)

XGBoost → MAPE: 6.54%
Prophet → MAPE: 4.98%
Improved Prophet → MAPE: 5.29%

🔨 dirk_heineken 0.0 (1095 samples)

XGBoost → MAPE: 3.98%
Prophet → MAPE: 4.06%
Improved Prophet → MAPE: 3.78%

🔨 dirk_heineken regular (1095 samples)

XGBoost → MAPE: 6.40%
Prophet → MAPE: 8.11%
Improved Prophet → MAPE: 7.14%

🔨 jumbo_desperados (1095 samples)

XGBoost → MAPE: 16.84%
Prophet → MAPE: 9.08%
Improved Prophet → MAPE: 10.44%

🔨 jumbo_heineken 0.0 (1095 samples)

XGBoost → MAPE: 7.41%
Prophet → MAPE: 5.75%
Improved Prophet → MAPE: 5.71%

🔨 jumbo_heineken regular (1095 samples)

XGBoost → MAPE: 8.25%
Prophet → MAPE: 6.29%
Improved Prophet → MAPE: 5.57%

✅ 27 models | Avg MAPE: 10.13%

5. Detailed SM×SKU Results

print_detailed_smsku(s3_df)

pivot = s3_df.pivot_table(values='mape', index=['supermarket','sku'],

columns='model', aggfunc='mean')
pivot['Best'] = [Link](axis=1)
pivot['Best MAPE'] = [Link](columns='Best').min(axis=1)
print('\n' + [Link](2).to_string())

======================================================================
==========
📍 ALBERT-HEIJN
======================================================================
==========
SKU Model MAPE% MAE
RMSE
----------------------------------------------------------------------
--------
desperados Prophet 5.78% ⭐ 5.76
8.15
Improved Prophet 6.72% 6.69
8.66
XGBoost 53.14% 53.37
123.86

heineken 0.0 XGBoost 5.58% ⭐ 1.83

4.58
Improved Prophet 6.36% 2.23
4.58
Prophet 8.23% 3.25
5.13

heineken regular Improved Prophet 8.24% ⭐ 5.24

6.59
Prophet 12.98% 8.26
9.29
XGBoost 40.96% 26.91
60.94

======================================================================
==========
📍 DIRK
======================================================================
==========
SKU Model MAPE% MAE
RMSE
----------------------------------------------------------------------
--------
desperados Prophet 4.98% ⭐ 2.74
3.72
Improved Prophet 5.29% 2.92
3.87
XGBoost 6.54% 3.60
8.13

heineken 0.0 Improved Prophet 3.78% ⭐ 5.47

28.98
XGBoost 3.98% 5.83
30.01
Prophet 4.06% 5.72
28.73

heineken regular XGBoost 6.40% ⭐ 4.92

6.06
Improved Prophet 7.14% 5.33
6.57
Prophet 8.11% 6.06
7.13

======================================================================
==========
📍 JUMBO
======================================================================
==========
SKU Model MAPE% MAE
RMSE
----------------------------------------------------------------------
--------
desperados Prophet 9.08% ⭐ 6.77
32.54
Improved Prophet 10.44% 8.08
32.76
XGBoost 16.84% 15.02
37.62

heineken 0.0 Improved Prophet 5.71% ⭐ 1.88

4.49
Prophet 5.75% 1.86
4.53
XGBoost 7.41% 2.90
5.04

heineken regular Improved Prophet 5.57% ⭐ 5.43

21.95
Prophet 6.29% 5.95
22.06
XGBoost 8.25% 7.51
36.37

model Improved Prophet Prophet XGBoost

Best Best MAPE
supermarket sku

albert-heijn desperados 6.72 5.78 53.14

Prophet 5.78
heineken 0.0 6.36 8.23 5.58
XGBoost 5.58
heineken regular 8.24 12.98 40.96
Improved Prophet 8.24
dirk desperados 5.29 4.98 6.54
Prophet 4.98
heineken 0.0 3.78 4.06 3.98
Improved Prophet 3.78
heineken regular 7.14 8.11 6.40
XGBoost 6.40
jumbo desperados 10.44 9.08 16.84
Prophet 9.08
heineken 0.0 5.71 5.75 7.41
Improved Prophet 5.71
heineken regular 5.57 6.29 8.25
Improved Prophet 5.57

6. Global XGBoost (Single Model, All Series)

Stack all 9 SM×SKU series → add supermarket_encoded + sku_encoded → train one
XGBoost.

print('='*80)
print('STRATEGY 4: GLOBAL XGBOOST')
print('='*80)

global_xgb, global_xgb_df, global_xgb_pred, le_sm, le_sku, feat_xgb =

train_global_model(df, 'xgboost')

pivot_xgb = global_xgb_df.pivot_table(values='mape',
index='supermarket', columns='sku')
pivot_xgb['Avg'] = pivot_xgb.mean(axis=1)
print('\n' + pivot_xgb.round(2).to_string())

======================================================================
==========
STRATEGY 4: GLOBAL XGBOOST
======================================================================
==========

Overall: MAPE=6.78%, MAE=5.35, RMSE=19.12, Accuracy(±10%)=88.1%

sku desperados heineken 0.0 heineken regular Avg

supermarket
albert-heijn 5.99 7.48 3.64 5.70
dirk 4.78 5.55 3.48 4.60
jumbo 18.95 7.33 2.79 9.69

7. Global LightGBM (Single Model, All Series)

Same approach as Global XGBoost but using LightGBM (leaf-wise growth, often faster/better on
tabular data).
print('='*80)
print('STRATEGY 5: GLOBAL LIGHTGBM')
print('='*80)

global_lgb, global_lgb_df, global_lgb_pred, _, _, feat_lgb =

train_global_model(df, 'lightgbm', le_sm, le_sku)

pivot_lgb = global_lgb_df.pivot_table(values='mape',
index='supermarket', columns='sku')
pivot_lgb['Avg'] = pivot_lgb.mean(axis=1)
print('\n' + pivot_lgb.round(2).to_string())

======================================================================
==========
STRATEGY 5: GLOBAL LIGHTGBM
======================================================================
==========

Overall: MAPE=7.20%, MAE=5.60, RMSE=18.84, Accuracy(±10%)=84.2%

sku desperados heineken 0.0 heineken regular Avg

supermarket
albert-heijn 8.05 6.74 5.99 6.93
dirk 5.03 5.14 4.58 4.92
jumbo 17.26 8.00 3.43 9.56

8. Global Models: XGBoost vs LightGBM

gc = [Link]([global_xgb_df, global_lgb_df], ignore_index=True)
pivot_g = gc.pivot_table(values='mape', index=['supermarket','sku'],
columns='model', aggfunc='mean')
pivot_g['Winner'] = pivot_g.idxmin(axis=1)
print(pivot_g.round(2).to_string())

fig, axes = [Link](1, 2, figsize=(18, 7))

[Link]('Global Models: Feature Importance', fontsize=16,
fontweight='bold')

for ax, model, feat, title, color in [

(axes[0], global_xgb, feat_xgb, 'Global XGBoost', '#e63946'),
(axes[1], global_lgb, feat_lgb, 'Global LightGBM', '#6a0dad')
]:
imp = [Link](model.feature_importances_,
index=feat).sort_values().tail(15)
clr = ['#ff6b6b' if f in ['supermarket_encoded','sku_encoded']
else color for f in [Link]]
[Link]([Link], [Link], color=clr, alpha=0.8,
edgecolor='black')
ax.set_title(title, fontweight='bold'); [Link](True, alpha=0.3,
axis='x')
plt.tight_layout(); [Link]()

print(f'\n🏆 Global XGBoost Avg MAPE:

{global_xgb_df["mape"].mean():.2f}%')
print(f'🏆 Global LightGBM Avg MAPE: {global_lgb_df["mape"].mean():.2f}
%')

model Global LightGBM Global XGBoost

Winner
supermarket sku

albert-heijn desperados 8.05 5.99

Global XGBoost
heineken 0.0 6.74 7.48 Global
LightGBM
heineken regular 5.99 3.64
Global XGBoost
dirk desperados 5.03 4.78
Global XGBoost
heineken 0.0 5.14 5.55 Global
LightGBM
heineken regular 4.58 3.48
Global XGBoost
jumbo desperados 17.26 18.95 Global
LightGBM
heineken 0.0 8.00 7.33
Global XGBoost
heineken regular 3.43 2.79
Global XGBoost

🏆 Global XGBoost Avg MAPE: 6.66%

🏆 Global LightGBM Avg MAPE: 7.14%
9. Cross-Strategy Comparison
all_results = [Link]([s1_df, s2_df, s3_df, global_xgb_df,
global_lgb_df], ignore_index=True)

pivot_all = all_results.groupby(['strategy','model'])
['mape'].agg(['mean','min','max','count']).round(2)
pivot_all.columns = ['Avg MAPE%','Best','Worst','# Models']
print(pivot_all.to_string())

best = pivot_all['Avg MAPE%'].idxmin()

print(f'\n🏆 Best: {best[0]} / {best[1]} → {pivot_all.loc[best, "Avg
MAPE%"]}%')

strategy_order = ['Supermarket','SKU','SM×SKU','Global']
model_order = ['XGBoost','Prophet','Improved Prophet','Global
XGBoost','Global LightGBM']
colors = {'XGBoost':'#1f77b4','Prophet':'#ff7f0e','Improved
Prophet':'#2ca02c',
'Global XGBoost':'#e63946','Global LightGBM':'#6a0dad'}

plot_comparison(all_results, strategy_order, model_order, colors)

Avg MAPE% Best Worst # Models

strategy model
Global Global LightGBM 7.14 3.43 17.26 9
Global XGBoost 6.66 2.79 18.95 9
SKU Improved Prophet 23.49 18.38 29.17 3
Prophet 23.71 18.46 30.14 3
XGBoost 27.95 22.17 32.99 3
SM×SKU Improved Prophet 6.58 3.78 10.44 9
Prophet 7.25 4.06 12.98 9
XGBoost 16.57 3.98 53.14 9
Supermarket Improved Prophet 23.26 18.76 27.49 3
Prophet 23.14 18.29 27.50 3
XGBoost 26.33 21.50 28.78 3

🏆 Best: SM×SKU / Improved Prophet → 6.58%

10. Prediction Error Analysis
Raw error analysis across all models — before applying any acceptance threshold.

all_preds = [Link](s1_preds + s2_preds + s3_preds +

[global_xgb_pred, global_lgb_pred], ignore_index=True)

print(f'{"Model":<25} {"MAPE%":>8} {"MAE":>10} {"RMSE":>10} {"Median|

Err|%":>14}')
print('-'*78)
for mn in sorted(all_preds['model'].unique()):
sub = all_preds[all_preds['model'] == mn]
abs_pct = sub['percentage_error'].abs()
print(f'{mn:<25} {abs_pct.mean():>7.2f}%
{sub["error"].abs().mean():>9.2f} '
f'{[Link]((sub["error"]**2).mean()):>9.2f}
{abs_pct.median():>13.2f}% ')

#plot_error_analysis(all_preds, all_results, strategy_order,

model_order, colors)

Model MAPE% MAE RMSE Median|Err|

%
----------------------------------------------------------------------
--------
Global LightGBM 7.20% 5.60 18.84 3.81%
Global XGBoost 6.78% 5.35 19.12 3.29%
Improved Prophet 13.80% 18.18 35.96 6.53%
Prophet 14.20% 18.32 36.02 6.96%
XGBoost 21.03% 27.61 55.17 7.22%

10. Accuracy Analysis (Custom Acceptance Threshold)

Set CUSTOM_THRESHOLD below and re-run to see how accuracy changes at different tolerance
levels.

CUSTOM_THRESHOLD = 10

all_preds['is_acceptable'] = (all_preds['percentage_error'].abs() <=

CUSTOM_THRESHOLD).astype(int)

print('='*100)
print(f'ACCURACY ANALYSIS — Acceptance Threshold: ±{CUSTOM_THRESHOLD}
%')
print('='*100)

acc_summary = all_preds.groupby(['strategy','model']).apply(
lambda g: (g['percentage_error'].abs() <= CUSTOM_THRESHOLD).mean()
* 100
).round(1)
print(f'\nAccuracy Rate (% of predictions within ±{CUSTOM_THRESHOLD}%
error):')
print(acc_summary.unstack().fillna('-').to_string())

print(f'\nPer-Model Stats:')
for mn in sorted(all_preds['model'].unique()):
sub = all_preds[all_preds['model'] == mn]
acc = sub['is_acceptable'].mean() * 100
print(f' {mn:<25} {acc:>5.1f}% acceptable
({int(sub["is_acceptable"].sum()):>4}/{len(sub)})')

plot_accuracy_analysis(all_results, all_preds, strategy_order,

model_order, colors,
threshold=CUSTOM_THRESHOLD)

======================================================================
==============================
ACCURACY ANALYSIS — Acceptance Threshold: ±10%
======================================================================
==============================

Accuracy Rate (% of predictions within ±10% error):

model Global LightGBM Global XGBoost Improved Prophet Prophet
XGBoost
strategy

Global 84.2 88.1 - -

-
SKU - - 38.2 39.8
20.6
SM×SKU - - 86.2 82.0
85.3
Supermarket - - 37.2 37.2
25.3

Per-Model Stats:
Global LightGBM 84.2% acceptable (1049/1246)
Global XGBoost 88.1% acceptable (1098/1246)
Improved Prophet 65.4% acceptable (1509/2308)
Prophet 63.3% acceptable (1462/2308)
XGBoost 58.6% acceptable (1286/2196)
12. Export Predictions
export_predictions(
{'strategy1': s1_preds, 'strategy2': s2_preds, 'strategy3':
s3_preds},
global_preds=[global_xgb_pred, global_lgb_pred]
)

print(f'\n📁 All files in: {PRED_DIR}')

for f in sorted(PRED_DIR.glob('*.csv')):
print(f' {[Link]} ({[Link]().st_size/1024:.1f} KB)')

✅ strategy1: 1461 rows

✅ strategy2: 1461 rows
✅ strategy3: 3890 rows
✅ Combined: 9304 rows →
Implementation/predictions/all_predictions_combined.csv

📁 All files in: Implementation/predictions

all_predictions_combined.csv (1041.1 KB)
global_lightgbm_albert-heijn_desperados.csv (15.9 KB)
global_lightgbm_albert-heijn_heineken_0_0.csv (17.7 KB)
global_lightgbm_albert-heijn_heineken_regular.csv (16.0 KB)
global_lightgbm_dirk_desperados.csv (14.9 KB)
global_lightgbm_dirk_heineken_0_0.csv (16.7 KB)
global_lightgbm_dirk_heineken_regular.csv (15.4 KB)
global_lightgbm_jumbo_desperados.csv (16.5 KB)
global_lightgbm_jumbo_heineken_0_0.csv (16.0 KB)
global_lightgbm_jumbo_heineken_regular.csv (16.6 KB)
global_lightgbm_predictions.csv (145.0 KB)
global_xgboost_albert-heijn_desperados.csv (14.5 KB)
global_xgboost_albert-heijn_heineken_0_0.csv (16.3 KB)
global_xgboost_albert-heijn_heineken_regular.csv (14.8 KB)
global_xgboost_dirk_desperados.csv (13.7 KB)
global_xgboost_dirk_heineken_0_0.csv (15.3 KB)
global_xgboost_dirk_heineken_regular.csv (14.1 KB)
global_xgboost_jumbo_desperados.csv (15.1 KB)
global_xgboost_jumbo_heineken_0_0.csv (14.6 KB)
global_xgboost_jumbo_heineken_regular.csv (15.3 KB)
global_xgboost_predictions.csv (133.0 KB)
strategy1_predictions.csv (151.4 KB)
strategy2_predictions.csv (148.1 KB)
strategy3_predictions.csv (437.7 KB)

13. Final Recommendation

for strat in ['Supermarket','SKU','SM×SKU','Global']:
sub = all_results[all_results['strategy']==strat]
if len(sub) == 0: continue
print(f'\n ── {strat} Level ──')
 for mn in sub['model'].unique():
ms = sub[sub['model']==mn]
print(f' {mn:<25} Avg: {ms["mape"].mean():>6.2f}% Best:
{ms["mape"].min():>6.2f}% Worst: {ms["mape"].max():>6.2f}%')

best = pivot_all['Avg MAPE%'].idxmin()

print(f"""
{'='*100}
🏆 BEST OVERALL: {best[0]} / {best[1]} → {pivot_all.loc[best, 'Avg MAPE
%']}% Avg MAPE

Strategy Guide:
• Supermarket Level → Supply chain / distribution planning
• SKU Level → Product portfolio / production scheduling
• SM × SKU Level → Store inventory / targeted promotions
• Global Models → Simpler deployment (1 model for all)

📁 All predictions: Implementation/predictions/

""")

── Supermarket Level ──
XGBoost Avg: 26.33% Best: 21.50% Worst:
28.78%
Prophet Avg: 23.14% Best: 18.29% Worst:
27.50%
Improved Prophet Avg: 23.26% Best: 18.76% Worst:
27.49%

── SKU Level ──
XGBoost Avg: 27.95% Best: 22.17% Worst:
32.99%
Prophet Avg: 23.71% Best: 18.46% Worst:
30.14%
Improved Prophet Avg: 23.49% Best: 18.38% Worst:
29.17%

── SM×SKU Level ──
XGBoost Avg: 16.57% Best: 3.98% Worst:
53.14%
Prophet Avg: 7.25% Best: 4.06% Worst:
12.98%
Improved Prophet Avg: 6.58% Best: 3.78% Worst:
10.44%

── Global Level ──
Global XGBoost Avg: 6.66% Best: 2.79% Worst:
18.95%
Global LightGBM Avg: 7.14% Best: 3.43% Worst:
17.26%
======================================================================
==============================
🏆 BEST OVERALL: SM×SKU / Improved Prophet → 6.58% Avg MAPE

Strategy Guide:
• Supermarket Level → Supply chain / distribution planning
• SKU Level → Product portfolio / production scheduling
• SM × SKU Level → Store inventory / targeted promotions
• Global Models → Simpler deployment (1 model for all)

📁 All predictions: Implementation/predictions/