LEFT JOIN для анализа воронок и конверсий

LEFT JOIN — один из самых важных инструментов для аналитика, работающего с воронками конверсии, анализом оттока и поиском пользователей без активности. В отличие от INNER JOIN, который возвращает только совпадающие записи, LEFT JOIN сохраняет все строки из левой таблицы, даже если для них нет соответствия в правой.

Механика LEFT JOIN для анализа воронок

Воронка конверсии — это последовательность этапов, через которые проходит пользователь (регистрация → первый визит → добавление в корзину → покупка). LEFT JOIN позволяет увидеть, сколько пользователей "отвалилось" на каждом этапе.

SELECT 
    u.user_id,
    u.registration_date,
    fv.first_visit_date,
    ac.added_to_cart_date,
    o.first_order_date
FROM users u
LEFT JOIN (
    SELECT user_id, MIN(visit_date) AS first_visit_date
    FROM visits
    GROUP BY user_id
) fv ON u.user_id = fv.user_id
LEFT JOIN (
    SELECT user_id, MIN(cart_date) AS added_to_cart_date
    FROM cart_events
    GROUP BY user_id
) ac ON u.user_id = ac.user_id
LEFT JOIN (
    SELECT user_id, MIN(order_date) AS first_order_date
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id
WHERE u.registration_date >= '2024-01-01';

Этот запрос создает полную картину воронки. Если пользователь не дошел до какого-то этапа, соответствующее поле будет NULL.

Работа с NULL-значениями для расчета конверсий

NULL-значения в результате LEFT JOIN — это ключ к анализу. Они показывают отсутствие активности на следующем этапе.

Расчет конверсии между этапами:

SELECT 
    COUNT(DISTINCT u.user_id) AS total_users,
    COUNT(DISTINCT fv.user_id) AS users_with_visit,
    COUNT(DISTINCT ac.user_id) AS users_added_to_cart,
    COUNT(DISTINCT o.user_id) AS users_with_order,
    ROUND(100.0 * COUNT(DISTINCT fv.user_id) / COUNT(DISTINCT u.user_id), 2) AS visit_rate,
    ROUND(100.0 * COUNT(DISTINCT ac.user_id) / COUNT(DISTINCT fv.user_id), 2) AS cart_rate,
    ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT ac.user_id), 2) AS purchase_rate
FROM users u
LEFT JOIN visits fv ON u.user_id = fv.user_id
LEFT JOIN cart_events ac ON u.user_id = ac.user_id
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.registration_date >= '2024-01-01';

Здесь COUNT(DISTINCT fv.user_id) подсчитывает только пользователей, для которых есть визит (не NULL), а деление дает процент конверсии.

Поиск пользователей без активности

LEFT JOIN идеально подходит для выявления неактивных пользователей или тех, кто не совершил целевое действие.

Задача: найти зарегистрированных пользователей без заказов

SELECT 
    u.user_id,
    u.user_name,
    u.email,
    u.registration_date,
    CURRENT_DATE - u.registration_date AS days_since_registration
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL
    AND u.registration_date < CURRENT_DATE - INTERVAL '30 days'
ORDER BY u.registration_date;

Условие o.order_id IS NULL фильтрует только тех пользователей, для которых не нашлось ни одного заказа. Это основа для анализа оттока и построения реактивационных кампаний.

Анализ пропущенных событий

Задача: найти пользователей, которые добавили товар в корзину, но не оформили заказ

SELECT 
    c.user_id,
    u.user_name,
    COUNT(DISTINCT c.product_id) AS products_in_cart,
    MAX(c.cart_date) AS last_cart_activity,
    o.order_id
FROM cart_events c
INNER JOIN users u ON c.user_id = u.user_id
LEFT JOIN orders o ON c.user_id = o.user_id 
    AND o.order_date >= c.cart_date
    AND o.order_date <= c.cart_date + INTERVAL '7 days'
WHERE o.order_id IS NULL
GROUP BY c.user_id, u.user_name, o.order_id
HAVING MAX(c.cart_date) >= CURRENT_DATE - INTERVAL '14 days';

Этот запрос находит брошенные корзины — пользователей, которые добавили товары, но не купили их в течение 7 дней.

Расчет retention с помощью LEFT JOIN

Задача: посчитать retention пользователей на 7-й день после регистрации

SELECT 
    DATE(u.registration_date) AS cohort_date,
    COUNT(DISTINCT u.user_id) AS total_users,
    COUNT(DISTINCT CASE 
        WHEN v.visit_date BETWEEN u.registration_date + INTERVAL '7 days' 
            AND u.registration_date + INTERVAL '7 days' + INTERVAL '1 day'
        THEN v.user_id 
    END) AS retained_users,
    ROUND(100.0 * COUNT(DISTINCT CASE 
        WHEN v.visit_date BETWEEN u.registration_date + INTERVAL '7 days' 
            AND u.registration_date + INTERVAL '7 days' + INTERVAL '1 day'
        THEN v.user_id 
    END) / COUNT(DISTINCT u.user_id), 2) AS retention_rate_day7
FROM users u
LEFT JOIN visits v ON u.user_id = v.user_id
WHERE u.registration_date >= '2024-01-01'
GROUP BY DATE(u.registration_date)
ORDER BY cohort_date;

Практические советы

Явная проверка NULL: Всегда используйте IS NULL или IS NOT NULL, а не = NULL (это не работает в SQL).

Порядок таблиц имеет значение: В LEFT JOIN левая таблица — это база, правая — дополнение. Меняя порядок, вы меняете логику запроса.

Комбинирование с CASE: Используйте CASE WHEN column IS NOT NULL THEN 1 ELSE 0 END для создания бинарных флагов прохождения этапа.