National Rail Dashboard

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. I have been asked to create an exploratory dashboard. The aim of this dashboard is to:

1. Identify the most popular routes
2. Determine peak travel times
3. Analyze revenue from different ticket types & classes
4. Diagnose on-time performance and contributing factor

Here is what I did to clean up the date:

• Column data types changed
• Changed some column names to better reflect content
• Reason for delay column contains both “Weather” and “Weather Conditions” which I made “Weather”

To transform the data for analysis, here's what I did:

1. Additional columns were created for specific data to facilitate analysis
2. Created a day of week sort table to sort the bars in graph.
3. Created columns

• DepartureTimeOfDay : {6am - 12pm : morning; 12pm-5pm : afternoon; 5pm-8pm : evening; 8pm-12am : night; 12am-6am : Dawn}
• JourneyDayOfWeek : the day of week the journey occurred
• DayOfMonth: the day in the month journey occurred
• TimeDifference - the difference between actual arrival time and scheduled arrival time
• DaysToJourney: days between purchase and travel
• DepartureHour: hour of day train departed

These are the visualization tools used :

Cards

Used to display key metrics and summary statistics.

Bar and Column Chart

Used to compare frequency of categories at a glance.

Clustered Bar Chart

Used where the differences in categories within a measure needed to be shown.

Area Chart

Used to visualize trends over time.

Pie and Doughnut Chart

Used to show proportions of a whole.

Combo Chart

Used to show patterns between 2 different matrices using different scales.

Tables

Used to display relevant data in a structured manner.

Matrix

Used to summarize, aggregate and place data in a hierarchy.

Filter

Used to focus on specific subsets of data during analysis

Here were my findings:

Though most people purchase Standard tickets in advance, the tickets are usually purchased just a few hours ahead of the journey.

Throughout the week, we get about the same patronage volume.

As expected, mornings are peak time for train rides as most people have tightly scheduled mornings to get to work and appointments.

We saw a surprising decrease in the use of online channels from 2023 to 2024.

- 2023

- 2024

Naturally, our cheapest tickets get the most patronage, and subsequently, generates the most revenue.

Generally, a small percentage of the journeys were either delayed or canceled.

Bristol Temple Meads Station shows an extraordinary efficiency with 100% of journeys from there being on time. However, this station has only 16 entries, too small a sample to base long term decision making on.

Whereas Edinburgh Waverley station always has delays averaging 15.27 minutes. This may be explained by the fact that this station very recently entered our data collection pool with only 51 entries so far.

Delays at Edinburgh Waverley Station so far always result in requests for refunds which has cost us 2,093. Delays were sited to be caused by Staffing issues.

Generally, Technical issues and Signal failure are 2 operational issues contributing vastly to delays and cancellations.

To conclude,

• We may find it profitable to put incentives in place for patrons to purchase non refundable tickets well ahead of time (not just a few hours).
• Though a small sample size, a closer look at how Bristol Temple Meads station is able to maintain its operational efficiency so far may prove beneficial to us.
• Edinburgh Waverley’s staffing issues should be tackled promptly to correct the current state of consistent delays.
• A look at why there has been a decrease in online purchase in a more recent year may prove beneficial as this is contrary to what is expected as technology advances.