Setting up competitor price tracking for 1C-Bitrix

Competitor Price Monitoring Setup for 1C-Bitrix

Competitor price tracking is built using one of two approaches: a ready-made monitoring service (Competera, Metacommerce, Priceva) or a custom parser. A ready-made service is simpler and more reliable, but expensive for a large catalogue. A custom parser is flexible and cheap to operate, but requires regular maintenance whenever competitor websites change. In both cases the task of setup is the same: collect data, store it in 1C-Bitrix, and present it to the manager in a convenient view.

Data Storage Architecture

Regardless of the data source (service or parser), the storage structure is the same:

Competitors bl_price_competitors:

CREATE TABLE bl_price_competitors (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(255) NOT NULL,
    domain   VARCHAR(255) UNIQUE,
    active   BOOLEAN DEFAULT true,
    logo_url VARCHAR(512)
);

Competitor Prices bl_competitor_prices:

CREATE TABLE bl_competitor_prices (
    id             SERIAL PRIMARY KEY,
    product_id     INT NOT NULL,               -- b_iblock_element.ID
    competitor_id  INT REFERENCES bl_price_competitors(id),
    price          NUMERIC(12,2) NOT NULL,
    url            VARCHAR(512),               -- competitor's page URL
    in_stock       BOOLEAN DEFAULT true,
    checked_at     TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (product_id, competitor_id)         -- one current price
);
CREATE INDEX idx_comp_prices_product ON bl_competitor_prices(product_id, checked_at DESC);

History bl_competitor_prices_history — a table partitioned by month to store changes without bloating the main table.

Integration via Monitoring Service API

When using a ready-made service, an agent fetches data and writes it to bl_competitor_prices:

function SyncCompetitorPrices(): string
{
    $client = new PriceMonitoringClient(MONITORING_API_KEY);
    $data   = $client->getPrices(['date' => date('Y-m-d')]);

    foreach ($data['products'] as $item) {
        $productId = ProductMapper::findBySku($item['sku']);
        if (!$productId) continue;

        foreach ($item['competitors'] as $comp) {
            $competitorId = CompetitorTable::getOrCreateByDomain($comp['domain']);

            // Save to history before updating
            $current = CompetitorPriceTable::getByProductAndCompetitor($productId, $competitorId);
            if ($current && $current['PRICE'] != $comp['price']) {
                CompetitorPriceHistoryTable::add([
                    'PRODUCT_ID'    => $productId,
                    'COMPETITOR_ID' => $competitorId,
                    'PRICE'         => $current['PRICE'],
                    'RECORDED_AT'   => $current['CHECKED_AT'],
                ]);
            }

            CompetitorPriceTable::addOrUpdate([
                'PRODUCT_ID'    => $productId,
                'COMPETITOR_ID' => $competitorId,
                'PRICE'         => $comp['price'],
                'URL'           => $comp['url'],
                'IN_STOCK'      => $comp['in_stock'],
                'CHECKED_AT'    => new \Bitrix\Main\Type\DateTime(),
            ]);
        }
    }

    return __FUNCTION__ . '();';
}

Price Position Calculation

After each update we compute aggregates and position in bl_product_price_position:

-- Updated by a trigger or agent after synchronisation
INSERT INTO bl_product_price_position (product_id, our_price, min_comp, avg_comp, max_comp, rank, updated_at)
SELECT
    cp.product_id,
    bcp.PRICE as our_price,
    MIN(cp.price) as min_comp,
    ROUND(AVG(cp.price), 2) as avg_comp,
    MAX(cp.price) as max_comp,
    (SELECT COUNT(*) + 1 FROM bl_competitor_prices cp2
     WHERE cp2.product_id = cp.product_id AND cp2.price < bcp.PRICE) as rank,
    NOW()
FROM bl_competitor_prices cp
JOIN b_catalog_price bcp ON bcp.PRODUCT_ID = cp.product_id AND bcp.CATALOG_GROUP_ID = 1
GROUP BY cp.product_id, bcp.PRICE
ON CONFLICT (product_id) DO UPDATE SET
    our_price = EXCLUDED.our_price, min_comp = EXCLUDED.min_comp,
    avg_comp = EXCLUDED.avg_comp, rank = EXCLUDED.rank, updated_at = NOW();

Alerts on Competitor Price Changes

The agent compares new prices with previous ones and sends a notification to managers on significant changes (a competitor drops below our price):

foreach ($priceChanges as $change) {
    if ($change['new_price'] < $change['our_price'] && $change['old_price'] >= $change['our_price']) {
        // Competitor just became cheaper than us
        $message = sprintf(
            'Competitor %s has lowered the price on %s to %s (ours: %s)',
            $change['competitor_name'],
            $change['product_name'],
            number_format($change['new_price'], 2, '.', ' '),
            number_format($change['our_price'], 2, '.', ' ')
        );
        \Bitrix\Main\Mail\Event::send([
            'EVENT_NAME' => 'COMPETITOR_PRICE_ALERT',
            'LID'        => SITE_ID,
            'C_FIELDS'   => ['MESSAGE' => $message],
        ]);
    }
}

Timeline