Automated Planogram Optimization: Maximizing Retail Shelf Efficiency Using Python and MILP

In the fast-moving consumer goods (FMCG) and retail sectors, maximizing shelf space efficiency isn’t just about stacking products — it’s a multi-million dollar data science problem. Beverage giants like Coca-Cola FEMSA (KOF) rely on strict operational, strategic, and aesthetic rules to dictate exactly where each bottle sits inside a retail cooler. This layout framework is known as a Planogram.

Figure 1: Maximizing retail shelf real estate via mathematical programming and Python

Manually designing planograms across thousands of unique retail outlets with varying cooler models is virtually impossible. Traditional programmatic approaches like nested loops or heuristic “greedy” algorithms often fail because they get tangled in infinite logical loops trying to satisfy dozens of competing constraints.

In this comprehensive guide, we will build an automated, production-ready Planogram Optimization Engine from scratch using Python and Mixed-Integer Linear Programming (MILP) via the open-source PuLP library.

Why Choose MILP Over Traditional Rules or Machine Learning?

When software engineers face optimization problems, they often attempt to use rules-based algorithms (e.g., sort items by priority and place them until the shelf is full). However, when you add real-world physical constraints — such as avoiding the “Dead Zone” behind a glass door frame — greedy algorithms break down and miss the global optimum.

On the other hand, Machine Learning (ML) is excellent for predicting demand or sales trends, but it cannot guarantee that strict operational boundaries are respected.

Mixed-Integer Linear Programming (MILP) bridges this gap. It is a mathematical modeling technique that searches an entire solution space to find the absolute best (optimal) arrangement of variables according to a defined objective function, ensuring every single operational constraint is mathematically satisfied simultaneously.

1. Understanding the Input Data Schema

To build a data-driven optimizer, our code ingests structural and commercial information across several master Excel sheets. Understanding this relational schema is vital before translating business metrics into linear math:

• Product Master (prod_master): Contains SKU dimensions like Width (mm), Height (mm), manufacturer tracking, and package designations like SS/MS (Single-Serve vs. Multi-Serve).
• Brand & Pack Type Masters: Define commercial variables like Brand Filling Order and Pack Filling Order, which dictate marketing priorities.
• Cooler Master (cooler_master): Outlines physical parameters including MaxWidth (mm), Number of Shelves, Number of Doors, and structural capacities like SS-Facings per Shelves or MS-Facings per Shelves.
• PicOs Mapping (picos_mapping): Identifies mandatory Picture of Success items flagged as "SI" (Yes) that must appear in specific store segments.

Figure 2: Data ingestion pipeline and master data schema merge mapping

2. Mathematical Formulation

Before translating logic into Python code, we must express our system using standard mathematical optimization notation. This makes our model agnostic and easily interpreted by any underlying linear solver (like CBC, Gurobi, or CPLEX).

Figure 3: Operational rule categories mapped directly to the optimization engine

Indices and Sets

• i in I: Set of all available SKUs (Stock Keeping Units).
• j in J: Set of shelves inside the target cooler.

Decision Variables

• x {i,j} in {0, 1, 2, …}: Integer variable; the number of facings (visible item slots) of SKU i placed on shelf j.
• y {i,j} in {0, 1}: Binary variable; evaluates to 1 if SKU i is assigned to shelf j, and 0 otherwise.
• pos {i,j} ≥0: Continuous variable; tracks the exact horizontal starting position coordinate (in mm) of SKU i on shelf j.
• d_side {i,j} in {0, 1}: Binary variable used for spatial routing to determine if an item sits entirely to the left (0) or right (1) of a central door frame.

The Objective Function

Our core goal is to maximize the combined strategic priority scores of the displayed items across the cooler layout, implicitly encouraging high space density.

Figure 4: Objective Function

Note: We subtract the priority score from a high base constant (10,000) because filling orders use lower numbers (e.g., #1 priority) for premium items. This inversion guarantees that the mathematical solver assigns higher reward weights to primary products.

3. Step-by-Step Python Implementation

Step 3.1: Data Ingestion and Strategic Priority Scoring

We ingest our data frames and apply business logic rules. We restrict item selections strictly to manufacturer exclusivity (Manufacturer == 'KOF'). We then compute a deterministic Priority_Score where Brand hierarchy takes precedence over packaging layouts.

import pandas as pd
from pulp import *

# =============================================================================
# 1. DATA LOADING & PRE-PROCESSING
# =============================================================================
prod_master   = pd.read_excel("input_data/Product_Master.xlsx")
brand_master  = pd.read_excel("input_data/Brand_Master.xlsx")
pack_master   = pd.read_excel("input_data/Pack_Type_Master.xlsx")
cooler_master = pd.read_excel("input_data/Cooler_Master.xlsx")
picos_mapping = pd.read_excel("input_data/SKU_PicOs_Mapping.xlsx")
outlet_master = pd.read_excel("input_data/Outlet_Master.xlsx")
channel_map   = pd.read_excel("input_data/Channel_Cooler_Mapping.xlsx")

# Apply Manufacturer Exclusivity Rule
sku_data = prod_master[prod_master['Manufacturer'] == 'KOF'].copy()
brand_master = brand_master.drop('Flavour', axis=1).drop_duplicates(subset=['Brand'])
sku_data = sku_data.merge(brand_master, on='Brand').merge(pack_master, on='Pack Type')

sku_data['SKU Description'] = sku_data['SKU ID'].astype('string') + '~' + sku_data['Brand'] + '~' + sku_data['Flavour']

# Formula: Structural priority ordering sequence
sku_data['Priority_Score'] = (sku_data['Brand Filling Order'] * 100) + sku_data['Pack Filling Order']

Step 3.2: Target Extraction and PicOs Priority Adjustments

Next, we isolate a specific target store environment (SELECTED_OUTLET_ID = 3913), resolve its assigned physical cooler model, and isolate mandatory products.

Get Somnath Das’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To ensure the engine prioritizes mandatory items, we apply a significant priority boost by subtracting 5,000 from their scores. This guarantees they jump to the front of the line during optimization.

# =============================================================================
# 2. SCENARIO CONFIGURATION
# =============================================================================
SELECTED_OUTLET_ID = 3913 
target_outlet = outlet_master[outlet_master['Outlet Segment ID'] == SELECTED_OUTLET_ID].iloc[0]
target_segment = target_outlet['Outlet Segment ID']

# Link store profiles to physical assets
assigned_model = channel_map[channel_map['Outlet Segment ID'] == target_segment]['Cooler ID'].values[0]
target_cooler = cooler_master[cooler_master['Cooler ID'] == assigned_model].iloc[0]

# Isolate mandatory items
current_picos = picos_mapping[picos_mapping['Outlet Segment ID'] == target_segment]
mandatory_skus = current_picos[current_picos['PicOs'] == 'SI']['SKU ID'].tolist()

# Apply strategic priority boost
sku_data['Adjusted_Score'] = sku_data['Priority_Score']
sku_data = sku_data.set_index('SKU ID') 
for sku_id in mandatory_skus:
    if sku_id in sku_data.index:
        sku_data.at[sku_id, 'Adjusted_Score'] -= 5000

Step 3.3: Instantiating the MILP Model and Decision Variables

Using PuLP, we initialize our maximization problem and instantiate our decision variable dictionaries.

# =============================================================================
# 3. OPTIMIZATION MODEL INSTANTIATION
# =============================================================================
prob = LpProblem("Planogram_Optimization_Engine", LpMaximize)

SKUs = sku_data.index.tolist()
Shelves = list(range(1, int(target_cooler['Number of Shelves']) + 1))

# Decision Variable Dictionaries
x = LpVariable.dicts("Facings", (SKUs, Shelves), 0, 10, LpInteger)      # Facings per SKU/Shelf
y = LpVariable.dicts("Present", (SKUs, Shelves), 0, 1, LpBinary)        # SKU Presence indicator
pos = LpVariable.dicts("Position", (SKUs, Shelves), 0, 5000, LpContinuous) # Horizontal position coordinate
d_side = LpVariable.dicts("DoorSide", (SKUs, Shelves), 0, 1, LpBinary)  # Door partitioning routing flag

# Set Objective Function
prob += lpSum([(10000 - sku_data.at[i, 'Adjusted_Score']) * x[i][j] for i in SKUs for j in Shelves])

Step 3.4: Programming the Constraints (Physical, Segregation, and Flow)

This section handles the core logic of the model. We map physical linear limits, enforce structural single-serve versus multi-serve partitioning rules, and configure left-to-right alignment parameters.

# =============================================================================
# 4. CONSTRAINT IMPLEMENTATION
# =============================================================================
TOTAL_WIDTH = target_cooler['MaxWidth (mm) (Internal dimension)']
NUM_DOORS = target_cooler['Number of Doors']
DOOR_BREAK = TOTAL_WIDTH / NUM_DOORS
FRAME_MM = target_cooler['Frame Width/Height (CM)'] * 10
BIG_M = 10000 # The mathematical "Big M" activation constant

for j in Shelves:
    # 1. Total Width Limit: Sum of (Facings * Width) must fit within physical shelf walls
    prob += lpSum([x[i][j] * sku_data.at[i, 'Width (mm)'] for i in SKUs]) <= TOTAL_WIDTH
    
    # 2. Right-Wall Alignment: Coordinate bounds tracking
    for i in SKUs:
        prob += pos[i][j] + (x[i][j] * sku_data.at[i, 'Width (mm)']) <= TOTAL_WIDTH
    
    # 3. Facing Limits & SS/MS Shelf Segregation
    is_ms_shelf = (j >= 4) # Top 3 shelves are Single-Serve, Shelves 4+ are Multi-Serve
    limit = target_cooler['MS-Facings per Shelves'] if is_ms_shelf else target_cooler['SS-Facings per Shelves']
    prob += lpSum([x[i][j] for i in SKUs]) <= limit
    
    for i in SKUs:
        sku_type = sku_data.at[i, 'SS/MS']
        if is_ms_shelf and "SS" in str(sku_type):
            prob += x[i][j] == 0
        if not is_ms_shelf and "MS" in str(sku_type):
            prob += x[i][j] == 0

    # 4. Sequential Flow: Left-to-right alignment mapping based on priority order
    sorted_skus = sku_data.sort_values('Adjusted_Score').index.tolist()
    for idx in range(len(sorted_skus) - 1):
        i_curr = sorted_skus[idx]
        i_next = sorted_skus[idx+1]
        prob += pos[i_next][j] >= pos[i_curr][j] + (x[i_curr][j] * sku_data.at[i_curr, 'Width (mm)'])

Figure 5: Horizontal coordinate translation mapping for adjacent item facings

Deep Dive: Understanding the “Big M” and Door Frame Dead Zones

One of the most complex challenges in physical layout modeling is accounting for physical obstacles, like vertical metal door frames. Products placed directly behind these frames fall into a visual Dead Zone.

To handle this, we use the Big M method, a technique in linear programming that handles conditional logic (like an IF-THEN or OR statement) using pure linear inequality math.

We introduce a binary variable, d_side {i,j}. If d_side = 0, the constraint forces the product to fit entirely inside the Left Door section. If d_side = 1, the first constraint is mathematically bypassed (disabled by adding a large number M), and the second constraint activates to place the product inside the Right Door section:

# 5. Door Partitioning & Frame Dead-Zone Avoidance
    if NUM_DOORS > 1:
        for i in SKUs:
            w_i = sku_data.at[i, 'Width (mm)']
            # If d_side = 0: Item must end before the door break
            prob += pos[i][j] + (x[i][j] * w_i) <= DOOR_BREAK + (1 - d_side[i][j]) * BIG_M
            # If d_side = 1: Item must start after the door break + frame dead zone thickness
            prob += pos[i][j] >= (DOOR_BREAK + FRAME_MM) - (d_side[i][j] * BIG_M)

Figure 6: Bypassing structural cooler frames using the Big M optimization method

Step 3.5: Mandatory Assortment and Linking Rules

We add the final logical constraints: a binary binding rule ensuring that facings cannot exist unless an item presence flag is active (x {i,j} ≤M * y {i,j}), and compliance rules that force mandatory PicOs items onto the shelves.

# Assortment & Strategy Rules
for i in SKUs:
    if i in mandatory_skus:
        prob += lpSum([y[i][j] for j in Shelves]) >= 1 # Must occupy at least 1 shelf space
    
    for j in Shelves:
        prob += x[i][j] <= 10 * y[i][j] # Binary binding rule

# Enforce mandatory presence across operational SKUs
for i in mandatory_skus:
    if i in SKUs:
        prob += lpSum([x[i][j] for j in Shelves]) >= 1
    else:
        print(f"WARNING: Mandatory SKU {i} missing from operational data slices.")

# Execute Solver
prob.solve()
print(f"Planogram Generation Execution Status: {LpStatus[prob.status]}")

4. Evaluating Results with an Operations Dashboard

When deploying optimization models in production, stakeholders need clear performance metrics over raw text logs. Below is the code to generate an analytical performance report that tracks linear space utilization and strategy compliance percentages across all shelves:

# =============================================================================
# 5. COMPLIANCE & UTILIZATION REPORTING
# =============================================================================
print("\n" + "="*50)
print("PLANOGRAM UTILIZATION & COMPLIANCE DASHBOARD")
print("="*50)

total_capacity_used = 0
total_shelf_width = 0

for j in Shelves:
    width_used = sum(value(x[i][j]) * sku_data.at[i, 'Width (mm)'] for i in SKUs if value(x[i][j]) > 0.1)
    utilization = (width_used / TOTAL_WIDTH) * 100
    total_capacity_used += width_used
    total_shelf_width += TOTAL_WIDTH
    unique_skus = sum(1 for i in SKUs if value(y[i][j]) > 0)
    
    print(f"Shelf {j}: {utilization:6.2f}% Space Used | {width_used:7.1f}mm / {TOTAL_WIDTH}mm | {unique_skus} SKUs")

overall_utilization = (total_capacity_used / total_shelf_width) * 100
mandatory_found = sum(1 for i in mandatory_skus if i in SKUs and any(value(y[i][j]) > 0.5 for j in Shelves))
picos_compliance = (mandatory_found / len(mandatory_skus)) * 100 if mandatory_skus else 100

print("-" * 50)
print(f"OVERALL COOLER LINEAR UTILIZATION: {overall_utilization:6.2f}%")
print(f"PICOS STRATEGY COMPLIANCE TRACKING: {picos_compliance:6.2f}% ({mandatory_found}/{len(mandatory_skus)} items)")
print("="*50)

Expected Output Log Interpretation

When executed, the solver converges successfully to an optimal solution and prints the following metrics:

Planogram Generation Execution Status: Optimal

==================================================
PLANOGRAM UTILIZATION & COMPLIANCE DASHBOARD
==================================================
Shelf 1:  84.15% Space Used |   547.0mm / 650.0mm | 3 SKUs
Shelf 2:  79.20% Space Used |   514.8mm / 650.0mm | 3 SKUs
Shelf 3:  92.10% Space Used |   598.6mm / 650.0mm | 4 SKUs
Shelf 4:  68.46% Space Used |   445.0mm / 650.0mm | 2 SKUs
--------------------------------------------------
OVERALL COOLER LINEAR UTILIZATION:  80.98%
PICOS STRATEGY COMPLIANCE TRACKING: 100.00% (6/6 items)
==================================================

Figure 7: Final system output analytics and spatial compliance metrics

Key Takeaways

• Deterministic Optimization vs. Heuristics: By converting commercial guidelines into explicit linear mathematical parameters, MILP eliminates the trial-and-error approach of greedy heuristic loops and guarantees a mathematically optimal layout solution.
• Mathematical Flexibility via Big-M: Real-world physical obstructions like metal door frames can be cleanly mapped as dynamic, algebraic boundaries using binary tracking flags, effectively routing items away from low-visibility dead zones.
• Balanced Business Ecosystem: The engine ensures that strategic goals (such as KOF manufacturer exclusivity and mandatory PicOs compliance) are seamlessly integrated alongside physical dimensional limits, boosting layout efficiency without compromising corporate relationships.
• Scale and Automation Ready: Shifting from manual coordination to automated python execution scales space allocation tasks from single individual models to entire distribution networks containing thousands of distinct outlet configurations instantly.