This project aims to develop a comprehensive dashboard to analyze and visualize train ticket sales data for National Rail, a company that provides business services to passenger train operators in England, Scotland, and Wales.The data contains mock train ticket sales for National Rail in the UK, from January to April 2024, including details on the type of ticket, the date & time for each journey, the departure & arrival stations, the ticket price, and more.

Objectives

• Identify the most popular routes
• Determine peak travel times
• Analyze revenue from different ticket types & classes
• Diagnose on-time performance and contributing factors

Identify the most popular routes

Out of 64 routes, the route from Manchester Piccadilly to Liverpool Lime Street is the most popular with 4,338 total trips, and London Euston to Birmingham New Street is the second highest with 3998 trips. While the most revenue generated is London Kings Cross to York route, which is the third most popular route in terms of trips, this suggests that the people travelling on the London Kings Cross to York route tend to pay more , possibly due to business travel or the popularity of York as a tourist destination.

Peak Travel Times

"Trips by Arrival Time" chart shows the number of trips arriving at various hours of the day. Morning Peak: A significant spike in arrivals is observed between 7 AM and 9 AM, with the highest number of trips arriving at 8 AM (3K trips). Evening Peak: Another noticeable peak occurs between 5 PM and 7 PM, with 6 PM seeing the most arrivals (2K trips). Reflecting the return journey of commuters.

The "Trips by Departure Time" chart shows there is a peak in departures between 6 AM and 8 AM, with the highest number of departures at 7 AM (3K trips).The evening peak for departures is observed between 4 PM and 6 PM, with the highest departures at 5 PM (3K trips).

Revenue from Ticket Types & Classes

Advance Tickets are among the highest revenue contributors among all ticket types. These tickets are typically booked in advance at a discounted rate. Off-Peak Tickets are the second-highest revenue contributor. These tickets are sold for travel during non-peak hours, offering flexibility and affordability. There is not much difference in revenue between Off-Peak and Anytime tickets, which contribute 29% and 28% of the revenue respectively.

Standard Class tickets generate the most revenue at approximately £560,184, accounting for about 80% of the total revenue. This is followed by First Class tickets, which generate £143,035.

Revenue Analysis by Journey Statuses

The majority of the revenue is generated from on-time trips.This is expected as on-time trips likely represent successful service delivery, leading to customer satisfaction and repeat business. Revenue from cancelled trips is significantly lower compared to on-time and delayed trips.While delayed trips still generate revenue, they may lead to customer dissatisfaction and potential loss of future business.

On-Time Performance and Contributing Factors

A total of 31653 trips were scheduled from January - April 2024.From which 27841(86.2)% trips were sucessfull and made it on time.Out of 2292(7.24%) were deleyaed and 1880(5.94%) got cancelled.Overall, almost 90% of trips were successful.

Reasons for Delays and Cancellations

Cancellations

Signal failure is the top reason for cancellations, followed by staff shortages. The total revenue loss due to canceled trips is approximately €45,000

Delays

The most common reason for delays is weather conditions, which caused 927 delays.Technical issues are the second most common reason for delays, causing 472 delays.

Recommendations

Peak Travel Times

Given the clear peaks during morning and evening rush hours, it is essential to allocate more resources (such as additional trains and staff) during these periods to accommodate the high passenger volume. Align maintenance schedules with off-peak hours to ensure minimal disruption.

Improve On-Time Performance

Focus on strategies to enhance punctuality, such as better route planning, real-time traffic monitoring, and efficient resource allocation.Invest in upgrading technical infrastructure to reduce the occurrence of technical issues.

Optimize Revenue

Highlight the perks of booking early and give discounts to encourage people to book ahead. Use flexible pricing and offer packages with extras to make more money and give customers more for their money.

Conclusion

The dashboard provides a comprehensive analysis of ticket sales data, offering valuable insights into travel patterns, revenue streams, and operational performance. These insights support data-driven decision-making, helping National Rail enhance service delivery, optimize schedules, and increase customer satisfaction.