NATIONAL RAIL ANALYSIS : An Exploratory Analysis of Train Ticket Sales for National Rail in the UK from December – April 2024

This Project aim to

• Identify the most popular routes

• Determine peak travel times

• Analyze revenue from different ticket types and classes

• Diagnose on-time performance and contributing factors 

ANALYSIS PROCESS

Data Cleaning: I used Excel in changing the TRANSACTION ID data type to numeric

Data Analysis: I loaded the data to Power BI and use DAX measure in calculating total trips for each route and peak travel time.

INSIGHTS

• 31653 Total Trips
• 65 Routes
• Most popular routes are

1. Birmingham New Street to London St Pancras
2. Liverpool Lime Street to London Euston
3. Liverpool Lime Street to Manchester Piccadilly
4. London Euston to Birmingham New Street
5. London Euston to Manchester Piccadilly
6. London Kings Cross to York
7. London Paddington to Reading
8. London St Pancras to Birmingham New Street
9. London St Pancras to Leicester
10. Manchester Piccadilly to Liverpool Lime Street
11. Manchester Piccadilly to London Euston
12. London Paddington to Oxford

• Peak travel times are 6am and 6pm
• Total Revenue generated is £703,219
• Total Revenue Loss incurred is £38,702
• Advance, Offpeak and Anytime ticket type has total revenue of £293601, £209223, £200395 respectively
• Standard, First Class ticket class has total revenue of £560184, £143035 respectively
• 27481 Ontime trips
• 1880 Cancelled Trips
• 2292 Delayed Trips
• Technical Issue is mostly the cause of delay and cancellation of trips

Recommendations

• Employ more staffs
• Improve Working Conditions
• Regular Checks and Maintenance of trains
• Analyze past weather data to understand how different weather scenarios can be predicted and prepared for.