02.05.2025

E-commerce SQL Исследование: Поведение клиентов и анализ продаж

📋 Техническое задание

Цель:
Понять поведение клиентов, построить воронку продаж, определить ключевые метрики, влияющие на выручку.
На выходе: SQL-запросы, ER-диаграмма и краткие выводы.

🧩 Структура базы данных

База содержит следующие таблицы:

customers
leads_qualified
leads_closed
orders
order_items
order_payments
order_reviews
products
product_category_name_translations
sellers

ER-диаграмма создана в DBeaver:

📊 Аналитика по шагам

1. Воронка продаж

Цель: Посчитать, сколько клиентов прошли путь: → lead
→ qualified
→ заказ
→ оплата

– Всего лидов:

SELECT COUNT(*) AS total_leads FROM leads_qualified;

– Квалифицированные лиды:

SELECT COUNT(DISTINCT mql_id) AS qualified_leads FROM leads_closed WHERE won_date IS NOT NULL;

– Сделавшие заказы:

SELECT COUNT(DISTINCT o.customer_id) AS buyers
FROM leads_closed lc
JOIN order_items oi ON lc.seller_id = oi.seller_id
JOIN orders o ON oi.order_id = o.order_id
WHERE lc.won_date IS NOT NULL;

– Оплата заказов:

SELECT COUNT(DISTINCT o.customer_id) AS paying_customers
FROM leads_closed lc
JOIN order_items oi ON lc.seller_id = oi.seller_id
JOIN orders o ON oi.order_id = o.order_id
JOIN order_payments op ON o.order_id = op.order_id
WHERE lc.won_date IS NOT NULL;

2. Клиентская аналитика

– Уникальные покупатели:

SELECT COUNT(DISTINCT customer_id) FROM orders;

– Повторные заказы:

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

Повторных заказов нет.

– Средний чек:

SELECT AVG(payment_value) AS avg_order_value FROM order_payments;

– LTV по клиентам:

SELECT customer_id, SUM(payment_value) AS ltv
FROM orders o
JOIN order_payments op ON o.order_id = op.order_id
GROUP BY customer_id;

3. Анализ заказов

– Заказы по месяцам:

SELECT strftime('%Y-%m', order_purchase_timestamp) AS month, COUNT(*) AS order_count
FROM orders
GROUP BY month;

– Оплаты по типам:

SELECT payment_type, COUNT(*) AS payment_count
FROM order_payments
GROUP BY payment_type;

– Популярные категории:

SELECT pct.product_category_name_english, COUNT(*) AS orders_count
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN product_category_name_translations pct 
  ON p.product_category_name = pct.product_category_name
GROUP BY pct.product_category_name_english
ORDER BY orders_count DESC
LIMIT 10;

4. Анализ продавцов

– Количество продавцов:

SELECT COUNT(DISTINCT seller_id) FROM sellers;

– Выручка по продавцам:

SELECT oi.seller_id, SUM(op.payment_value) AS revenue
FROM order_items oi
JOIN order_payments op ON oi.order_id = op.order_id
GROUP BY oi.seller_id
ORDER BY revenue DESC
LIMIT 10;

5. Отзывы и рейтинг

– Средний рейтинг:

SELECT AVG(review_score) AS avg_score FROM order_reviews;

– Средний рейтинг по категориям:

SELECT pct.product_category_name_english, AVG(orw.review_score) AS avg_score
FROM order_reviews orw
JOIN orders o ON orw.order_id = o.order_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN product_category_name_translations pct 
  ON p.product_category_name = pct.product_category_name
GROUP BY pct.product_category_name_english
ORDER BY avg_score DESC
LIMIT 10;

📝 Выводы:

Потери происходят на каждом этапе воронки, особенно между лидом и заказом.
Повторных заказов никто не совершает.
Средний чек — стабильный и влияет на общий доход.
Большая часть оплат — кредитной картой.
Некоторые продавцы дают непропорционально высокую выручку.
Пиковый спрос: ноябрь - март.