Вычисляем Вес в приросте в Superset
Сегодня я продолжу знакомить вас с нестандартными задачами, которые появляются в моей работе, и способами их решения.
За информацию из этой статьи, я был готов заплатить деньги, убить или сходить на концерт Шамана.
Уверен, что мой рассказ будет полезен не только пользователям Superset, но и всем аналитикам, которые используют SQL в своей работе и учёбе.
Не буду рассказывать про базовое построение таблиц на BI системе Superset, с этим прекрасно справятся тонны видео на ютубе и бесполезные курсы (про которые я писал ранее). Но сразу скажу, что суперсет в отличие от некоторых других BI систем начинает раскрываться только если ты знаешь SQL, так что хотим мы этого или нет — погружаться в тонкости языка придётся.
Больше двух недель на поиск оптимального решения, ошибки, костыли и элегантный финал.
Итак, Что такое «Вес в приросте» и чем он отличается от обычного «Прироста», и в чем собственно говоря — сложность?
Разберёмся на примере:
Допустим продажи у нас идут в нескольких категориях, пусть это будет «Retail, E-com, и B2B».
По итогам месяца, в сравнении с этим же месяцем прошлого года:
Продажи в Ритейле выросли на 100%,
E-com на 60%,
а канал B2B просел на -90%.
Общий прирост продаж за месяц составил 76%, и из них «Вес» Ритейла составил 63%, E-com 19%, а канал B2B составил всего -6%.
Если мы сложим «Вес в приросте», то мы получим сам Прирост — 76%
Формул «Веса» можно вывести несколько, и сейчас я использую такую:
(Продажи текущие — Продажи прошлые) / Сумма Прошлых продаж
Несмотря на то, что Канал B2B практически полностью перестал продавать, сумма продаж по этому каналу настолько мала, что почти ни оказали влияния на общий итог.
Показать, какой из каналов, какой вес в общий прирост вносит — можно как раз с помощью показателя «Вес в приросте» или иногда встречал формулировку «Вклад в прирост». По сути это наша дельта/разница в продажах, переведённая в проценты.
Давайте быстро сгенерируем данные для анализа (за этой строчкой скрывается два часа генерации хоть сколько-то осмысленных данных).
В первом комментарии к посту в телеграмме будет файл csv для теста, вы можете использовать его если хотите поупражняться в других BI системах, да на худой конец в том же Excel.
Что у нас есть:
Сформулируем главную задачу, на которую мы должны ответить с помощью дашборда:
Какой прирост и какой вес в приросте по месяцам дают разные каналы продаж?
Для примера интересен обычный тип клиентов, в горороде Калининграде. Дашборд должен фильтроваться и подходить для любого региона.
Дополнительные задачи мы сейчас не обсуждаем — проявим творческую натуру, как на тестовых заданиях при трудоустройстве.
Создаём намётки для будущего дашборда, описывать каждый чарт я не буду, чтобы не увеличивать и так не маленькую статью, все они простые:
Весь дашборд собирается за пять минут:
Продажи по месяцам на столбчатой диаграмме
Средняя цена продажи в зависимости от типа клиента
Продажи по каналам с итогом на круговой диаграмме
Продажи по регионам и типу клиента на древовидной диаграмме
Ну и просто сумма продаж за Июль, куда ж мы без красивых отдельно стоящих чисел дублирующую информацию с графиков
Сейчас дашборд не очень информативен, в нём не хватает какой либо динамики и понимания куда мы движемся, сравнения с предыдущим периодом.
Давайте создадим метрику/показатель/меру (зависит от перевода superset) — Прирост оборота, который покажет как растёт или падает любой срез в рублях.
Примечание: данная метрика в такой формуле не учитывает количество рабочих дней
Для этого в supersete создаём метрику прирост
CASE WHEN SUM («sales_last») = 0 THEN NULL ELSE (SUM («sales») / SUM («sales_last»)) — 1 END
Примечание: Не забываем добавить в формулу — условие на проверку деления на ноль, так как если данные будут глубже (например до клиента), и мы захотим вывести их в таблицу, то обязательно будут клиенты которые не покупали в текущем периоде и при выводе табличных данных мы получим ошибку.
Теперь мы можем сделать отдельный график с приростом по месяцам
И вывести отдельно последний прирост заодно
Или наложить его на график с продажами, что я и сделал, сменив тип диаграммы на смешанную
И создав например сводную таблицу по регионам с приростом
И по каналам
Что мы тут видим? Прирост канала B2B перевалил ща 200%, а Еком падает каждый месяц. Посмотрим на продажи подробнее, чтобы визуально проверить, мало ли у нас ошибка в данных или в формуле.
И правда, вот мы видим и рост Ритейла и падение Екома, и кратный рост канала B2B
Вот мы и подошли к тому, из-за чего мы все тут собрались. Какой вклад в Июльский прирост в 22% принёс каждый канал?
И тут у меня произошёл затык. Я просто после первых попыток закопался и не понимал что делать дальше.
Нужно написать такую меру (как Прирост), которая бы взяла разницу в продажах текущего периода конкретного канала и поделила бы его на итоговый показатель продаж прошлого периода. В экселе я бы сослался на конкретную ячейку, в питоне сохранил бы переменную, а как сделать это тут? Как мне найти итоговую сумму, с учетом того что таблица может фильтроваться?
Ниже аж шесть вариантов)
1-ое, что сразу пришло в голову:
пишу меру «ves» и пытаюсь сделать по простому, что-то вроде
(sales - sales_last) / SUM(sales_last)На выходе сходу получаю предупреждение, что я пытаюсь использовать агрегатную функцию внутри определения поля ves, в то время как остальные поля в SELECT и GROUP BY не соответствуют агрегатным функциям.
Потом пробую как-то так:
SUM((sales - sales_last) * ((SUM(sales) OVER() / SUM(sales_last) OVER()) - 1)) / SUM(sales - sales_last)Тоже всё не так. Через пяток другой попыток оставляю написание универсальной меры, пока не придёт новое вдохновение
2 — таблица из запросов
Когда-то раньше я уже выполнял похожую одноразовую задачу и сделал по аналогии:
У меня уже, чуть ранее, были созданы датасеты, отфильтрованные по каналу.
Я создал ещё один виртуальный датасет-запрос, который для каждого из каналов — дополнительно фильтровал его по месяцу, считал прирост, сумму, сохранял, и в итоге повторял это действие снова и снова, склеивая через юнион запросы по каждому месяцу. На выходе я получил правильную таблицу, которая вообще никак не была связана с дашбордом, так как существовала в своём отдельно виртуальном мире. Таблица действительно отражала вес в приросте по каналам, но вообще никак не фильтровалась. И плюс задействовала несколько лишних специально созданных таблиц, которые обитали на сервере, мозоля мне глаза.
Код был настолько ужасен, что решил не показывать тот ужас, который был в запросе. Просто оставлю это в анналах истории нечитабельной картинкой
Тут только четыре месяца практически ручного ввода. Плюс планировалось несколько дашбордов с небольшими изменениями и тогда бы этот код дублировался еще примерно 20 раз, что меня пугало.
Рабочего фильтруемого варианта всё еще нет
3. Индивидуальные расчёты веса в приросте.
Во тьме отчаяния и далеко не сразу — появляется лучик надежды. В одной из подсказок добрых людей мне предлагают написание индивидуальной меры. Без всяких виртуальных таблиц пишется новый показатель, с помощью оператора SQL FILTER, он то как раз и помогает нам ограничить набор данных, к которому применяется агрегатная функция
(SUM (sales — sales_last) FILTER (WHERE channel = 'E-com' AND month = 'Июль') * ((SUM (sales) FILTER (WHERE month = 'Июль') / SUM (sales_last) FILTER (WHERE month = 'Июль')) — 1)) / (SUM (sales) FILTER (WHERE month = 'Июль') — SUM (sales_last) FILTER (WHERE month = 'Июль'))
И получаем «Вес в приросте» на каждый из интересующих нас срезов. Отдельным числом и что самое важное — данные прекрасно фильтруются
Можно добавить УФ, и выделить отрицательный вес, или вклад более 10%
Вот например фильтр по гор. Москва
4. Таблица Handlebars
В целом, главная задача решена. Чуть затянуто, затратно, но результат уже рабочий и с полным функционалом. Осталось придумать как запихнуть это всё в таблицу, так как на большом количестве чисел — удобство полностью пропадало — всё же таблица куда легче воспринималось (в моём рабочем варианте — таких чисел было около 15) — на помощь пришла визуализация на Handlebars.
Возможностей визуализации довольно много, но уже не со стандартной кастомизацией Суперсета, а с помощью написания html таблицы и css кода. Самое главное что пригодилось в текущей ситуации — это что таблицы могла «вклеивать» в конкретную ячейку конкретное значение, т.е. ранее написанную индивидуальную меру.
Корректируем меры, умножаем на 100 и округляем до 1 знака после запятой и добавляем их в панель Handlebars Template
И результат
В целом результат есть, но всё таки еще далеёк от идеала, и прописывать весь год и все показатели довольно муторное дело.
5. Почти правильная мера
В общем, в какой-то момент казалось наткнулся на правильное решение:
Мы создаём виртуальный датасет (или просто превращаем текущий) добавив столбец total_month_last который является суммой sales_last для каждой группы строк, сгруппированных по месяцу, используя конструкцию:
SUM(sales_last) OVER(partition by month) as total_month_last
Ну вот казалось бы и всё, заходим в показатели и редактируем »ves»
(SUM (sales) — SUM (sales_last)) / NULLIF (AVG (total_month_last), 0)
В отличии от прошлых попыток мы просто берем среднее значение »total_month_last» (как вариант просто MAX)
Берем таблицу с приростам по каналам, меняем показатель прирост на показатель ves и сохраняем новый чарт.
Тадам! всё получилось. Сверяем с цифрами сделанными ранее — всё ок.
Итоговая таблица строится, не требует никаких дополнительных настроек, ручного прописывания месяца и тд.
Фильтруем, ставим Владивосток и…
Не удалось…
Нижние «индивидуальные веса» перестроились, а вот наша новая таблица стала некорректной. Она так и считает total_month_last по месяцу, но не для конкретного региона, а для всех. Идеально показывает наше изначальное нефильтрованное состояние.
6. Шаблонизация от Jinja
Я обещал элегантный финал? Вот он!
Используем условную логику для добавления фильтров в запрос.
Мы берем наш виртуальный датасет и прописываем там следующие дополнения:
where 1=1 {% if filter_values ('region') %} and region in {{ filter_values ('region')|where_in }} {% endif %} … и далее для каждого фильтра…
Что тут происходит:
where 1=1 — всегда истинно: подобная конструкция часто используется для упрощения добавления дополнительных условий через наличие или отсутствие определенных фильтров.
{% if filter_values ('territory') %} и далее: — Это блоки условного включения на основе фильтров. Они выполняются с использованием шаблонизации (например, Jinja), которая позволяет динамически изменять запрос на основании наличия значений фильтров.
С замиранием сердца ставим ранее созданные фильтры в суперсете…
Как и просили — Вес в приросте по каналам, для обычных клиентов в Калининграде
Всё отлично работает.
Теперь можно в два клика сделать новую таблицу — например интересен Вес Екома по городам:
Пару секунд и готово
Вот теперь действительно можно погрузиться и посмотреть в разных разрезах на наши изначальные данные. Как видим Москва тут, единственная с Апреля показывает положительный вес (и прирост соответственно)
Еще два клика и меняем показатель «вес» на «прирост», все мгновенно пересчитывается
Вот и всё. Задача решена, счастливый финал. Все довольны. Пока готовил материал сам разобрался еще глубже.
Ставьте лайки, пишите комментарии. Надеюсь данный материал будет вам полезен.
Мой личный блог в телеграмме про аналитику данных
