Босс: кто покупает в Кубике

Это проект-босс блока D. Вопросы Миры большие, данные в нескольких таблицах, и в каждой своя болячка. Идём тремя фазами, как в первом боссе: разведка, бой, трофей.

Фаза 1. Разведка

Прежде чем считать, проверим, с чем живёт база. Одна строка, пять вопросов:

SELECT
  (SELECT count(*) FROM orders) AS orders,
  (SELECT count(*) FROM orders WHERE status = 'cancelled') AS cancelled,
  (SELECT count(*) FROM (SELECT lower(email) FROM customers
                         GROUP BY lower(email) HAVING count(*) > 1) t) AS shared_emails,
  (SELECT count(*) FROM (SELECT order_id FROM deliveries
                         GROUP BY order_id HAVING count(*) > 1) t) AS retried_orders,
  (SELECT count(*) FROM returns) AS returns;

 orders | cancelled | shared_emails | retried_orders | returns
--------+-----------+---------------+----------------+---------
   6555 |       364 |            19 |            418 |     273

Четыре болячки, и у каждой своё лечение:

1. Отмены. 364 заказа не состоялись. Это не выручка и не покупка, их отсекаем условием status <> 'cancelled' везде.
2. Двойники. 19 адресов почты встречаются у двух аккаунтов: человек зарегистрировался второй раз, набрав адрес с заглавной буквы. Считаем людей по lower(email), а не по id.
3. Вторые попытки доставки. У 418 заказов в deliveries две строки. Подключишь эту таблицу к выручке, и эти заказы посчитаются дважды. Для выручки deliveries не нужна совсем.
4. Возвраты. 273 позиции вернули, это 600 525 ₽. Их вычитаем из выручки и маржи.

Насколько это важно? Выручка с отменёнными заказами 23,10 млн ₽. Без отмен 21,87 млн ₽. Без отмен и возвратов 21,27 млн ₽. А если по ошибке соединить позиции с доставками, получится 23,20 млн ₽: на 1,9 млн больше правды. Вот и разгадка весенней цифры Миры: старый отчёт склеил заказы с доставками, и каждая вторая попытка курьера посчиталась как вторая продажа.

Портрет покупателя

Покупателей, то есть людей хотя бы с одним не отменённым заказом, 2927. Аккаунтов у них 2940: тринадцать человек покупали с обоих аккаунтов. Откуда они:

SELECT coalesce(c.city, 'не указан') AS city,
       count(DISTINCT lower(c.email)) AS buyers,
       sum(i.qty * i.price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1
ORDER BY revenue DESC
LIMIT 4;

 city            | buyers | revenue
-----------------+--------+------------
 Москва          |    904 | 7156332.00
 Санкт-Петербург |    565 | 4159498.00
 Новосибирск     |    219 | 1562605.00
 Екатеринбург    |    189 | 1352504.00

Москва и Петербург дают половину покупателей и половину выручки. Каналы почти равны: поиск (847 человек), соцсети (726), маркетплейс (693). Но первая покупка у них разная. Клиент с игротеки начинает в среднем с 3811 ₽, клиент с маркетплейса с 2751 ₽. Это первый след, который пригодится в фазе 2.