Setting up demand forecasting for 1C-Bitrix products

Configuring Demand Forecasting for Products in 1C-Bitrix

Warehouse orders products by intuition: if last month sold 100 units — ordered 120. Result: seasonal products run out at peak, non-seasonal ones freeze working capital. Demand forecasting — not ML magic, it's statistics from historical sales data already in b_sale_order_basket.

Data source: sales history in Bitrix

All sales stored in b_sale_order_basket (order items) and b_sale_order (order headers). For forecasting need completed orders — those where b_sale_order.STATUS_ID corresponds to final statuses (usually F — completed).

Basic query for product sales history:

SELECT
    DATE_TRUNC('month', o.DATE_INSERT) AS sale_month,
    ob.PRODUCT_ID,
    SUM(ob.QUANTITY) AS qty_sold
FROM b_sale_order_basket ob
JOIN b_sale_order o ON o.ID = ob.ORDER_ID
WHERE o.STATUS_ID IN ('F', 'D')  -- completed and delivered
  AND o.CANCELED = 'N'
  AND ob.PRODUCT_ID = :product_id
  AND o.DATE_INSERT >= NOW() - INTERVAL '24 months'
GROUP BY 1, 2
ORDER BY 1;

24 months — minimum horizon for detecting annual seasonality.

Simple forecasting methods

For most online stores three methods without ML are sufficient:

Simple Moving Average (SMA). Forecast for next month = average of last N months. N = 3–6 for stable demand, N = 2 for volatile.

Weighted Moving Average (WMA). Last month has weight 3, previous — 2, third — 1. Reacts faster to trends.

Holt-Winters Method (triple exponential smoothing). Accounts for trend and seasonality. More complex to implement, but significantly more accurate for products with pronounced seasonality.

function forecastSimpleMA(array $monthlySales, int $periods = 3): float
{
    $recent = array_slice($monthlySales, -$periods);
    return array_sum($recent) / count($recent);
}

function forecastWMA(array $monthlySales, int $periods = 3): float
{
    $recent  = array_slice($monthlySales, -$periods);
    $weights = range(1, $periods);
    $total   = array_sum($weights);
    $sum     = 0;
    foreach ($recent as $i => $qty) {
        $sum += $qty * $weights[$i];
    }
    return $sum / $total;
}

Seasonality coefficient

For seasonal products (winter clothing, garden tools, school supplies) moving average will systematically error. Seasonality coefficient calculated from 2+ years of data:

// Average monthly sales volume over 2 years
$annualAvg = array_sum($monthlySales) / count($monthlySales);

// Seasonality coefficient for each month
$seasonalIndex = [];
for ($month = 1; $month <= 12; $month++) {
    $monthData = array_filter(
        $monthlySales,
        fn($m) => (int)date('m', strtotime($m['date'])) === $month
    );
    $monthAvg = array_sum(array_column($monthData, 'qty')) / max(count($monthData), 1);
    $seasonalIndex[$month] = $annualAvg > 0 ? $monthAvg / $annualAvg : 1.0;
}

// Forecast with seasonality
$baseForecast  = forecastSimpleMA($rawSales, 3);
$targetMonth   = (int)date('m', strtotime('+1 month'));
$adjustedForecast = $baseForecast * $seasonalIndex[$targetMonth];

Storing forecasts and reorder point

Forecast results saved to own table:

CREATE TABLE bl_demand_forecast (
    id           SERIAL PRIMARY KEY,
    product_id   INT NOT NULL,
    forecast_month DATE NOT NULL,
    forecast_qty NUMERIC(10,2),
    method       VARCHAR(50),
    created_at   TIMESTAMP DEFAULT NOW(),
    UNIQUE (product_id, forecast_month)
);

Based on forecast, recommended order volume calculated: forecast_qty * safety_factor - current_stock. safety_factor = 1.2–1.5 (buffer for forecast inaccuracy and delivery time).

Bitrix agent recalculates forecasts weekly and writes to bl_demand_forecast. Admin interface shows products where current stock below recommended order level.

What we configure

• Selection of sales history from b_sale_order_basket filtered by final statuses
• SMA/WMA algorithm for products with stable demand
• Seasonal coefficient calculation from 24-month history
• bl_demand_forecast table and weekly recalculation agent
• Admin report: products below recommended order threshold
• Export recommendations to Excel or upload to 1C for automatic purchase request formation