Многие владельцы бизнеса не хотят погружаться в аналитику. И это нормально. У этих людей совершенно другие приоритеты: им нужно развивать свой бизнес, встречаться с важными клиентами, управлять персоналом, решать стратегические проблемы. У них нет времени погружаться в детали.
И в этой ситуации крайне важно иметь под рукой опытного аналитика, желательно дата саентиста. Имея такого человека в штате, предприниматель сможет максимально глубоко проанализировать накопленные данные и получить картину своего бизнеса с множества сторон! И он получит эти инсайты быстро, что позволит ему оперативно скорректировать вектор развития бизнеса в сторону максимизации прибыли.
Почему Python? Делать анализ бизнеса только силами Excel – уже моветон. Возможности этого инструмента сильно ограничены. Нет, Excel – отличная программа для анализа данных. Однако, как и любая другая программа, она может делать только то, что задумано создавшими ее программистами. А вот что делать, если вам нужны нестандартные решения? Вот тут-то и проявляется вся мощь языка программирования Python и пакета Pandas!
Я люблю Python и Pandas за их неограниченный потенциал. Даже анализируя одни и те же данные бизнеса, вы можете делать с ними невероятные вещи, находить неочевидные инсайты, многократно обогащать данные и создавать из них множество новых показателей и данных.
В этой статье я хочу поделиться с вами одним интересным исследованием – анализом данных сети отелей в Крыму. Это небольшая сеть, все данные состояли всего из двух таблиц. Но, как оказалось, работать с ними можно часами и днями. И чем больше погружаешься в процесс, тем больше новых идей приходит – как еще можно покрутить данные, чтобы найти что-то еще.
Ну что-ж, поехали…
Начинаем традиционно с установки библиотеки pandas и загрузки данных. Загрузим таблицу с отелями.
import pandas as pd
df1 = pd.read_csv('crimea1.csv', sep=';')
df1.head(10)
Рис. 1: Датафрейм с данными отелей
Загрузим таблицу с туристами.
df2 = pd.read_csv('crimea2.csv', sep=';', encoding='cp1251')
df2.head(10)
Рис. 2: Датафрейм с данными гостей отелей
Посмотрим каковы размеры таблиц.
print('В таблице №1 строк/колонок:', df1.shape)
print('В таблице №2 строк/колонок:', df2.shape)В таблице №1 строк/колонок: (60, 9)
В таблице №2 строк/колонок: (1077, 7)
Посмотрим на типы данных в столбцах.
df1.info()
Рис. 3: Типы данных в столбцах датафрейма Отели
Также посмотрим 2ю таблицу
df2.info()
Рис. 4: Типы данных в столбцах датафрейма Туристы
Посмотрим статистические показатели.
df1.describe()
Рис. 5: Описательные статистики датафрейма Отели
Видно что средняя выручка отеля составляет 59290 руб, а средняя прибыль – 26148 руб в сутки. Минимальная выручка – 25319 р, минимальная прибыль – 4889 руб в сутки.
Давайте теперь посмотрим на статистические данные по гостям отелей.
df2.describe()
Рис. 6: Описательные статистики датафрейма Туристы
Всего за этот период отели посетило 1077 гостей, в среднем они арендовали номера на 135.7 часов, и оставили средний рейтинг отелям в 4.85.
Давайте теперь посчитаем суммарную выручку отелей.
df1['revenue'].sum()3557406
И суммарную прибыль.
df1['profit'].sum()1568886
А также среднюю рентабельность бизнеса (в %).
round((df1['profit'].sum() / df1['revenue'].sum()), 2)0.44
То есть сеть прибыльна, и рентабельность составляет 44%. Хорошо.
Можем также посчитать размах выручки по дням (разницу между мин и макс).
def min_max(df):
max_value = df['revenue'].max()
min_value = df['revenue'].min()
return max_value - min_value
df1.groupby('date').apply(min_max)date
2021-07-01 40920
2021-07-02 70214
2021-07-03 88503
2021-07-04 86532
2021-07-05 67476
2021-07-06 77980
2021-07-07 56990
2021-07-08 68456
2021-07-09 43109
2021-07-10 55304
dtype: int64
Видим, что размах дневной выручки немалый, и колеблется в диапазоне от 40 тыс до 90 тыс рублей.
С общей статистикой все более менее понятно. Давайте посмотрим на качество собранных данных. Посмотрим насколько корректно заполнены таблицы. Посчитаем все NaN / None / пропуски (% пустых ячеек).
df1.isna().mean().sort_values(ascending=False)date 0.0
city 0.0
hotel 0.0
total_rooms 0.0
ocup_rooms 0.0
revenue 0.0
oper_costs 0.0
adv_costs 0.0
profit 0.0
dtype: float64
В первой таблице пропусков нет. А что со второй?
df2.isna().mean().sort_values(ascending=False)user_id 0.0
date 0.0
name 0.0
hotel 0.0
how_find_us 0.0
resting_time 0.0
rating 0.0
dtype: float64
Видим что все хорошо. Все строки в таблицах заполнены данными.
Давайте теперь посмотрим какие самые популярные города для отдыха в этой сети отелей.
df1['city'].value_counts()Yalta 30
Alushta 20
Gurzuf 10
Name: city, dtype: int64
А какие самые популярные отели?
df2['hotel'].value_counts()Breeze 281
Rapsodia 261
AquaMania 189
Moreon 137
Skyline 106
Alpina 103
Name: hotel, dtype: int64
А на какие дни пришлось наибольшее число заездов туристов?
df2['date'].value_counts().head(5)2021-07-04 121
2021-07-03 120
2021-07-02 113
2021-07-07 113
2021-07-06 111
Name: date, dtype: int64
Построим графики этих показателей.
import seaborn as sns
import matplotlib.pyplot as plt
plt.figure(figsize=(15,7))
plt.subplot(2,2,1)
sns.barplot(df1['city'].value_counts().index, df1['city'].value_counts().values)
plt.title('Популярность городов')
plt.subplot(2,2,2)
sns.barplot(df2['hotel'].value_counts().index, df2['hotel'].value_counts().values)
plt.title('Популярность отелей')
plt.subplot(2,2,3)
sns.barplot(df2['date'].value_counts().index, df2['date'].value_counts().values)
plt.title('Даты заездов')
plt.subplot(2,2,4)
sns.barplot(df2['rating'].value_counts().index, df2['rating'].value_counts().values)
plt.title('Оценки (рейтинги) от гостей')
plt
Рис. 7: Диаграммы исследовательского (EDA) анализа данных Pandas + Seaborn
Продолжим наш бизнес-анализ. Давайте посмотрим на ТОП-10 каналов привлечения туристического трафика в отели.
df2['how_find_us'].value_counts().head(10)yandex_adv 75
google_adv 64
regular_customer 54
by_recommendation 48
agg_tvil.ru 43
seo 42
agg_onetwotrip.com 39
agg_travelata.ru 36
agg_yandex_travel 35
outdoor 35
Name: how_find_us, dtype: int64
Самые плохо работающие каналы привлечения клиентов:
df2['how_find_us'].value_counts().tail(5)agg_booking.com 28
agg_hotellook.ru 27
tour_agents 26
vk_adv 26
agg_level.travel 25
Name: how_find_us, dtype: int64
Давайте посмотрим какие отели оказались наиболее прибыльными в этот период.
df1.groupby('hotel').sum().sort_values(by='profit', ascending=False)
Рис. 8: Сводная таблица отелей по размеру прибыли в порядке убывания
Видим что лидерами по прибыли стали отели Breeze и Rapsodia.
Давайте теперь сгруппируем выручку и расходы по датам.
df1.groupby('date').sum()
Рис. 9: Совокупная выручка и расходы отелей по датам
Очевидно самыми прибыльными днями были 03 и 04 июля. Прибыль в эти составила около 200 тыс. руб в сутки. Какие же города принесли наибольшую прибыль?
df1.groupby('city').sum()
Рис. 10: Сводная таблицы прибыли отелей по городам
Очевидно что это Ялта и Алушта, с большим отрывом. Правда в Гурзуфе пока всего 1 отель.
Какие еще метрики мы можем глянуть? Да, много чего. В Pandas можно даже посчитать показатели выручки по размеру номерного фонда!
df1.groupby('total_rooms').sum()
Рис. 11: Показатели выручки по размеру номерного фонда отелей
Здесь все без неожиданностей. Хотя отели с 14 номерами оказались немного более прибыльными чем 15-местные.
Давайте копнем глубже. И посмотрим как менялся средний рейтинг по датам, в какие дни клиенты обслуживались лучше всего.
df2.groupby('date')['rating'].mean().sort_values(ascending=False)date
2021-07-03 4.941667
2021-07-07 4.933628
2021-07-06 4.923423
2021-07-08 4.915789
2021-07-02 4.893805
2021-07-09 4.891509
2021-07-04 4.876033
2021-07-01 4.740385
2021-07-05 4.699029
2021-07-10 4.697802
Name: rating, dtype: float64
Рассмотрим какие отели получили лучший средний рейтинг.
df2.groupby('hotel')['rating'].mean().sort_values(ascending=False)hotel
Moreon 5.018248
AquaMania 4.883598
Skyline 4.853774
Breeze 4.850534
Rapsodia 4.846743
Alpina 4.631068
Name: rating, dtype: float64
Очевидно что Moreon больше всего нравится постояльцам. А вот персоналу Alpina есть над чем поработать. Еще одна закономерность – крупные отели (Breeze, Rapsodia) в среднем получают более худшие оценки, чем небольшие.
Интересно также посмотреть влияет ли то, откуда пришел клиент, на выставленную оценку (рейтинг) отеля.
df2.groupby('how_find_us')['rating'].mean().sort_values(ascending=False)how_find_us
seo 5.238095
agg_onlinetours.ru 5.142857
agg_tripadvisor.ru 5.112903
agg_level.travel 5.080000
tour_agents 5.038462
social 5.030303
agg_yandex_travel 5.014286
agg_onetwotrip.com 5.000000
agg_travelata.ru 4.986111
agg_other_sites 4.942857
unknown 4.937500
instagram_adv 4.933333
agg_ostrovok.ru 4.928571
agg_roomguru.ru 4.906250
google_adv 4.882812
agg_airbnb.com 4.875000
regular_customer 4.870370
agg_booking.com 4.839286
by_recommendation 4.833333
yandex_adv 4.833333
telegram_adv 4.774194
agg_bronevik.com 4.758065
vk_adv 4.750000
outdoor 4.714286
agg_trivago.com 4.709677
agg_101hotels.com 4.671875
agg_sutochno.ru 4.516129
agg_tvil.ru 4.465116
agg_hotellook.ru 4.462963
facebook_adv 4.397059
Name: rating, dtype: float64
Из данных выше можно сделать вывод, что самые лояльные постояльцы в отели приходят через органический SEO трафик, вбивая название отелей в поисковые системы или заходя на сайты гостиниц напрямую.
Еще неплохо показали себя интернет-агрегаторы: onlinetours.ru, tripadvisor.ru и level.travel. Также лояльных клиентов приводят турагенты и соцсети. Однако соцсети хорошо работают только с постов самих гостиниц. Конкретно рекламные посты в Facebook, VK и проч. показывают обратные результаты.
Рейтинги – это хорошо. Но не они приносят деньги, а почасовая оплата номеров. Поэтому интересно взглянуть из каких каналов клиенты дольше всего задерживались в гостиничных номерах.
df2.groupby('how_find_us')['resting_time'].mean().sort_values(ascending=False)how_find_us
telegram_adv 179.387097
agg_tripadvisor.ru 162.322581
seo 155.595238
agg_onlinetours.ru 154.314286
agg_level.travel 151.960000
unknown 150.687500
agg_trivago.com 148.161290
agg_airbnb.com 142.892857
agg_onetwotrip.com 142.717949
regular_customer 140.481481
instagram_adv 136.400000
outdoor 135.571429
agg_bronevik.com 134.967742
agg_tvil.ru 134.651163
agg_roomguru.ru 133.750000
agg_yandex_travel 133.428571
tour_agents 133.384615
google_adv 130.828125
by_recommendation 130.583333
vk_adv 130.461538
agg_101hotels.com 129.781250
agg_booking.com 128.678571
agg_other_sites 128.514286
yandex_adv 127.093333
agg_travelata.ru 126.916667
agg_ostrovok.ru 123.357143
agg_sutochno.ru 118.967742
agg_hotellook.ru 116.444444
facebook_adv 110.588235
social 108.848485
Name: resting_time, dtype: float64
А вот здесь уже совсем другой расклад. Самая длительная аренда номеров была у постояльцев, пришедших по рекламе с Telegram и агрегатора Tripadvisor. Здесь также в лидерах сайты компании (seo) и постоянные клиенты.
Аутсайдеры по продолжительности отдыха – это клиенты с соцсетей и с топовых агрегаторов (Ostrovok, Yandex, Sutochno.ru).
Давайте снова вернемся на уровень наверх и посмотрим среднюю выручку и прибыль каждого отеля.
df1.groupby('hotel')[['revenue', 'profit']].mean()
Рис. 12: Средняя выручка и прибыль по каждому отелю
В лидерах ожидаемо Breeze и Rapsodia – самые крупные и заполняемые отели.
А что же с продолжительностью отдыха? В каких отелях гости задерживаются дольше всего?
df2.groupby('hotel')['resting_time'].mean().sort_values(ascending=False)hotel
Moreon 138.474453
Skyline 138.471698
Breeze 137.156584
AquaMania 136.444444
Rapsodia 133.655172
Alpina 129.019417
Name: resting_time, dtype: float64
И здесь совершенно другие лидеры – Moreon и Skyline. Отсюда можно сделать вывод: чем меньше отель, чем он более камерный и уютный – тем дольше люди хотят в нем оставаться.
Давайте вернемся к оценкам (рейтингам) постояльцев. И посмотрим какие оценки отелям ставили чаще всего.
df2.pivot_table(index='hotel', columns='rating',
values='name', aggfunc='count',
fill_value=0, margins=True)
Рис. 13: Количество полученных оценок отелями по шкале от 1 до 6
Картина получилась весьма интересная. Видно что постояльцы в целом неплохо оценивают отели этой сети и выбирают оценки по шкале от 4 до 6. Но все же бывали случаи когда гости были недовольны гостиницами. Есть даже 4 оценки ниже 2х баллов.
Интересно также посмотреть на процентное соотношение оценок.
df2['rating'].value_counts(normalize=True)4.0 0.200557
5.5 0.196843
6.0 0.185701
5.0 0.183844
4.5 0.167131
3.0 0.024141
3.5 0.017642
2.5 0.012999
2.0 0.007428
1.0 0.002786
1.5 0.000929
Name: rating, dtype: float64
Из этой статистики мы можем сделать следующие выводы:
- 20% гостей поставили отелям – 4.0 балла;
- 56.6% гостей – 5 и выше баллов;
- 16.7% гостей – 4.5 балла;
- Процент совсем недовольных клиентов – около 2.3-3%.
Вернемся к финансовым показателям. Построим таблицу топ самых прибыльных результатов по выручке.
df1.sort_values(by='revenue', ascending=False).head(6)
Рис. 14: Топ самых прибыльных результатов по размеру выручки
Топ самых неудачных / убыточных дней.
df1.sort_values(by='profit', ascending=True).head(6)
Рис. 15: Самые неприбыльные дни для отелей
Сводный топ по прибыльности отелей в разрезе города / названия.
df1.sort_values(by=['profit', 'city', 'hotel'], ascending=[False, True, True]).head(10)
Рис. 16: Сводная таблица лучших отелей по прибыльности по названию и городу
Топ гостей по продолжительности отдыха.
df2.sort_values(['resting_time', 'name'], ascending=[False, True]).head(10)
Рис. 17: Таблица гостей с наибольшим временем проживания
Видим что длительные гости в основном оставляли хорошие оценки. Проверим эту гипотезу на обратной выборке. Выведем топ нелояльных / недовольных клиентов, которые быстро покинули отели.
df2.sort_values(['resting_time', 'name'], ascending=[True, True]).head(10)
Рис. 18: Таблица гостей с наименьшим временем проживания
В основном одни низкие оценки. Отсюда делаем вывод – недовольные клиенты редко задерживаются в отелях больше чем на 25-30 часов.
Что еще можем поисследовать? Ну, например, какие отели в процентах приводят больше всего гостей?
df2['hotel'].value_counts(normalize=True)Breeze 0.260910
Rapsodia 0.242340
AquaMania 0.175487
Moreon 0.127205
Skyline 0.098422
Alpina 0.095636
Name: hotel, dtype: float64
Можем посмотреть занятость номеров в отелях в разрезе дат.
df1.pivot_table(index='date', columns='hotel', values='ocup_rooms',
aggfunc='sum', fill_value=0, margins=True)
Рис. 19: Занятые номера по отелям на каждую дату
Посмотрим на заполняемость номеров в процентном соотношении. Для этого добавим в датафрейм новый вычисляемый столбец occupancy_rate.
df1['occupancy_rate'] = df1.ocup_rooms / df1.total_rooms
df1[['date', 'hotel', 'total_rooms', 'ocup_rooms', 'occupancy_rate']].head(10)
Рис. 20: Ежедневная статистика процента занятости каждого отеля
Давайте также посмотрим какова средняя заполняемость в разрезе отелей за все время.
df1.groupby('hotel')['occupancy_rate'].mean().sort_values(ascending=False)hotel
Rapsodia 0.790909
Breeze 0.780556
Alpina 0.735714
Skyline 0.706667
Moreon 0.685000
AquaMania 0.675000
Name: occupancy_rate, dtype: float64
И здесь перед нами встает неожиданный факт – чем крупнее отель, тем выше его заполняемость. Каковы могут быть причины? Текущие данные не объясняют это. Возможно это связано с тем что названия таких гостиниц у всех на слуху.
Давайте теперь посмотрим на среднюю заполняемость всех отелей этой гостиничной сети по датам.
df1.groupby('date')['occupancy_rate'].mean().sort_values(ascending=False)date
2021-07-04 0.806217
2021-07-03 0.794769
2021-07-07 0.765572
2021-07-02 0.756469
2021-07-09 0.755026
2021-07-01 0.738143
2021-07-06 0.728499
2021-07-05 0.695551
2021-07-08 0.634067
2021-07-10 0.615428
Name: occupancy_rate, dtype: float64
И здесь мы видим в лидерах снова 03 и 04 июля, как и в случае с финансовыми данными. Налицо прямая корреляция – чем выше заполняемость, тем выше выручка.
Интересно также взглянуть на среднюю прибыль с каждого номера. Для этого построим в таблице еще один столбец profit_per_room.
df1['profit_per_room'] = df1.profit / df1.ocup_rooms
df1[['date', 'hotel', 'ocup_rooms', 'profit_per_room']].head(10)
Рис. 21: Средняя прибыль с номера по каждому отелю и по каждой дате
А вот это уже интересная метрика, в которую хочется копнуть глубже.
Давайте взглянем на статистические показатели этого столбца.
df1['profit_per_room'].describe()count 60.000000
mean 1393.295675
std 391.271298
min 559.500000
25% 1139.045455
50% 1360.482143
75% 1613.478261
max 2393.230769
Name: profit_per_room, dtype: float64
Согласно статистике, в среднем каждый сданный номер приносил владельцу отелей 1393 руб в сутки, минимально – 559 р/с, максимально – 2393 р/с. Медианное значение близко к среднему, а это значит что за этот период в основном резких колебаний ценообразования не было.
Посмотрим какова средняя прибыль с 1 номера в разрезе отелей.
df1.groupby('hotel')['profit_per_room'].mean().sort_values(ascending=False)hotel
Breeze 1580.873608
Alpina 1575.391911
Rapsodia 1535.860103
Moreon 1393.465056
AquaMania 1177.752481
Skyline 1096.430894
Name: profit_per_room, dtype: float64
Неожиданно высокие показатели у единственного и крошечного отеля Alpina в Гурзуфе. Очевидно, что менеджмент этой гостиницы работает грамотно с ценами на номера. Чего не скажешь об Aquamania и Skyline, они сдают номера слишком дешево.
Посмотрим также на среднюю прибыльность номеров в разрезе дат.
df1.groupby('date')['profit_per_room'].mean()date
2021-07-01 1318.578333
2021-07-02 1483.468280
2021-07-03 1557.969192
2021-07-04 1597.750558
2021-07-05 1293.818591
2021-07-06 1382.943939
2021-07-07 1359.384711
2021-07-08 1326.501429
2021-07-09 1428.487797
2021-07-10 1184.053924
Name: profit_per_room, dtype: float64
Здесь без неожиданностей – и 3, и 4 июля были самыми прибыльными днями.
Интересно глянуть статистику по средней цене номеров в разрезе отелей и дат.
df1.pivot_table(index='date', columns='hotel',
values='profit_per_room',
aggfunc='sum', fill_value=0)
Рис. 22: Средняя цена номеров в отелях по датам
Цифр много. Слишком шумно. Для большей наглядности давайте построим тепловую карту (хитмап).
forheat = df1.pivot_table(index='date', columns='hotel',
values='profit_per_room',
aggfunc='sum', fill_value=0)
sns.heatmap(forheat, cmap="BuPu")
Рис. 23: Тепловая карта цен на номера в отелях по датам
Интерпретируется тепловая карта предельно просто – чем светлее пятно, тем дешевле были сданы номера, чем темнее пятно, тем они были сданы дороже.
И здесь мы видим очень интересные особенности:
- Руководство Skyline всегда сдает номера дешево;
- Менеджмент Alpina умудряется сдавать номера дороже всех;
- Некоторые дни (01-07, 05-07) не приносят нормальной прибыли всей сети (видимо из-за скидок, конкуренции на сайтах);
- У всех отелей колебания дневных цен на номера довольно значительны.
Интересно также глянуть таким же образом на статистику заполняемости отелей.
forheat2 = df1.pivot_table(index='date', columns='hotel',
values='occupancy_rate',
aggfunc='sum', fill_value=0)
sns.heatmap(forheat2, cmap="BuPu")
Рис. 24: Тепловая карта заполняемости номеров в отелях по датам
Здесь мы тоже видим весьма интересные особенности:
- Некоторые дни (08-07, 10-07) провальны для всей сети отелей (скорее всего из-за непогоды);
- У отелей AquaMania и Moreon наблюдаются первые признаки проблем с заполняемостью;
- Руководство Skyline не просто всегда сдает номера дешево, но и умудряется почти половину их оставлять пустыми;
- Наиболее стабильный в плане заполняемости постояльцами отель – это Rapsodia.
Давайте посмотрим что еще умеет Pandas.
Допустим нам нужно отсеить из нашей базы клиентов с минимальными оценками и минимальной продолжительностью аренды, чтобы отдел маркетинга мог связаться с ними и узнать что не так. Построение такой таблицы делается всего лишь в пару строк.
problem_clients = df2[(df2['rating'] <= 2.5) & (df2['resting_time'] < 30)]
problem_clients
Рис. 25: Клиенты, поставившие минимальные оценки и проживавшие в отелях наименьшее время
Интересные возможности предлагает совмещение фильтров Pandas и функций Python. Например, с помощью функции ниже можно найти все ситуации когда отели приносили максимум выручки при минимальной заполняемости.
df1.loc[lambda x: (x['revenue'] > 40000) & (x['ocup_rooms'] < 15)]
Рис. 26: Таблица с днями когда отели приносили наибольшую выручку при наименьшей заполняемости
Можно также объединить таблицы и поработать над анализом совмещенных данных.
df3 = df2.merge(df1, how='left', on=['hotel', 'date'])
df3 = df3[['user_id', 'name', 'date', 'hotel', 'profit_per_room', 'how_find_us', 'resting_time', 'rating']]
df3
Рис. 27: Объединенная таблица отелей и гостей
Теперь можно посчитать какие источники трафика приводят больше клиентов, которые готовы платить дороже рынка.
df3.groupby('how_find_us')['profit_per_room'].mean().sort_values(ascending=False).head(12)how_find_us
agg_onetwotrip.com 1570.674781
agg_roomguru.ru 1569.966561
agg_tripadvisor.ru 1568.712809
agg_hotellook.ru 1564.688581
by_recommendation 1547.603968
agg_booking.com 1510.018365
facebook_adv 1502.619814
agg_airbnb.com 1498.907399
vk_adv 1489.630812
social 1488.006090
agg_sutochno.ru 1487.971845
agg_ostrovok.ru 1486.271134
Можно поискать дубли в наших данных. И понять случайно ли они возникли или нет.
df3.nunique()user_id 1077
name 1058
date 10
hotel 6
profit_per_room 60
how_find_us 30
resting_time 209
rating 11
dtype: int64
Видим число уникальных user_id не совпадает с числом name. Это значит что у нас было порядка 20 клиентов, которые посетили несколько отелей нашей сети. Давайте посмотрим на них.
doubles = df3.loc[df3.duplicated(subset=['name'], keep=False), :]
doubles.sort_values(by='name').head(10)
Рис. 28: Таблица с гостями, которые побывали в нескольких отелях гостиничной сети
Можно также сделать отдельную колонку для пометки клиентов, кто уже приезжал в эту сеть отелей.
df3['is_loyal'] = df3.duplicated(subset='name', keep=False).apply(
lambda x: 'True' if x else 'False')
df3.sort_values(by='is_loyal', ascending=False)
Рис. 29: Обогащение данных признаком (столбцом) is_loyal, отмечающим что постоялец уже приезжал в другие гостиницы сети
Функции Pandas и Python будут также полезны для сложных фильтров. Например, для подсчета средней прибыли на номер, по клиентам кто пришел через Яндекс.
Напомню что у нас есть несколько каналов с yandex.
df3.loc[df3.how_find_us.str.contains('yandex') == True, ['how_find_us']]
Рис. 30: Вывод строк датафрейма по маске (в нашем случае ‘yandex’)
В этой ситуации лучше всего также скомбинировать код Pandas и Python. Но для начала нужно создать новый объединенный датафрейм.
df4 = df2.merge(df1, how='left', on=['hotel', 'date'])
df4 = df4[['user_id', 'date', 'hotel', 'ocup_rooms', 'revenue', 'profit', 'how_find_us']]
# Теперь создадим вычисляемую колонку со средней прибылью
df4['yndx_profit'] = df4.apply(lambda x: (x.profit / x.ocup_rooms) if 'yandex' in x.how_find_us else None, axis=1)
df4[df4['yndx_profit'] > 0].head()
Рис. 31: Датафрейм с добавленным столбцом средней прибыли на занятый номер с рекламного канала ‘yandex’
И теперь нам остается лишь посчитать среднюю прибыль на номер по клиентам с Яндекса за все время.
df4['yndx_profit'].mean()1437.3976389740005
Что можно делать с этими данными? Ну как минимум сравнить с другими каналами трафика. Например с seo.
df4['seo_profit'] = df4.apply(lambda x: (x.profit / x.ocup_rooms)
if 'seo' in x.how_find_us else None, axis=1)
df4['seo_profit'].mean()1419.0495566569077
Или сравнить с прибыльностью по постоянным клиентам и по рекомендации.
df4['loyal_profit'] = df4.apply(lambda x: (x.profit / x.ocup_rooms)
if 'regular_customer' or 'by_recommendation' in x.how_find_us else None, axis=1)
df4['loyal_profit'].mean()1456.7186629526461
Как видим, лучшую прибыль на номер приносят именно постоянные клиенты или клиенты по рекомендации.
Если вы точно знаете какие клиенты генерят наибольшую прибыль для бизнеса, то можно углубиться еще дальше. Например отсеить всех клиентов, кроме VIP, и посмотреть какую выручку они принесли в этот месяц.
Допустим, нашего VIP-клиента зовут Эдуард. Давайте посмотрим в каких отелях он останавливался и сколько прибыли принес.
df3['is_eduard'] = df3.loc[df3.name.str.contains('Эдуард'), 'profit_per_room']
df3[df3['is_eduard'] > 0].head()
Рис. 32: Отфильтрованная таблица по маске имени клиента ‘Эдуард’
Согласен, пример с Эдуардом не удался. У нас их два, и они как-то непохожи на лояльных клиентов. Но суть остается та же.
Вообще, стоит отметить, что фильтры в Pandas реализованы бесподобно – минимум кода, все просто и быстро. Вот, к примеру, как мы можем оставить в таблице только клиентов пришедших с сайтов-агрегаторов.
df3[df3.how_find_us.str.contains('agg')]
Рис. 33: Отфильтрованная таблица по маске ‘agg’, в которой остались только клиенты пришедшие с сайтов-агрегаторов
Таким же образом мы можем изучить данные по клиентам из Яндекс и Google.
df3[df3.how_find_us.str.contains('yandex|google')]
Рис. 34: Отфильтрованная таблица по маскам ‘yandex’ или ‘google’
Невооруженным взглядом видно что агрегаторы приводят больше клиентов в отели, чем знаменитые поисковики.
Это легко проверить написанием небольшой функции, в котором мы объединим все значения агрегаторов, а также поисковиков и соцсетей.
def traffic_grouped(row):
if row.startswith('agg'):
row = 'aggregators'
if row.startswith('yandex') or row.startswith('google'):
row = 'context_ads'
elif row.startswith('vk') or row.startswith('facebook') or row.startswith('instagram') or row.startswith('telegram'):
row = 'social'
return row
df2['how_find_us'] = df2['how_find_us'].apply(traffic_grouped)
df2['how_find_us'].value_counts()aggregators 547
social 154
context_ads 139
regular_customer 54
by_recommendation 48
seo 42
outdoor 35
unknown 32
tour_agents 26
Name: how_find_us, dtype: int64
Теперь можно построить красивую сводную таблицу по всем рекламным источникам по каждому отелю.
df2.pivot_table(index='how_find_us', columns='hotel', values='name',
aggfunc='count', fill_value=0, margins=True)
Рис. 35: Статистика рекламных источников и приведенных гостей в отели
Какие выводы мы можем сделать из таблицы выше:
- Статистика по источникам клиентов примерно одинаковая, но…
- В Alpina был всего 1 постоянный клиент, в то время как в других отелях их около 10 и выше;
- Похоже что турагенты не хотят приводить клиентов в Skyline;
- У отеля Skyline также наименьшее число гостей по рекомендации;
- Маркетологи Moreon плохо работают с соцсетями.
Интересно также посмотреть статистику рекламных каналов по датам.
df2.pivot_table(index='how_find_us', columns='date', values='name',
aggfunc='count', fill_value=0)
Рис. 36: Статистика эффективности рекламных источников по датам
Здесь более ровная картина. Почти без отклонений. Разве что можно выделить топовые дни (3 и 4 июля), когда отличный приход клиентов был с агрегаторов и по рекомендациям.
Вернемся к финансовым показателям. Здесь можно найти еще много инсайтов. Например сделать сравнительную таблицу по разнице средней и дневной выручки.
df1['av_revenue'] = df1.groupby('hotel')['revenue'].transform('mean')
df1['difference'] = df1['av_revenue'] - df1['revenue']
df1['in_percent'] = round(100 * df1['difference'] / df1['av_revenue'], 1)
df1[['date', 'hotel', 'revenue', 'av_revenue', 'difference', 'in_percent']].sort_values(by=['hotel', 'date']).head(15)
Рис. 37: Разница выручки абсолютная и в процентах от средней выручки по каждому отелю и на каждую дату
Теперь для детального анализа финансовых показателей эту таблицу логично разбить на отдельные по каждому отелю.
# Список отелей
hotels = ['Alpina', 'AquaMania', 'Breeze', 'Moreon', 'Rapsodia', 'Skyline']
# Создание словаря для калькуляций и необходимых столбцов для каждого отеля
revenues = {hotel: df1[df1['hotel'] == hotel][['date', 'hotel', 'revenue', 'av_revenue', 'difference', 'in_percent']].sort_values(by='date') for hotel in hotels}И теперь уже можно детально проанализировать каждый отель, его динамику выручки, поискать причины ее изменения.
alpina_rev
Рис. 38: Ежедневная разница выручки абсолютная и в процентах от средней выручки отеля ‘Alpina’
Можно также построить графики для большей наглядности.
hotels_rev = df1[['date', 'hotel', 'revenue', 'av_revenue', 'difference', 'in_percent']]
sns.factorplot(x='date', y='difference', col='hotel', data=hotels_rev, col_wrap=3, kind='bar')
plt.suptitle('Динамика выручки по отелям',size=16)
plt.subplots_adjust(top=.925)
Рис. 39: Динамика отклонения выручки по отелям
Красиво и наглядно.
Почему бы таким же образом не взглянуть на заполняемость отелей гостями.
df1['occupancy_rate'] = df1.ocup_rooms / df1.total_rooms
hotels_ocup = df1[['date', 'hotel', 'total_rooms', 'ocup_rooms', 'occupancy_rate']]
sns.factorplot(x='date', y='occupancy_rate', col='hotel', data=hotels_ocup, col_wrap=3, kind='bar')
plt.suptitle('Динамика заполняемости отелей',size=16)
plt.subplots_adjust(top=.925)
plt.ylim(0.4,1.0)
Рис. 40: Динамика заполняемости отелей гостями
Этот график достоин находится на главном дашборде руководителя, поскольку он по сути и есть пульс бизнеса. И в этом вся мощь Pandas и вдумчивого Data Science!
Что еще можно также наглядно проанализировать? Ну разумеется рейтинги! Построим таблицу со средним рейтингом отелей по датам.
hotels_rating = pd.DataFrame(df2.groupby(['date', 'hotel'])['rating'].mean()).reset_index()
hotels_rating.head()
Рис. 41: Средний рейтинг отелей по датам
И теперь визуализируем его.
sns.factorplot(x='date', y='rating', col='hotel', data=hotels_rating, col_wrap=3, kind='bar')
plt.suptitle('Динамика рейтинга отелей',size=16)
plt.subplots_adjust(top=.925)
plt.ylim(3.8,5.5)
Рис. 42: Динамика оценок отелей постояльцами
Такие визуализации позволят своевременно понять, что с обслуживанием в каком-либо отеле что-то не так и оперативно принять меры.
Какую аналитику данных этого бизнеса мы еще можем провести? Да, много чего.
Например, мы можем разбить отели на группы (например, по размеру номерного фонда) и посчитать прибыль на номер по каждой группе.
df1['profit_per_room'] = df1.profit / df1.ocup_rooms
def divide_hotels(df):
df['big_hotels'] = df.apply(lambda x: x['profit_per_room']
if x['total_rooms'] > 30 else None, axis=1)
df['medium_hotels'] = df.apply(lambda x: x['profit_per_room']
if x['total_rooms'] <= 30 and x['total_rooms'] > 20 else None, axis=1)
df['small_hotels'] = df.apply(lambda x: x['profit_per_room']
if x['total_rooms'] <= 20 and x['total_rooms'] > 10 else None, axis=1)
return df
# Создадим новый датафрейм и применим к нему нашу функцию
ppr_grouped = divide_hotels(df1)
ppr_grouped[['date', 'hotel', 'occupancy_rate', 'big_hotels', 'medium_hotels', 'small_hotels']].head(8)
Рис. 43: Прибыль со сданного номера по разным группам отелей
print('Средняя прибыль с номера (маленькие отели):', ppr_grouped['small_hotels'].mean())
print('Средняя прибыль с номера (средние отели):', ppr_grouped['medium_hotels'].mean())
print('Средняя прибыль с номера (большие отели):', ppr_grouped['big_hotels'].mean())Средняя прибыль с номера (маленькие отели): 1355.095953686836
Средняя прибыль с номера (средние отели): 1177.7524808184144
Средняя прибыль с номера (большие отели): 1558.3668554728224
Такой же анализ можно произвести и по заполняемости групп отелей.
def divide_hotels2(df):
df['big_hotels'] = df.apply(lambda x: x['occupancy_rate']
if x['total_rooms'] > 30 else None, axis=1)
df['medium_hotels'] = df.apply(lambda x: x['occupancy_rate']
if x['total_rooms'] <= 30 and x['total_rooms'] > 20 else None, axis=1)
df['small_hotels'] = df.apply(lambda x: x['occupancy_rate']
if x['total_rooms'] <= 20 and x['total_rooms'] > 10 else None, axis=1)
return df
# Создадим новый датафрейм и применим к нему нашу функцию
ocu_grouped = divide_hotels2(df1)
print('Средняя заполняемость номеров (маленькие отели):', ocu_grouped['small_hotels'].mean())
print('Средняя заполняемость номеров (средние отели):', ocu_grouped['medium_hotels'].mean())
print('Средняя заполняемость номеров (большие отели):', ocu_grouped['big_hotels'].mean())Средняя заполняемость номеров (маленькие отели): 0.7091269841269842
Средняя заполняемость номеров (средние отели): 0.675
Средняя заполняемость номеров (большие отели): 0.7857323232323232
И в заключение этого объемного исследования предлагаю создать еще одну сводную таблицу, достойную дашборда руководителя. Это min и max показатели значений в разрезе отелей.
df5 = df2.merge(df1, how='left', on=['hotel', 'date'])
df5[['hotel', 'ocup_rooms', 'resting_time', 'rating', 'revenue', 'profit']].groupby('hotel').agg({'min', 'max'})
Рис. 44: Сводная таблица минимальных и максимальных показателей по каждому отелю
Эта компактная таблица позволяет буквально за несколько секунд понять общую картину и принять первые управленческие решения.
На этом наш демонстрационный анализ бизнес-данных окончен. Спасибо за внимание!
