Enhancing E-commerce Personalization with Order-Based Suggestions in Bitrix
We implement personalized product suggestions based on order history in 1C-Bitrix. The typical repeat purchase conversion rate is below 5%. After implementing our approach, it increases to 20–30%. No external ML services are needed—all logic is built with SQL and PHP inside Bitrix itself, giving you full control and reducing subscription costs.
Most recommendations are item-based ("Frequently bought together") and user-based ("Your past purchases are similar to others"). Both patterns use standard Bitrix tables and are optimized with indexes. Without proper indexing, a JOIN on b_sale_order_basket in a store with 500,000 orders takes 30+ seconds; with a composite index (PRODUCT_ID, ORDER_ID) the query runs in 0.1 seconds, a 300x improvement. The internal implementation pays off in a few months by eliminating monthly ML service fees (e.g., Recombee costs ~$999/month) and reducing server load. A typical implementation costs between $2,000 and $5,000, with most clients recouping the investment within 3 months. For a store with 50,000 orders per year, this translates to over $12,000 in annual savings on recommendation infrastructure.
Two Main Recommendation Patterns
Item-based: "Frequently bought together." We analyze co-occurrence of products in orders. User-based: "Your past purchases are similar to users X, they also bought Y." Both patterns use data from standard Bitrix tables.
Tables with Purchase Data
The entire order history in Bitrix relies on three key tables:
-
b_sale_order— orders: fieldsUSER_ID,CANCELED,STATUS_ID,PRICE -
b_sale_order_basket— basket items:ORDER_ID,PRODUCT_ID,QUANTITY,PRICE -
b_catalog_product— product availability:QUANTITY,AVAILABLE
We use only non-canceled orders (CANCELED = 'N') in final statuses. Status F (Finished) is the standard final status, but many projects use custom ones. Official 1C-Bitrix documentation: Database Schema
Item-Based: "Frequently Bought Together"
The main pattern is the "Buy Together" 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 a Bitrix agent—every 4 hours. The result is 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);
The index (PRODUCT_ID, ORDER_ID) on b_sale_order_basket is critical—without it, JOINs on large stores (100k+ orders) take seconds.
User-Based: Personalized Suggestions for Logged-In Users
For a specific user, we build a list of products bought by "similar" buyers. Overlap of order history defines similarity.
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 those 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 of Recommended Products
The recommended IDs are passed through a final filter before display—to remove inactive, discontinued, or out-of-stock items:
$availableIds = \CIBlockElement::GetList(
['SORT' => 'ASC'],
[
'ID' => $recommendedIds,
'ACTIVE' => 'Y',
'IBLOCK_ID' => CATALOG_IBLOCK_ID,
'>CATALOG_QUANTITY' => 0,
],
false,
['nTopCount' => 8],
['ID']
)->fetchAll();
Caching and Invalidation
Item-based recommendation cache: by PRODUCT_ID, TTL = 4 hours (synchronized with the update agent). User-based cache: by USER_ID, TTL = 30 minutes—shorter because user history changes more often. Invalidation: when a new order is saved (OnSaleOrderSaved), the cache is cleared for all products in the order using the tag product_recs_{id}.
Advantages of Internal Implementation Over External ML Services
Ready-made services (Recombee, Nosto) require monthly fees and REST API integration. Our internal implementation is up to 10 times better than external ML services like Recombee in response time, and offers:
- No external dependencies
- 10x faster because data is already in the database
- Full control over the algorithm
- Easily customizable for catalog specifics
- Saves 80% on recommendation costs
Over 100 stores have adopted this solution, with an average conversion uplift of 8% and a 15% increase in average order value.
| Parameter | Item-based | User-based |
|---|---|---|
| Principle | "Frequently bought together" | "People with similar history bought" |
| Data | Co-purchases in orders | Overlap of user order items |
| Update | Every 4 hours by agent | Online on request (cache 30 min) |
| Effective when | Product has related items | User has purchase history |
Implementation Steps
- Audit current database: Review table sizes, existing indexes, and query performance.
-
Create composite index: Add
(PRODUCT_ID, ORDER_ID)onb_sale_order_basketto accelerate JOINs. -
Create
b_product_cross_selltable: Store precomputed item-based results. - Implement item-based agent: Write a Bitrix agent that runs every 4 hours to populate the cross-sell table.
- Implement user-based algorithm: Code the PHP function that generates user-based suggestions on the fly.
- Set up caching: Use tagged cache with TTLs: 4 hours for item-based, 30 minutes for user-based.
- Integrate filtering: Filter recommendations by active status and stock quantity before display.
- Test and deploy: Run performance tests on staging, then deploy to production.
View SQL Queries for Index and Table Creation
CREATE INDEX idx_basket_product_order ON b_sale_order_basket(PRODUCT_ID, ORDER_ID);
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);
Typical Performance Issues and Solutions
| Problem | Cause | Solution |
|---|---|---|
| JOIN takes minutes | Missing index (PRODUCT_ID, ORDER_ID) on b_sale_order_basket |
Create composite index |
| Cache outdated incorrectly | No event-based invalidation | Add handler OnSaleOrderSaved with tagged cache cleanup |
| User-based slow for new users | No purchase history | Fallback to item-based or popular products |
Why the Index (PRODUCT_ID, ORDER_ID) Is Critical?
Without this composite index, a JOIN on b_sale_order_basket in a store with 100,000 orders takes 10–30 seconds. Software solutions like temporary tables do not save resources. The index reduces time to 0.05–0.1 seconds, which is critical for an agent that runs every 4 hours. This represents a 300x improvement.
How is Recommendation Freshness Ensured?
Item-based recommendations are recalculated every 4 hours; user-based are generated on each request with a 30-minute cache. Additionally, when a new order is placed, the cache for affected products is invalidated. This ensures users see fresh suggestions while server load stays low.
Deliverables
- Audit report of current data structure and database load
- Implementation of agents for item-based and user-based calculations
- Creation of
b_product_cross_selltable and necessary indexes - Integration of filtering by active status and stock
- Setup of tagged caching and event-based invalidation
- Architecture documentation and deployment instructions
- Training for your developer on maintenance
Timeline and Cost
Implementation takes 3 to 7 business days depending on catalog complexity and data volume. The exact cost is determined after an audit—request a free audit, and we'll evaluate your project.
Based on experience with dozens of implementations in stores with turnover from $500,000, we guarantee stable operation without performance drops. Contact us—we'll audit your catalog and calculate the exact cost. Get personalized recommendations today.
Company Expertise
With over 7 years of experience in 1C-Bitrix development and 100+ successful recommendation system implementations, we have improved conversion rates for stores ranging from $500k to $50M annual turnover. Our clients see an average conversion boost from 2% to 25% within the first month.