About this project

Business Task

As a BI Developer for National Rail, a company that provides business services to passenger train operators in England, Scotland, and Wales, I've been tasked by my manager to create an exploratory dashboard. They want to discover insights on their Revenue, Routes and Punctuality.

Business Questions

1. Analyze revenue from different ticket types & classes

2. Identify the most popular routes & Determine peak travel times

3. Diagnose on-time performance and contributing factors

About the Data

The data contains mock train ticket sales for National Rail in the UK, from January to April 2024, including details on the type of ticket, the date & time for each journey, the departure & arrival stations, the ticket price, and more.

UK Train Rides

Mock train ticket data for National Rail in the UK, from Jan to Apr 2024. The data contains 1 table consisting of 18 fields and 31653 records in csv format.

Tools Used

Excel (data cleaning & manipulation)

Tableau (exploration, manipulation & visualization)

Data Cleaning

Excel Edits:

Changed time to HH:MM AM/PM (on all the time related columns)

Changed price to currency removed .00 since there were no cents

Checked for duplicates – none were found

Checked for blanks – none were found

Use IF formula to add a refund amount column

Created a Delays (min) column by subtracting the Actual Arrival Time from the Arrival Time columns and multiply by 1440, then formatted the column to be a number (min). I filtered the data to only include rows that had a delayed journey status

Created a Trip Length (min) column by subtracting the Arrival Time from the Departure Time columns and multiply by 1440, then formatted the column to be a number (min). I filtered the data to not include rows that had a Cancelled journey status

Created an Actual Trip Length (min) column by subtracting the Actual Arrival Time from the Departure Time columns and multiply by 1440, then formatted the column to be a number (min). I filtered the data to not include rows that had a Cancelled journey status

Tableau:

Grouped "Weather" and "Weather Conditions" since they are weather related

Grouped "Staffing" and "Staff Shortage" since they are staff related

Created numerous calculated fields to single out metrics and calculate percentages

Insights

● Standard Class Advance tickets make up the majority of tickets purchased, and also have the greatest refund requests.

● 36% of passengers purchase their tickets online using a credit card, but only 24% of those requested a refund.

● £991K of revenue vs £703K could be realized if there were no discounts or refunds.

● Most passengers travel between 6AM to 6PM Wednesday through Sunday on the Manchester Piccadilly to Liverpool Lime Street route.

● Delayed trips were late by an average of 42 minutes mainly due to weather and Cancelled trips were due to signal failure.

● Staffing accounted for 19% of delays and cancellations, through careful planning it could be prevented.

● Although Manchester Piccadilly is the busiest departure station, Liverpool Lime Street had the most delays (mostly due to weather) and London Paddington had the most cancellations (mostly due to staffing).

● Liverpool Lime Street also had the most combined delays and cancellations (mostly due to weather). This is perhaps because this station is on the coast.