UK Railway's Train Track Insights

Objective:

For the Maven Rail Challenge, you'll play the role of a BI Developer for National Rail, a company that provides business services to passenger train operators in England, Scotland, and Wales.

You've been asked by your manager to create an exploratory dashboard that helps them:

· Identify the most popular routes

· Determine peak travel times

· Analyze revenue from different ticket types & classes

· Diagnose on-time performance and contributing factors

Analysis:

The dashboard provides an overview of the explanatory analysis for Mail Railways over four months in 2024. It consists of four main sections: Trips Analysis, Revenue Analysis, Train Delays, and Ticket Analysis. Use the filters and button tabs for insights, patterns, and exploration.

The dataset required some transformation regarding dates and times to enable perfect analysis across train routes over time. This included creating numerous DAX measures for in-depth analysis of peak travel times, trips, revenue generated, and other sections. Key performance indicators (KPIs) were selected to cover total trains, delays, and revenue over the four-month period, along with statistics calculated from other attributes contributing to these KPIs. Overall, 19,871 (96%) of trips were completed, with 90% on time and 5.34% delayed.

The first section covers the overall train trips analysis, focusing on the top six routes by train volume. The Manchester Piccadilly - Liverpool Lime Street route has the highest number of trains, totaling 2,565. Additionally, the top six busiest departure stations are highlighted, with Manchester Piccadilly leading with 3,521 departures. A visualization also shows total trips by hour for each weekday, indicating that the peak travel times are at 6, 7, and 8 AM, and 4, 5, and 6 PM.

The second section presents the total revenue analysis, providing an overview of the top six routes by highest revenue, with London King's Cross-York generating the most revenue. It also highlights the top departure stations by revenue and shows revenue generated hourly across weekdays, indicating that peak revenue times align with the peak travel times mentioned earlier.

The third section presents an analysis of trip delays, providing insights into the reasons for canceled and delayed trains. It shows the total number of delayed and canceled trains over four months and breaks this down by weekdays and hours. During this period, 5.43% of trains were delayed, and 3.98% were canceled. The leading causes of delays were technical issues and signal failures.

The final section offers an overview of total ticket sales, detailing the types and classes of tickets sold, and the most frequently used payment method, which was credit card. It also shows the revenue generated by different ticket classes/types. Tickets booked in advance, primarily online, generated the highest revenue.

Key Insights:

Some of the key findings from the analysis include:

High Trip Completion Rate:

· Over the four-month period, 96% of trips (19,871) were completed, with 90% on time and 5.34% delayed.

Top Routes and Stations:

· The Manchester Piccadilly - Liverpool Lime Street route had the highest number of trains, totaling 2,565.

· Manchester Piccadilly was the busiest departure station, with 3,521 departures.

Peak Travel Times:

· Peak travel times were identified at 6, 7, and 8 AM, and 4, 5, and 6 PM on weekdays.

Revenue Insights:

· The London King's Cross - York route generated the highest revenue.

· Peak revenue times aligned with the peak travel times, indicating a strong correlation between high passenger volumes and revenue generation.

Trip Delays and Cancellations:

· During the four months, 5.43% of trains were delayed, and 3.98% were canceled.

· The main causes of delays were technical issues and signal failures.

· Delays and cancellations were analyzed across weekdays and hours to understand patterns better.

Ticket Sales and Revenue:

· Detailed analysis of ticket types and classes revealed that tickets booked in advance, primarily online, generated the highest revenue.

· Credit cards were the most frequently used payment method.

Design/Theme:

I've created a custom background using Figma, carefully selecting colors from a train image. To ensure a visually pleasing and cohesive dashboard, I fine-tuned diverging color palettes using coolors.io, achieving a perfect match for each theme. This selected color palette is consistently applied across all visualizations.