Вступление
BoxPlot, диаграмма размаха, ящик с усами, усиковая диаграмма и многие другие вариации названия — это удобный и наглядный способ визуализации размаха ваших данных через так называемые квантили.
Чем он может помочь при анализе данных? На основе такой диаграммы можно сделать такие наблюдения, как:
- каковы ключевые значения, например: средний показатель, медиана, процентили и так далее;
- существуют ли выбросы (аномальные значения) и каковы они;
- симметричны ли данные;
- насколько плотно сгруппированы данные;
- смещены ли данные и, если да, то в каком направлении;
На рисунке ниже пример наиболее привычного внешнего вида такой диаграммы.
общий вид графика BoxPlot
Каждый “ящик” представляет собой распределение какой-либо числовой величины.
Горизонтальная линия внутри прямоугольника — медиана — это значение, находящееся строго посередине всех наблюдений вашего распределения, если бы эти наблюдения были отсортированы по-возрастанию.
Например, если мы возьмем данные по среднему чеку какого-либо интернет магазина, отсортируем их по-возрастанию, то медианой будет являться такое значение чека, ровно половина которых из выборки меньше его, а другая половина больше.
Вы возможно спросите, чем медиана отличается от среднего? Среднее арифметическое больше подвергнуто влиянию аномальных значений в вашей выборке. Например у вас средний чек составляет примерно от 3 до 5 тысяч рублей. Если в вашу систему аналитики или CRM каким-то образом, возможно из-за ошибки или из-за тестовой заявки, попадет покупка намного превышающая эти значения, скажем, 500 тысяч, то среднее арифметическое значительно сдвинется в бОльшую сторону. Медиана же практически не изменится, а может и вовсе остаться прежней.
Визуальный пример выглядел бы как-то так. Допустим мы имеем простейшую выборку данных о среднем чеке:
150, 150, 153, 160, 200, 202, 223, 240, 252, 7000
Среднее значение по этой выборке составит 873, а медиана — 201.
Только из-за одного аномального значения. Поэтому многие придерживаются мнения, что этот показатель намного надежнее среднего и используют в анализе данных его.
Возвращаемся к рисунку. Сами прямоугольники представляют собой межквартильный размах — значения, находящиеся между нижним и верхним квартилями. Квартили — это значения, делящие ряд данных на 4 четверти. Соответственно нижний квартиль расположен на позиции 25%, а верхний квартиль на позиции 75%.
Начало работы
Итак, мы кратко разобрались с понятиями и признали полезность такой диаграммы, но есть плохая новость — в BigQuery отсутствует нативная возможность её построения.
В этой статье мы покажем, как все-таки можно построить график, очень похожий на boxplot.
Для начала нам нужны данные. В нашем примере они искусственные и представляют собой таблицу с полями:
- Дата визита
- Client ID
- Пол пользователя
- Возраст пользователя
- ID транзакции
- Доход с транзакции
Рис.2. Пример данных
Давайте построим графики распределения размера среднего чека в разрезе пола клиентов.
Импортируем нашу таблицу в BigQuery или создаем поток данных через StreamMyData.
Написание SQL-запроса
С помощью следующего SQL-запроса предобрабатываем данные:
SELECT * FROM (
SELECT
'female_revenue' AS parameter,
PERCENTILE_CONT(revenue, 0) OVER() AS minimum,
PERCENTILE_CONT(revenue, 0.25) OVER() AS lower_quartille,
PERCENTILE_CONT(revenue, 0.5) OVER() AS median,
PERCENTILE_CONT(revenue, 0.75) OVER() AS upper_quartille,
PERCENTILE_CONT(revenue, 1) OVER() AS maximum,
FROM
(SELECT revenue FROM `dataupload-230410.dima_tasks_sql.boxplot_test_data_2`
WHERE revenue > 0 AND sex = 'female')
LIMIT 1)
UNION ALL
SELECT * FROM (
SELECT
'male_revenue' AS parameter,
PERCENTILE_CONT(revenue, 0) OVER() AS minimum,
PERCENTILE_CONT(revenue, 0.25) OVER() AS lower_quartille,
PERCENTILE_CONT(revenue, 0.5) OVER() AS median,
PERCENTILE_CONT(revenue, 0.75) OVER() AS upper_quartille,
PERCENTILE_CONT(revenue, 1) OVER() AS maximum,
FROM
(SELECT revenue FROM `dataupload-230410.dima_tasks_sql.boxplot_test_data_2`
WHERE revenue > 1208 AND sex = 'male')
LIMIT 1)
Данный запрос в нашем случае состоит из 2 блоков, вычисляющих Медиану, Квартили, а также максимальное и минимальное значения по стоимости транзакции сначала для женщин, затем для мужчин. Эти 2 блока “складываются” по-вертикали в одну табличку с помощью оператора UNION ALL.
Внутри каждого из блоков происходит отбор необходимых столбцов по заданному фильтру из исходной выгрузки данных:
(SELECT revenue FROM `dataupload-230410.dima_tasks_sql.boxplot_test_data_2`
WHERE revenue > 0 AND sex = 'female')
Здесь мы говорим, “дай нам все значения из столбца доход, у которых пол пользователя женский”.
Затем из получившихся данных вычисляем минимальное значение, квартили, медиану и максимальное значение, а также даем название ‘female_revenue’ отобранным строкам.
Проставляем LIMIT 1 для того, чтобы по каждому полу вывелась только одна строчка
Результат всего SQL-запроса выглядит так:
Построение графика в Looker
Сохраняем наш SQL-запрос в качестве “представления” (view) в BigQuery и импортируем это представление в Looker Studio:
Рис.3. Данные в Looker Studio
Затем выбираем тип диаграммы “Столбчатая комбинированная диаграмма”:
Рис.4. Типы графиков
И располагаем показатели из таблицы следующим образом. Параметр — “parameter”, Показатель — показатели по-возрастанию от минимального значения до максимального:
Рис.5. Параметры и показатели
Затем нужно проставить некоторые настройки во вкладке “Стиль”:
- Ряд данных 1 — тип “Гистограмма”, заливка цвета фона листа. В нашем случае белый:
Рис.6. Ряд данных 1 - Ряд данных 2 — тип “Гистограмма”, заливка светло-зеленая. Это наш верхний “ус”:
Рис.7. Ряд данных 2 - Ряд данных 3 — тип “Линейная диаграмма”, цвет контрастирующий с цветом графика, отмечаем галочки “Показывать точки” и “Метки данных”, толщину линии ставим “Не выбрано”. Это наша медиана:
Рис.8. Ряд данных 3 - Ряд данных 4 — тип “Гистограмма”, заливка темно-зеленая. Это наш межквартильный размах:
Рис.9. Ряд данных 4 - Ряд данных 5 — тип “Гистограмма”, заливка светло-зеленая. Это наш верхний “ус”:
Рис.10. Ряд данных 5 - В блоке “Общие” обязательно отмечаем галочкой “Многоуровневые столбцы”:
Рис.11. Общие настройки стиля - В блоке “обозначения” убираем легенду:
Рис.12. Легенда
Результат
Посмотрим, что у нас получилось:
Рис.13. Результат.
Темно-зеленый — межквартильный размах, нижняя и верхняя границы которого соответственно — 25% квартиль и 75% квартиль. Белые точки — медиана. Светло-зеленая заливка — “усы”.
Выводы
Какие выводы мы можем составить по этой диаграмме:
- медианный чек у у мужчин примерно на 10-15% выше, чем у женщин;
- распределение чеков у женщин более плотное и с меньшим количеством выбросов;
- мужчины совершают больше дорогих покупок, чем женщины;
- минимальный чек у мужчин выше, чем у женщин;
Подобные графики можно построить на совершенно любых значимых для вас данных. Например посмотреть такое же распределение чеков в разрезе возраста, пола + возраста, регионов, рекламных кампаний, рекламных систем и так далее.
Главное, чтобы были данные, удобно собранные в одном месте, а с этим, как всегда, вам поможет StreamMyData.
