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

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

Система рекомендаций

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

Бизнес-модель

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

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

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

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

До начала работы

Аналитики в аэропорту используют MS SQL Server, Python и Power BI. Менеджеры смотрят отчеты в Power BI, получают рассылки по почте. Подход к принятию решений — Data‑informed. Это верхнеуровневый подход, который учитывает как количественные данные, так внешние факторы, прошлый опыт и интуицию.

В хранилище данных (DWH) есть главные показатели пассажиропотока: рейсы, пассажиры, загрузка рейсов и пр. В DWH находятся фактические и прогнозные данные в разбивке по дням. Подробнее про теорию устройства DWH и процесс обработки данных можно почитать тут.

Первый этап: базовая система рекомендаций

Логика

Для базовой системы рекомендаций выбрали 4 показателя: 

1. выручка,

2. количество пассажиров, 

3. количество вылетающих рейсов, 

4. коэффициент загрузки рейсов. 

Главный показатель — выручка. Остальные показатели — драйверы изменений выручки.

Коэффициент загрузки рейсов = количество пассажиров на рейсе / количество кресел в самолете * 100%. Максимальная загрузка = 100%.

Рекомендация выводится, если хотя бы один показатель показывает отрицательное отклонение. Отрицательное отклонение – снижение показателя от прогнозного уровня более, чем на 3%. 

Если отрицательных отклонений нет, то ситуация нормальная. Рекомендация не выводится, менеджер не предпринимает активных действий.

Порог в 3% был выбран исходя из общего подхода аэропорта к прогнозированию. Прогноз считался качественным, если отклонение от факта составило не более +/- 3%.

Цифровой идентификатор исхода состоит из 4 цифр: каждая отвечает за отклонение одного показателя. 4 ставится в случае, если показатель имеет отрицательное отклонение, 5 – в обратном случае. Например, на этой неделе у авиакомпании «X» на направлении «Y» код 4455:

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

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

Рекомендации разрабатывались на основе опыта менеджеров по работе с авиакомпаниями и ретроспективных данных. Аналитики провели с менеджерами 3 брейн шторма, подобрали по 2-3 рекомендации для каждого исхода и согласовали финальный результат.

Получаем всего 17 исходов (2^4 +1):

• 2 возможных варианта для каждого из 4 показателей (включая 5555 – все показатели в порядке);

• 1 исход с идентификатором 3333 на случай резкого падения числа рейсов и пассажиров.

Техника

Технически базовая система рекомендаций работает так:

1. Фактические и прогнозные значения формируются в нужном виде с помощью MS SQL Server и хранятся в витрине в DWH;

2. Расчет отклонений происходит в Power BI с помощью DAX, как и все дальнейшие шаги;

3. Составляем цифровой идентификатор;

4. Формируем текстовое описание ситуации;

5. Выводим рекомендацию;

6. Визуализируем результаты;

7. Рассылаем отчет еженедельно менеджерам на почту с помощью подписки в 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,"Проверить информацию о действующих ограничениях для направления; Проверить информацию о компании (банкротство)",
"Не определено")

Итог

По понедельникам менеджер видит таблицу: 

Менеджеры сразу видят негативные отклонения, рекомендация подсказывает первые шаги для решения проблемы.

Но есть и недостаток базовой системы: она дает только общие рекомендации, в ней не хватает дополнительных проверок и показателей. Например, при цифровом идентификаторе 4455 выводится рекомендация «Провести анализ средней выручку на рейс». Менеджер проводит анализ самостоятельно, но лучше — чтобы его проводила система и выдавала конкретные услуги, по которым стало меньше выручки. Поэтому появилась расширенная система рекомендаций.

Второй этап: расширенная система рекомендаций

Логика

Будем использовать такие термины и их определения:

• ID — цифровой идентификатор.

• Проблема — отрицательное отклонение фактических показателей к прогнозным (порог: -3%).

• Проверка — поиск в данных причин найденной проблемы.

Новый цифровой идентификатор состоял из 20 цифр — по одной для результата каждой проверки. Результатом проверки выступают цифры от 0 до 9, максимально — 10 возможных вариантов.

На проекте было реализовано 11 проверок. В расширенной системе появились 7 дополнительных проверок, например, отклонение средней выручки на рейс, отклонения из‑за изменения услуг, закрытие направлений и пр. Четыре проверки из базовой системы остались без изменений. Максимальное количество различных результатов проверки было увеличено до 5.

Получаем матрицу проверок (часть проверок и исходов заменена «Х», чтобы не утяжелять восприятие).

Расширенная система рекомендаций имеет уже 122 880 ID и рекомендаций (2^5*4^4*5*3). Учитываем, что проверки №6-11 могут не выполняться в зависимости от комбинации первых 5 проверок. Например, если первые 5 цифр в идентификаторе равны 5, алгоритм не будет выполнять проверки №6-11.

Техника

Технически расширенная система рекомендаций работает так:

1. Фактические и прогнозные значения формируются в нужном виде с помощью MS SQL Server и хранятся в витрине в DWH;

2. Рассчитываем отклонения происходит в SQL и формируем результат каждой проверки в виде цифры;

3. Составляем ID в SQL;

4. Описываем ситуацию и присваиваем рекомендацию на основе кода Python;

5. Сохраняем ID и рекомендации в таблицу;

6. Добавляем таблицу в DWH;

7. Визуализируем результаты;

8. Рассылаем отчет еженедельно менеджерам на почту с помощью подписки в 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:

Если седьмой проверке был присвоен код 5, то «Направление открыто» не будет рекомендацией. Такая рекомендация ничем не поможет менеджеру, поэтому алгоритм переходит к следующей проверке, чтобы найти причину отклонения. Алгоритм проверки «Закрытие направлений» выглядит так:

Итог

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

Что пошло не так?

Недостатки расширенной системы рекомендаций

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

Изменилась геополитическая ситуация, большая часть проверок и рекомендаций потеряла смысл. Система не отвечала новым вызовам, так как в авиации поменялось многое: ограничения на международное авиасообщение, ортодромия (время, дальность, география полета), флот авиакомпаний, курс валют, спрос на авиаперевозки и пр.

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

1. Логика:

• Попытка решать постоянно меняющиеся проблемы фиксированным механизмом;

• Отсутствие прогностической функции алгоритмов — направленность на анализ фактических показателей и соотношение отклонений к бюджету или прошлым периодам;

• База для сравнения алгоритма (прошлый год, бюджет и т. д.) становится неактуальной быстро в изменяющейся среде.

2. Техника:

• Отсутствие гибкости системы — для изменения алгоритма необходимо:

  • пересматривать смысловую часть алгоритма;

  • через IT департамент корректировать код SQL для выполнения проверок;

  • переписывать код Python для описаний и рекомендаций;

• Трудоемкость процесса — создание рекомендаций и комментариев требует анализа всех ID (более 100 тысяч возможных комбинаций ID);

• Большое количество проверок — необходимость прописывать алгоритмы и учитывать изменения большого количества переменных.

Итог

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

Было решено вернуться к базовой системе рекомендаций, откорректировать и уточнить основные 17 рекомендаций. Базовую систему легко поддерживать, изменять и интерпретировать.

Проблема с недостатком детализированной информации была решена добавлением в отчет Power BI подсказок и страниц, где можно найти нужную аналитику. Например, отклонение средней выручки на рейс или список самолетов у авиакомпании.