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

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

Анализ брошенных корзин (Abandoned Cart Analysis) — это критическая задача для e-commerce платформ, позволяющая понять, почему пользователи не завершают покупки, и выявить возможности для увеличения конверсии.

Определение брошенной корзины

Брошенная корзина — это когда пользователь добавил товары в корзину, но не завершил процесс оформления покупки в течение определённого периода (обычно 24-48 часов).

SQL-запрос для выявления брошенных корзин

WITH cart_events AS (
    SELECT 
        user_id,
        session_id,
        event_time,
        event_type,
        cart_value,
        item_count,
        LAG(event_type) OVER (PARTITION BY session_id ORDER BY event_time) as prev_event
    FROM analytics.user_events
    WHERE event_type IN ('add_to_cart', 'checkout_start', 'purchase_complete', 'cart_view')
        AND event_time >= CURRENT_DATE - INTERVAL '30 days'
),
user_sessions AS (
    SELECT 
        user_id,
        session_id,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN event_time END) as last_cart_action,
        MAX(CASE WHEN event_type = 'purchase_complete' THEN event_time END) as purchase_time,
        MAX(cart_value) as final_cart_value,
        MAX(item_count) as items_in_cart,
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN 1 END) as add_to_cart_count
    FROM cart_events
    GROUP BY user_id, session_id
)
SELECT 
    user_id,
    session_id,
    final_cart_value,
    items_in_cart,
    add_to_cart_count,
    last_cart_action,
    EXTRACT(HOUR FROM CURRENT_TIMESTAMP - last_cart_action) as hours_since_abandonment,
    CASE 
        WHEN purchase_time IS NULL 
             AND last_cart_action IS NOT NULL 
             AND CURRENT_TIMESTAMP - last_cart_action > INTERVAL '24 hours'
        THEN 'abandoned'
        WHEN purchase_time IS NOT NULL 
        THEN 'completed'
        ELSE 'active'
    END as cart_status
FROM user_sessions
WHERE last_cart_action IS NOT NULL
ORDER BY final_cart_value DESC;

Анализ причин отказа (behavioral analysis)

WITH abandoned_carts AS (
    SELECT 
        user_id,
        session_id,
        final_cart_value,
        items_in_cart,
        last_cart_action
    FROM user_sessions
    WHERE cart_status = 'abandoned'
),
user_behavior AS (
    SELECT 
        ac.user_id,
        ac.session_id,
        ac.final_cart_value,
        ac.items_in_cart,
        COUNT(DISTINCT CASE WHEN ue.event_type = 'page_view' THEN ue.page_name END) as pages_viewed,
        COUNT(DISTINCT CASE WHEN ue.event_type = 'checkout_start' THEN 1 END) as checkout_attempts,
        COUNT(DISTINCT CASE WHEN ue.event_type = 'promo_code_view' THEN 1 END) as discount_views,
        COUNT(DISTINCT CASE WHEN ue.event_type = 'payment_error' THEN 1 END) as payment_errors,
        COUNT(DISTINCT CASE WHEN ue.event_type = 'shipping_cost_view' THEN 1 END) as shipping_views,
        COUNT(DISTINCT CASE WHEN ue.event_type = 'review_page_view' THEN 1 END) as review_views
    FROM abandoned_carts ac
    LEFT JOIN analytics.user_events ue ON ac.session_id = ue.session_id
        AND ue.event_time BETWEEN ac.last_cart_action - INTERVAL '1 hour' 
                             AND ac.last_cart_action + INTERVAL '30 minutes'
    GROUP BY ac.user_id, ac.session_id, ac.final_cart_value, ac.items_in_cart
)
SELECT 
    *,
    CASE 
        WHEN payment_errors > 0 THEN 'Payment Issue'
        WHEN checkout_attempts = 0 THEN 'Never Started Checkout'
        WHEN shipping_views > 0 AND final_cart_value > 100 THEN 'Likely: High Shipping Cost'
        WHEN pages_viewed < 5 THEN 'Low Engagement'
        WHEN review_views > 0 THEN 'Likely: Considering Options'
        ELSE 'Other Reasons'
    END as abandonment_reason
FROM user_behavior
ORDER BY final_cart_value DESC;

Python-анализ с Pandas

import pandas as pd
import numpy as np
from datetime import datetime, timedelta

# Загружаем события
events_df = pd.read_csv('user_events.csv')
events_df['event_time'] = pd.to_datetime(events_df['event_time'])

# Выявляем брошенные корзины
df = events_df.sort_values(['session_id', 'event_time'])

last_cart = df[df['event_type'] == 'add_to_cart'].groupby('session_id').agg({
    'event_time': 'max',
    'user_id': 'first',
    'cart_value': 'last',
    'item_count': 'last'
}).reset_index()

purchases = df[df['event_type'] == 'purchase_complete'].groupby('session_id')['event_time'].min()

last_cart['purchased'] = last_cart['session_id'].isin(purchases.index)
last_cart['abandoned'] = ~last_cart['purchased']
last_cart['hours_since_abandonment'] = (datetime.now() - last_cart['event_time']).dt.total_seconds() / 3600

# Фильтруем: только абашуки, прошедшие 24 часа
Abandoned = last_cart[(last_cart['abandoned']) & (last_cart['hours_since_abandonment'] > 24)]

# Анализ по стоимости корзины
abandonment_by_value = Abandoned.groupby(
    pd.cut(Abandoned['cart_value'], bins=[0, 50, 100, 250, 1000], 
           labels=['<$50', '$50-100', '$100-250', '>$250'])
).agg({
    'session_id': 'count',
    'cart_value': ['mean', 'sum']
})

print("Брошенные корзины по стоимости:")
print(abandonment_by_value)

# Анализ по количеству товаров
abandonment_by_items = Abandoned.groupby('item_count').agg({
    'session_id': 'count',
    'cart_value': 'mean'
}).sort_values('session_id', ascending=False).head(10)

print("\nТоп-10 количеств товаров в брошенных корзинах:")
print(abandonment_by_items)

Сегментация пользователей

WITH user_metrics AS (
    SELECT 
        user_id,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase_complete' THEN session_id END) as completed_purchases,
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN session_id END) as cart_sessions,
        SUM(CASE WHEN event_type = 'purchase_complete' THEN cart_value ELSE 0 END) as total_spent,
        MAX(event_time) as last_activity
    FROM analytics.user_events
    GROUP BY user_id
)
SELECT 
    user_id,
    completed_purchases,
    cart_sessions,
    ROUND(CAST(completed_purchases AS FLOAT) / NULLIF(cart_sessions, 0) * 100, 2) as conversion_rate,
    total_spent,
    EXTRACT(DAY FROM CURRENT_TIMESTAMP - last_activity) as days_since_activity,
    CASE 
        WHEN completed_purchases = 0 AND cart_sessions > 0 THEN 'Never Converter'
        WHEN conversion_rate < 25 AND completed_purchases > 0 THEN 'Low Converter'
        WHEN conversion_rate >= 50 THEN 'High Converter'
        ELSE 'Regular Customer'
    END as customer_segment
FROM user_metrics
WHERE cart_sessions > 0;

Ключевые метрики

• Abandoned Cart Rate: (брошенные) / (добавленные в корзину)
• Cart Value Recovery: сумма потенциальных продаж
• Most Abandoned Items: товары, которые часто добавляют но не покупают
• Abandonment Funnel: на каком этапе падает конверсия

Этот анализ помогает выявить проблемы в checkout процессе и спланировать retargeting кампании.