Как рассчитать конверсию из просмотра товара в покупку?

Расчёт конверсии из просмотра товара в покупку

Конверсия — это ключевая метрика e-commerce, которая показывает, какой процент пользователей, посетивших товар, его купили. Data Engineer отвечает за сбор и обработку данных для точного расчёта этой метрики.

Определение метрики

Конверсия (conversion rate) рассчитывается по формуле:

Conversion Rate = (Количество покупок) / (Количество просмотров товара) * 100%

Например, если товар просмотрели 1000 раз, а купили 50 раз, конверсия = 50 / 1000 * 100% = 5%.

Сбор данных о просмотрах и покупках

# Пример: отправка событий в аналитику
# Frontend код
const trackProductView = (productId, userId) => {
    fetch('/api/v1/analytics/event', {
        method: 'POST',
        body: JSON.stringify({
            event_type: 'product_view',
            product_id: productId,
            user_id: userId,
            timestamp: new Date().toISOString(),
            session_id: getSessionId()
        })
    })
}

const trackPurchase = (productId, userId, amount) => {
    fetch('/api/v1/analytics/event', {
        method: 'POST',
        body: JSON.stringify({
            event_type: 'purchase',
            product_id: productId,
            user_id: userId,
            amount: amount,
            timestamp: new Date().toISOString(),
            session_id: getSessionId()
        })
    })
}

ETL для обработки событий

# Backend: приём и сохранение событий
from fastapi import FastAPI, HTTPException
from sqlalchemy import create_engine, Column, String, Integer, Float, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from datetime import datetime
import json

app = FastAPI()
Base = declarative_base()

class AnalyticsEvent(Base):
    __tablename__ = 'events'
    
    event_id = Column(Integer, primary_key=True)
    event_type = Column(String(50))  # 'product_view' или 'purchase'
    product_id = Column(String(50))
    user_id = Column(String(50))
    session_id = Column(String(100))
    amount = Column(Float, nullable=True)  # для покупок
    timestamp = Column(DateTime)
    created_at = Column(DateTime, default=datetime.utcnow())

@app.post('/api/v1/analytics/event')
async def log_event(event: dict, db: Session):
    """
    Логирует событие просмотра или покупки
    """
    db_event = AnalyticsEvent(
        event_type=event['event_type'],
        product_id=event['product_id'],
        user_id=event['user_id'],
        session_id=event.get('session_id'),
        amount=event.get('amount'),
        timestamp=datetime.fromisoformat(event['timestamp'])
    )
    db.add(db_event)
    db.commit()
    return {'ok': True}

SQL для расчёта конверсии

-- Базовая таблица с событиями
CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_type VARCHAR(50),  -- 'product_view' или 'purchase'
    product_id VARCHAR(50),
    user_id VARCHAR(50),
    session_id VARCHAR(100),
    amount DECIMAL(10, 2),
    timestamp TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Простой расчёт конверсии по товару
CREATE VIEW v_product_conversion AS
SELECT 
    product_id,
    COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) as view_count,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as purchase_count,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END)::FLOAT / 
    COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) * 100 as conversion_rate
FROM events
WHERE event_type IN ('product_view', 'purchase')
GROUP BY product_id;

-- Конверсия по дням
CREATE VIEW v_daily_conversion AS
SELECT 
    DATE(timestamp) as date,
    COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) as daily_views,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as daily_purchases,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END)::FLOAT / 
    COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) * 100 as daily_conversion_rate
FROM events
WHERE event_type IN ('product_view', 'purchase')
GROUP BY 1
ORDER BY 1 DESC;

Учёт временного промежутка

Важно: пользователь может просмотреть товар, но купить его через день или неделю. Нужно учитывать это в расчётах:

-- Конверсия с учётом временного окна (24 часа между просмотром и покупкой)
CREATE VIEW v_conversion_within_24h AS
SELECT 
    p.product_id,
    COUNT(DISTINCT p.user_id) as views_in_period,
    COUNT(DISTINCT CASE 
        WHEN EXISTS (
            SELECT 1 FROM events pu
            WHERE pu.event_type = 'purchase'
            AND pu.product_id = p.product_id
            AND pu.user_id = p.user_id
            AND pu.timestamp BETWEEN p.timestamp AND p.timestamp + INTERVAL '24 hours'
        ) THEN p.user_id 
    END) as purchases_within_24h,
    COUNT(DISTINCT CASE 
        WHEN EXISTS (
            SELECT 1 FROM events pu
            WHERE pu.event_type = 'purchase'
            AND pu.product_id = p.product_id
            AND pu.user_id = p.user_id
            AND pu.timestamp BETWEEN p.timestamp AND p.timestamp + INTERVAL '24 hours'
        ) THEN p.user_id 
    END)::FLOAT / COUNT(DISTINCT p.user_id) * 100 as conversion_24h
FROM events p
WHERE p.event_type = 'product_view'
GROUP BY 1;

Атрибуция покупки к просмотру

-- Связываем каждую покупку с соответствующим просмотром
CREATE TABLE conversion_funnel AS
SELECT 
    p.event_id as view_event_id,
    pu.event_id as purchase_event_id,
    p.product_id,
    p.user_id,
    p.session_id,
    p.timestamp as view_timestamp,
    pu.timestamp as purchase_timestamp,
    EXTRACT(EPOCH FROM (pu.timestamp - p.timestamp)) / 3600 as hours_to_purchase,
    pu.amount as purchase_amount
FROM events p
JOIN events pu ON 
    p.product_id = pu.product_id
    AND p.user_id = pu.user_id
    AND p.event_type = 'product_view'
    AND pu.event_type = 'purchase'
    AND pu.timestamp > p.timestamp
    AND pu.timestamp <= p.timestamp + INTERVAL '30 days'  -- конверсионное окно
ORDER BY p.timestamp DESC;

-- Берём только первую покупку после просмотра
CREATE TABLE conversion_funnel_deduplicated AS
SELECT 
    DISTINCT ON (view_event_id) *
FROM conversion_funnel
ORDER BY view_event_id, purchase_timestamp ASC;

Расчёт конверсии по каналам и когортам

-- Конверсия по источнику трафика
CREATE VIEW v_conversion_by_source AS
SELECT 
    u.traffic_source,
    COUNT(DISTINCT CASE WHEN e.event_type = 'product_view' THEN e.user_id END) as views,
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) as purchases,
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END)::FLOAT / 
    COUNT(DISTINCT CASE WHEN e.event_type = 'product_view' THEN e.user_id END) * 100 as conversion_rate
FROM events e
JOIN users u ON e.user_id = u.user_id
WHERE e.event_type IN ('product_view', 'purchase')
GROUP BY u.traffic_source;

-- Когортный анализ (по дате первого посещения)
CREATE VIEW v_cohort_conversion AS
SELECT 
    DATE_TRUNC('week', u.created_at) as cohort_week,
    COUNT(DISTINCT CASE WHEN e.event_type = 'product_view' THEN e.user_id END) as cohort_views,
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END) as cohort_purchases,
    COUNT(DISTINCT CASE WHEN e.event_type = 'purchase' THEN e.user_id END)::FLOAT / 
    COUNT(DISTINCT CASE WHEN e.event_type = 'product_view' THEN e.user_id END) * 100 as cohort_conversion
FROM events e
JOIN users u ON e.user_id = u.user_id
WHERE e.event_type IN ('product_view', 'purchase')
GROUP BY 1
ORDER BY 1 DESC;

Мониторинг аномалий в конверсии

# Обнаружение резкого падения конверсии
import pandas as pd
from scipy import stats

def detect_conversion_anomaly():
    """
    Обнаруживает аномальное падение конверсии
    """
    query = """
    SELECT 
        DATE(timestamp) as date,
        COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) as views,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as purchases,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END)::FLOAT / 
        COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) * 100 as conversion_rate
    FROM events
    WHERE event_type IN ('product_view', 'purchase')
        AND timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY 1
    ORDER BY 1
    """
    
    df = pd.read_sql(query, db_engine)
    
    # Вычисляем z-score для последнего дня
    mean = df['conversion_rate'][:-1].mean()
    std = df['conversion_rate'][:-1].std()
    last_day_rate = df['conversion_rate'].iloc[-1]
    z_score = abs((last_day_rate - mean) / std)
    
    if z_score > 2.5:  # Порог аномалии
        send_alert(
            f"Conversion rate anomaly detected: {last_day_rate:.2f}% "
            f"(expected: {mean:.2f}%, z-score: {z_score:.2f})"
        )
        return True
    
    return False

Best Practices

• Дефиниция: четко определи, что считается "просмотром" и "покупкой"
• Сеансы: используй session_id для корректного подсчёта уникальных пользователей
• Фильтры: исключи ботов и тестовые покупки из расчётов
• Временные окна: выбери подходящее окно для атрибуции (24ч, 7дней, 30дней)
• Когортный анализ: отслеживай изменение конверсии для разных когорт пользователей
• Мониторинг: наблюдай за аномалиями в реальном времени
• Сегментация: рассчитывай конверсию отдельно по каналам, устройствам, географии