Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Агрегации в аналитике Аналитические задачи в бизнесе

Каждую минуту кто-то в мире совершает покупку или просто заходит на сайт интернет-магазина. Сегодня даже самые маленькие фирмы собирают большое количество данных о покупках, клиентах и партнерах. Данные такого объема бывает трудно помещать на один график и анализировать визуально, поэтому аналитикам нередко приходится прибегать к сжатию данных — агрегации.

В этом уроке вы узнаете, что такое агрегация и как работать с агрегированными данными. Мы изучим пять основных агрегаций и научимся строить их в Google Sheets.

Что такое агрегация

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

На схеме агрегацию данных можно изобразить так:

avatar

К основным агрегациям можно отнести:

  • Количество данных
  • Сумма значений
  • Среднее значение
  • Минимальное значение
  • Максимальное значение

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

Далее на примере аналитической задачи мы разберем основные функции агрегации и узнаем, как их реализовывать в Google Sheets.

Основные агрегации

Представим, что мы аналитики в интернет-магазине «Все для дома». Нам нужно предоставить руководителю отчет о покупках за прошедший месяц. Известно, что ему нужны ответы на следующие вопросы:

  • Как часто клиенты покупали у нас?
  • Сколько мы заработали денег на покупках?
  • Какой средний чек покупки?
  • Какая была максимальная покупка?
  • Какая была минимальная покупка?

Для ответа на эти вопросы нам предоставили таблицу с суммами покупок за последний месяц. Задача — собрать агрегированные данные по информации о покупках.

Для удобства будем собирать эти данные в такую таблицу:

avatar

Количество данных: как часто клиенты покупали у нас?

Чтобы ответить на этот вопрос, можно посчитать количество всех совершенных покупок за месяц.

Нам нужно посчитать количество строк в таблице. В Google Sheets это можно сделать с помощью функции COUNT:

  • Шаг 1. В нужной ячейке вводим =COUNT
  • Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
  • Шаг 3. Нажимаем Enter

avatar

Количество данных для этого набора — 181. Много это или мало? Мы не можем ответить, пока не сравним это количество покупок с той же метрикой за прошлые месяцы.

Сумма значений: cколько мы заработали денег на покупках?

Выручку от продаж можно получить, если сложить суммы всех покупок за месяц.

Посчитаем сумму всех значений столбца «Сумма покупки» в нашей таблице. В Google Sheets это можно сделать с помощью функции SUM.

  • Шаг 1. В нужной ячейке вводим =SUM
  • Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
  • Шаг 3. Нажимаем Enter

avatar

Выручка интернет-магазина за этот месяц составила 848 855,7. На этом этапе мы выяснили, что в прошлом месяце 181 покупка принесла 848 855,7 рубля.

Среднее значение: какой средний чек покупки?

Средний чек покупки — это то же самое, что и среднее значение суммы всех покупок.

Посчитаем сумму всех значений и поделим ее на количество значений. В Google Sheets это можно сделать с помощью функции AVERAGE:

  • Шаг 1. В нужной ячейке вводим =AVERAGE
  • Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
  • Шаг 3. Нажимаем Enter

avatar

Таким образом, средний чек за месяц составил 4 689,81 рубля. Другими словами, именно столько тратят покупатели в нашем интернет-магазине в среднем за одну покупку.

Минимальное значение: какая была минимальная покупка?

Чтобы рассчитать минимальную покупку, нужно найти минимальное значение всех покупок.

Можно отсортировать набор данных по возрастанию и взять самое первое значение. В Google Sheets это можно сделать с помощью функции MIN:

  • Шаг 1. В нужной ячейке вводим =MIN
  • Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
  • Шаг 3. Нажимаем Enter

avatar

Минимальная сумма покупки месяца составила 1,25. Теперь мы знаем, что за месяц не было ни одной покупки ниже этой суммы. Возможно, за такую цену был приобретен пакет, что может указывать на самый дешевый товар в магазине.

Максимальное значение: какая была максимальная покупка?

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

Отсортируем набор данных по убыванию и возьмем первое значение. В Google Sheets это можно сделать с помощью функции MAX:

  • Шаг 1. В нужной ячейке вводим =MAX
  • Шаг 2. Открываем скобки и указываем диапазон значений, для которых нужно посчитать агрегацию
  • Шаг 3. Нажимаем Enter

avatar

Самая дорогая покупка в прошлом месяце обошлась в 46 935 рублей. Это значение сильно выше среднего чека — значит оно могло сильно сместить расчет среднего чека. Мы можем сделать вывод, что в прогнозах не стоит полагаться на получившееся среднее значение.

В итоге у нас получилась вот такая таблица агрегированных данных:

avatar

Краткий отчет может выглядеть следующим образом:

«За прошлый месяц была совершена 181 покупка, что принесло фирме выручку в размере 848 855 рублей. Минимальное и максимальное значения покупок сильно далеки друг от друга, поэтому разброс между суммами покупок большой. Это означает, что средний чек может быть сильно смещен. Вероятно, расчеты среднего чека не показывают, какую сумму среднестатистический покупатель действительно тратит в магазине».

В дополнение скажем, что агрегации можно применять и к нечисловым данным. Например, мы хотим описать набор данных с именами наших клиентов. Тогда можно посчитать количество уникальных значений — количество уникальных имен. Так мы сможем посчитать самое распространенное имя среди наших клиентов — то есть самое часто встречающееся значение. В статистике его еще называют модой.

Выводы

В этом уроке мы узнали, что такое агрегация и для чего нужно агрегировать данные. Теперь вы умеете реализовать агрегацию в Google Sheets.

Напомним ключевые выводы урока:

  • Агрегация — это способ преобразования набора данных в одно результирующее значение. С помощью агрегации мы можем охарактеризовать набор данных, описать его
  • Основные функции агрегации в Google Sheets — это COUNT, SUM, AVERAGE, MIN, MAX
  • Агрегированные данные — это набор агрегаций, посчитанных по одному набору данных. Чем больше агрегаций, тем больше можно узнать о данных

Самостоятельная работа

Как часто клиенты покупали у нас за прошлый месяц? Ответить на этот вопрос можно, рассчитав по имеющимся данным всего одну агрегацию — количество покупок. Однако не всегда бывает так, что ответ на один вопрос кроется в одной агрегации. Чтобы сделать больше аналитических выводов, бывает полезно посчитать сразу несколько агрегаций, даже если их специально никто не требовал.

Представим, что мы решаем аналитические задачи для онлайн-школы, которая занимается подготовкой школьников к ЕГЭ. Онлайн-школа запустилась три месяца назад, и ей хочется оценить успеваемость своих студентов.

В конце каждого месяца студентам предлагали решить пробный вариант экзамена. Экзаменаторы считали качество как процент правильных ответов на задания.

Нажмите, чтобы увидеть тестовые данные

Чтобы решить эту задачу, мы изучим данные об успеваемости за три месяца.

Шаг 1. Посмотрите на данные. Какие агрегации можно посчитать?

Нажмите, чтобы увидеть ответ

По имеющимся данным можно посчитать среднее значение. Оно покажет, какой балл студенты получаются на экзамене в среднем.

Обратите внимание, что среднее значение чувствительно к большой разнице между значениями. Например, один студент сдал экзамен на 100, а другой — на 20, то среднее между ними равно 60. При этом мы не можем сказать, что средняя успеваемость равна 60.

Поэтому полезно посчитать и другие базовые агрегации, которые показывают, есть ли большая разница в значениях. Так мы узнаем, насколько показательны наши значения. Вот эти агрегации стоит еще посчитать:

  • Минимальное значение

  • Максимальное значение

Шаг 2. Рассчитайте агрегации для каждого периода. Какие значения у вас получились?

Нажмите, чтобы увидеть ответ

За три месяца по каждой агрегации должны получиться такие цифры:

eyJpZCI6IjAxYjMwZDZkMzBlM2EwMDAyMmViY2RiYmU1MjAxMjhlLnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=d40773095f9f73d321899e18fedd85a820d5453be46316fb73d6c01672c4db86

Чтобы заполнить такую таблицу в Google Sheets, необходимо использовать функции:

  • AVERAGE() для расчета среднего значения

  • MIN() — для минимального значения

  • MAX() – для максимального значения

Шаг 3. Рассчитайте агрегации по истории онлайн-школы в целом. Какие значения у вас получились? Какие аналитические выводы можно сделать, посмотрев на все агрегации?

Нажмите, чтобы увидеть ответ

В ту же самую таблицу можно добавить колонку с агрегирующими значениями за все периоды:

eyJpZCI6IjQ0ZjU3NDkxMjBkMGY3NmIxZDFjMmVmZDBiZGZhMTVjLnBuZyIsInN0b3JhZ2UiOiJjYWNoZSJ9?signature=a48367d65819969c1e89f6d49c95d28f9c0402b9913989fdd922cbbc14bd3362

Такую колонку можно получить, используя уже имеющиеся значения в таблице: взять среднее значение по уже посчитанным средним, минимальное по всем минимальным и так далее.

На основе этих данных можно сделать такие выводы о последнем месяце:

  • Средний балл студентов – 65. Это меньше, чем в первый месяц. При этом мы не можем однозначно сказать, что успеваемость стала хуже

  • Судя по максимальным значениям, впервые за три месяца появился студент, который смог написать экзамен на 100 баллов из 100

  • Минимальный балл вырос до 42. Это показывает, что за последний месяц выросла успеваемость самых слабых студентов


Дополнительные материалы

  1. Функции в Google Sheets
  2. Список покупок

Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты

Об обучении на Хекслете

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ
1000
упражнений
2000+
часов теории
3200
тестов

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

  • 130 курсов, 2000+ часов теории
  • 1000 практических заданий в браузере
  • 360 000 студентов
Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы
профессия
от 5 025 ₽ в месяц
новый
Сбор, анализ и интерпретация данных
9 месяцев
с нуля
Старт 25 апреля

Используйте Хекслет по-максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Отправляя форму, вы принимаете «Соглашение об обработке персональных данных» и условия «Оферты», а также соглашаетесь с «Условиями использования»