Project Rundown

Objective:

To create an interactive and exploratory dashboard that will:

1. Identify the most popular train routes

2. Determine peak travel times

3. Analyze revenue from different ticket types & ticket classes

4. Diagnose on-time performance and contributing factors

Process:

Reviewing the Data:

• Familiarized with the provided data consisting of two CSV tables: one with working data and another with variable information.
• Imported the working data into Power BI for initial data cleaning, formatting, and manipulation.

Processing Data:

• Formatted currency and date columns.
• Reviewed each column’s variables, consolidating the 'Reason for Delay' column to unify various delay reasons.
• Ensured data consistency and accuracy through cleaning and validation processes.

Dashboard Concept:

• Outlined key metrics and potential visuals, dividing the dashboard into four main sections based on the initial requirements.
• Designed the overall aesthetic and layout to ensure a user-friendly interface.

Data Analysis and Exploration:

• Created a ‘Measures’ table to store DAX calculations for data analysis.
• Utilized various sorting/grouping DAX expressions for more detailed graphics.

Dashboard

Overview Page:

• Displays general ticket information: quantity, purchase methods, monthly ticket totals, and average price.
• Highlights the number of stations covered, with a focus on the top 3 departure and arrival stations, their on-time performance, and revenue.
• KPIs and visuals to provide a summary of other key metrics.

Overview Dashboard Insights:

Online Sales: With 59% of purchases done online, there is a clear trend towards digital transactions. Contactless and debit card usage could be from in station purchases.

Utilization of Discount Railcard: Despite the discount railcard availability, 66% of passengers do not use it. Identifying reasons for low adoption (e.g., awareness, perceived value, eligibility) and implementing targeted marketing or adjustments could boost usage, increasing ticket sales and revenue.

Efficiency Based on Punctuality: Focusing on maintaining or improving punctuality for the top arrival stations (average 82% on-time) and departure stations (average 89% on-time) can boost customer satisfaction, repeat business, and operational efficiency. Addressing factors for underperforming stations can optimize operations and reduce associated costs.

Route Overview:

• Showcases ticket classes, highlights reasons for delay and refunds totals per ticket class.
• Features an interactive route map and a general timeline to visualize passenger patterns over a 24-hour period.
• There are separate dashboards for Arrival and Departure information. These are accessed via the buttons on the main route page.
• If one chooses, there is a Passenger dashboard which displays passenger trip information.

Departures:

• Visualizations exclude canceled trips to provide a clearer picture of actual departures.
• Identifies peak travel times: 6-8am and 4-6pm, influenced by commuters and possible students.
• Breaks down ticketing by station.
• Uses tooltips to display additional information for a more detailed coverage without cluttering the visuals.

Departure Station Insights:

Departure Stations: The distance between Edinburgh and the nearest departure station in Leeds represents a substantial area for possibly increasing the number of stations along this route and has significant profit potential. By enhancing accessibility and service coverage in this corridor, it can effectively capture untapped market demand and optimize revenue generation.

Arrivals:

• Similar layout to Departures but focuses on average delay times and performance issues.
• Examines trip delays, on-time percentages, and cancellations.
• Analyzes station-specific delays to highlight where performance improvements can be made.
• Only arrivals had data on delays.

Arrival Station Insights:

Arrival Stations: Same with Departures, there are substantial areas that are vacant. This significantly increases commuter time by bus or other means to get to/from a station. Costs involved in laying new rail is substantial so thorough research would need to be conducted in those areas to see if rail expansion is even viable.

Delay Factors: Delays in railway operations can be categorized into two primary factors: weather-related delays, which are unpredictable despite forecasts, and operational delays typically caused by technical malfunctions or staffing issues. Understanding these delays requires a deeper analysis to determine their root causes, severity levels, and the condition of existing equipment. This data-driven approach will enable targeted improvements to optimize efficiency and enhance customer satisfaction by minimizing disruptions.

Overall Route Recommendations:

Station Expansion Strategy: Conduct feasibility studies to identify optimal locations for new departure and arrival stations between Edinburgh and Leeds. Studies to include but not limited to, resident surveys, land surveys, and market demand research. Focus on areas with significant population density but lacking sufficient station access. This will capture untapped market demand. Evaluate development costs against potential revenue increases to ensure budget and long-term strategic objectives.

Peak Travel Times:

• Uses DAX calculations to identify peak travel hours and group them into categories.
• Includes a heatmap to visualize passenger patterns and trends over different hours of the day.
• Addressed issues with sorting by creating a separate table for 'DayOfWeek' and DAX to ensure correct hour of the day order.

Travel Time Insights:

Peak Travel Times: Standard work should have the highest commuter numbers as people are either on their way to or from work. These hours are from 9am-5pm. Next are the 'Non-Work' hours from 6pm-12am. This time frame is peak for those doing errands after work, going to events or something similar.

Time Between Stations: It's to be expected that the Edinbburgh Station has the highest average travel time. Not only is Edinburgh the northern most station but there is a sizeable distance between it and the nearest station.

Passenger Volumne: The heatmap clearly displays the peek travel times for those that commute to and from either work or school.

Travel Time Recommendations:

Passenger Volumn: Ensure that trains have sufficient capacity during peak hours to handle the high passenger volume. This may involve larger trains or adding additional cars to existing services. Put in place measures to manage passenger flow at major stations during peak hours, such as additional staffing, clear signage, and crowd control measures to help with boarding and exiting. These recommendations will help reduce congestion, minimize travel times, and ensure a more comfortable and efficient journey for all commuters.

Revenue

• Summarizes monthly revenue amounts. Incorporated custom tooltip that breaks down the months into days of the week for that month.
• Highlights the difference between gross and net revenue amounts.
• Provides a detailed revenue breakdown by ticket type and class to identify key revenue drivers.

Revenue Insights:

Refunds: Approximately 4% of trip transactions resulted in a refund. Weather related delays resulted in the lowest refund rate at 9.69% but accounted for the majority of delays. Technical and staffing issues attributed to the highest refund rates with Technical issues accounting for over 50% of refunds.

Revenue December is shown but the month only contained a weeks worth of data from 2023 which had 34 tickets sold for that time frame. For January, the highest grossing day of the week was Wednesday at $39K. February highest grossing day was Friday at $25k. March's highest grossing surprisingly was Sunday at $29k and April's highest grossing day was Tuesday at $30k.

From the 'Revenue Breakdown', We can see that passengers prefer to:

1. Purchase tickets online
2. Purchase Standard Class tickets
3. Not use the discount railcard
4. Purchase tickets in advance.

Revenue Recommendations:

Improve Technical and Staffing Reliability: Implement a rigorous maintenance schedule to minimize technical issues. Conduct regular training sessions and improve the staffing process to ensure that adequate staff is available, especially during peak times. Invest in better monitoring and early detection systems to quickly address technical problems before they impact service.

Refund/Delays: Enhance the communication system to inform passengers promptly about weather-related delays and offer alternative options to reduce the impact. Consider offering flexible rescheduling options rather than refunds to retain revenue.

Ticket Recommendations:

1. Improve the user experience on the online ticketing platform to make it more intuitive and faster.
2. Provide a seamless mobile experience to cater to on-the-go customers.
3. Offer exclusive online discounts or loyalty points to encourage more online purchases.
4. Offer early bird discounts for passengers who book tickets well in advance.
5. Provide additional benefits for advance purchasers, such as seat selection or free Wi-Fi.
6. Railcard: Reevaluate the discount railcard program to understand why it is underutilized. Simplify the process of acquiring and using the discount railcard. Promote the benefits of the discount railcard through targeted marketing campaigns.