National Rail
About this project
Two interactive dashboards were created.
1) National Rail: Sales and Revenue Performance Dashboard
Objectives
To identify routes generating most sales and revenue.
To identify peak sales hours.
To identify monthly revenue and growth of ticket sales.
To identify revenue generated by payment method.
To identify what types of tickets are purchased by type of customer.
Measures
Potential money refunded: This measure calculates the total potential refund amount, summing up the prices of tickets where a refund has been requested.
% of Net Revenue by Railcard Holders: Calculated as the ratio of net revenue from total gross revenue.
Net Revenue: This measure calculates the net revenue by subtracting the total potential refunds from the total ticket sales.
Average Revenue per Ticket: This measure calculates the average revenue earned per ticket after accounting for potential refunds. It divides the total net revenue by total tickets sold.
Month over Month Ticket Sales Growth: This measure tracks the percentage change in the number of tickets sold from one month to the next.
Insights found
In this dashboard, we've focused on the Manchester Piccadilly to Liverpool Lime Street route as a case study to illustrate how to extract and interpret key data points and trends.
Route Manchester Piccadilly to Liverpool Lime Street had the most tickets sales (4626), generating 17K gross revenue and only making a potential revenue loss of 1.9%. January made the most revenue, whereas February made the least. Most tickets were bought in the morning (40%) between 01:00am-11:00am. Most tickets were purchased at 7am (442 tickets), followed by 17:00pm (421) and 19:00pm (413). None railway card holders bought 2581 tickets compared to 2045 tickets for railcard holders. Most travelers purchased advanced tickets, followed by peak tickets and anytime tickets, with the exception of disabled railcard holders who purchased more off-peak tickets compared to advanced tickets. For purchase type, online generated 1,07K more profit than station. Travelers used mostly contactless payments when making online purchases, and credit cards were mostly used when purchasing tickets at the station. Debit card was the least used payment method for both purchasing types.
- National Rail: Train Service Performance Dashboard
Objectives
To identify the number of reported issues, refund requests, and potential refund amounts due to delays and cancellations.
To identify customer refund requests to determine the proportion of tickets refunded versus not refunded.
To categorize train delays by the duration of the delay to better understand their frequency and impact.
To analyze train routes to determine the number of travelers and assess route performance based on the number of reported issues, refund requests, and potential refund amounts.
To identify refund requests between railcard holders and non-railcard holders who requested the most refunds, and identify which group of railcard holders request the most refunds.
To identify potential refund amounts on a monthly basis.
To assess refund rates performance by comparing them to 10% of total travelers.
To track financial performance by monitoring revenue and refund metrics.
Measures
Refund request: This measure calculates the total number of tickets for which a refund has been requested.
Potential money refunded: This measure calculates the total potential amount that could be refunded, summing up the prices of tickets where a refund has been requested.
Train delay categories: Categorize train delays into five duration-based groups: minor (1-15 minutes), moderate (16-60 minutes), severe (61-180 minutes), cancellations and on time to assess service reliability.
Net Revenue: This measure calculates the net revenue by subtracting the total potential refunds from the total ticket sales.
Refund rate performance goal: Calculate 10% of all ticket sales to establish a performance benchmark for refund rates.
Refund % of gross revenue: Calculate the percentage of gross revenue represented by potential refunds to assess the financial impact of refunds on total earnings.
Refunds: Railcard vs Non-Railcard: Calculates who is asking for the most refunds between railcard and non-railcard holders.
Refunds by Railcard Type: Calculates who is asking for the most refunds from the railcard holder group (adult, senior, disabled).
Insights found
In this dashboard, we've focused on the Liverpool Lime Street to London Euston route as a case study to illustrate how to extract and interpret key data points and trends.
The Liverpool Lime Street to London Euston route has a gross revenue of $113,299, with potential refunds totaling $13,126 (12%). The route has the highest number of reported issues and refund requests compared to other routes, significantly impacting customer satisfaction. 80% (879 out of 1097 travelers) on this route reported issues. Of the issues reported, 78% of travelers requested refunds totaling $8,225. While travelers complained mostly about weather conditions (63%), majority of refunds were requested due to technical issues by railcard holders (especially adults). The refund rate exceeded the 10% refund rate benchmark by 55%, with 171 refunds compared to the goal of 110. The highest potential refund amounts were generated in January at $3,573 and the lowest in February at $2,809. Moderate delays accounted for 59% of all delays.
Dashboard Interactions
Visual 1 (Tickets by Refund Request YES filter)
When "Yes" is selected from the "Tickets by Refund Request" visual, the dashboard filters to display only information related to refund requests. This selection impacts various components of the dashboard:
Issue Analysis: Reports, Refunds & Amounts: The "Count of Issues Reported" is removed because when the "Yes" filter is applied, the number of issues would match the number of refund requests, making this metric redundant.
Revenue and Refund Overview: Since the focus is on customers who have requested refunds, metrics such as Gross Revenue, Refund % of Gross Revenue, and Net Revenue are no longer relevant. These values are thus replaced with zeroes or an appropriate placeholder to indicate not applicable data in this context.
Travel Route Analysis: Issues, Refunds, and Sales: Travelers and Issues reported have been replaced by an appropriate placeholder (-) as the columns in this table have the exact same value as the refund requests column, thus not applicable.
Travel Route Analysis: Issues, Refunds, and Sales - When the "Yes" filter for tickets by refund request is selected, the 'Travelers' and 'Issues Reported' columns display a placeholder (-) because these columns contain values identical to those in the 'Refund Requests' column. This occurs because selecting 'Yes' implies that every counted traveler in this filter had an issue that led to a refund request, making their display redundant.
Screenshot before the Yes filter has been applied
Screenshot after the Yes filter has been applied
Visual 1 (Tickets by Refund Request NO filter)
When "No" is selected from the "Tickets by Refund Request" visual, the dashboard filters to display only information related to refund requests. This selection impacts various components of the dashboard:
Issue Analysis: Reports, Refunds & Amounts: The "Count of Refund Requests" and "Potential Refund Amount" are removed because these metrics are irrelevant when refunds have not been requested. This section will now only display the "Count of Issues Reported," acknowledging that customers may report issues without necessarily requesting refunds.
Revenue and Refund Overview: Metrics related to refunds, such as "Potential Refund Amount" and "Refund % of Gross Revenue," are set to zero because they do not apply when there are no refunds. "Gross Revenue" and "Net Revenue" remain visible and equal, as there are no deductions from refunds affecting these amounts.
Potential Refund Amount by Month: This visual becomes redundant and is therefore blank since it is designed to show refund amounts over time, which are non-existent under the "No" refund scenario.
Refund Rate Performance: When the "No" filter is selected in the "Tickets by Refund Request" visual, the "Refund Rate Performance" KPI visual is completely removed. This occurs because, by focusing on tickets for which no refund request was made, it is not possible to evaluate the refund rate performance.
Refunds: Railcard vs Non-Railcard and Refunds by Railcard Type: These visuals are set to 0 because they track refund requests by railcard holders / non railcard holders and different types of railcard holders, which are irrelevant when the filter excludes refund data.
Screenshot before the No filter has been applied
Screenshot after the No filter has been applied
Visual 2 (Issue Analysis: Reports, Refunds & Amounts)
When any category is selected from the "Issue Analysis: Reports, Refunds & Amounts" visual, the dashboard filters to display only information related to issue analysis. This selection impacts a single component of the dashboard:
- Travel Route Analysis: Issues, Refunds, and Sales: Travelers have been replaced by an appropriate placeholder (-) as this column has the exact same value as the issues reported column thus not applicable in this context of selection. The reason for this is that an issue being reported implies that it was experienced by a traveler, making the travelers' count redundant.
Screenshot before any category has been selected.
Screenshot after any category has been selected.
The same logic has been applied if multiple categories have been selected from "Issue Analysis: Reports, Refunds & Amounts" then travelers column would show a dash.
Visual 3 (Train Delays Categories)
Train Delays Categories - The visual follows the same logic as 'Issue Analysis: Reports, Refunds & Amounts' (Visual 2). The affected categories are Cancelled, Moderate Delays, Minor Delays, and Severe Delays. When any of these four categories are selected from the visual, the dashboard filters to display only information related to the chosen selection. This selection impacts a single component of the dashboard (Travelers column) in the Travel Route Analysis: Issues, Refunds, and Sales visual.
Screenshot after any four delay categories has been selected.
The same logic has been applied if multiple categories have been selected from the four affected categories from "Train Delays Categories" then travelers column would show a dash.
Slicer called Journey statues has the same logic like Visual 2 (Issue Analysis: Reports, Refunds & Amounts) for both selections (Cancelled and Delayed).
Screenshot after 'cancelled' has been selected from slicer.
Screenshot after 'delayed' has been selected from slicer.
I have also implement the same logic if both cancelled and delayed were selected from the journey slicer together, where the travelers column would show a dash.
Visual 4 (Refunds: Railcard / Non-Railcard Holders)
When any element from the dashboard is selected, the values within the textbox adjust accordingly. The only element that does not adjust, is the railcard slicer. and the values are replaced with a placeholder (-) because the purpose of this textbox is to show who has asked for the most refunds between 2 groups: none railcard holders and railcard holders. Since selecting any option from the railcard slicer would indicate that we only looking at railcard holders, that would not allow us to calculate who has asked for the most refunds between the 2 groups as non railcard holders are not included.
Screenshot after any option from railcard slicer has been selected.
Additional Dashboard Interactions for Visual 4 (Refunds: Railcard / Non-Railcard Holders)
If both railcard holders and non-railcard holders did not request refunds, we place a placeholder of 0 in the text box to indicate that no one asked for a refund.
If both railcard holders and non-railcard holders have an equal number of refund requests, we display both railcard users and non-railcard users as a tie.
Additional Dashboard Interactions for Visual 5 (Refunds by Railcard Type)
If all railcard holders did not request refunds, we place a placeholder of 0 in the text box to indicate that no one asked for a refund.
Since no options are selected in the railcard slicer, we are including all railcard holders. If there is a tie between any 2 railcard holders, we will display both railcard holders as a tie if they are both the highest numbers. If there is a tie between 3 railcard holders, we will display all 3 railcard holders as a tie. The same logic applies if all options are selected in the railcard slicer.
Screenshot of a tie between all three railcard holders
Screenshot of a tie between any two railcard holders who have the greatest values.
Screenshot of a tie between any two railcard holders who do not have the highest values. This indicates that even if there is a tie between adult and disabled railcard holders, they are individually lower than senior. Therefore, senior is chosen as the railcard holder who asked for the most refunds.
Since only the adult and disabled options are selected in the railcard slicer, we are only looking at those two groups to determine who has asked for the most refunds. As we can see, the senior group has the most refunds overall, but because senior is not selected in our railcard slicer, it is ignored. The check only filters between adult and disabled, resulting in the adult group asking for the most refunds. Additionally, the percentage amount and the total refund amount adjust accordingly to the selected filter, providing the most accurate result based on the selected options in the railcard slicer. The same logic can be applied if adult and senior, or senior and disabled were the only selections chosen.
Since only the senior and disabled options are selected in the railcard slicer, we are only looking at those two groups to determine who has asked for the most refunds. In this case both groups have a tie in refund request and they both shown as tie. The same logic applies if adult and senior had a tie, or adult and disabled had a tie and they were the only selections chosen.
Since only the senior and disabled options are selected in the railcard slicer, we are only looking at those two groups to determine who has asked for the most refunds. In this case, both groups have not asked for any refund requests, thus shown as 0. The same logic applies if adult and senior asked for no refund request, or adult and disabled asked for no refund request and they were the only selections chosen.
Since only adult is selected in the railcard slicer, we are only looking at the adult group and this only returns the results of refunds for the adult group. The same logic applies if disabled or senior were only selected in the railcard slicer.
