Трансформируем ваши данные в прибыль

Пн — Пт: с 10:00 до 19:00

ГлавнаяБлогКак построить график BoxPlot в LookerStudio с использованием BigQuery
,

Как построить график BoxPlot в LookerStudio с использованием BigQuery

5 минут(ы)

Вступление

BoxPlot, диаграмма размаха, ящик с усами, усиковая диаграмма и многие другие вариации названия — это удобный и наглядный способ визуализации размаха ваших данных через так называемые квантили.

Чем он может помочь при анализе данных? На основе такой диаграммы можно сделать такие наблюдения, как:

  • каковы ключевые значения, например: средний показатель, медиана, процентили и так далее;
  • существуют ли выбросы (аномальные значения) и каковы они;
  • симметричны ли данные;
  • насколько плотно сгруппированы данные;
  • смещены ли данные и, если да, то в каком направлении;

На рисунке ниже пример наиболее привычного внешнего вида такой диаграммы.

общий вид графика 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. Пример данных
Рис.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-запроса выглядит так:

Результат всего SQL-запроса

Построение графика в Looker

Сохраняем наш SQL-запрос в качестве “представления” (view) в BigQuery и импортируем это представление в Looker Studio:

Рис.3. Данные в Looker Studio
Рис.3. Данные в Looker Studio

Затем выбираем тип диаграммы “Столбчатая комбинированная диаграмма”:

Рис.4. Типы графиков
Рис.4. Типы графиков

И располагаем показатели из таблицы следующим образом. Параметр — “parameter”, Показатель — показатели по-возрастанию от минимального значения до максимального:

Рис.5. Параметры и показатели
Рис.5. Параметры и показатели

Затем нужно проставить некоторые настройки во вкладке “Стиль”:

  • Ряд данных 1 — тип “Гистограмма”, заливка цвета фона листа. В нашем случае белый:
    Рис.6. Ряд данных 1
    Рис.6. Ряд данных 1
  • Ряд данных 2 — тип “Гистограмма”, заливка светло-зеленая. Это наш верхний “ус”:
    Рис.7. Ряд данных 2
    Рис.7. Ряд данных 2
  • Ряд данных 3 — тип “Линейная диаграмма”, цвет контрастирующий с цветом графика, отмечаем галочки “Показывать точки” и “Метки данных”, толщину линии ставим “Не выбрано”. Это наша медиана:
    Ряд данных 3 - тип “Линейная диаграмма”, цвет контрастирующий с цветом графика, отмечаем галочки “Показывать точки” и “Метки данных”, толщину линии ставим “Не выбрано”. Это наша медиана:
    Рис.8. Ряд данных 3
  • Ряд данных 4 — тип “Гистограмма”, заливка темно-зеленая. Это наш межквартильный размах:
    Рис.9. Ряд данных 4
    Рис.9. Ряд данных 4
  • Ряд данных 5 — тип “Гистограмма”, заливка светло-зеленая. Это наш верхний “ус”:
    Рис.10. Ряд данных 5
    Рис.10. Ряд данных 5
  • В блоке “Общие” обязательно отмечаем галочкой “Многоуровневые столбцы”:
    Рис.11. Общие настройки стиля
    Рис.11. Общие настройки стиля
  • В блоке “обозначения” убираем легенду:
    Рис.12. Легенда
    Рис.12. Легенда

Результат

Посмотрим, что у нас получилось:

Рис.13. Результат.
Рис.13. Результат.

Темно-зеленый — межквартильный размах, нижняя и верхняя границы которого соответственно — 25% квартиль и 75% квартиль. Белые точки — медиана. Светло-зеленая заливка — “усы”.

Выводы

Какие выводы мы можем составить по этой диаграмме:

  • медианный чек у у мужчин примерно на 10-15% выше, чем у женщин;
  • распределение чеков у женщин более плотное и с меньшим количеством выбросов;
  • мужчины совершают больше дорогих покупок, чем женщины;
  • минимальный чек у мужчин выше, чем у женщин;

Подобные графики можно построить на совершенно любых значимых для вас данных. Например посмотреть такое же распределение чеков в разрезе возраста, пола + возраста, регионов, рекламных кампаний, рекламных систем и так далее.

Главное, чтобы были данные, удобно собранные в одном месте, а с этим, как всегда, вам поможет StreamMyData.