Aller au contenu

Pipeline Excellence Promotionnelle Lactalis

Diagrammes

Diagrammes Step 05 - Building Baseline Effects¶

Vue d'ensemble du processus¶

graph TB
    Start([main]) --> VE[main_volume_effects]
    VE --> QC[main_quality_check]

    subgraph "Calcul des effets de volume"
        V1[Uplift Volume]
        V2[Cannibalization Volume]
        V3[Forward Buying<br/>Galbani uniquement]
        V4[Halo & Pantry Loading<br/>Initialisation]

        V1 --> V2 --> V3 --> V4
    end

    subgraph "Quality Check"
        Q1[Stock-up effect analysis]
        Q2[Base 100 normalization]
        Q3[Export CSV results]

        Q1 --> Q2 --> Q3
    end

    VE --> V1
    V4 --> QC
    QC --> Q1
    Q3 --> End([Fin])

Dispatcher selon business unit¶

flowchart TD
    Start[apply_volume_effects_computation]

    Check1{Sell_out_availability?}
    Start --> Check1

    Check1 -->|1| SellOut[compute_volume_effects_with_sell_out]
    Check1 -->|0| Check2{Business Unit?}

    Check2 -->|italy_galbani| Galbani[compute_volume_effects_italy_galbani]
    Check2 -->|italy_parmalat| Parmalat[compute_volume_effects_italy_parmalat]
    Check2 -->|autres| Skip[Log: Unknown BU]

    style Galbani fill:#e3f2fd
    style Parmalat fill:#fff3e0
    style SellOut fill:#e8f5e9

Calcul Uplift Volume¶

stateDiagram-v2
    [*] --> Check: Pour chaque ligne

    Check --> PromoCheck: Flag_Promo = 1?

    PromoCheck --> BaselineCheck: OUI
    PromoCheck --> SetNaN: NON

    BaselineCheck --> HasBaseline: Baseline existe?

    HasBaseline --> CalcUplift: OUI
    HasBaseline --> SetNaN: NON

    CalcUplift --> Formula: Volume - Baseline

    Formula --> CheckNegative: Uplift < 0?

    CheckNegative --> SetNaN2: OUI
    CheckNegative --> KeepValue: NON

    SetNaN --> Result: Uplift = NaN
    SetNaN2 --> Result
    KeepValue --> Result: Uplift = valeur

    Result --> [*]

Calcul Cannibalization Volume¶

flowchart LR
    subgraph "Conditions"
        C1(Flag_qualified_week = 1)
        C2(Flag_Outliers = 0)
        C3{Conditions remplies?}

        C1 --> C3
        C2 --> C3
    end

    subgraph "Calcul"
        CALC(Volume - Baseline)
        MIN("min(résultat, 0)")

        CALC --> MIN
    end

    C3 -->|OUI| CALC
    C3 -->|NON| NAN(Cannib = NaN)
    MIN --> RESULT(Cannib_Volume)

    style MIN fill:#ffebee

Forward Buying Galbani - Vue d'ensemble¶

graph TB
    subgraph "1. Delta Promo Intensity"
        D1[Sell-in intensity]
        D2[Sell-out intensity<br/>Channel spécifique]
        D3[Delta = SI - SO]
        D4[Si < 0 → 0]

        D1 --> D3
        D2 --> D3
        D3 --> D4
    end

    subgraph "2. Forward Buying Factors"
        F1[Classification par<br/>sous-catégorie]
        F2[Risk mapping<br/>low/mid/high]
        F3[Factors<br/>0.2/0.4/0.6]

        F1 --> F2 --> F3
    end

    subgraph "3. Calcul final"
        C1[Delta_adjusted =<br/>Delta × Factor]
    end

    D4 --> C1
    F3 --> C1

sequenceDiagram
    participant Main as compute_volume_effects
    participant Fetch as fetch_promo_intensities
    participant DB as Database

    Main->>Fetch: Request promo intensities

    Note over Fetch: Sell-in aggregation
    Fetch->>DB: SELECT New_category, EAN, Year,<br/>SUM(volumes) FROM step_3_01_after_scoping<br/>GROUP BY ...
    DB-->>Fetch: Sell-in data

    Note over Fetch: Sell-out aggregation
    Fetch->>DB: SELECT EAN, Year, SUM(volumes)<br/>FROM sell_out_base<br/>WHERE Channel = 'IS+LSP (100-399mq) (7002)'
    DB-->>Fetch: Sell-out data

    Fetch->>Fetch: Calculate intensities
    Fetch->>Fetch: Merge on EAN, Year
    Fetch->>Fetch: Delta = SI - SO

    alt Missing Delta - Case 1
        Fetch->>Fetch: Calculate category average<br/>for matched EANs
        Fetch->>Fetch: Fill missing with avg
    else Missing Delta - Case 2
        Fetch->>DB: Get SUB_CAT mapping
        Fetch->>Fetch: Calculate at SUB_CAT level
        Fetch->>Fetch: Average by New_category
    end

    Fetch-->>Main: DataFrame with Delta values

Forward Buying Factors Mapping¶

graph LR
    subgraph "Risk Classification"
        R1[Forward_buying_classification<br/>SUB_CAT_PROD_LIV2<br/>M7_SUB_CAT_PROD<br/>Forward_buying_risk]
    end

    subgraph "Factor Mapping"
        M1[low → 0.2]
        M2[medium → 0.4]
        M3[high → 0.6]
    end

    subgraph "EAN Assignment"
        E1[sell_in_base<br/>EAN, EAN_desc<br/>Sub_Category_1/2]
        E2[Merge on<br/>sub-categories]
        E3[EAN → Factor]
    end

    R1 --> M1
    R1 --> M2
    R1 --> M3

    E1 --> E2
    M1 --> E2
    M2 --> E2
    M3 --> E2
    E2 --> E3

Parmalat - Initialisation¶

stateDiagram-v2
    [*] --> CalcUplift: compute_volume_effects_italy_parmalat

    CalcUplift --> InitHalo: Calculer Uplift (standard)

    InitHalo --> SetNaN: Halo_Volume = NaN

    SetNaN --> InitPantry: Pantry_Loading_Volume = NaN

    InitPantry --> CalcCannib: Calculer Cannibalization

    CalcCannib --> Save: Sauvegarder table

    Save --> [*]: step_4_01_model_with_effects_baseline

    note right of InitHalo
        Halo et Pantry Loading
        seront calculés dans
        Step 6.01 après
        promotion code carryover
    end note

Sell-out - Halo & Pantry Loading¶

flowchart TD
    Param{"Halo_and_pantry_loading<br/>computation = 1?"}

    Param -->|OUI| Calc
    Param -->|NON| Init("Halo = NaN<br/>Pantry = NaN")

    subgraph Calc[Calcul effectif]
        C1(Flag_Buying_period_post_promo = 1)
        C2(Flag_Outliers = 0)
        C3{Conditions OK?}

        C1 --> C3
        C2 --> C3

        C3 -->|OUI| Delta("Δ = Volume - Baseline")
        C3 -->|NON| SetNaN(NaN)

        Delta --> Halo("Halo = max(Δ, 0)")
        Delta --> Pantry("Pantry = min(Δ, 0)")
    end

Quality Check - Stock-up Effect¶

sequenceDiagram
    participant QC as quality_check_stock_up_effect
    participant DB as Database
    participant Calc as Calculator

    QC->>DB: Load step_3_02_model_data_baseline
    DB-->>QC: DataFrame

    QC->>QC: Filter years_in_scope

    loop Pour chaque groupe (Brand×Category×Year×EAN×Retailer)
        QC->>Calc: Calculate averages

        Note over Calc: Base: Flag_qualified_week=1
        Calc->>Calc: base_avg = mean(volumes)

        Note over Calc: Promo: Flag_Promo=1
        Calc->>Calc: promo_avg = mean(volumes)

        Note over Calc: Post-promo weeks
        Calc->>Calc: week+1 avg after promo
        Calc->>Calc: week+2 avg after promo
        Calc->>Calc: week+3 avg after promo

        Calc->>Calc: Normalize to base 100
        Calc-->>QC: Results
    end

    QC->>QC: Aggregate by Brand×Category×Year
    QC->>QC: Export to CSV

Structure des résultats Quality Check¶

graph TD
    subgraph "Données calculées"
        D1("No_promo = 100<br/>Base de référence")
        D2("Promo = Index pendant promo")
        D3("Week_plus_one = Index S+1")
        D4("Week_plus_two = Index S+2")
        D5("Week_plus_three = Index S+3")
    end

    subgraph "Agrégation finale"
        A1("GROUP BY<br/>Year, Brand, Category")
        A2("MEAN de tous les index")
    end

    subgraph "Export CSV"
        E1("qc1_stock_up_effect_{bu}_{country}.csv")
        E2("Séparateur: ';'")
    end

    D1 --> A1
    D2 --> A1
    D3 --> A1
    D4 --> A1
    D5 --> A1

    A1 --> A2 --> E1

Flow complet avec types de données¶

graph LR
    subgraph "Input"
        I1[(step_3_02_model_data_baseline)]
        I2[promo_config.json]
    end

    subgraph "Processing"
        P1[Type casting<br/>Year: int<br/>Volumes: float<br/>Flags: float]
        P2[Calculs vectorisés]
        P3[Gestion NaN]
    end

    subgraph "Output"
        O1[(step_4_01_model_with_effects_baseline)]
        O2[Quality checks/<br/>qc1_stock_up_effect_*.csv]
    end

    I1 --> P1
    I2 --> P1
    P1 --> P2
    P2 --> P3
    P3 --> O1
    P3 --> O2

    style O1 fill:#90EE90
    style O2 fill:#fff9c4

Points clés par business unit¶

mindmap
  root((Step 05<br/>Baseline<br/>Effects))
    Galbani
      Forward Buying
        Delta promo intensity
        Channel IS+LSP
        Factors par sous-cat
        Delta_adjusted final
      Uplift/Cannib
        Calculs standards
        Neutralisation négatifs
      Pas de Halo/Pantry
        Sans sell-out
    Parmalat
      Pas de Forward Buying
        Calculé en Step 6.01
      Uplift/Cannib
        Calculs standards
      Halo/Pantry = NaN
        Besoin promo codes
        Step 6.01
    Sell-out
      Paramètre config
        Halo_and_pantry_loading
      Si activé
        Post-promo effects
        Max/Min avec baseline
      Cannibalization
        Standard

Gestion des erreurs et cas limites¶

flowchart TD
    subgraph "Tables manquantes"
        T1[table_exists check]
        T2[Skip avec log]
        T1 -->|False| T2
    end

    subgraph "Colonnes adaptatives"
        C1{Sales_Volumes<br/>existe?}
        C2[Utiliser Sales_Volumes]
        C3[Utiliser Week_total_volume]
        C1 -->|OUI| C2
        C1 -->|NON| C3
    end

    subgraph "Valeurs manquantes"
        V1[Delta missing]
        V2[Category average]
        V3[Sub-category average]
        V1 --> V2
        V2 -->|Still missing| V3
    end

    subgraph "Protection calculs"
        P1[Division par zéro]
        P2[Valeurs infinies]
        P3[Return NaN]
        P1 --> P3
        P2 --> P3
    end