Predicting What Sells Next: Demand Forecasting for Magento 2 with Python + Prophet

Before we write any code, let’s be clear about the size of the leak. Industry-average numbers for mid-market Magento stores:

“A forecast doesn’t need to be perfect. It needs to be better than the gut it’s replacing. For most Magento merchants, that bar is much lower than they think.”

Prophet wants two columns: ds (date) and y (the numeric value to forecast). Your job is to turn 18 months of Magento order data into one row per SKU per day. The query below runs against your read-replica if you have one; if you don’t, run it during off-peak.

SELECT
    DATE(o.created_at) AS ds,
    oi.sku             AS sku,
    SUM(oi.qty_ordered) AS y
FROM sales_order_item oi
INNER JOIN sales_order o
    ON o.entity_id = oi.order_id
WHERE o.status IN ('complete', 'processing', 'shipped')
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 18 MONTH)
  AND oi.product_type = 'simple'
GROUP BY DATE(o.created_at), oi.sku
ORDER BY ds ASC, sku ASC;

Three rules that trip people up: (1) only simple products — configurable + bundle parents double-count if you don’t. (2) Skip canceled and holded — they poison the trend. (3) Use the order’s created_at, not the line-item’s updated_at — consistency matters more than precision here.

From Python, the load is one line via SQLAlchemy. Use PyGento if you want a typed ORM over the Magento schema; pd.read_sql is fine for our purposes.

Three credible options. None of them is “the best” in isolation. The right pick depends on your catalog shape, your team’s Python comfort, and how much GPU you can throw at training. Side by side:

My default recommendation for Magento mid-market: start with Prophet. It hits 80% of the accuracy of an LSTM at 5% of the operational complexity. If you outgrow it, you’ll know — your MAPE will plateau and your top-revenue SKUs will start asking for cross-SKU pattern learning. Until then, Prophet wins.

Five files, ~120 lines of Python total, end-to-end. Each step has the code, the rationale, and the most common gotcha.

-- Daily units shipped per SKU, last 18 months
-- Skip canceled / held / pending_payment orders.
SELECT
    DATE(o.created_at)               AS ds,
    oi.sku                            AS sku,
    SUM(oi.qty_ordered)               AS y
FROM sales_order_item oi
INNER JOIN sales_order o
    ON o.entity_id = oi.order_id
WHERE o.status IN ('complete', 'processing', 'shipped')
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 18 MONTH)
  AND oi.product_type = 'simple'
GROUP BY DATE(o.created_at), oi.sku
ORDER BY ds ASC, sku ASC;

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://forecast:***@mysql:3306/magento"
)

df = pd.read_sql(SQL_DAILY_SKU, engine)
df['ds'] = pd.to_datetime(df['ds'])

# Pick the SKU we want to forecast first.
sku = 'WJ12-XS-Blue'
series = (
    df[df.sku == sku][['ds', 'y']]
    .set_index('ds')
    .asfreq('D', fill_value=0)   # fill zero-sales days
    .reset_index()
)

from prophet import Prophet
from prophet.make_holidays import make_holidays_df

# Country-specific holidays. Use 'IN' for India, 'GB' for UK, etc.
holidays = make_holidays_df(
    year_list=[2024, 2025, 2026, 2027],
    country='US',
)
# Add your own ecommerce holidays
bfcm = pd.DataFrame({
    'holiday': 'bfcm',
    'ds':      pd.to_datetime(['2024-11-29', '2025-11-28', '2026-11-27']),
    'lower_window': -1, 'upper_window': 3,
})
holidays = pd.concat([holidays, bfcm])

m = Prophet(
    yearly_seasonality=True,
    weekly_seasonality=True,
    daily_seasonality=False,
    holidays=holidays,
    seasonality_mode='multiplicative',
    changepoint_prior_scale=0.05,
)
m.fit(series)

future = m.make_future_dataframe(periods=90, freq='D')
forecast = m.predict(future)

out = forecast[[
    'ds', 'yhat', 'yhat_lower', 'yhat_upper',
    'trend', 'weekly', 'yearly'
]].tail(90)

out['sku'] = sku
out['model_version'] = 'prophet-v1-2026-05-18'
out['run_at'] = pd.Timestamp.utcnow()

# Clip negative predictions (no such thing as negative sales)
for col in ['yhat', 'yhat_lower', 'yhat_upper']:
    out[col] = out[col].clip(lower=0).round(2)

from sqlalchemy.dialects.mysql import insert as mysql_insert
from sqlalchemy import Table, MetaData

meta = MetaData()
forecast_tbl = Table('panth_forecast', meta, autoload_with=engine)

rows = out.to_dict(orient='records')
for chunk in [rows[i:i+500] for i in range(0, len(rows), 500)]:
    stmt = mysql_insert(forecast_tbl).values(chunk)
    stmt = stmt.on_duplicate_key_update(
        yhat=stmt.inserted.yhat,
        yhat_lower=stmt.inserted.yhat_lower,
        yhat_upper=stmt.inserted.yhat_upper,
        model_version=stmt.inserted.model_version,
        run_at=stmt.inserted.run_at,
    )
    engine.execute(stmt)

12 months of history on the left. 90 days of forecast on the right, with two shaded confidence bands — the inner 80% (your day-to-day plan) and the outer 95% (your worst-case safety stock).

Read this chart like a weather forecast: the further out you look, the wider the cone. Day 1–14 the 80% band is tight enough to set reorder points. Day 60–90 the band is wide — useful for capacity planning, not for daily reorder.

A forecast you haven’t backtested isn’t a forecast — it’s a guess with a Python wrapper. Use Prophet’s built-in cross_validation + performance_metrics helpers; they slice the historical series with a rolling origin and score every fold.

Real numbers from a mid-market Hyvä Magento store I worked on (apparel, ~2,400 active SKUs, 18 months of history, daily forecast at a 28-day horizon):

Below is a pre-computed 90-day forecast for one SKU (WJ12-XS-Blue — Hyvä Hoodie — XS / Blue, current on-hand 142 units). Drag the horizon and safety-stock multiplier to see the reorder recommendation update live.

A forecast that lives in a Python notebook is a hobby project. A forecast that shows up on the product-edit page is operational. Three integration patterns, ordered by complexity:

The Magento PHP container has no business knowing about Prophet, pandas, or cmdstan. Run the forecasting service as a separate container that talks to the same MySQL. The repo’s docker-compose.python-expose.yml already runs a Python sidecar pattern — we extend it with a forecaster service.

# docker-compose.python-expose.yml (excerpt)
services:
  forecaster:
    image: python:3.12-slim
    container_name: kishansavaliya_forecaster
    working_dir: /app
    volumes:
      - ./python/forecaster:/app
      - ./python/requirements-forecast.txt:/app/requirements.txt
    environment:
      MYSQL_HOST: mysql
      MYSQL_DB: magento
      MYSQL_USER: forecast_ro
      MYSQL_PASSWORD: ${MYSQL_FORECAST_RO_PWD}
    command: >
      sh -c "pip install -q -r requirements.txt &&
              python -m forecaster.cli --horizon 90 --schedule weekly"
    depends_on:
      - mysql
    restart: unless-stopped
    networks:
      - magento_default

Two operational rules. (1) Use a read-only MySQL user (forecast_ro) for the Magento reads — the sidecar has no business writing to sales_order. (2) Use a separate, write-scoped user for the panth_forecast table writes. Two roles, two passwords, zero cross-table risk.

Same idea as the model-comparison table earlier, but scored 1–10 and weighted to YOUR priorities. Drag the sliders to set how much each dimension matters; the ranking re-orders live.

• Scores are my opinion based on production Magento integrations — not a peer-reviewed benchmark.
• Prophet wins on the “ship a usable forecast this sprint” axis. LSTM wins on the “squeeze the last 2% of MAPE” axis.
• For 90% of mid-market Magento stores, Prophet is the right answer.

A forecast on a dashboard is interesting. A forecast wired into an operational decision is valuable. Three patterns that move money:

Every forecast can be wrong. Here are the failure modes that bite every real-world Magento + Prophet deploy I’ve seen.

1. 01

   Promo-driven demand spikes blow the forecast

   A flash sale, an influencer mention, a press hit — none of these are in the historical series in the same way. Prophet treats them as outliers and either inflates the trend or just misses entirely. Counter-pattern: tag promo days with a custom regressor; exclude them from baseline training when you can.

2. 02

   New SKUs have no history (cold-start)

   A SKU launched last week has 7 data points. Prophet needs ~12 weeks. Fall back to a category-level forecast scaled by an analog-SKU multiplier for the first 60 days. Re-evaluate weekly until the SKU has enough history to model independently.

3. 03

   Supplier lead-time uncertainty

   Your forecast can be perfect and you can still stock out if the supplier is 3 weeks late. Track actual_lead_time as a separate distribution (gamma-fit on the last 12 orders) and convolve it with the forecast to get a real probability of stockout, not just a point estimate.

4. 04

   Model drift after store layout changes

   You redesign the PDP, drop add-to-cart friction, or change category structure — demand patterns shift but the model doesn’t know. Watch MAPE on a rolling 14-day window. A 30%+ spike in MAPE = retrain immediately, even outside the weekly cadence.

5. 05

   Garbage-in on dirty order data

   Test orders, canceled-but-not-canceled status, refunded line items, configurable-vs-simple SKU mismatches — all skew the signal. Build a 30-line data-quality test that runs before every training cycle. Reject the run if > 2% of rows are anomalies.

Same question, four different answers. Pick the row that matches your GMV band:

• Facebook Prophet documentation facebook.github.io/prophet
• Predictive analytics for Magento — MageBytes blog magebytes.com
• 42signals — The hidden cost of stockouts 42signals.com/blog
• Saras Analytics — Ecommerce demand forecasting guide sarasanalytics.com
• Analytics Vidhya — Retail demand forecasting walkthrough analyticsvidhya.com
• scikit-learn time-series cross-validation scikit-learn.org