National Rail Insights

Introduction

The objective of this project was to develop an exploratory dashboard for Nation Rail managers to gain initial insights into their passengers and operations. Specifically, this dashboard seeks to address the following key questions:

• What are the most popular routes?
• Are there more passengers on weekdays compared to weekends?
• How does the traveling behavior of railcard holders differ from non-railcard holders?
• What are the peak travel times?
• What is the revenue trend, and how is it distributed by ticket class and type?
• What is the on-time performance, and what are the common causes of delays?

Methodology

The dashboard is divided into two sections:

1. Traveler Behavior: This section showcases the top 5 routes based on passenger numbers, a breakdown by day and railcard holder status, and peak travel times.
2. Operating Performance: This section highlights revenue and its breakdown by ticket class and type, on-time performance, reasons for delays, and refund amounts.

The primary metrics are passengers, revenue, and on-time performance. To visually represent the most popular routes, I mapped the train stations using their longitude and latitude coordinates and created the map in Python. The remaining visuals were created directly in Power BI.

Data insights

• All journeys occurred between January 1, 2024, and April 30, 2024 (4 months). During this period, nearly 32,000 passengers traveled. The five most popular routes, which accounted for almost 64% of all passengers, were:

• There are three notable drops in passenger numbers during this period: January 1, March 1, and April 1. The reduced travel on January 1 and April 1 can be attributed to these being public holidays in the UK. However, the dip on March 1 is unusual and might indicate a data issue.

• There are no significant differences in travel behavior by day, whether for the top 5 routes or for all routes.

• There are notable differences among railcard holders. Nearly 50% of travelers on the top two routes use railcards. Additionally, the route from Manchester Piccadilly to Liverpool Lime Street has a high proportion of senior railcard holders.

• Peak travel times are between 6 AM and 8 AM, and between 4 PM and 6 PM, which are also the most popular times on weekends. Peak times vary by route; for instance, the London Paddington to Reading route lacks an afternoon peak, as most passengers return to London, while the London St Pancras to Birmingham New Street route has no morning peak, as most passengers are traveling to London.

• Revenue is influenced by the number of tickets sold and the distance traveled. Longer distances, particularly with anytime and first-class tickets, generate higher revenue. Revenue patterns mirror the daily drops in passenger numbers, with an additional drop observed in February. Filtering by other visuals reveals that this revenue drop is due to an increase in the use of advance tickets during this period. Additional data points and further analysis is required to understand the factors driving this behavior.

• On-time performance has remained relatively stable over this period, ranging from 80% to 90%. However, there are significant differences between routes. For example, the Liverpool Lime Street to London Euston route has an on-time performance of only 20%. While weather is a major factor, operational and staffing issues should also be addressed, especially since this route is among the top three revenue generators. Refunds for technical issues on this route account for more than 50% of the total refunded amount.

CONCLUSION

An interactive dashboard, divided into two sections, was developed to provide preliminary insight into National Rail passengers, revenue, and on-time performance. A noticeable decline in advance ticket sales for February travel has led to decreased revenue. While weather remains the primary cause of delays, which is beyond National Rail's control, attention can be directed towards addressing technical issues and optimizing staffing to enhance performance, particularly on specific routes, thus reducing refunds and maximizing profits.

For further analysis and optimizations, I would explore the possibility of forecasting passengers, implementing an optimal pricing strategy and predicting refunds.