![](https://habrastorage.org/getpro/habr/upload_files/331/f6b/7e7/331f6b7e73c1dbeb23af43c2b5316507.jpg)
Аналитики строили систему рекомендаций для менеджеров по работе с авиакомпаниями. Рекомендация должна помочь менеджеру вовремя заметить отклонения в показателях авиакомпании, оперативно отреагировать и принять экономически выгодное решение.
Эта статья о том, как аналитики делали базовую систему рекомендаций, расширяли и углубляли её, увеличивали количество возможных исходов до сотни тысяч и возвращались к началу. Расскажем о том, как система устроена, какие предлагает рекомендации и почему оказалось, что 17 исходов лучше ста тысяч. Вот как это было…
Система рекомендаций
В этой статье понятие «система рекомендаций» не имеет ничего общего с рекомендательными системами и машинным обучением. Система рекомендаций предлагает не продукт пользователю исходя из его предпочтений, она предлагает решение менеджеру исходя из отклонений показателей. Система рекомендаций в этом случае больше напоминает систему поддержки принятия решений.
Бизнес-модель
![](https://habrastorage.org/getpro/habr/upload_files/374/0c5/38c/3740c538c1165d0277ed226de3906a7e.jpg)
В авиации десятки показателей применяются для измерения экономической выгодности рейса. На треть из них влияет аэропорт, остальные — контролируются авиакомпанией. Если менеджеры аэропорта и представители авиакомпаний эффективно общаются, то растут доходы и тех, и других.
Аэропорты и авиакомпании вместе открывают направления, увеличивают частоту рейсов или повышают рентабельность полетов. Это возможно благодаря оперативной реакции на изменение спроса пассажиров, геополитической ситуации и экономических факторов.
Чем больше пассажиров летает и чем больше авиакомпания выполняет рейсов, тем больше доход перевозчика и аэропорта. Доходы обоих прямо коррелируют с количеством пассажиров на рейсах. Конечно, далеко не стоимость билета попадает в выручку аэропорта, большая часть остается у авиакомпании.
Менеджеру по работе с авиакомпаниями в аэропорту приходится отслеживать показатели 20 разных авиалиний и принимать на их основе оперативные решения. Это трудоёмко, поэтому аэропорту потребовалась система рекомендаций.
До начала работы
![Схема обработки данных в аэропорту Схема обработки данных в аэропорту](https://habrastorage.org/getpro/habr/upload_files/e85/ae4/bd5/e85ae4bd55c94c65ebbb1a6ad0df2d77.jpg)
Аналитики в аэропорту используют MS SQL Server, Python и Power BI. Менеджеры смотрят отчеты в Power BI, получают рассылки по почте. Подход к принятию решений — Data‑informed. Это верхнеуровневый подход, который учитывает как количественные данные, так внешние факторы, прошлый опыт и интуицию.
В хранилище данных (DWH) есть главные показатели пассажиропотока: рейсы, пассажиры, загрузка рейсов и пр. В DWH находятся фактические и прогнозные данные в разбивке по дням. Подробнее про теорию устройства DWH и процесс обработки данных можно почитать тут.
Первый этап: базовая система рекомендаций
Логика
![](https://habrastorage.org/getpro/habr/upload_files/9aa/356/741/9aa35674138c32ea15a05a55460cb7cd.png)
Для базовой системы рекомендаций выбрали 4 показателя:
выручка,
количество пассажиров,
количество вылетающих рейсов,
коэффициент загрузки рейсов.
Главный показатель — выручка. Остальные показатели — драйверы изменений выручки.
Коэффициент загрузки рейсов = количество пассажиров на рейсе / количество кресел в самолете * 100%. Максимальная загрузка = 100%.
Рекомендация выводится, если хотя бы один показатель показывает отрицательное отклонение. Отрицательное отклонение – снижение показателя от прогнозного уровня более, чем на 3%.
Если отрицательных отклонений нет, то ситуация нормальная. Рекомендация не выводится, менеджер не предпринимает активных действий.
Порог в 3% был выбран исходя из общего подхода аэропорта к прогнозированию. Прогноз считался качественным, если отклонение от факта составило не более +/- 3%.
Цифровой идентификатор исхода состоит из 4 цифр: каждая отвечает за отклонение одного показателя. 4 ставится в случае, если показатель имеет отрицательное отклонение, 5 – в обратном случае. Например, на этой неделе у авиакомпании «X» на направлении «Y» код 4455:
![Пример формирования цифрового идентификатора Пример формирования цифрового идентификатора](https://habrastorage.org/getpro/habr/upload_files/4f6/d11/10c/4f6d1110c4b7487670df61812b956826.png)
Выручка от рейсов авиакомпании снизилась из-за снижения числа пассажиров, но число рейсов и загрузка такие, как прогнозировалось. Возможная причина: авиакомпания использует меньшие самолеты, чем могла бы. Поэтому не все желающие могут улететь, перевозится меньше пассажиров и выручка снижается.
Рекомендация: предложить авиакомпании увеличить тип самолета на направлении. Также система порекомендует менеджеру проверить среднюю выручку на рейс, возможно, дело не только в пассажирах. Например, авиакомпания стала меньше пользоваться услугами аэропорта.
Рекомендации разрабатывались на основе опыта менеджеров по работе с авиакомпаниями и ретроспективных данных. Аналитики провели с менеджерами 3 брейн шторма, подобрали по 2-3 рекомендации для каждого исхода и согласовали финальный результат.
Получаем всего 17 исходов (2^4 +1):
2 возможных варианта для каждого из 4 показателей (включая 5555 – все показатели в порядке);
1 исход с идентификатором 3333 на случай резкого падения числа рейсов и пассажиров.
Техника
![Схема работы базовой системы рекомендаций Схема работы базовой системы рекомендаций](https://habrastorage.org/getpro/habr/upload_files/ded/cb4/162/dedcb41627110c8ec87f79300048a631.jpg)
Технически базовая система рекомендаций работает так:
Фактические и прогнозные значения формируются в нужном виде с помощью MS SQL Server и хранятся в витрине в DWH;
Расчет отклонений происходит в Power BI с помощью DAX, как и все дальнейшие шаги;
Составляем цифровой идентификатор;
Формируем текстовое описание ситуации;
Выводим рекомендацию;
Визуализируем результаты;
Рассылаем отчет еженедельно менеджерам на почту с помощью подписки в Power BI.
Далее рассмотрим пример, используем уже описанные коды 3333, 4455 и 5555. Показатель с пометкой «ACT» — фактические, «Budget» — бюджетные, «%» — размер отклонения в%.
ID — цифровой идентификатор, Description — описание ситуации, Recommendation — рекомендация для менеджера.
PAX – пассажиропоток, ATM – рейсы, LF – загрузка, Revenue – выручка, RPA – средняя выручка на рейс.
Шаг 2:
PAX ACT = sum([PAX Actual])
PAX Budget = sum([PAX Budget])
PAX Budget% = round(divide([PAX ACT]-[PAX Budget], [PAX Budget]),0)
Шаг 3:
ID = IF(AND([PAX Budget%]=-100,[ATM Budget%]=-100),
3333,
1000*(IF([PAX Budget%]<-3,4,5))+100*(IF([ATM Budget%]<-3,4,5))+10*(IF([LF Budget%]<-3,4,5))+1*(IF([Revenue Budget%]<-3,4,5))
Шаг 4:
Description = SWITCH([ID],
5555,"Соответствует или превышает прогноз",
4455,"Снизились пассажиропоток и количество рейсов",
…
3333,"Авиакомпания прекратила полеты",
"Не определено")
Шаг 5:
Recommendation = SWITCH([ID],
5555,"Действий не требуется",
4455,"Проверить изменения в типах ВС, предложить увеличить тип самолета; Провести анализ средней выручки на рейс: использование тарифов/услуг и применение скидок”,
…
3333,"Проверить информацию о действующих ограничениях для направления; Проверить информацию о компании (банкротство)",
"Не определено")
Итог
По понедельникам менеджер видит таблицу:
![Пример почтовой рассылки с базовой системой рекомендаций Пример почтовой рассылки с базовой системой рекомендаций](https://habrastorage.org/getpro/habr/upload_files/4e9/02f/601/4e902f601d68b77f731c0766ab8cb163.png)
Менеджеры сразу видят негативные отклонения, рекомендация подсказывает первые шаги для решения проблемы.
Но есть и недостаток базовой системы: она дает только общие рекомендации, в ней не хватает дополнительных проверок и показателей. Например, при цифровом идентификаторе 4455 выводится рекомендация «Провести анализ средней выручку на рейс». Менеджер проводит анализ самостоятельно, но лучше — чтобы его проводила система и выдавала конкретные услуги, по которым стало меньше выручки. Поэтому появилась расширенная система рекомендаций.
Второй этап: расширенная система рекомендаций
Логика
![](https://habrastorage.org/getpro/habr/upload_files/5fd/b63/888/5fdb6388809a2e59369b6fef65e5a4b3.png)
Будем использовать такие термины и их определения:
ID — цифровой идентификатор.
Проблема — отрицательное отклонение фактических показателей к прогнозным (порог: -3%).
Проверка — поиск в данных причин найденной проблемы.
Новый цифровой идентификатор состоял из 20 цифр — по одной для результата каждой проверки. Результатом проверки выступают цифры от 0 до 9, максимально — 10 возможных вариантов.
На проекте было реализовано 11 проверок. В расширенной системе появились 7 дополнительных проверок, например, отклонение средней выручки на рейс, отклонения из‑за изменения услуг, закрытие направлений и пр. Четыре проверки из базовой системы остались без изменений. Максимальное количество различных результатов проверки было увеличено до 5.
Получаем матрицу проверок (часть проверок и исходов заменена «Х», чтобы не утяжелять восприятие).
![Матрица проверок расширенной системы рекомендаций Матрица проверок расширенной системы рекомендаций](https://habrastorage.org/getpro/habr/upload_files/5ed/ad0/4c3/5edad04c3aab76debb2ec8a0c1d2a652.png)
Расширенная система рекомендаций имеет уже 122 880 ID и рекомендаций (2^5*4^4*5*3). Учитываем, что проверки №6-11 могут не выполняться в зависимости от комбинации первых 5 проверок. Например, если первые 5 цифр в идентификаторе равны 5, алгоритм не будет выполнять проверки №6-11.
Техника
![Схема работы расширенной системы рекомендаций Схема работы расширенной системы рекомендаций](https://habrastorage.org/getpro/habr/upload_files/858/5d8/d4f/8585d8d4febe1b61eee0f61ee28c0620.jpg)
Технически расширенная система рекомендаций работает так:
Фактические и прогнозные значения формируются в нужном виде с помощью MS SQL Server и хранятся в витрине в DWH;
Рассчитываем отклонения происходит в SQL и формируем результат каждой проверки в виде цифры;
Составляем ID в SQL;
Описываем ситуацию и присваиваем рекомендацию на основе кода Python;
Сохраняем ID и рекомендации в таблицу;
Добавляем таблицу в DWH;
Визуализируем результаты;
Рассылаем отчет еженедельно менеджерам на почту с помощью подписки в Power BI.
Рассмотрим пример для проверки «Закрытие направлений».
Шаг 2:
merge
into [Система рекомендаций] t
using (
select
a.airport
,max(z.[Закрыто вообще]) as [Закрыто вообще]
,max(z.[Закрыто для туристов]) as [Закрыто для туристов]
from [AIRPORTS] a with (nolock)
join [Закрытие направлений] z with (nolock) on a.airport_code=z.airport_code
group by a.airport
) s on t.airport=s.airport
when matched then update set
f7n=case when s.[Закрыто вообще]='Закрыто' then 4 when s.[Закрыто для туристов]='Закрыто' then 3 end;
-- 5 Направление открыто
-- 4 Закрыто вообще
-- 3 Закрыто для туристов
Шаг 3:
--ИТОГ ID
raiserror('ИТОГ ID',0,1) with nowait;
update [Система рекомендаций]
set ID=
convert(nvarchar(50),coalesce(f1n,0))
+convert(nvarchar(50),coalesce(f2n,0))
+convert(nvarchar(50),coalesce(f3n,0))
+convert(nvarchar(50),coalesce(f4n,0))
+convert(nvarchar(50),coalesce(f5n,0))
+convert(nvarchar(50),coalesce(f6n,0))
+convert(nvarchar(50),coalesce(f7n,0))
+convert(nvarchar(50),coalesce(f8n,0))
+convert(nvarchar(50),coalesce(f9n,0))
+convert(nvarchar(50),coalesce(f10n,0))
+convert(nvarchar(50),coalesce(f11n,0))
GO
Шаг 4:
Description = []
for i in range(df.shape[0]):
#"Действий не требуется", если первые 5 цифры = 5
if df['Проверка1'][i] == df['Проверка2'][i] == df['Проверка3'][i] == df['Проверка4'][i] == 5 == df['Проверка5'][i]:
Description.append('Действий не требуется')
else:
#проверка № 7 "Открытие/закрытие направлений"
if df['Проверка7'][i] == 3:
Description.append('Направление закрыто для туристического авиасообщения')
elif df['Проверка7'][i] == 4:
Description.append('Направление закрыто для авиасообщения')
df['Description'] = Description
Шаг 5:
![](https://habrastorage.org/getpro/habr/upload_files/f27/64e/a8e/f2764ea8e0e0767503540774f639558a.png)
Если седьмой проверке был присвоен код 5, то «Направление открыто» не будет рекомендацией. Такая рекомендация ничем не поможет менеджеру, поэтому алгоритм переходит к следующей проверке, чтобы найти причину отклонения. Алгоритм проверки «Закрытие направлений» выглядит так:
![](https://habrastorage.org/getpro/habr/upload_files/3a6/1fd/031/3a61fd031670fdcaa41fa426d07ef12d.png)
Итог
Для менеджеров визуальное представление отчета не изменилось, поменялись лишь рекомендации.
Что пошло не так?
![](https://habrastorage.org/getpro/habr/upload_files/e71/787/ee3/e71787ee394636f8904e3f26c454f11d.jpg)
Недостатки расширенной системы рекомендаций
Расширенная система рекомендаций создавалась в условиях ковидных ограничений. В качестве объяснений отклонений выступали текущие запреты на авиасообщение, набор самолетов авиакомпаний, время в пути и пр.
Изменилась геополитическая ситуация, большая часть проверок и рекомендаций потеряла смысл. Система не отвечала новым вызовам, так как в авиации поменялось многое: ограничения на международное авиасообщение, ортодромия (время, дальность, география полета), флот авиакомпаний, курс валют, спрос на авиаперевозки и пр.
В расширенной версии системы рекомендаций были две группы недостатков.
1. Логика:
Попытка решать постоянно меняющиеся проблемы фиксированным механизмом;
Отсутствие прогностической функции алгоритмов — направленность на анализ фактических показателей и соотношение отклонений к бюджету или прошлым периодам;
База для сравнения алгоритма (прошлый год, бюджет и т. д.) становится неактуальной быстро в изменяющейся среде.
2. Техника:
Отсутствие гибкости системы — для изменения алгоритма необходимо:
пересматривать смысловую часть алгоритма;
через IT департамент корректировать код SQL для выполнения проверок;
переписывать код Python для описаний и рекомендаций;
Трудоемкость процесса — создание рекомендаций и комментариев требует анализа всех ID (более 100 тысяч возможных комбинаций ID);
Большое количество проверок — необходимость прописывать алгоритмы и учитывать изменения большого количества переменных.
Итог
Аналитики рассмотрели вариант адаптации расширенной системы рекомендаций. По предварительным расчетам это заняло бы не менее 6 месяцев, а за это время внешние условия и алгоритмы могут устареть еще раз.
Было решено вернуться к базовой системе рекомендаций, откорректировать и уточнить основные 17 рекомендаций. Базовую систему легко поддерживать, изменять и интерпретировать.
Проблема с недостатком детализированной информации была решена добавлением в отчет Power BI подсказок и страниц, где можно найти нужную аналитику. Например, отклонение средней выручки на рейс или список самолетов у авиакомпании.