Riding Smooth: Optimizing National Rail Travel

For this project, I am acting as a BI Developer for National Rail, a company that provides business services to passenger train operators in England, Scotland, and Wales.

My manager has asked me to create an exploratory dashboard that helps them:

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

They provided me with mock train ticket data for National Rail in the UK for journeys between Jan-Apr 2024

I created a multi-page dashboard report with the following pages:

• Cover Page - Report Introduction and for page navigation.
• Overview - Showing an Overview of KPIs.
• Revenue - Showing Revenue KPIs in more detail.
• Performance - Showing Performance KPIs and peak travel time trends.

Data Preparation & Cleaning

The data was provided in a csv file and was clean. There was no need to impute or remove missing values. I added a route column to the table, which joined the departure station and the arrival station into a column to help with the analysis. A delay column was added, which calculated the difference between the arrival time and the actual arrival time to make calculating the average delay time easier. I also added an hour column to the table. This was based on the departure time to help with showing peak times for departure. I then tidied up the text in the reasons for delay column to reduce the number of reasons for delay. For example, I changed "Weather Conditions" to "Weather" and "Staff Shortage" to "Staffing" and I capitalized the first letter of each word in that column.

Cover Page

This page is the navigation page for the report and contains a brief introduction to the report.

Overview Page

Key Insights

• Customers purchased 31,653 tickets, resulting in £703,219 in revenue after refunds.
• Delays or cancellations affected 13% of the total trips, while 87% of trips were completed on time. There were 1,880 trip cancellations. Since the number of trips that experienced delays or cancellations exceeded 10%, we need to examine the reasons for these delays or cancellations in order to identify areas where we can reduce the number of delays or cancellations.
• January 31, 2024 had the highest revenue, although not the highest number of trips which was March 9, 2024. March 27, 2024 had the highest number of cancellations. March 1 and April 1 had the lowest revenue and number of trips.
• The Manchester Piccadilly to Liverpool Lime Street route had the highest number of trips at 4,628 trips.
• The Liverpool Lime Street to Birmingham New Street route is the least popular route, with just 14 trips.

Revenue Page

Key Insights

• Refunds caused a loss of £38,702 or 5.22% of revenue.
• Weekends contributed £196,984 or 27% to the raised revenue, while working week days contributed £544,937 or 73%.
• Standard class tickets raised 80% of revenue at £592,522 whilst first-class tickets raised 20% or £149,399.
• Online purchases were just slightly ahead of purchases at stations at £382,754.
• Credit card was the most popular payment type with payments totalling £469,511.
• The adult rail card was the most popular rail card purchased, raising £86,330 of revenue.
• Out of the ticket types, the advanced ticket was most popular for both first at £66,886 and standard class at £242,388 of revenue.
• Trips that arrived on time earned £569,561 of revenue
• The highest sales volume occurred on January 31, 2024. With £9,196 of sales. Whilst the lowest was April 1, 2024 with just £1,562 of sales.
• The route that earned the most revenue was the London King's Cross to York route generating £179,498 or 26% of revenue. Whilst the Edinburgh Waverley to London King's Cross raised no revenue.

Performance Page

Key Insights

• Weather was the biggest cause of delays or cancellations. Affecting 1,372 or 4% of trips. Traffic problems impacted 314 or 1% of trips and require investigation to determine potential solutions.
• Manchester Piccadilly to Liverpool Lime Street was the busiest, with 4,628 trips, 3,984 of these trips arrived on time, with 354 trips being delayed and 290 trips cancelled.
• Signal failure is the second most common reason for a delay, with 970 trips affected by that. This problem needs to be investigated to create a plan to solve this problem. Staffing problems affected 809 trips and also needs investigating and a plan to solve this problem raised.