Maven Rails Exploratory Dashboard

Task: Create an exploratory dashboard that evaluates performance on a fictitious railway service provider.

Goals:

1. Identifying the most popular routes.
2. Determining peak travel times.
3. Analyzing revenue from different ticket types & classes.
4. Diagnosing on-time performance and contributing factors.

Data:

1. The data that was provided was fairly clean but not complete and needed some minor tweaks.
2. The only thing that is missing from the data was the coordinates of the different railway stations. Therefore, I went to search for it and got this website: https://github.com/ellcom/UK-Train-Station-Locations/blob/master/uk-train-stations.csv
3. I adjusted the reason for delay "Weather Conditions" to just "Weather"
4. I created a route column (departure location - arrival location), and multiple time tables to calculate time related metrics.
5. I scraped the web using OpenStreetMap API to get the route coordinates (best route) of the different routes for the route map visual.

Plan: My plan was to separate each of the requirements into 3 separate pages, namely, Trips page, Revenue page and Time page which shows their respective KPI's and visuals to provide insights.

Page 1: Trips On the left side panel, the visuals show the popular departure and popular arrival locations for passengers and the most popular route (top and bottom included). The right panel shows a map that shows the (best) route of the trips that were taken, and the metrics associated with the trips.

Page 2: Revenue The top visuals are the main metrics that relates to the revenue. Below that is the line chart that compares the number of transactions and the total revenue for the departure and arrival locations and also the routes. The bottom visual represents the breakdown of the revenue in regards to ticket information while also including information about the trips.

Page 3 : Time The top visuals are the main metrics that shows the trips in relation to time. The middle visuals are matrices that represent the number of monthly trips by time and also the number of trips by route broken down by time while also including information about the trips. Finally, the last visual shows the breakdown of delays/cancellations/refunds with additional information from the trips.

Findings:

Trips Page

1. There are 19, 871 train trips with a total of 31, 653 passengers overall. This breaks down to 18, 019 (90.7 %) of On Time Journeys, 1,062 (5.3 %) of Delayed Journeys and 790 (4.0%) of Cancelled Journeys. The trips overall are averaging to 641 daily, 2,839 weekly and 4,968 monthly.
2. The most popular departure and arrival locations are in Manchester, London, Liverpool and Birmingham which are some of the biggest city in the UK.
3. The most popular routes are also from the biggest cities such as Manchester, London, Liverpool and Birmingham going to smaller cities such as York and Reading with at least 2,000 trips overall.
4. More popular routes tend to have a lesser distance traveled compared to the least popular routes.

Revenue Page

1. A total of £742,000 ( - £39,000 for refunds) for the revenue with an average ticket price of £23. Additionally, 1118(4%) refunds are requested by customers.

2. The popular departure locations with the highest revenue are in some of the biggest cities such as London, Manchester and Birmingham with London Kings Cross as the most profitable location. Although London Kings Cross has the highest revenue of £200,000, it has a total of 4,200 transactions which is lesser compared to Manchester which has a total of 5700 transactions but only £75,000 in revenue. This suggests that some locations offer a lesser ticket price and higher for other locations. Furthermore, the popular arrival locations are also in major cities with York as the top profitable arrival location.

3. The most popular route is from London Kings Cross - York with a revenue of £183,000 and 4,000 transactions. The same behavior also happens where some routes have a higher transaction count but with lower revenue compared to other routes.

4. Most customers are not owners of a rail card and people buy their tickets almost equally online and on station. Standard Ticket Class are more preferred with an Advance ticket type as it offers a 50% discount indicating a cost effective purchase.

Time Page

1. There is an average delay time of 42 minutes for delayed trips. Peak departure hours ranges from 6 AM to 8 AM and peak arrival hours ranges from 4PM to 7PM with an average of 30 minutes travel time overall. This suggests that customers are commuting before working hours and right after working hours and only a few trips are happening outside of the commuting hours. Additionally, there is a 94.4 % On time - Delayed Percentage(excluding cancelled trips) which is an excellent ratio for all successful trips.

2. Delayed trips starts from 8AM and arrives at 12 PM with an average travel time of 137 minutes overall and cancelled trips peaks at 6 AM and 6 PM.

3. Most trips are distributed during the early morning and late afternoon and only a few trips on night time.

4. Signal Failure is the top reason for cancels with 424 trips, 217 delays, 207 cancels and 215 refunds. Followed by Technical Issue with the highest refund percentage of 82% along with 373 trips, 268 delays, 105 cancels and 305 refunds. High percentage of refund requests also contribute from Traffic and Staffing setbacks. Lastly, Traffic has the highest cancel percentage with a total of 152 trips and 100 cancels.