Nguyen Hai Tram

About this project

Business Case:

As a participant at Maven Challenge, I were tasked with playing the role of a BI Developer for UK National Rail, a company that provides business services to passenger train operators in England, Scotland, and Wales. My aim to create an exploratory dashboard to understand overall performance of the company and analyze user behavior. The key business problems are:

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

Data processing

In this part, I will add some new columns for Railway table to support for metrics in the dashboard:

• Route Name = Departure Station & Arrival Station

• Route ID = Departure Station & Arrival Station & Departure Time & Arrival Time

• Length of Route = Actual Arrival Time – Departure Time

• Departure Time of Previous Route: ( Previous Route is the Route starts right before the current Route)

I use OFFSET to get Departure time of the previous Route.

This field is used to calculate layover time between trips

• Length of Delay = Actual Arrival Time – Arrival Time

This is my data model:

Data Analysis & Insights:

My dashboard include three tabs:

1. Overview:

This tab includes below highlighted points:

• Trail network using map chart

• In overall, The peak travel day is Sunday Wednesday and the peak travel time is between 6 a.m-8 a.m and 4 p.m and 6 p.m

• Manchester to Liverpool is the most popular routes in volume, but London Kings Cross to York is the route generating the highest revenue.

• Cancelled and Delayed Routes account for around 13% amount of total transaction, quite high percentage. which can have impact on user experience. And the common reason for this is Signal Failure.

2. Route Analysis:

This tab focuses on key metrics of a certain route, which help the company to know the performance of each routes, define the problem (if any), from that can have suitable solution in detail.

I will take example with the highest demand – route Manchester – Liverpool, as you can see from below capture, some insight from this dashboard are:

• The percentage of Delayed/Cancelled Route is quite high (nearly 15%) and the average length of Delayed is twice the average Route Length. This is serious issue that can affect user experience.
• Focus on the reason for Delayed/Cancelled Journey to find better solution to minimize signal failures, handle weather-related disruptions and the staff resources.
• Layover time is 0.9H means that every hour, there will be a trip for this route. It’s reasonable with the route has very high demand. If the demand increases strongly, the company can consider to enhance the train frequency to meet the passenger demand.

     3. User behavior

This tab emphasizes user trend in many factors:

• Purchase Type: user tend to buy ticket online, so the company should invest in infrastructure and technology for online channel.
• Ticket Class: Passenger mostly want to buy Standard Class
• Revenue: Advanced Ticket has the largest contribution on total revenue.
• With Cancelled/Delayed Journey: around 30% refund request, which made a huge lost on total revenue.