Setting up product recommendations based on 1C-Bitrix purchase history

Setting up product recommendations based on purchase history 1C-Bitrix

Purchase history is the strongest behavioral signal. The user has already paid money — this is not a view or a click, it is confirmed interest. Product recommendations based on purchase history work according to two patterns: "buyers of this product also bought" (item-based) and "your past purchases are similar to purchases of users X, they also took Y" (user-based). Both patterns are implemented directly in Bitrix without external ML services.

Tables with purchase data

All order history in Bitrix is in three key tables:

• b_sale_order — orders: fields USER_ID, CANCELED, STATUS_ID, PRICE
• b_sale_order_basket — order contents: ORDER_ID, PRODUCT_ID, QUANTITY, PRICE
• b_catalog_product — product availability: QUANTITY, AVAILABLE

For recommendations, we use only non-canceled orders (CANCELED = 'N') in final statuses. Status F (Finished) is the standard final status, but many projects use custom statuses.

Item-based: "frequently bought together"

Main pattern — a "Frequently bought with this product" block on the product card:

SELECT
    ob2.PRODUCT_ID,
    COUNT(DISTINCT ob1.ORDER_ID) AS co_purchase_count,
    SUM(ob2.QUANTITY)            AS total_qty
FROM b_sale_order_basket ob1
JOIN b_sale_order_basket ob2
    ON ob1.ORDER_ID = ob2.ORDER_ID
    AND ob2.PRODUCT_ID != ob1.PRODUCT_ID
JOIN b_sale_order o
    ON o.ID = ob1.ORDER_ID
    AND o.CANCELED = 'N'
    AND o.DATE_INSERT > NOW() - INTERVAL '90 days'
WHERE ob1.PRODUCT_ID = :target_product_id
GROUP BY ob2.PRODUCT_ID
ORDER BY co_purchase_count DESC
LIMIT 20;

This query runs offline via Bitrix agent — every 4 hours. Results are written to a table:

CREATE TABLE b_product_cross_sell (
    SOURCE_ID        INT NOT NULL,
    RECOMMENDED_ID   INT NOT NULL,
    SCORE            INT NOT NULL,
    UPDATED_AT       TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (SOURCE_ID, RECOMMENDED_ID)
);
CREATE INDEX idx_cross_sell_source ON b_product_cross_sell(SOURCE_ID, SCORE DESC);

Index (PRODUCT_ID, ORDER_ID) on b_sale_order_basket is critical — without it JOIN on large stores (100k+ orders) will execute in seconds.

User-based: personalized recommendations for authorized user

For a specific user, a list of products is built that "similar" users have purchased. "Similarity" is an intersection of purchase history.

function getUserBasedRecs(int $userId, int $limit = 8): array {
    // 1. Current user's purchase history
    $myOrderIds = array_column(
        \Bitrix\Sale\OrderTable::getList([
            'filter' => ['USER_ID' => $userId, 'CANCELED' => 'N'],
            'select' => ['ID'],
        ])->fetchAll(),
        'ID'
    );

    if (empty($myOrderIds)) return getPopularItems($limit);

    $myProductIds = array_column(
        \Bitrix\Sale\Internals\BasketTable::getList([
            'filter' => ['ORDER_ID' => $myOrderIds],
            'select' => ['PRODUCT_ID'],
        ])->fetchAll(),
        'PRODUCT_ID'
    );

    // 2. Users who bought the same products
    // 3. Products of these users that we don't have
    $res = $GLOBALS['DB']->Query("
        SELECT ob2.PRODUCT_ID, COUNT(DISTINCT o2.USER_ID) AS score
        FROM b_sale_order_basket ob1
        JOIN b_sale_order o1 ON o1.ID = ob1.ORDER_ID AND o1.USER_ID = {$userId}
        JOIN b_sale_order_basket ob2 ON ob2.ORDER_ID IN (
            SELECT DISTINCT o3.ID FROM b_sale_order o3
            JOIN b_sale_order_basket ob3 ON ob3.ORDER_ID = o3.ID
                AND ob3.PRODUCT_ID IN (" . implode(',', array_map('intval', $myProductIds)) . ")
            WHERE o3.USER_ID != {$userId} AND o3.CANCELED = 'N'
        )
        WHERE ob2.PRODUCT_ID NOT IN (" . implode(',', array_map('intval', $myProductIds)) . ")
        GROUP BY ob2.PRODUCT_ID
        ORDER BY score DESC
        LIMIT {$limit}
    ");

    $ids = [];
    while ($row = $res->Fetch()) $ids[] = (int)$row['PRODUCT_ID'];
    return $ids;
}

Filtering recommended products

Recommended IDs are passed to a final filter before display — remove inactive, discontinued, with zero inventory:

$availableIds = \CIBlockElement::GetList(
    ['SORT' => 'ASC'],
    [
        'ID'        => $recommendedIds,
        'ACTIVE'    => 'Y',
        'IBLOCK_ID' => CATALOG_IBLOCK_ID,
        '>CATALOG_QUANTITY' => 0,
    ],
    false,
    ['nTopCount' => 8],
    ['ID']
)->fetchAll();

Cache and invalidation

Cache for item-based recommendations: by PRODUCT_ID, TTL = 4 hours (synchronized with the update agent). Cache for user-based: by USER_ID, TTL = 30 minutes — shorter because user history changes. Invalidation: when a new order is saved (OnSaleOrderSaved), clear the cache for all products in the order by tag product_recs_{id}.