Как найти пользователей просмотревших товар без добавления в корзину?

Как найти пользователей, просмотревших товар без добавления в корзину

Это классическая задача аналитики для e-commerce, которая помогает выявить товары с потенциальными проблемами конверсии и возможности для ремаркетинга. Рассмотрю несколько подходов к её решению.

Определение задачи

Нам нужно найти пользователей, которые:

1. Просмотрели товар (страница товара была загружена)
2. НЕ добавили его в корзину
3. (Опционально) количество уникальных просмотров

Это требует анализа событий пользователей и их последовательностей.

Подход 1: SQL с использованием LEFT JOIN

Это самый прямолинейный подход для статических данных:

SELECT DISTINCT
    pv.user_id,
    pv.product_id,
    COUNT(*) as view_count,
    MAX(pv.view_timestamp) as last_view_time
FROM product_views pv
LEFT JOIN cart_additions ca 
    ON pv.user_id = ca.user_id 
    AND pv.product_id = ca.product_id
    AND ca.add_timestamp > pv.view_timestamp
WHERE ca.cart_id IS NULL  -- Левая часть есть, правой нет
GROUP BY pv.user_id, pv.product_id
ORDER BY view_count DESC;

Как это работает:

• product_views содержит все просмотры товаров
• cart_additions содержит добавления в корзину
• LEFT JOIN сохраняет все записи из левой таблицы
• WHERE ca.cart_id IS NULL выбирает только те просмотры, у которых нет соответствующего добавления в корзину
• COUNT(*) дает количество повторных просмотров одного товара пользователем

Подход 2: SQL с использованием NOT IN

Альтернативный подход, который может быть проще для понимания:

SELECT 
    user_id,
    product_id,
    COUNT(*) as view_count,
    COUNT(DISTINCT DATE(view_timestamp)) as days_viewed
FROM product_views
WHERE (user_id, product_id) NOT IN (
    SELECT DISTINCT user_id, product_id
    FROM cart_additions
)
GROUP BY user_id, product_id
ORDER BY view_count DESC;

Преимущества:

• Более читаемо
• Хорошо работает для малых наборов данных в подзапросе

Недостатки:

• Может быть медленнее на очень больших таблицах

Подход 3: SQL с использованием ANTI JOIN (рекомендуемый для больших данных)

SELECT 
    pv.user_id,
    pv.product_id,
    COUNT(*) as total_views,
    COUNT(DISTINCT DATE(pv.view_timestamp)) as unique_view_days,
    MAX(pv.view_timestamp) as last_view,
    MIN(pv.view_timestamp) as first_view
FROM product_views pv
WHERE NOT EXISTS (
    SELECT 1
    FROM cart_additions ca
    WHERE ca.user_id = pv.user_id
    AND ca.product_id = pv.product_id
)
GROUP BY pv.user_id, pv.product_id
HAVING COUNT(*) >= 2  -- Опционально: товар просмотрен хотя бы дважды
ORDER BY total_views DESC;

Этот подход самый оптимальный для больших таблиц.

Подход 4: Apache Spark (для распределенной обработки)

Когда данные слишком большие для одной машины:

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("ViewsWithoutCart").getOrCreate()

# Чтение данных
product_views = spark.read.parquet("s3://bucket/events/product_views")
cart_additions = spark.read.parquet("s3://bucket/events/cart_additions")

# Агрегация просмотров
views_agg = product_views.groupBy("user_id", "product_id").agg(
    F.count("*").alias("total_views"),
    F.countDistinct(F.to_date("view_timestamp")).alias("unique_view_days"),
    F.max("view_timestamp").alias("last_view"),
    F.min("view_timestamp").alias("first_view")
)

# Агрегация добавлений в корзину
cart_agg = cart_additions.groupBy("user_id", "product_id").agg(
    F.count("*").alias("add_count")
).select("user_id", "product_id")

# ANTI JOIN - левая часть без пересечения с правой
result = views_agg.join(
    cart_agg,
    on=["user_id", "product_id"],
    how="anti"  # ANTI JOIN
).filter(
    F.col("total_views") >= 2  # Опционально: хотя бы 2 просмотра
).orderBy(F.desc("total_views"))

result.write.parquet("s3://bucket/output/users_viewed_not_added")

Подход 5: Временной анализ (с учетом последовательности событий)

Если важна последовательность — нужно убедиться, что пользователь просмотрел товар ДО момента истечения сессии:

WITH user_product_events AS (
    SELECT 
        user_id,
        product_id,
        view_timestamp,
        'view' as event_type,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY view_timestamp) as rn
    FROM product_views
    
    UNION ALL
    
    SELECT 
        user_id,
        product_id,
        add_timestamp,
        'add_to_cart' as event_type,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY add_timestamp) as rn
    FROM cart_additions
)
SELECT 
    user_id,
    product_id,
    COUNT(CASE WHEN event_type = 'view' THEN 1 END) as view_count
FROM user_product_events
GROUP BY user_id, product_id
HAVING 
    COUNT(CASE WHEN event_type = 'view' THEN 1 END) > 0
    AND COUNT(CASE WHEN event_type = 'add_to_cart' THEN 1 END) = 0
ORDER BY view_count DESC;

Подход 6: Сегментация пользователей для ремаркетинга

Логично дополнить анализ сегментацией:

WITH viewed_not_added AS (
    SELECT 
        pv.user_id,
        pv.product_id,
        p.product_name,
        p.price,
        p.category,
        COUNT(*) as view_count,
        MAX(pv.view_timestamp) as last_view
    FROM product_views pv
    JOIN products p ON pv.product_id = p.id
    LEFT JOIN cart_additions ca 
        ON pv.user_id = ca.user_id 
        AND pv.product_id = ca.product_id
    WHERE ca.cart_id IS NULL
    GROUP BY pv.user_id, pv.product_id, p.product_name, p.price, p.category
)
SELECT 
    user_id,
    COUNT(*) as abandoned_views_count,
    SUM(price) as total_value_at_risk,
    MIN(last_view) as earliest_last_view,
    MAX(last_view) as latest_last_view,
    DATEDIFF(CURRENT_DATE, MAX(last_view)) as days_since_last_view,
    CASE 
        WHEN COUNT(*) > 5 THEN 'High Priority'
        WHEN COUNT(*) > 2 THEN 'Medium Priority'
        ELSE 'Low Priority'
    END as remarketing_segment
FROM viewed_not_added
GROUP BY user_id
HAVING DATEDIFF(CURRENT_DATE, MAX(last_view)) <= 30  -- Только за последний месяц
ORDER BY total_value_at_risk DESC;

Оптимизация запроса

-- Добавьте индексы для ускорения
CREATE INDEX idx_pv_user_product ON product_views(user_id, product_id);
CREATE INDEX idx_pv_timestamp ON product_views(view_timestamp);
CREATE INDEX idx_ca_user_product ON cart_additions(user_id, product_id);

-- Используйте partition по датам если таблицы очень большие
ALTER TABLE product_views 
PARTITION BY RANGE(YEAR(view_timestamp)) (
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Важные замечания

1. Определение просмотра: Нужно четко определить, что считается просмотром (загрузка страницы, скролл на странице товара и т.д.)

2. Временное окно: Не стоит рассматривать очень старые просмотры. Используйте WHERE условие для фильтрации по датам.

3. Несколько визитов: Пользователь может переходить в корзину на следующий день. Убедитесь, что анализируете достаточный период.

4. Дубликаты: Одна и та же пара (user_id, product_id) может быть в обеих таблицах несколько раз. Используйте DISTINCT если нужны уникальные пользователи.

5. Производительность: Для таблиц с миллиардами строк ANTI JOIN обычно быстрее, чем LEFT JOIN + WHERE IS NULL.

Использование результатов

Полученные данные можно использовать для:

• Email ремаркетинга: отправить письма о товарах, которые пользователи просматривали
• Динамической рекламы: показывать объявления о просмотренных товарах
• A/B тестирования: улучшать страницы товаров с низкой конверсией
• Анализа UX: выявлять проблемы на страницах товаров

Этот анализ критичен для повышения конверсии в e-commerce и увеличения среднего чека.