Введение, или почему красивые отчеты нужно выкидывать только в красивую корзину
Задумывались ли вы, насколько обманчивой может быть простая бизнес-метрика, если смотреть на неё без контекста?
Представьте типичную ситуацию на совещании: руководство смотрит на цифру среднего чека, и эта цифра в 4 раза меньше планируемой. Первая реакция топ-менеджера вполне предсказуема: «Почему так мало? У нас автосервис премиум брендов! Давайте избавимся от всех этих мелких работ вроде сезонного шиномонтажа и хранения колес. Они нам только портят красивую статистику, перегружают мастеров и размывают показатели!»
Звучит логично? На первый взгляд — абсолютно. Но если залезть в реальные данные с головой, выясняется, что подобное «оптимизаторское» решение способно лишить компанию доброй трети валовой выручки.
В этой статье я хочу поделиться реальным кейсом анализа базы данных за последние 10 лет работы крупного автомобильного дилера. Под капотом нашего исследования — многолетний массив из сотен тысяч заказов, история продаж автомобилей, детальные расшифровки по работам, запчастям и поведению клиентов.
Если вы не любитель кода на Python — смело пролистывайте серые блоки и спойлеры. Я постарался сделать так, чтобы графики и бизнес-выводы говорили сами за себя.
О чем мы поговорим в этой статье:
Как очистить 10-летний архив заказов из базы и не сойти с ума от «зависших» заказов и странных номенклатур.
Почему слепое верующее отношение к среднему чеку приводит к катастрофе в маркетинге (и при чем тут Симпсон).
Как правильно разделить клиентов на «своих» (купивших авто у нас) и «чужих», и почему их поведение кардинально отличается.
Главная интрига: почему автовладельцы, регулярного приезжающие на «дешевую» сезонную переобувку, за свой жизненный цикл (LTV) приносят компании в 3 раза больше денег, чем те, кто заезжает исключительно на редкий крупный ремонт.
Итак, погружаемся в детали!
Шаг 1. Готовим данные: отсекаем шум и смотрим на макропоказатели
Как водится в любых реальных проектах, выгрузка из ERP-системы редко приходит в идеальном «стерильном» виде. А уж тем более если это исторический датасет за 10 лет работы предприятия.
В сыром датасете содержится 8 основных характеристик: уникальный ID заказа, даты создания, взятия в работу, обновления и завершения, хешированный VIN-номер автомобиля, а также начисленная и фактически уплаченная сумма.
Первым делом импортируем необходимые библиотеки и наведем порядок. Так же в рамках этой публикации нам придется убрать реальные цифры и перевести все в проценты:
import pandas as pd import numpy as np import matplotlib.pyplot as plt import seaborn as sns from matplotlib.ticker import FuncFormatter # Загружаем выгрузку заказ-нарядов data = pd.read_csv('data/orders_2015_2024.csv', sep=';') # Приводим колонки с датами к формату datetime date_columns = ['date_created', 'date_started', 'date_updated', 'date_completed'] for col in date_columns: data[col] = pd.to_datetime(data[col], format='%d.%m.%Y %H:%M') # АНОНИМИЗАЦИЯ: Смотрим на структуру базы в относительных величинах (%) total_records = len(data) canceled_orders = data['sum_paid'].isna().sum() print(f"Общая структура сырой базы: 100.0%") print(f"Доля незавершенных/неоплаченных заказов: {(canceled_orders / total_records) * 100:.1f}%") # Отфильтровываем только завершенные и оплаченные заказы completed_orders = data[ (data['date_completed'].notna()) & (data['sum_paid'].notna()) ].copy() print(f"Доля успешно обработанных заказов для анализа: {(len(completed_orders) / total_records) * 100:.1f}%")
Общая структура сырой базы: 100.0% Доля незавершенных/неоплаченных заказов: 8.4% Доля успешно обработанных заказов для анализа: 91.6%
Около 8% заказов в базе оказались незавершенными или неоплаченными (отказы клиентов, технические дубли, ошибки менеджеров при вводе заказов). В этот раз не будем разбираться, почему эти заказы зависли — отнесем это к «мелким недоразумениям», одной рукой прикроем глаза, а второй просто выкинем их из выборки и больше не будем о них вспоминать.
В итоге у нас остается чистый массив, составляющий более 91% от исходного датасета. Накопленная за десятилетие плотность данных (счет идет на сотни тысяч завершенных заказов) гарантирует, что наше исследование будет репрезентативным, а статистические выбросы не исказят финальные бизнес-выводы.
Динамика выручки: 10 лет на качелях
Давайте взглянем на то, как менялась ежемесячная выручка сервиса на протяжении всей декады:
# Группируем данные по месяцам даты завершения заказа monthly_revenue = ( completed_orders.set_index("date_completed") .groupby(pd.Grouper(freq="ME"))["sum_paid"] .sum() .reset_index() ) base_value = monthly_revenue["sum_paid"].iloc[0] monthly_revenue["revenue_pct"] = (monthly_revenue["sum_paid"] / base_value) * 100
Посмотреть код для настройки и отрисовки графика выручки
# Пусть график выглядит красиво sns.set_theme(style="white") # Убираем стандартную серую сетку Seaborn plt.rcParams["font.family"] = "sans-serif" # Чистый шрифт без засечек fig, ax = plt.subplots(figsize=(11, 5.5), dpi=150) # Построение графика sns.lineplot( data=monthly_revenue, x="date_completed", y="revenue_pct", color="#0F62FE", # Современный технологичный синий (IBM Design) linewidth=2.5, marker="o", markersize=7, markerfacecolor="#0F62FE", markeredgecolor="white", markeredgewidth=1.5, ax=ax, ) # Тонкая настройка сетки и границ (десеткация) ax.grid( axis="y", linestyle="--", color="#E0E0E0", linewidth=0.7 ) # Только горизонтальные линии sns.despine(left=True, bottom=False) # Удаляем верхнюю, правую и левую границы # 5. Оформление текста (Выравнивание по левому краю для заголовка) ax.text( 0.0, 1.12, "Динамика ежемесячной выручки", fontsize=16, fontweight="bold", color="#161616", transform=ax.transAxes, ) ax.text( 0.0, 1.06, "Относительные показатели продаж компании (Базовый месяц = 100%)", fontsize=11, color="#525252", transform=ax.transAxes, ) # Убираем стандартные крупные подписи осей (все понятно из контекста) ax.set_xlabel("", fontsize=11, color="#525252", labelpad=10) ax.set_ylabel("", fontsize=11, color="#525252") # Форматирование дат на оси X ax.tick_params(axis="both", which="major", labelsize=10, colors="#525252") fig.autofmt_xdate(rotation=0, ha="center") # Красивое выравнивание дат без наклона # Форматирование оси Y def percent_format(x, pos): return f"{int(x)}%" ax.yaxis.set_major_formatter(FuncFormatter(percent_format)) # Аккуратные подписи значений (выводим через одну, но оставляем последнюю) step = 2 # Шаг отображения: 2 = через одну, 3 = через две и так далее total_points = len(monthly_revenue) for i, row in monthly_revenue.iterrows(): # Условие: выводим точку, если она кратна шагу ИЛИ если это самый последний месяц if (i % step == 0) or (i == total_points - 1): # Небольшая проверка: если последняя точка встает слишком близко к предыдущей по шагу, # можно пропустить предпоследнюю подпись во избежание наложения if (i == total_points - 1) and ( (i - 1) % step == 0 ) and (total_points > 1): # (Опционально) этот блок автоматически предотвратит наложение # последней точки на предпоследнюю, если они идут подряд pass ax.text( row["date_completed"], row["revenue_pct"] + 4, # Смещение вверх относительно линии f"{int(row['revenue_pct'])}%", ha="center", va="bottom", fontsize=9, fontweight="semibold", color="#393939", ) plt.tight_layout() plt.show()
На графике отчетливо видны три ключевых тренда:
Ярко выраженная сезонность: Начало каждого года (январь–февраль) традиционно стартует с просадки. Конец года — короткий всплеск в ноябре с последующим затишьем в праздники.
Устойчивый рост до 2022 года: Предприятие планомерно наращивало обороты, пик выручки пришелся на 2021 — начало 2022 года.
Кризис и восстановление: В 2022 году произошел прогнозируемый обвал (уход европейских брендов, проблемы с поставками оригинальных запчастей). Однако хорошая новость в том, что к 2024 году выручка начала уверенно восстанавливаться.
Загадка слишком низкого чека
А теперь давайте посчитаем стандартную метрику, на которую так любит ориентироваться топ-менеджмент — средний чек. Нам снова придется перевести абсолютные финансовые показатели в относительный формат: примем средний чек за первый год наблюдений за 100%, а для остальных лет посчитаем индекс роста.
# Считаем средний чек по годам economics_metrics = ( completed_orders.set_index("date_completed") .groupby(pd.Grouper(freq="YE")) .agg( total_revenue=("sum_paid", "sum"), average_check=("sum_paid", "mean"), total_orders=("order_id", "count"), ) .reset_index() ) economics_metrics["year"] = economics_metrics["date_completed"].dt.year # ========================================== # АНОНИМИЗАЦИЯ: Расчет индекса среднего чека # ========================================== base_check = economics_metrics["average_check"].iloc[0] # Первый год = 100% for idx, row in economics_metrics.iterrows(): # Считаем процент относительно базового года check_index = (row["average_check"] / base_check) * 100 print( f"Год {int(row['year'])}: Индекс среднего чека = {check_index:.1f}%" )
Год 2015: Индекс среднего чека = 100.0% Год 2017: Индекс среднего чека = 110.1% Год 2020: Индекс среднего чека = 120.0% Год 2022: Индекс среднего чека = 124.1% Год 2024: Индекс среднего чека = 138.2%
Стоп. Давайте задумаемся.
Мы анализируем крупного дилера, работающего в премиум-сегменте (где запчасти и нормо-часы за 10 лет подорожали в разы). Почему к 2024 году индекс среднего чека вырос всего до 138% от уровня 2015 года? С учетом накопившейся за декаду инфляции (которая превысила 80–90%) это означает, что в реальном выражении наш средний чек не просто не вырос — он ощутимо «просел»!
Неужели клиенты премиальных марок стали настолько экономными? Или мы ремонтируем машины «вполсилы»?
Чтобы ответить на этот вопрос, нам придется спуститься на уровень ниже — в конкретные услуги и работы внутри каждого заказа. И поверьте, там нас ждет очень неожиданный сюрприз.
Шаг 2. Разбор номенклатуры: кто сбил средний чек премиум-сервиса?
Чтобы понять, почему простая арифметика выдает скромный средний чек на заказ, давайте заглянем в детализацию работ внутри этих заказов. Посмотрим на топ самых часто выполняемых номенклатурных позиций за 10 лет, переведя их абсолютное количество в проценты от общего объема оказанных услуг:
# Загружаем детальную историю по работам и запчастям внутри заказов orders_breakdowns = pd.read_csv('data/orders_breakdowns_historical.csv', sep=';') # Объединяем с базой завершенных заказов completed_orders_detailed = completed_orders.merge(orders_breakdowns, on='order_id') # Считаем долю каждой услуги от общего объема всех выполненных строк top_items_pct = (completed_orders_detailed['item_id'].value_counts(normalize=True).head(10) * 100) print("Топ-10 самых частых услуг в сервисе за 10 лет (в % от общего объема работ):") print(top_items_pct.round(2).astype(str) + '%')
Топ-10 самых частых услуг в сервисе за 10 лет (в % от общего объема работ): 40-0001-02 12.2% # Сезонное хранение комплекта колес 00-8030-01 12.2% # Подготовка автомобиля к работе 54-1011-01 5.5% # Входящий тест электрооборудования 40-1450-99 2.8% # Снятие и установка рабочих колес 40-2070-99 2.8% # Балансировка колес 40-2440-99 2.7% # Замена шин (демонтаж/монтаж) 42-0004-01 2.6% # Проверка тормозной системы 40-2181-01 2.5% # Балансировочные грузики 00-9015-00 1.8% # Осмотр ходовой части 54-0650-01 1.6% # Поддержание напряжения бортовой сети при диагностике
Вот она, разгадка!
Оказывается, огромную долю в структуре заездов занимают вовсе не капитальные ремонты двигателей или замена пневмоподвески, а массовые сезонные операции: шиномонтаж, балансировка, хранение колес и базовые экспресс-диагностики.
В базе найдутся десятки тысяч чеков на мелкие фиксированные суммы (составляющие едва ли четверть от целевого среднего чека компании), где кроме кода 40-0001-02 (Сезонное хранение) больше ничего нет.
Что происходит со средним чеком?
Включается банальный математический эффект: гигантский объем регулярных чеков с минимальной стоимостью «тянет» арифметическое среднее вниз. Бизнес видит скромную итоговую цифру в отчете и думает, что зарабатывает мало на одном клиенте.
Интересные статьи
Интересные статьи
