Trip Away Travel Aggregator Analysis
Objective:
To enhance the performance and user experience of "Trip Away," a comprehensive travel aggregator, through in-depth data analysis.
Goals:
This data-driven approach will leverage Python to extract valuable insights and provide actionable recommendations for improving the platform's efficiency and customer satisfaction.
#!pip uninstall -y kaleido
!pip install kaleido==0.2.1
Collecting kaleido==0.2.1 Downloading kaleido-0.2.1-py2.py3-none-manylinux1_x86_64.whl.metadata (15 kB) Downloading kaleido-0.2.1-py2.py3-none-manylinux1_x86_64.whl (79.9 MB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 79.9/79.9 MB 9.6 MB/s eta 0:00:00 Installing collected packages: kaleido Successfully installed kaleido-0.2.1
import os
os.makedirs("images", exist_ok=True)
The analysis draws on data spanning a period of more than 3 years, 10 months, and 7 days.
import pandas as pd
import numpy as np
import datetime
from matplotlib import pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go
import kaleido
import warnings
warnings.filterwarnings('ignore')
bookings = pd.read_csv("https://raw.githubusercontent.com/mcadriaans/eda_travel-aggregator/refs/heads/main/data/bookings.csv")
bookings.head()
| customer_id | booking_id | from_city | from_country | to_city | to_country | booking_time | device_type_used | INR_Amount | service_name | no_of_passengers | days_to_departure | distance_km | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | customer_259 | booking_82 | Gurgaon | India | Ahmedabad | India | 2020-02-05T16:12:08Z | Desktop | 2565.28 | MMT | 1.0 | 10.0 | 747.8 |
| 1 | customer_303 | booking_156 | Delhi | India | Brussels | Belgium | 2018-11-21T08:21:47Z | Android | 23120.00 | YATRA | 1.0 | 1.0 | 6701.5 |
| 2 | customer_203 | booking_99 | Devanhalli | India | Frankfurt am Main | Germany | 2019-12-16T22:54:58Z | Android | 25717.60 | YATRA | 1.0 | 32.0 | 7712.0 |
| 3 | customer_211 | booking_319 | Gurgaon | India | Frankfurt am Main | Germany | 2021-10-29T12:25:38Z | Desktop | 135969.60 | GOIBIBO | 2.0 | 69.0 | 6112.5 |
| 4 | customer_287 | booking_222 | Gurgaon | India | Roissy-en-France | France | 2020-08-11T16:09:10Z | Android | 31791.20 | GOIBIBO | 1.0 | 3.0 | 6570.4 |
sessions = pd.read_csv("https://raw.githubusercontent.com/mcadriaans/eda_travel-aggregator/refs/heads/main/data/sessions.csv")
sessions.head()
| session_id | search_id | search_time | session_starting_time | booking_id | |
|---|---|---|---|---|---|
| 0 | session_322 | search_784 | 2020-01-21T21:35:38.910Z | 2020-01-21T21:10:12Z | NaN |
| 1 | session_322 | search_776 | 2020-01-21T21:37:37.697Z | 2020-01-21T21:10:12Z | NaN |
| 2 | session_322 | search_623 | 2020-01-21T21:36:11.392Z | 2020-01-21T21:10:12Z | NaN |
| 3 | session_322 | search_270 | 2020-01-21T21:16:07.685Z | 2020-01-21T21:10:12Z | NaN |
| 4 | session_322 | search_905 | 2020-01-21T21:34:55.673Z | 2020-01-21T21:10:12Z | NaN |
bookings.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 339 entries, 0 to 338 Data columns (total 13 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 customer_id 339 non-null object 1 booking_id 339 non-null object 2 from_city 339 non-null object 3 from_country 339 non-null object 4 to_city 339 non-null object 5 to_country 339 non-null object 6 booking_time 339 non-null object 7 device_type_used 339 non-null object 8 INR_Amount 339 non-null float64 9 service_name 339 non-null object 10 no_of_passengers 339 non-null float64 11 days_to_departure 339 non-null float64 12 distance_km 339 non-null float64 dtypes: float64(4), object(9) memory usage: 34.6+ KB
Observations
sessions.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 1497 entries, 0 to 1496 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 session_id 1497 non-null object 1 search_id 1497 non-null object 2 search_time 1497 non-null object 3 session_starting_time 1497 non-null object 4 booking_id 366 non-null object dtypes: object(5) memory usage: 58.6+ KB
Observations
Not every search session results in a booking. Users might search for travel options, compare prices, and then decide not to book. Therefore, the booking_id column is only populated when a booking is actually made.
print(f'There are {bookings["booking_id"].nunique()} unique bookings in the data.')
There are 339 unique bookings in the data.
print(f'There are {sessions["session_id"].nunique()} unique sessions in the data.')
There are 331 unique sessions in the data.
print(f'There are {sessions["search_id"].nunique()} unique searches in the data.')
There are 1360 unique searches in the data.
Visualize the distribution of bookings per session using Histogram.
#ℹ️ Find bookings per session
bookings_per_session = sessions.groupby('session_id')['booking_id'].count().reset_index(name='nr_bookings')
#bookings_per_session
#ℹ️ Plot histogram
sns.histplot(bookings_per_session['nr_bookings'], kde=True, color='green')
plt.title('Distribution of Bookings per Session')
plt.xlabel('Number of Bookings')
plt.ylabel('Frequency')
plt.show()
Key Insights
Most sessions contain a single booking, while a small number include two bookings, and only one session reaches three bookings. The sharp peak at one booking indicates that users typically complete a single transaction per visit, suggesting that multi‑booking behavior is rare and may represent special cases such as group or repeat purchases.
Determine the days of the week with the highest number of bookings.
# ℹ️ Convert booking_time to datetime object
bookings['booking_time'] = pd.to_datetime(bookings['booking_time'])
# ℹ️ Extract weekday from booking_time
bookings['weekday'] = bookings['booking_time'].dt.strftime('%A')
# ℹ️ Count the number of bookings by weekday
bookings_per_weekday = bookings['weekday'].value_counts().reset_index()
print(f'The day of the week with the highest number of bookings is {bookings_per_weekday.head(1)["weekday"].values[0]}. ')
print(f'It has a total of {bookings_per_weekday["count"].values[0]} bookings.')
The day of the week with the highest number of bookings is Thursday. It has a total of 65 bookings.
Visualize this distribution using a pie chart.
# ℹ️ Find the index of the highest value
max_index = bookings_per_weekday['count'].idxmax()
# ℹ️ Create explode list
explode = [0.1 if i == max_index else 0 for i in range(len(bookings_per_weekday))]
# ℹ️ Create pie chart
plt.pie(bookings_per_weekday['count'], labels=bookings_per_weekday['weekday'], autopct='%1.1f%%', explode=explode)
plt.title('Distribution of Bookings by Weekday')
plt.show()
Key Insights
Calculate the total number of bookings and the Gross Booking Value (INR) for each service name.
service_name_aggregations = bookings.groupby("service_name"). agg({
'booking_id' : ['count'],
'INR_Amount' : ['sum']}).reset_index()
# ℹ️ Update column names
service_name_aggregations.columns = ['service_name', 'total_bookings', 'revenue(INR)']
#service_name_aggregations
Visualize using Bar chart comparing total bookings and revenue per service.
# ℹ️ Create a figure and a set of subplots
fig, ax1 = plt.subplots()
# ℹ️ Plot the total bookings
ax1.bar(service_name_aggregations['service_name'], service_name_aggregations['total_bookings'], color='b', alpha=0.7, label='Total Bookings')
# ℹ️ Create a twin Axes sharing the x-axis
ax2 = ax1.twinx()
# ℹ️ Plot the revenue
ax2.bar(service_name_aggregations['service_name'],service_name_aggregations['revenue(INR)'], color='r', alpha=0.3, label='Revenue')
# ℹ️ Add labels
ax1.set_xlabel('Service Name')
ax1.set_ylabel('Total Bookings', color='b')
ax2.set_ylabel('Revenue', color='r')
# ℹ️ Add a title
plt.title('Total Bookings and Revenue per Service')
# ℹ️ Add legends
ax1.legend(loc='upper right')
ax2.legend(loc='upper right', bbox_to_anchor=(1.0,0.9))
# ℹ️ Show the plot
plt.show()
The axes are independent and should not be interpreted as directly comparable values. The purpose of the dual axis is to visualize both metrics side by side, not to imply they share the same unit or magnitude.
Key Insights
GOIBIBO leads with the highest total bookings and revenue, indicating strong market presence and effective customer acquisition.
MMT (MakeMyTrip) shows the lowest revenue (under 1 million) and fewest bookings, suggesting challenges in conversion or pricing strategy.
YATRA maintains moderate performance in both metrics, reflecting a stable but smaller market share.
Identify the most booked route (from_city to to_city) for customers who have more than one booking.
# ℹ️ Counting total bookings per customer
bookings_by_customers = bookings['customer_id'].value_counts().reset_index(name='total_bookings')
# ℹ️ Filtering customers with more than one booking.
customers_multiple_bookings = bookings_by_customers[bookings_by_customers['total_bookings'] > 1]
# ℹ️ Filter bookings to only include customers listed in customers_multiple_bookings
bookings_filtered = bookings[bookings['customer_id'].isin(customers_multiple_bookings['customer_id'])]
# ℹ️ Counting the number of bookings for each city pair
route_bookings_count = bookings_filtered.groupby(['from_city', 'to_city'])['booking_id'].count().reset_index(name='total_bookings')
# Sorting the results and displaying the top routes
route_bookings_count.sort_values(by='total_bookings', ascending=False, inplace=True)
Visualize the top 5 routes displayed in a horizontal bar chart.
# ℹ️ Create a route column
route_bookings_count['route'] = route_bookings_count['from_city'] + ' -> ' + route_bookings_count['to_city']
# ℹ️ Create bar chart
ax = sns.barplot(data=route_bookings_count.head(), x='total_bookings', y='route', hue='route')
# ℹ️ Remove axis spines top and right
sns.despine()
# ℹ️ Update plot layout
plt.title('Top 5 Routes by Total Bookings for Customers with Multiple Reservations')
plt.xlabel('Total Bookings')
plt.ylabel('')
plt.legend(loc='upper left', bbox_to_anchor=(1.05, 1), borderaxespad=0.)
plt.show()
Key Insights
Repeat travelers concentrate around Gurgaon, which appears in three of the top five routes, confirming its role as a major origin hub.
The Gurgaon → Roissy-en-France route leads all others, suggesting strong international travel demand or frequent business connections between these cities.
Secondary routes such as Gurgaon → Rāja Sānsī and Durlaga → Bhubaneswar indicate regional travel patterns, possibly tied to domestic business or leisure trips.
Repeat bookings cluster around a small number of popular routes, rather than being spread evenly across many destinations.
Determine the top 3 departure cities where customers book significantly in advance (only consider cities with at least 5 departures).
# ℹ️Count the departures per city
departures_per_city = bookings.groupby(by='from_city')['booking_id'].count().reset_index(name='total_departures')
# ℹ️ Filter and reset index for cities with 5+ departures
departures_per_city_more_than_5 = departures_per_city[departures_per_city['total_departures'] >= 5].reset_index(drop=True)
# Filter bookings for cities with 5+ departures
departures_per_city_more_than_5_filtered = bookings[bookings['from_city'].isin(departures_per_city_more_than_5['from_city'])]
# Group by city and find each city's TYPICAL (median) lead time
city_median_lead_time = departures_per_city_more_than_5_filtered.groupby('from_city')['days_to_departure'].median().reset_index(name='median_days_to_departure')
# Now sort the CITIES by their typical lead time, and take the top 3
top_3_cities_advance_booking = city_median_lead_time.sort_values(by='median_days_to_departure', ascending=False).head(3)
top_3_cities_advance_booking
| from_city | median_days_to_departure | |
|---|---|---|
| 8 | New Delhi | 18.0 |
| 1 | Bālāpur | 17.5 |
| 6 | Madhyamgram | 15.5 |
Use Box plot showing days to departure for different cities.
# ℹ️ Create a Boxplot
sns.boxplot(data=departures_per_city_more_than_5_filtered, x='from_city', y='days_to_departure', hue='from_city')
plt.xticks(rotation=90)
plt.title('Distribution of the number of days between booking and departure for various cities')
plt.xlabel('')
plt.ylabel('')
plt.show()
Key Insights
Plot a heatmap displaying the correlation between numerical columns in the bookings dataset.
num_cols = bookings.select_dtypes(include=['float64', 'int64']).columns.tolist()
sns.heatmap(bookings[num_cols].corr(), annot=True)
plt.show()
Key Insights
Longer travel distances are strongly linked to higher booking costs, confirming that distance is a major driver of total fare.
The number of passengers shows a moderate positive relationship with cost, meaning group or family bookings tend to increase total spending.
The weak correlations between booking lead time and other variables suggest that how early customers book doesn't significantly affect price or distance.
Overall, the data indicates that distance and passenger count are the most influential factors in determining booking value.
Identify the most used device type for bookings for each service provider.
# ℹ️ Count total bookings by service name and device type
service_device_booking_count = bookings.groupby(by=['service_name', 'device_type_used'])['booking_id'] \
.count().reset_index(name='total_bookings')
#service_device_booking_count
Visualize using Stacked bar chart for device usage per service provider.
#ℹ️ Pivot the dataframe
df_pivot = service_device_booking_count.pivot(index='service_name', columns='device_type_used', values='total_bookings')
df_pivot
# ℹ️ Plot the stacked bar chart
df_pivot.plot(kind='bar', stacked=True)
plt.xlabel('Service')
plt.ylabel('Total Bookings')
plt.title('Total Bookings by Service and Device Type')
plt.legend(title='Device')
plt.xticks(rotation=360)
plt.show()
Key Insights
GOIBIBO leads in total bookings and shows the most diverse device usage, with strong engagement from iOS, desktop, and Android users.
MMT relies heavily on desktop users, suggesting a more traditional or work-based booking pattern.
YATRA maintains balanced usage across desktop and Android platforms, indicating broader accessibility and appeal to mobile users.
Tablet usage remains minimal across all services, showing limited adoption for travel bookings on that device type.
Plot a time series analysis showing the number of bookings per quarter, categorized by device type.
# ℹ️ Convert booking time to quarter format and store as string
bookings['quarter'] = bookings['booking_time'].dt.to_period('Q').astype(str)
# ℹ️ Find the number of bookings per quarter, categorized by device type.
bookings_per_quarter_device = bookings.groupby(['quarter', 'device_type_used'])['booking_id'].count()
bookings_per_quarter_device = bookings_per_quarter_device.reset_index(name='total_bookings')
# ℹ️ Create a line plot showing quarterly bookings by device type
bookings_per_quarter_device = bookings.groupby(['quarter', 'device_type_used'])['booking_id'].count()
bookings_per_quarter_device = bookings_per_quarter_device.reset_index(name='total_bookings')
plt.figure(figsize=(10, 5))
sns.lineplot(data=bookings_per_quarter_device, x='quarter', y='total_bookings', hue='device_type_used')
plt.title('Quarterly Bookings Analysis by Device Type')
plt.legend(title='Device Type')
plt.ylabel('Total Bookings')
plt.xlabel('Quarters')
plt.xticks(rotation=45)
plt.show()
Key Insights
Desktop and iOS bookings peaked sharply in early 2020, likely reflecting strong pre-pandemic travel activity before a noticeable decline in subsequent quarters.
Android and Mobile Web showed steady growth until Q1 2020, then gradually decreased, suggesting a shift in user behavior or reduced travel demand after that period.
Tablet usage remained consistently low across all quarters, confirming minimal engagement from this device type.
Overall, the data reveals a clear surge in bookings around Q1 2020 across most devices, followed by a steady decline. This pattern that aligns with major market disruptions during that time.
$\text{oBSR} = \frac{\text{Total Bookings}}{\text{Total Searches}}$
# ℹ️ Merge the two datasets
merged = pd.merge(sessions, bookings, on='booking_id', how='left')
# ℹ️ Convert 'search_time' to datetime
merged['search_time'] = pd.to_datetime(merged['search_time'], format='ISO8601', errors='coerce')
merged['date'] = merged['search_time'].dt.date
merged['weekday'] = merged['search_time'].dt.strftime('%A')
# ℹ️ Aggregate total bookings, searches, and customers by date
daily_summary = merged.groupby(by='date').agg(
total_bookings=('booking_id', 'nunique'),
total_searches=('search_id', 'nunique'),
total_customers = ('customer_id', 'nunique')
).reset_index()
# ℹ️ Calculate oBSR as the ratio of total bookings to total searches
daily_summary['oBSR'] = np.round(daily_summary['total_bookings'] / daily_summary['total_searches'], 2)
# ℹ️ Create a Plotly line chart
fig = px.line(daily_summary, x='date', y='oBSR', color_discrete_sequence=['darkgoldenrod'])
# ℹ️ Customize the layout
fig.update_layout(
xaxis_title='Date',
yaxis_title=' ',
title={
'text': 'Overall Booking-to-Search Ratio',
'x': 0.5,
'xanchor': 'center'
}
)
# ℹ️ Save the plot as PNG
fig.write_image("images/book_search_ratio.png", engine="kaleido")
# ℹ️ Show the plot
fig.show()
Key Insights
The oBSR fluctuates sharply over time, showing periods of high conversion where nearly every search led to a booking, followed by intervals of low engagement.
Peaks around mid-2019 and early 2020 suggest strong booking activity relative to search volume, possibly driven by seasonal travel demand or promotional campaigns.
The frequent dips indicate inconsistent conversion performance, which may reflect changes in pricing, competition, or user behavior.
Overall, the ratio's volatility highlights that search interest doesn't always translate into bookings, emphasizing the need for steady optimization of the booking funnel.
What is the average of oBSR for each month of the year?
# ℹ️ Extract the month from the 'date' column and map it to month names
daily_summary['month'] = pd.to_datetime(daily_summary['date']).dt.month
# map month to month name
month_mapping = {
1: 'Jan', 2: 'Feb', 3: 'Mar', 4: 'Apr', 5: 'May', 6: 'Jun', 7: 'Jul', 8: 'Aug', 9: 'Sep', 10: 'Oct', 11: 'Nov', 12: 'Dec'}
daily_summary['month']= daily_summary['month'].map(month_mapping)
# ℹ️ Calculate the mean oBSR for each month
avg_monthly_oBSR = np.round(daily_summary.groupby('month')['oBSR'].mean(), 2).reset_index(name = 'avg_monthly_oBSR')
month_order = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
# ℹ️ Sort the DataFrame by the defined month order
avg_monthly_oBSR['month']= pd.Categorical(avg_monthly_oBSR['month'], categories=month_order, ordered=True)
avg_monthly_oBSR = avg_monthly_oBSR.sort_values(by='month')
# ℹ️ Create a Plotly line chart
fig = px.line(avg_monthly_oBSR, x='month', y='avg_monthly_oBSR',color_discrete_sequence=['green'])
# ℹ️ Customize the layout
fig.update_layout(
xaxis_title='Month',
yaxis_title='Average oBSR',
title={
'text': 'Average oBSR for Each Month',
'x': 0.5,
'xanchor': 'center'
}
)
# ℹ️ Save the plot as PNG
fig.write_image("images/avg_monthly_obsr.png", engine="kaleido")
# ℹ️ Show the plot
#fig.show()
Observations
Key Insights
The average booking-to-search ratio (oBSR) fluctuates throughout the year, showing distinct seasonal patterns.
June and September record the highest oBSR values, suggesting stronger conversion rates during mid-year travel seasons.
July shows the lowest oBSR, indicating reduced conversion efficiency, possibly due to high search activity without corresponding bookings.
Early months like February and late months like November maintain moderate ratios, reflecting steady but less pronounced booking performance.
Overall, the data suggests that mid-year periods drive the most effective conversion from search to booking, while summer months may require targeted engagement strategies.
What is the average of oBSR for each day of week?
# Convert the 'date' column to datetime format and extract the weekday name
daily_summary['weekday'] = pd.to_datetime(daily_summary['date']).dt.day_name()
# ℹ️ Calculate the mean oBSR for each day of the week
avg_daily_oBSR = np.round(daily_summary.groupby('weekday')['oBSR'].mean(), 2).reset_index(name = 'avg_daily_oBSR')
weekday_order = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']
# ℹ️ Sort the DataFrame by the defined month order
avg_daily_oBSR['weekday']= pd.Categorical(avg_daily_oBSR['weekday'], categories=weekday_order, ordered=True)
avg_daily_oBSR = avg_daily_oBSR.sort_values(by='weekday')
# ℹ️ Create a Plotly line chart
fig = px.line(avg_daily_oBSR, x='weekday', y='avg_daily_oBSR', title='Average Daily oBSR',
color_discrete_sequence=['purple'])
# ℹ️ Customize the layout
fig.update_layout(
xaxis_title='Weekday',
yaxis_title='Average oBSR',
title={
'text': 'Average oBSR for Each Day',
'x': 0.5,
'xanchor': 'center'
}
)
# ℹ️ Save the plot as PNG
fig.write_image("images/avg_daily_obsr.png", engine="kaleido")
# ℹ️ Show the plot
fig.show()
Bin customers based on booking frequency
customer_booking_counts = bookings['customer_id'].value_counts().reset_index()
# ℹ️ Calculate the minimum and maximum values
min_value = customer_booking_counts['count'].min()
max_value = customer_booking_counts['count'].max()
# ℹ️ Total number of data points
N = len(customer_booking_counts)
# ℹ️ Sturges' Rule
num_bins = int(1 + np.log2(N))
# ℹ️ Calculate the bin width
bin_width = (max_value - min_value) / num_bins
# ℹ️ Create the bin edges and round them to 2 decimal points
bin_edges = [round(min_value + i * bin_width, 2) for i in range(num_bins + 1)]
# ℹ️ Assign intervals to booking counts
customer_booking_counts['intervals'] = pd.cut(customer_booking_counts['count'], bins=bin_edges, include_lowest=True)
interval_counts = customer_booking_counts['intervals'].value_counts().reset_index()
# ℹ️ Draw a barplot
plt.figure(figsize=(10, 6))
sns.barplot(data=interval_counts, x='intervals', y ='count', hue='intervals')
sns.despine()
plt.title('Distribution of Customer Booking Counts')
plt.xlabel('Intervals')
plt.ylabel('Count')
plt.legend().remove()
plt.xticks(rotation=45)
plt.show()
Key Insights
The distribution of booking counts is heavily skewed, with the vast majority of customers making only one booking.
A small fraction of users book two or more times, and repeat bookings drop sharply beyond that point.
This pattern indicates a high proportion of one-time customers, suggesting limited repeat engagement or loyalty.
The long tail of low-frequency repeat customers highlights potential for growth through retention strategies.
Bin customers based on total amount spent
total_amount_per_customer = bookings.groupby('customer_id')['INR_Amount'].sum().reset_index(name='total_amount')
# ℹ️ Calculate the minimum and maximum values
min_value = total_amount_per_customer['total_amount'].min()
max_value = total_amount_per_customer['total_amount'].max()
# ℹ️ Calculate the bin width
bin_width = (max_value - min_value) / num_bins
# ℹ️ Create the bin edges and round them to 2 decimal points
bin_edges = [round(min_value + i * bin_width, 2) for i in range(num_bins + 1)]
# ℹ️ Assign intervals to booking counts
total_amount_per_customer['intervals'] = pd.cut(total_amount_per_customer['total_amount'], bins=bin_edges, include_lowest=True)
# ℹ️ Round interval bounds to two decimal places
total_amount_per_customer['intervals'] = total_amount_per_customer['intervals'].apply(lambda x: f'({round(x.left, 2)}, {round(x.right, 2)}]')
# ℹ️ Calculate the counts of each interval and reset the index
interval_counts = total_amount_per_customer['intervals'].value_counts().reset_index()
# ℹ️ Draw a barplot
plt.figure(figsize=(10, 6))
sns.barplot(data=interval_counts, x='intervals', y ='count', hue='intervals')
sns.despine()
plt.title('Distribution of Customers Spending')
plt.xlabel('Intervals')
plt.ylabel('Count')
plt.legend().remove()
plt.xticks(rotation=45)
plt.show()
Key Insights
The majority of customers fall within the lowest spending range (INR 1.4K-INR 25K), indicating that most transactions are relatively small.
A smaller segment spends between INR 25K-INR 75K, showing moderate engagement and potential for upselling.
Very few customers spend above INR 1 lakh, highlighting a small but valuable high-spending group.
The overall distribution is right-skewed, meaning spending is concentrated among lower-value customers with a long tail of high-value outliers.
Identify unusual bookings with extreme price values.
# ℹ️ Define outlier threshold
price_mean = round(np.mean(bookings['INR_Amount']),2)
price_std = round(np.std(bookings['INR_Amount']),2)
threshold = price_mean + 3 * price_std
extreme_prices = bookings[bookings['INR_Amount'] > threshold]
Visualize a scatter plot to visualize booking prices vs. number of passengers.
# ℹ️ Create a scatterplot
sns.scatterplot(data=extreme_prices, x='no_of_passengers', y='INR_Amount')
plt.title('Fraudulent Booking Detection: Price Outliers')
plt.xlabel('No. of passengers')
plt.ylabel('Price')
plt.show()
Key Insights
A few bookings show extremely high prices (above INR 1.2 lakh) compared to the typical range, marking them as potential outliers.
These outliers mostly occur for bookings with two passengers, with one notable case at three passengers, suggesting that passenger count alone does not explain the price anomaly.
The sharp deviation from the mean price indicates possible data entry errors, system glitches, or fraudulent transactions.
Overall, the scatterplot effectively isolates high-value anomalies for further investigation.
Analyze how fluctuations in fare prices affect booking rates.
price_bins = pd.cut(bookings['INR_Amount'], bins=10)
price_sensitivity = bookings.groupby(price_bins)['booking_id'].count().reset_index()
price_sensitivity.columns = ['price_range', 'booking_count']
# ℹ️Plot bar chart
plt.figure(figsize=(10, 6))
ax = sns.barplot(data=price_sensitivity, x='price_range', y='booking_count', hue='price_range')
plt.title('Price Sensitivity Analysis')
plt.xlabel('Price Range')
plt.ylabel('Booking Count')
# ℹ️ Remove xtick labels
ax.set_xticklabels([])
# ℹ️ Create a second y-axis for the line plot
ax2 = ax.twinx()
# Convert the categorical data in the 'price_range' column into numerical codes
price_sensitivity['price_range_num'] = price_sensitivity['price_range'].cat.codes
# Use the numerical representation for the line plot
sns.lineplot(data=price_sensitivity, x='price_range_num', y='booking_count', ax=ax2, color='red')
ax2.set_ylabel('Booking Count (Line Plot)')
plt.show()
Key Insights
The analysis shows a strong inverse relationship between fare price and booking count; as prices increase, bookings drop sharply.
The lowest price range (INR 1.1K-INR 23K) attracts the highest number of bookings, confirming that customers are highly price-sensitive.
Mid-range prices (INR 23K-INR 65K) see moderate engagement, while bookings decline steeply beyond INR 65K, indicating limited demand for premium fare tiers.
The red line trend reinforces that booking activity decreases almost exponentially with rising prices, highlighting clear thresholds for optimal pricing.
Use a Funnel chart to visualize search-to-booking drop-off.
# Count DISTINCT searches, and DISTINCT searches that had a booking
total_searched = sessions['search_id'].nunique()
completed_bookings = sessions[sessions['booking_id'].notnull()]['search_id'].nunique()
abandoned_searches = total_searched - completed_bookings
abandoned_search_rate = round((abandoned_searches / total_searched) * 100, 2)
print(f'The abandoned search rate for searches that do not end in bookings is {abandoned_search_rate:.0f}%.')
The abandoned search rate for searches that do not end in bookings is 75%.
# ℹ️ Prepare data for the funnel chart
stages = ['Search', 'Complete Booking']
counts = [total_searched, completed_bookings]
funnel_data = {
'stages': stages,
'counts': counts
}
# ℹ️ Create the funnel chart using Plotly
fig = go.Figure(go.Funnel(
y=funnel_data['stages'],
x=funnel_data['counts'],
textinfo="value+percent initial",
marker={"color": ["#636EFA", "#EF553B"]}
))
# ℹ️ Update layout for better visualization
fig.update_layout(
title='Search-to-Booking Drop-off Funnel',
yaxis_title='Stages',
xaxis_title='Number of Customers',
width=800,
height=600
)
# ℹ️ Save the plot as PNG
fig.write_image("images/dropoff_funnel.png", engine="kaleido")
#ℹ️ Display the funnel chart
fig.show()
Key Insights
Identify repeat customers and analyze their preferences.
bookings_per_customer = bookings['customer_id'].value_counts().reset_index()
# ℹ️ Filter for repeat and one_time customers
repeat_customers = bookings_per_customer[bookings_per_customer['count'] > 1]
one_time_customers = bookings_per_customer[bookings_per_customer['count'] == 1]
# ℹ️ Create a piechart
labels = ['Repeat Customers', 'One-Time Customers']
sizes = [len(repeat_customers), len(one_time_customers)]
colors = ['#636EFA', '#EF553B']
plt.figure(figsize=(6, 6))
plt.pie(sizes, labels=labels, colors=colors, autopct='%1.1f%%')
plt.title('Loyalty Insights: Repeat vs. One-Time Customers')
plt.show()
Key Insights
The pie chart reveals that 91.5% of customers are one-time bookers, while only 8.5% are repeat customers.
This indicates a low customer retention rate, suggesting that most users engage with the platform only once.
The small proportion of repeat customers highlights limited long-term loyalty and potential gaps in post-booking engagement.
Strengthening retention strategies could significantly improve lifetime value and reduce acquisition costs.