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

I developed an exploratory Power BI dashboard for my manager that helps to understand the operating performance and ticket sales.

The Dataset

The dataset contained 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.

Business Use Case and Key Assumptions

National Rail is looking to enhance its operational efficiency, customer satisfaction and profitability through data-driven insights.

In my opinion, the first step of building the dashboard is to understand the user-specific requirements and expectations from the analysis, which helps to answer the questions they have.

Since the dashboard is for the manager, I am making some key assumptions to design the dashboard with the given data.

• The dashboard should provide a high-level view and further details about the daily operations, travel patterns, customer satisfaction and profitability. By leveraging these insights, they can make informed decisions to optimize their services, improve scheduling, and increase profitability.
• The main goal for the manager by viewing the dashboard would be to get answer for the key questions:
  • How is the daily performance of the network?
  • What is the frequency of delays, average delay duration and causes of delays?
  • Are there any routes that have a high passenger volume but with unsatisfactory service? Are there any improvements needed there?
  • What are the peak travel times: overall, and individual routes?
  • How do peak travel times correlate with on-time performance?
  • Are we focusing on providing the best service on the routes that generate the most revenue?
  • What are the Ticket Classes and Ticket Types which are generating the most revenue? How can we make the ticket packages more profitable and user-friendly?
• The main KPIs would be related to route popularity, peak travel times, revenue analysis, and on-time performance.
• The manager may want to dive deeper into the reasons of the delays, which can be reduced by improving the technical quality of the network.
• In terms of Sales, the customer satisfaction can also be evaluated by analyzing the number of Refund Requests.
• Since the dataset is only for the first 4 months of 2024, we cannot make a lot of comparisons such as peak travel periods or even peak months from last year. Hence, we are more focused on improving the performance in the future instead of comparing current performance with the past.

Dashboard Design

Once the dashboard requirements and expectations are set, the dataset has been analyzed and transformed to achieve all the KPIs and objectives, the next step is to design the dashboard interface.

Some of the practices I follow when developing a dashboard:

• I try to keep the color theme, styling and terminologies same as the standard in the company. We can get this idea from the company website or the systems being used internally.
• I keep the dashboard interactive and simple: I follow the principle “Less is more.”
• I give emphasis on the type of user who will be using the dashboard, i.e. their business and technical understanding. I create a page with summary and objectives of all the dashboards. This helps the users to use the dashboard efficiently and interpret information. It also helps the new users to self-educate.

Dashboards

Overview Dashboard

This is the main page of the report which gives a high-level view to the manager about the operations and sales performance of the rail network. There are filters at the top, which when selected, allow a deep dive into the data. Also, selection can be made within visuals to highlight critical information.

This report page is a starting point for achieving our four objectives:

• Identify the most popular routes
• Determine peak travel times
• Analyze revenue from different ticket types & classes
• Diagnose on-time performance and contributing factors

The key KPIs are shown at the top of the page which include the number of journeys, revenue generated (net revenue after refunds), refund requests and on-time journeys. These allow the manager to understand if the operations are satisfactory and profitable.

The middle row is focused on the quality of operations, reasons for inferior performance, and passenger behavior. This helps the manager to work on improving the efficiency of network operations and setting pricing strategies.

The last row is focused on sales which gives an overview of the types of most profitable tickets, monthly revenue, and the top routes having highest number of journeys and revenue. This helps the manager to ensure best quality of service at the most valuable routes.

For in-depth analysis, the user can navigate to other pages like Operating Performance, Sales and Revenue, Passenger Pattern and Journey Details. The pages can be accessed through the icons in the ribbon on the left. Additionally, there is a help button which navigates to the Help Page. It provides information to the users about the details and usage of the visuals.

Operating Performance Dashboard

This page is focused on the rail operations, and allows the manager to see the operations of the rail network and reasons behind degraded service. It allows them to focus on routes and factors which need most attention, enabling them to implement targeted improvements and enhance overall service reliability. A monthly and daily trend of delayed and canceled journeys also shows the quality of service over time. The Travel Pattern helps to determine peak travel times, where they can optimize scheduling, staffing, and pricing strategies to match demand.

The table gives a deeper insight to the most affected train routes which may need improvement and planning to upgrade the service quality and revenue generated. The planning may include prioritizing resource allocation and marketing efforts.

Sales and Revenue Dashboard

Besides improving the service quality, the manager would also want to increase net revenue. This page is focused on understanding the distribution of revenue which will be helpful to tailor sales strategies, promotions, and service offerings accordingly. For instance, some routes may be more profitable if First Class services are increased, new ticket types could be introduced or the existing ones could be made more flexible, more automated ticket machines can be installed at some stations, etc.

It also gives insight about the refund requests and refund amount, which in other words is the lost business. Revenue can be increased and refunds can be decreased by improving service quality.

The Ticket Sales Trend visual shows the trend but also the duration between the Purchase Date and the Travel Date, depicting the user behavior and preferences in buying ticket along with seasonality.

The table visuals give a deeper insight into each route with the revenue generated according to each Ticket Class and Ticket Type. Also, the Refund Amount and Refund Percentage is shown to analyze the amount of revenue lost.

Passenger Pattern Dashboard

This page gives in-depth analysis about the passenger pattern and may be helpful to understand the customer behavior, improve performance, and future planning.

It shows the passenger behavior in terms of Types of Tickets bought, Class of Service and the usage of Railcard. Most importantly, it shows travel pattern during the day and week. These factors may help the manager to understand the preferences, business opportunities and marketing strategies, thereby helping in route planning and resource allocation.

An important factor here could be the refund requests for travel delays and cancellations, which show the percentage of passengers who request refunds in case of any unusual occurrence; not all passengers request a refund in case the train is delayed or canceled. Keeping a check on refund requests may also be helpful in setting the regulations for ticket refunds.

Journey Details Dashboard

The table provides all the necessary information in a summarized form for every route which can be treated as a supplementary information with the dashboards.