Как мы считали идеальный средний чек: от Metabase до оконных функций SQL
Считаете средний чек по классической формуле «выручка / количество заказов» и думаете, что знаете свой бизнес?
Скорее всего, вы ошибаетесь минимум на 15–40%. Рассрочки, подарочные пакеты, нулевые транзакции и маркетинговые акции превращают ваш график в тыкву.
Мы взяли реальные данные клиента из малого бизнеса за 8 лет и с помощью Metabase и SQL очистили их до правды. После очистки «небольшой спад» на графике оказался иллюзией, а реальный чек — сильно выше.
Средний чек – один из важнейших показателей в бизнесе, который:
- дает понимание покупательской способности клиентов;
- упрощает прогноз выручки (если известны трафик и средний чек, то можно точнее оценивать будущие продажи);
- помогает находить точки роста (пакетные предложения, upsell и cross-sell увеличивают оборот);
- помогает оценивать эффективность маркетинга: какие акции, каналы и промо увеличивают сумму покупки, а значит, и прибыль.
Давайте разберемся, как грамотно считать средний чек, ведь это не просто сумма продаж, поделенная на количество платежей. В расчете есть много подводных камней, которые занижают или завышают средний чек.
В качестве примера возьмем базу данных платежей нашего клиента, который занят в малом бизнесе.
Для построения графиков будем использовать систему бизнес-аналитики Metabase – это наглядно покажет, чем отличаются разные подходы и каким может быть путь к идеальному среднему чеку. К тому же эту работу мы в реальности делали в Metabase, поэтому графики будут такими же, как в клиентском дашборде.
Вводные
Компания, которую будем анализировать, имеет в портфеле несколько продуктов стоимостью от 350 до 10 000 рублей. Товары можно покупать в рассрочку (что занижает средний чек), а можно в составе пакетов. А можно в рассрочку и в составе пакетов одновременно – подходы в бизнесе гибкие.
Среди клиентов есть как физические лица, так и юридические.
Нашей задачей было получить удобный для работы и принятия управленческих решений график изменения среднего чека физических лиц.
Первый подход
Начнем с базовой визуализации – средний чек поквартально c 2018 по 2025 год включительно. Формируется в конструкторе запросов Metabase в пару кликов на основе таблицы с транзакциями.
Сразу поместили на график линию тренда (пунктирная), которая показывает, что за 8 лет средний чек вырос. Хотя, судя по основному графику, в 2022–2023 годах был спад, который пришлось нагонять.
Влияние рассрочек на средний чек
Стали разбираться в причинах и первым делом решили избавиться от рассрочек. Их в структуре платежей всего 10%, и их анализом займемся уже в конце.
В базовом варианте платежи в рассрочках будут меньше, чем обычные, что уменьшает средний чек, однако конечная уплаченная сумма за товар остается такой же (или больше).
Более правильный вариант – всё же их оставить, но фиксировать не отдельными транзакциями, а одной в день первого платежа. В этой транзакции учитываем полную стоимость, которую оплатит или уже оплатил клиент.
Рассрочка как инструмент позволяет увеличить средний чек, если клиент вместо одного товара решает купить несколько или тот же товар, но в более дорогой комплектации.
Мы решили пока исключить рассрочки. Ниже представлен отчет, на котором оранжевым цветом обозначен прошлый график (с рассрочками), а синим выделены очищенные данные. Благо Metabase позволяет легко объединить два отчета на дашборде.
В отчете видно, что рассрочки запустили в 1-м квартале 2020 года, а также, что чистый средний чек выше, чем общий.
Но пока не ясна природа падения среднего чека в 3-м квартале 2021 года: то ли что-то изменилось в бизнесе, то ли данные содержат артефакты.
Группировка ближайших транзакций
Мы уже упоминали, что часть продуктов компании можно купить в пакете, а часть по отдельности. Но бывают ситуации, когда клиент приобретает несколько товаров в течение одного дня, вместо того чтобы разом оформить их в пакете.
Чтобы нивелировать такие моменты, все продажи на одного пользователя в течение дня будем считать как одну продажу. Логика такая: если клиент в течение короткого промежутка времени купил несколько товаров, значит, у него была возможность купить эти товары в одном чеке.
Если у вас кофейня или ритейл, то сессия, за которую учитываются покупки, может длиться 2–3 часа. Оплата завтрака за 1200 руб. с последующей покупкой булочки за 120 рублей должна считаться как один чек в 1320 рублей, а не два по 600.
Чтобы такое провернуть в Metabase, надо сперва построить запрос или модель с группировкой по пользователям и дням, а уже после, на его основе рассчитывать средний чек.
После всех манипуляций получаем такой график:
Снизу идет область из прошлой визуализации (средний чек без рассрочек), а сверху средний чек, посчитанный с учетом ближайших платежей.
Общая тенденция повторяется, но со временем графики начинают расходиться. Особенно это заметно с 2022 года. В тот год компания начала использовать стратегию допродаж и предлагала с дисконтом докупить оставшиеся позиции из пакета, если клиент ранее приобрел часть из них.
Каждый товар можно было купить по отдельности, и покупали их в течение дня по несколько штук. Если не суммировать платежи, то средний чек будет меньше, чем фактически платит клиент.
Что еще можно учесть:
- Убрать платежи, по которым были возвраты.
- Считать средний чек по товарным категориям, чтобы понять, где рост, а где падение.
- Убрать особые платежи или клиентов. Например, с юр. лицами мы уже разобрались, но также можно удалить транзакции сотрудников бизнеса, особенно если они берут с корпоративной скидкой по цене, близкой к себестоимости.
Исследование и очистка данных
Но всё же давайте выясним, что за падение было в 3-м квартале 2021 года. Предприниматель, для которого мы делали этот анализ, сходу не вспомнил, что там было.
Глубже погружаемся в тот период:
- Ограничиваем отчет двумя годами.
- Делаем разбивку по месяцам.
Получаем рабочий отчет с линией тренда. Основное падение произошло в июле 2021 года, после этого долго не получалось увеличить средний чек.
Идем смотреть список транзакций за этот период. Предприниматель, может, и не помнит причин, но данные помнят всё.
Не успели открыть таблицу, как сразу же нашли массу транзакций по 0 рублей. В тот год компания запустила акцию с бесплатным доступом к некоторым цифровым продуктам, и этот доступ в информационной системе оформлялся как транзакция с нулевой суммой. Что, разумеется, занижало средний чек.
Убираем из основного отчета такие «платежи», и график сразу преображается:
Снизу предыдущий отчет, сверху нормализованный средний чек. Как видите, он более линейный, без резких колебаний.
Хотя колебания еще остались, и их природу всё же лучше изучить.
Например, вы могли заметить, что в 3-м квартале 2025 года средний чек с нулями оказался выше среднего чека без нулей, чего быть не может. На самом деле это просто особенности сглаживания графика. Реальные значения там одинаковые.
В итоге мы детально проанализировали все транзакции в моменты колебания среднего чека, и они были связаны или с распродажами, или с запуском новых продуктов, цена которых отличалась от значений среднего чека.
Финальный график
В итоге осталась финальная версия графика, и давайте сравним её с начальной, в которой данные не чистились:
Реальный средний чек больше первоначального на 40%, а линия тренда не такая пологая. Теперь график отражает фактическое положение дел в компании, а не просто набор базовых формул.
Возвращаем рассрочки
Но на этом не всё. Хоть рассрочек и не так много, мы всё же решили их вернуть. И тут уже стандартных средств визуального конструктора запросов Metabase недостаточно.
Так как мы хотели в первый платеж рассрочки учитывать всю сумму рассрочки, то потребовались оконные функции SQL и цепочки CTE преобразований:
В случае, если рассрочка уже погашена, мы учитываем реальную полученную сумму платежа. Если же рассрочка еще активная, то мы передаем ожидаемую сумму. Это позволяет не занижать чек в моменте.
После написания всех SQL-запросов и преобразований мы получили такой отчет:
Снизу финальный график без учета рассрочек, а сверху тот, который пошел в работу. Как видите, рассрочки положительно влияют на средний чек. В данном случае рост выручки был на 10–15%.
Куда двигаться дальше
Мы показали лишь часть работы, которую проделали по одному бизнес-показателю. В конечном варианте, который ушел на дашборд, мы:
- добавили фильтры по группам товаров;
- добавили настройку параметров рассрочек;
- исключили возвраты;
- дополнили интерактивный дашборд выбором диапазона даты;
- добавили фильтр группировки, чтобы разбивать отчет не только по кварталам, но и по месяцам и годам.
Все эти дополнения возможны в Metabase, и освоить их вы можете на следующих курсах:
- Бизнес-аналитика с Metabase и SQL
- Основы SQL
- Продвинутый SQL с оконными функциями
- Бизнес-аналитика с Metabase и SQL (Stepik)
- Основы SQL (Stepik)
- Продвинутый SQL с оконными функциями (Stepik)