
Title: SQL Data Analysis Project
·
10 min read
·
Jan 10, 2024
Hey everyone! 🎉 Just wrapped up a SQL data analysis project, and I’m excited to share the incredible journey and the goals I’ve achieved along the way.
Successfully completing the SQL data analysis project was a major milestone. It required dedication, problem-solving skills, and countless hours of digging into the datasets. One of my primary goals was to enhance my SQL skills, and I’m proud to say that I can now craft intricate queries with confidence. My SQL skills have leveled up significantly, opening doors to more challenging project. Also, Cleaning messy datasets, Dealing with missing values, outliers, and ensuring data integrity are skills I’ve honed during this project.
METHODOLOGY
Data Preparation and Cleaning
Data Importation: The data was firstly converted into CSV (comma separated value). Then it was imported into PostgreSQL
Data Cleaning steps
I removed unnecessary spaces and comma and also changed the data type of Avg_daily_rate , revenue and revenue_loss columns from text to numeric
The columns in the database include:
.Booking ID: Unique identifier for each booking.
•Hotel: Type or name of the hotel within the Splendor Hotel Group.
•Booking Date: Date when the booking was made.
•Arrival Date: Date when the guests are scheduled to arrive.
•Lead Time: Number of days between the booking date and arrival date.
•Nights: Number of nights the guests are booked to stay.
•Guests: Number of guests included in the booking.
•Distribution Channel: The channel through which the booking was made (e.g., Direct, Online Travel Agent, Offline Travel Agent).
•Customer Type: Type of customer making the booking (e.g., Transient, Corporate).
•Country: Country of origin of the guests.
•Deposit Type: Whether a deposit was made for the booking (e.g., No Deposit, Deposit).
•Avg Daily Rate: Average daily rate for the booking.
•Status: Status of the booking (e.g., Check-Out, Canceled).
•Status Update: Date of the last status update for the booking.
•Canceled (0/1): Binary indicator of whether the booking was canceled (1 if canceled, 0 if not canceled).
•Revenue: Revenue generated from the booking.
•Revenue Loss: Loss in revenue if the booking was canceled (negative value if the booking wasn’t canceled).
Table ERD
Case Study Questions
Booking Patterns:
•What is the trend in booking patterns over time, and are there specific seasons or months with increased booking activity?
Finding trends in booking patterns over time involves analyzing data to identify recurring patterns, fluctuations, or changes in booking activity. This includes understanding the peak seasons or months when booking activity is consistently higher. To do this I used the extract and count function. I extracted the month from the booking date as well as using the count function to get the total number of bookings for each month
Customer Behavior Analysis:
. Which distribution channels contribute the most to bookings, and how does the average daily rate (ADR) differ across these channels?
Cancellation Analysis:
How does the revenue loss from cancellations compare across different customer segments and distribution channels?
Revenue Optimization:
•What is the overall revenue trend, and are there specific customer segments or countries contributing significantly to revenue?
Geographical Analysis:
•How does the distribution of guests vary across different countries, and are there specific countries that should be targeted for marketing efforts?
Identifying patterns in the distribution of guests based on their country of origin involves analyzing data to understand where guests predominantly come from
Operational Efficiency:
What is the average length of stay for guests, and how does it differ based on booking channels or customer types?
Are there patterns in check-out dates that can inform staffing and resource allocation strategies?
Impact of Deposit Types:
•How does the presence or absence of a deposit impact the likelihood of cancellations and revenue generation?
Analysis of Corporate Bookings:
•What is the proportion of corporate bookings, and how does their Average Daily Rate (ADR) compare to other customer types?
Time-to-Event Analysis:
•How does the time between booking and arrival date (lead time) affect revenue and the likelihood of cancellations?
Are there specific lead time ranges that are associated with higher customer satisfaction or revenue?
I organized the lead time into a four-month interval using the CASE function to determine which lead time ranges affects/ is associated with higher revenue and likelihood of cancelation.
Comparison of Online and Offline Travel Agents:
•What is the revenue contribution of online travel agents compared to offline travel agents?
•How do cancellation rates and revenue vary between bookings made through online and offline travel agents?
After using SQL to analyze, I imported my query into Power BI to visualize for better insight.
Key performance indicators: Total Revenue = $29.60M
January had the highest revenue generation and revenue loss While September had the lowest revenue generation, November had the lowest revenue loss.
The highest revenue was generated when the lead time ranged between 4 months (1–120 days). It can also be deduced that the higher the lead time range the lower the revenue.
Revenue peaked in 2016 with over 13M, while 2017 had a slight decrease generating 7M+ which is 6M less than 2016’s revenue.
Percentage of revenue loss: 30.7%
Total bookings: 119K
Percent of successful bookings: 74%
January had the most booking while June had the lowest booking.
The highest number of cancelations occurred for reservation without a deposit (No deposit)
Online Travel Agent was the distribution channel that contributed the most to bookings and cancellation.
Average length of stay: 3.43
Total number of Guests: 235K
Most guests were from Portugal
Embarking on this SQL data analysis journey has been incredibly rewarding This project has been a journey of growth, learning, and achievement. I’m excited to share my experience and showcase the results . Looking forward to applying these skills to new challenges and setting even more ambitious goals in the future! 💪🚀 #DataAnalysis #SuccessStory #Achievements #SQL #CareerGoals
2 likes · 13 views · Feb 27, 2025
Hey everyone! 🎉 Just wrapped up a SQL data analysis project, and I’m excited to share the incredible journey and the goals I’ve achieved along the way. Succes…