Maven Rail Challenge - Analyze performance of National Rail Company

PROJECT GOALS:

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

METRICS:

• Total Routes: The combination of departure stations and arrival destinations.
• Total Revenue: The sum of the price of each ticket, including refunded tickets.
• Revenue Without Refund: Revenue excluding refunded tickets.
• Total Tickets: The total number of tickets sold, including refunded tickets.
• On-time Ratio: The ratio of on-time tickets divided by total tickets.
• Refund Ratio: The ratio of refunded tickets divided by total tickets.

DASHBOARD EXPLANATION:

Instructions: Select the pages you want to view, and use the filters in the left panel to focus on a specific time frame, departure station, or arrival destination.

1. OVERVIEW PAGE:

Show overall main metrics such as Routes, Total Revenue, Revenue without refund, Tickets, Ontime Ratio and Refund Ratio

Explanation of Two Main Metrics: Tickets and Revenue

• Ticket Class: Breakdown of the number of tickets sold/revenue by class (e.g., Standard, First Class).
• Ticket Type: Analysis of tickets/revenue based on type (e.g., peak, off-peak).
• Purchase Type: Distribution of tickets sold/Revenue through various purchase methods (e.g., online, at the station).
• Payment Method: Insights into the payment methods used (e.g., credit card, cash).
• Routes: Number of tickets sold/revenue for each route.
• Departure Hours: Ticket sales/revenue distribution by departure hours, highlighting peak times.

Use the view button in the top right corner to switch between the detailed breakdowns of Tickets and Revenue, allowing for a comprehensive analysis of each metric by the specified categories.

2. TICKET DETAIL PAGE:

Focus on two parts: Refunds and On-time Performance, to understand the reasons behind refunded tickets and the low on-time ratio.

3. ANALYSIS REPORT PAGE:

Address the questions in the project goals using data from the two previous pages.

DATA ANALYTIC INSIGHTS:

1. Identifying the most popular routes

• In the total of approximately 31,000 tickets, the route from Manchester Piccadilly to Liverpool Lime Street has the highest ticket sales. However, in terms of revenue, the route from London Kings Cross to York generates the most revenue, amounting to $183,000.
• Manchester Piccadilly is the most popular departure location, while Birmingham New Street is the most popular destination.

2. Determining peak travel times

The busiest times of the day are from 6 AM to 8 AM and from 3 PM to 6 PM, with ticket sales more than doubling compared to other times of the day.

3. Analyzing revenue from different ticket types & classes

• Standard Class accounts for approximately 80% of the revenue.
• Advance, Off Peak and Anytime ticket types do not show significant differences in their revenue contributions. (accounts for 42%, 30% and 28% Respectively)

4. Diagnosing on-time performance and contributing factors of Refund

• Refund Analysis: During the analysis period, there were a total of 1,118 refunded tickets (Causing a decrease of 5% Revenue). About half of these were due to cancellations, while the other half were due to delays. The refund rate for off-peak tickets is higher compared to other types, standing at 4.3%.
  • Delays were primarily caused by technical issues and staffing problems, accounting for approximately 78% of refunded tickets.
  • Cancellations were mainly due to signal failures and weather conditions, making up about 50% of refunded tickets.
• Ontime performance Analysis: From January to April, the overall on-time ratio is 90%. The route from Liverpool Lime Street to London Euston has the lowest on-time ratio, with only 20%.
  • The on-time ratio also decreases during peak hours, with about 60% at 8 AM and 69% at 12 PM.
  • Liverpool Lime Street is the departure station with the lowest on-time ratio, around 75%.
  • London Euston is the arrival station with the lowest on-time ratio, approximately 25%.

==> Thank you for visiting to the end, for more information about this challenge, you can visit this link:

---

Tri Hai - 02/06/2024

Contact me at: https://www.linkedin.com/in/phungtrihai25112000/