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.