Step_04_Building_Model_Database - Construction de la base de données du modèle¶

Vue d'ensemble¶

Cette étape construit la base de données du modèle promotionnel en deux phases distinctes : le scoping (main_scoping) qui filtre et enrichit les données, puis la modélisation (main_modelling) qui calcule les baselines selon différentes méthodologies. C'est l'étape la plus complexe de la pipeline avec des traitements spécifiques par business unit.

Objectif principal¶

Filtrer les données selon des seuils de volume et d'intensité promotionnelle
Enrichir les données avec des informations de référence (catégories, groupes retailers)
Calculer les volumes ajustés et la marge brute ajustée
Déterminer la méthodologie de baseline appropriée (Standard/Statistical)
Calculer les volumes de baseline pour chaque produit/semaine
Gérer les cas spéciaux (In&Out, valeurs manquantes, redistribution volumes)

Position dans la pipeline¶

Étape précédente : Step_03_Data_Cleaning_and_Data_Quality_Checks
Étape suivante : Step_05_Building_Baseline
Appels distincts :
main_scoping : Filtrage et enrichissement
main_modelling : Calcul des baselines

Architecture technique¶

Flux de données - Phase Scoping¶

Entrée (Tables step_2_03_*)
    │
    ├── Configuration (promo_config.json)
    │   ├── Volume_filtering
    │   ├── Years_in_scope_for_tool_refresh
    │   └── Promo_weight_thresholds
    │
    └── Tables de référence
        ├── Sub_Category_Mapping_Italy
        ├── Retailer_Group_Italy
        └── Retailer_group_classification_parmalat

Traitement Scoping
    │
    ├── 1. Ajustement volumes low-volume retailers
    │   ├── Calcul share cumulé (seuil 25%)
    │   └── Thresholds par catégorie (arrondi à 5)
    │
    ├── 2. Calcul marge brute ajustée
    │   ├── Coûts unitaires moyens
    │   └── Gestion années incomplètes
    │
    └── 3. Agrégation sans Promotion_Code
        ├── Volumes semaine totaux/promo
        └── Flags promo

Sortie
    │
    ├── step_3_00_sell_in (détail avec Promotion_Code)
    └── step_3_01_after_scoping (agrégé par produit/semaine)

Flux de données - Phase Modelling¶

Entrée (step_3_01_after_scoping)
    │
    ├── Paramètres méthodologiques
    │   ├── Promo_intensity_limits (Low/Mid/High)
    │   ├── Outliers_percentiles
    │   ├── Buying_period_windows
    │   └── Number_of_weeks_lags_baseline
    │
    └── Données spécifiques
        ├── Statistical_methodology_parameters_parmalat
        ├── Forward_Buying_weeks_window_parmalat
        └── In_Out_Comparable_products_*

Traitement Modelling
    │
    ├── 1. Classification intensité promo
    │   ├── Calcul Promo_weight annuel
    │   ├── Calcul Promo_intensity annuel
    │   └── Clusters: Low/Mid/High
    │
    ├── 2. Méthodologie Standard (Low/Mid)
    │   ├── Flags outliers/pre/post-promo
    │   ├── Identification semaines qualifiées
    │   └── Moyenne N dernières semaines
    │
    ├── 3. Méthodologie Statistical (High)
    │   ├── Segmentation Q1/Q2Q3/Q4 (Galbani)
    │   ├── Segmentation H/L par catégorie (Parmalat)
    │   └── Calcul avec coefficient variation
    │
    └── 4. Ajustements finaux
        ├── In&Out rules
        ├── Valeurs manquantes/négatives
        └── Redistribution volumes (Parmalat)

Sortie
    │
    └── step_3_02_model_data_baseline
        ├── Baseline_Volume calculé
        ├── Baseline_methodology appliquée
        └── Flags et métriques statistiques

Concepts clés¶

1. Ajustement volumes low-volume retailers¶

Pour chaque année : - Retailers triés par volume total croissant - Identification des 25% premiers (share cumulé) - Pour ces retailers : calcul seuil par catégorie - Volumes < seuil → Adjusted_Volumes = 0

2. Calcul marge brute ajustée¶

Adjusted_gross_margin = 
    Net_net_sales
    + Promo_funding_off_invoice_Detail_6
    + (Average_unit_Costs_Product_and_Other * Adjusted_Volumes)
    + Logistics_Cost
    - (Average_unit_Costs_Level_Two * Adjusted_Volumes)
    + (Supply_chain_gap * Adjusted_Volumes)

3. Classification intensité promotionnelle¶

Promo_weight = Volume promo annuel / Volume total annuel
Promo_intensity = Nb semaines promo / Nb semaines avec ventes > 0
Clusters :
Low : ≤ seuil inférieur → Standard
Mid : entre seuils → Standard
High : > seuil supérieur → Statistical

4. Méthodologies de baseline¶

Standard¶

Semaines qualifiées : pas outlier, pas promo, pas post-promo
Baseline = moyenne des N dernières semaines qualifiées
N défini par Number_of_weeks_lags_baseline

Statistical¶

Segmentation par saison
Exclusion outliers par percentiles
Baseline selon coefficient de variation (VC) :
VC < 0.40 : Avg - 2×Std (Galbani) ou Avg - 1.5×Std (Parmalat)
0.40 ≤ VC ≤ 1.00 : Avg - Std
VC > 1.00 : Avg - 0.5×Std

5. Spécificités business unit¶

Galbani¶

Agrégation : Retailer_name × EAN × EAN_desc
Pas de flag pre-promo (données sell-in)
Segmentation : Q1, Q2Q3, Q4
In&Out : substitution avec ratio format

Parmalat¶

Agrégation : Retailer_classification × EAN (pas EAN_desc)
Forward buying windows dynamiques
Segmentation : H/L selon semaines par catégorie
Redistribution volumes avant baseline
In&Out : BRICK vs VALIGETTA

Implémentation détaillée - Phase Scoping¶

1. Fonction principale : `main_scoping()`¶

def main_scoping():
    countries_parameters_df = get_country_specific_parameters_from_json(json_path)

    for index, row in countries_parameters_df.iterrows():
        country = row['Country']
        apply_scoping(engine, countries_parameters_df, country)

2. Scoping Galbani : `apply_scoping_sell_in_italy_galbani()`¶

Processus : 1. Lecture données depuis step_2_03_sell_in_base 2. Ajout Year/Week depuis Period 3. Ajustement volumes low-volume retailers 4. Calcul marge brute ajustée 5. Enrichissement avec High_category et Retailer_group 6. Sauvegarde détaillée → step_3_00_sell_in 7. Agrégation sans Promotion_Code → step_3_01_after_scoping

Colonnes clés agrégation : - Week_total_volume : somme Adjusted_Volumes - Week_promo_volume : somme si Flag_Promo = 1 - Flag_Promo : 1 si Week_promo_volume > 0

3. Scoping Parmalat : `apply_scoping_sell_in_italy_parmalat()`¶

Adaptation colonnes :

# Pour compatibilité avec fonctions Galbani
df['Retailer_name_original'] = df['Retailer_name']  # Backup
df['Retailer_name'] = df['Retailer_classification']  # Pour calculs

Agrégation différente : - Groupby : Retailer_name (= classification) × EAN × Year × Week - Pas d'EAN_desc dans les clés

Implémentation détaillée - Phase Modelling¶

1. Fonction principale : `main_modelling()`¶

def main_modelling():
    countries_parameters_df = get_country_specific_parameters_from_json(json_path)

    for index, row in countries_parameters_df.iterrows():
        country = row['Country']
        apply_data_modelling(engine, countries_parameters_df, country)

2. Modelling Galbani : `data_model_sell_in_italy_galbani()`¶

Étapes clés :

Calcul métriques annuelles
Promo_weight et Promo_intensity par année
Classification en clusters Low/Mid/High
Ajout semaines manquantes
Cartesian product : combinaisons × 52 semaines
Remplissage volumes = 0 pour nouvelles lignes
Baseline Standard
Extension années si nécessaire (année -1)
Flags : outliers, post-promo (pas pre-promo)
Unique_Week_ID pour calcul séquentiel
Baseline sur semaines qualifiées
Baseline Statistical
Appel statistical_baseline_computation()
Traitement par segment Q1/Q2Q3/Q4
KPIs : Avg, Std, VC par segment
Ajustements
adjust_missing_weeks_standard_methodology() : recalcul Statistical si Standard échoue
manage_non_relevant_baseline_values() : 25e percentile si baseline ≤ 0
apply_in_out_rules() : substitution ou nullification

3. Modelling Parmalat : `data_model_sell_in_italy_parmalat()`¶

Spécificités majeures :

Redistribution volumes

# AVANT calcul baseline
df = redistribute_volumes_for_baseline_parmalat(df, engine)

Pattern : volumes consécutifs + 5 zéros
Si avg consécutif ≥ avg saisonnier → redistribution
Utilisé UNIQUEMENT pour baseline, pas autres KPIs
Paramètres saisonniers
Table Statistical_methodology_parameters_parmalat
52 colonnes (semaines) avec valeurs H/L
2 saisons par catégorie/année

Critères semaines qualifiées

# Vérification post-calcul Standard
standard_df, transfer_to_statistical = check_qualified_weeks_criteria_parmalat(
    standard_df, category_periods
)

Même saison ET dans 2 mois précédents
Sinon → transfert vers Statistical
In&Out rules spécifiques
Seuil : selling ≤ 41% ET promo > 90%
BRICK → baseline = 0
VALIGETTA → check substitute (0 si InOut ou absent)

4. Fonctions utilitaires clés¶

`adjust_volumes_based_on_threshold()`¶

Calcul retailers low-volume par année
Moyenne volumes non-promo par catégorie
Arrondi supérieur multiple de 5
Application seuil uniquement aux low-volume retailers

`compute_baseline_volume()` / `compute_baseline_volume_parmalat()`¶

Parcours séquentiel du groupe
Pour chaque semaine : recherche N dernières qualifiées
Si insuffisant : baseline = NaN
Gestion Unique_Week_ID pour continuité cross-year

`statistical_baseline_computation()` / `statistical_baseline_computation_parmalat()`¶

Galbani : 3 segments (Q1, Q2Q3, Q4)
Parmalat : 2 segments par catégorie (H, L)
Exclusion outliers par percentiles
Calcul VC et baseline selon règles

Gestion des erreurs¶

Types d'erreurs gérés¶

Type	Traitement	Impact
Fonction pays manquante	Fallback vers default	Continuation
Colonnes manquantes	Ajout avec fillna	Transparent
Division par zéro	Safe_divide → 0	Calculs protégés
Baseline manquant	Recalcul ou normalisation	Ajustement auto

Points de vérification Parmalat¶

# Vérifications critiques
if 'Baseline_Volume_Redistributed' not in df.columns:
    raise ValueError("ERROR: Column missing!")

# Vérification substitution volumes
if abs(after_replacement_sum - redistributed_sum) > 0.01:
    raise ValueError("ERROR: Volume replacement failed!")

Performance et optimisation¶

1. Calculs vectorisés¶

np.select() pour classifications conditionnelles
Operations groupby avec transform/agg
Éviter boucles sur DataFrame entier

2. Gestion mémoire¶

Drop colonnes temporaires après usage
Chunks pour insertions DB (10K lignes)
Filtrage précoce des données

3. Optimisations Parmalat¶

Pré-calcul lookups In&Out
Dictionnaires pour substitutions
Application vectorisée des updates

Points d'attention maintenance¶

1. Cohérence agrégations¶

Galbani : toujours avec EAN_desc
Parmalat : jamais EAN_desc, utiliser Retailer_classification

2. Ordre des opérations¶

Calcul métriques sur volumes ORIGINAUX
Redistribution (Parmalat uniquement)
Calcul baseline sur volumes redistributés
Restauration volumes originaux

3. Gestion années incomplètes¶

Détection : < 52 semaines
Action : utiliser année précédente pour certains calculs
Impact : Average_unit_Costs_Level_Two

4. Seuils et paramètres¶

Tous dans promo_config.json : - Promo_intensity_mid_lower_limit : défaut 0.60 - Promo_intensity_mid_upper_limit : défaut 0.75 - Outliers_percentile_lower_limit : défaut 0.05 - Number_of_weeks_lags_baseline : défaut 3

Troubleshooting¶

Problème : Baseline = NaN pour beaucoup de lignes¶

Causes : - Pas assez de semaines qualifiées - Données historiques insuffisantes - Critères trop restrictifs

Solutions : 1. Vérifier flags (outliers, promo, post-promo) 2. Réduire Number_of_weeks_lags_baseline 3. Vérifier seuils outliers

Problème : Row count mismatch (Parmalat)¶

Symptôme : Message "WARNING: Row count mismatch"

Vérifications :

-- Vérifier agrégations
SELECT COUNT(*), COUNT(DISTINCT CONCAT(Retailer_name, '|', EAN, '|', Year))
FROM step_3_01_after_scoping;

Problème : Redistribution non appliquée¶

Diagnostic :

# Vérifier colonne présente
'Baseline_Volume_Redistributed' in df.columns

# Vérifier valeurs différentes
(df['Week_total_volume'] != df['Baseline_Volume_Redistributed']).sum()

Problème : In&Out trop agressif¶

Symptômes : Beaucoup de baselines à 0

Analyse :

SELECT InOut_Status, COUNT(DISTINCT EAN) 
FROM step_3_02_model_data_baseline
GROUP BY InOut_Status;

Exemples d'utilisation¶

Ajout nouveau pays avec sell-out¶

Créer apply_scoping_sell_out_{country}() ou utiliser default
Créer data_model_sell_out_{country}() ou utiliser default
Adapter si structure données différente

Dans promo_config.json :

{
    "Country": "New_Country",
    "Promo_intensity_mid_lower_limit": 0.50,  // Au lieu de 0.60
    "Promo_intensity_mid_upper_limit": 0.70   // Au lieu de 0.75
}

Debug redistribution Parmalat¶

# Activer logs détaillés dans redistribute_volumes_for_baseline_parmalat()
log_message(f"  Redistributed {consecutive_sum:.0f} over {total_weeks} weeks "
            f"(avg {consecutive_avg:.1f} > season {season} avg {comparison_avg:.1f})")

Forcer méthodologie Statistical¶

# Dans data_model_sell_in_*
# Après classification normale :
df.loc[df['EAN'] == 'PROBLEMATIC_EAN', 'Baseline_methodology'] = 'Statistical'