Project Scenario

The scope or scenario for this project was as follows:

"You've been asked by your manager to create an exploratory dashboard that helps them:

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

Data

We were provided with 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.

Approach

My approach was dictated by the fact I had limited time to produce a report, so I needed to focus on base scope requirements and jettison the "nice to have" items that I may have included if I had more time (notes below).

As this was an exploratory report, I wanted to produce a clear report that focused on each type of analysis that was required and allowed an end user to go from a high-level analysis, down to a more granular level.

There should be some guidance in terms of looking at appropriate and relevant data and metrics, but I wanted to allow the end-user to have a reasonable field of exploration.

I also thought it a good idea to divide the report into four pages to align with the four requirements of the scope. This would provide good segregation and focus for the end user, and not overwhelm or confuse with too much information on one page.

Data Adjustments

There was a single table of journey data, and it was actually relatively clean, so there wasn't much in the way of cleaning or transformation to perform. The main aspects I looked at were:

• Categorisation of Stations to Regions
• Categorisation of Stations to Cities
• Joining Departure and Arriving Stations to form a "Journey Path"
• Categorisation of times to "AM Peak", "PM Peak" and "Off-Peak"

These categorisations allowed an assessment and drill down from high levels to lower levels as I mentioned above.

There was also some other minor adjustments and calculations like:

• Grouping delay reasons (e.g. "Weather" and "Weather Conditions" become "Weather")
• Calculating delay durations in minutes using scheduled and actual arrival times

Popular Routes

This was quite a simple page. The premise is to allow the end user to select a "Top N" (allowed to select between 3 and 10) and see the results looking at journey numbers from a high level to a lower level.

The top visuals provide a high-level overview looking at the Region trains depart from.

The visuals at the bottom provide that next level down, looking at individual station routes, and also city routes. For example, London has many stations, so this allows all London to Manchester journeys to be grouped.

Each region has it's own colour assigned in the top visual, which allows it to act as a legend for the bottom visuals.

You might wonder why there are two sets of visuals, one on the left and one on the right. Well, I am providing two scenarios. On the left was an analysis of all journeys, and on the right was an analysis of journeys that have the filters/slicers applied. That means on the right you could drill down to Midlands journeys at the weekend, and compare those numbers against all the journeys on the right.

Additional Items I could add with more time

• Parameter switch to switch to % of journeys
• Hidden panel for slicers and navigation

Peak Travel

Again, an attempt was made to keep the page relatively simple, with limited visuals, but maximum impact and information related to busy times.

On the left, I was going for a more "high-level" analysis of peak timing by looking at 3 hour blocks (e.g. 6am to 9am) to look at general peak periods. I made a rule that if a 3 hour block had more than 15% of journeys starting, it could be considered "peak".

So, I calculated % values based on All journeys and also the subset as selected by the slicers, and applied conditional formatting to highlight peak periods.

The Heat Maps were then used at a lower-level to allow analysis at a day and hour level. Colour coded heatmaps were used to give a general indication, rather than hard numbers, with the column chart at the top providing those aggregated numbers at a glance, if required.

I also allowed a further drill down at a city route and station route level, should the end-user need. This is also possible to action through the slicer panel.

Additional Items I could add with more time

• A more customised visual related to visualising the 3 hour time blocks (although, I like the simplicity here)
• Parameter switch to switch to peak arrival times
• Hidden panel for slicers and navigation

On-Time Performance

This page was more focused on delay rather than "On-Time", but the card and column chart on the left provides that high level overview of punctuality, and the end user can use the slicer panel to explore regions, cities, date ranges, etc as needed.

I decided to look at three aspects or impacts of delay, namely:

• number of delayed journeys
• average journey delay times
• value of refunds requested as a result of delays

And, following a similar path, I provided a hierarchy of analysis from high to low, providing totals/averages on the left, then a breakdown by category, and then a further breakdown over time.

I used colour here to align with the delay reason, and provided custom sorting on the visuals, thus avoiding the need to provide y-axis values on the visuals on the right hand side. This sorting was also applied against all three aspects of delay analysis so you can more effectively compare both horizontally and vertically.

As a note, the visual on the far right is the only non-native visual in this report, and was created using deneb.

I did experiment with other native visuals (below), but I liked the visuals I have produced, as it provided that segregation by line.

Additional Items I could add with more time

• Analysis of cancelled journeys / parameter or bookmark switch
• Additional detail on trends related to "on-time" journeys
• Hidden panel for slicers and navigation

Revenue Analysis

The review of revenue and ticket types was maybe the simplest and most straight forward.

Following a tried and tested method, I split my analysis into two, looking at 1st class and standard class journeys and performed a similar analysis on both.

On the left, I looked at high level values such as revenue, journeys, refunds and average ticket prices as well as relative % values. This was quite basic, although there is some conditional formatting to highlight if refunds exceeded 7.5% of total revenue. This was an arbitrary figure, but is the type of information you might get from interrogating your stakeholders.

I then added the next levels of analysis, breaking things down by ticket type, and then by departing city.

Additional Items I could add with more time

• Parameter switch to switch to % of revenue, refunds
• Drill-through to tables for revenue from different journeys
• Hidden panel for slicers and navigation

Cover Page

In lieu of a navigation panel, I opted for a cover page with page navigation, and then created a simple home button and page cycle buttons on each page.

This allowed me the bliss of not dealing with any bookmarks on this report, which is always a bonus.

Also, the picture on the cover is actually from my native Northern Ireland, which is not part of National Rail...