Using Alteryx to help get employees back to work post Covid-19

[Chris_Hutchinson]

Author:

Chris Hutchinson

Role:

Head of Product (Analytics)

Company:

TravelTime

Overview of Use Case

Covid-19 lockdown restrictions have led to an unprecedented level of 'working from home' all around the world. As restrictions begin to be lifted over the months ahead, both employers and employees will face new challenges related to commuting safely and risk minimization. 

In this use case we outline some of the ways in which Alteryx can be used to help answer these questions, and support safe 'back to work' strategies, whatever they may look like. To do this we will use the imaginary example of Acme Corp, that employs around 1,000 employees across Greater London, and operates 5 London offices.

The workflows used here include a range of macros from TravelTime which can be found in the Alteryx Gallery here.

Describe the business challenge or problem you needed to solve

The sorts of questions that will quickly become critical for all businesses to be able to answer include things like:

• What commute options are available for our employees if they wish to avoid using public transport?
• How can we efficiently allocate our employees between our multiple offices?
• How can we help employees avoid particularly busy stations or train lines?
• Now that we have more office real estate than we need, how should we best consolidate the number of sites that we operate to minimise the impact of employees?  

These sorts of analysis and optimisation problems will span across industries -  HR, Operations, and Facilities teams will likely be asked similar questions regardless of what vertical the organisation operates in.

For organisations in certain industries such as real estate advisory and consultancy, however, the ability to answer these sorts of questions will be vital to providing value to their clients as they attempt to navigate the 'new normal'.

Describe your working solution

The first question we want to answer is what are the options for our employees to commute to these various offices, particularly for different transport modes? There are two main reasons for this:

1. Before Covid-19, the majority of our employees commuted by public transport, but we would like to promote alternative commute options wherever possible in order to reduce the risk of exposure
2. For those employees who still want to commute by public transport, we’d like to encourage them to use the office with the shortest public transport commute time, for a similar risk reduction rationale

Our data is in two simple Excel spreadsheets, one containing the employee postcodes, and one the offices:

Here is the first workflow we need to run in Alteryx:

The steps in the workflow are as follows:

1. Use the Geocoder tool to convert the employee and office postcodes into lat-long coordinates
2. Use the Append Fields tool to combine the two data sets
3. Use the Travel Time Matrix tool to calculate the journey times. In the configuration of the tool we select all the methods of transport we want the journey times for – Cycling, Driving, Public Transport, and Walking. The tool will calculate the time for each employee to each office by each transport type – almost 20,000 individual routes
4. Use the Filter tool to select just the travel times. The Travel Time matrix tool can also be used to return distances and public transport fares
5. Use the Filter tool a second time to separate out the results for each Office ID
6. Use the Cross Tab tool to create the matrices, with Employee ID as the rows, Transport Method as the columns, and Travel Time as the metric

We now have a separate matrix for each office, with the travel time for each employee via the four different transport options. The output below shows the first few entries of the matrix for the Southwark office.

We can now use this data to spot sensible opportunities for employees to commute to one of the five different offices by a method of transport other than public transport. For example at the Southwark office, Employee 107 faces a 42 minute commute by public transport, but only a 25 minute cycle.

To assess the second point of allocating those employees who still wish to commute by public transport to the nearest office, we run an almost identical workflow as before:

The only change here is in the final Filter and Cross Tab tools. This time we separate out the results by transport type instead of office, and set the columns in the matrix as the Office ID.

We now have a separate matrix for each method of transport, and examining the public transport matrix allows us to establish which is the best office for each employee in terms of shortest commute time:

For example with Employee 132, if we wanted to encourage public transport commute times of no more than 30 minutes, they could use either the Paddington office (24 minutes) or even better the Shepherd’s Bush office (17 minutes).

Another aspect of our plan for employees returning to the office safely is to try reduce the need for particularly high risk individuals to travel through the busiest underground stations. To do this we add a third column to our employee data called ‘Risk Level’ and then run the following workflow:

The steps in the workflow are as follows:

1. Use the Filter tool to select only the 15 individuals that have been identified as High Risk
2. Use the Geocoder tool to convert the employee and office postcodes into lat-long coordinates
3. Use the Append Fields tool to combine the two data sets
4. Use the Routes tool configured with the Transport Method as Public Transport, and the Output Type as Detailed. This Output Type will return a separate line for each leg of the routes, with the turn-by-turn directions for the leg

5. Use the Filter tool with a custom filter to select any route legs that involve one of the five busiest underground stations that we want to highlight, as shown below

The first output we can use is the Browse tool directly from the Routes tool:

Using this visual output we can investigate which routes run through the stations we are looking to avoid. We can do even better than this though, by using the output from the second Filter tool:

This is a list of all of the combinations of high risk employees and offices which involve a route passing through one of the five busiest stations.

We can use this output to ensure that if they need to travel using public transport, we can allocate high risk employees to offices which at the very least avoid public transport routes that involve changing at busy stations.

A similar approach could be used to avoid whole tube lines by filtering the ‘Instructions’ field in the Routes tool output for the name(s) of the lines to be avoided.

Having assessed the travel options for our employees returning to one of the five offices, we decide that it would be sensible to reduce our real estate portfolio by closing one of the offices.

Going forwards, we want to encourage walking and cycling to work as much as possible, so we want to take accessibility by these modes into account when deciding which office to shut.

Below is the workflow we run for this analysis:

The steps in the workflow are as follows:

1. Use the Geocoder tool to convert the employee and office postcodes into lat-long coordinates
2. Use the Append Fields tool to combine the two data sets
3. Use the Reachable Points Filter tool configured with both Walking and Cycling selected, and an Isochrone Time of 30 minutes. This will return the journey from each employee address to each office as Reachable or Unreachable based on a 30 minute cut-off, for both cycling and walking
4. Use the Summarize tool to group the results by Reachable?, Office ID, and Transport Method, as well as to count by Transport method
5. Use the Cross Tab tool to create a matrix, with Office ID and Transport Method both as rows, Reachable? as the columns, and Count as the metric

The resulting matrix allows us to compare how many employees can walk or cycle to each office within 30 minutes. The True column is the count of how many employees can make the journey in under 30 minutes, while the False column is the count of those who can’t.

Using this analysis we can see which office provides the worst access for our current employees based on walking and cycling.

Canary Wharf turns out to be the least accessible of the offices for both options:

• Only 233 of our 997 employees can reach the office within a 30 minute cycle
• Only 11 of our 997 employees can reach the office within a 30 minute walk

Based on where our current employees live, Canary Wharf is clearly the best option for an office to be closed, in terms of accessibility by walking and cycling. However, taking a more long-term perspective, we also need to consider two additional factors:

1. Public transport is likely to return as the primary commute of choice in the years to come
2. We are looking to expand our workforce, and we are particularly keen to attract new talent from outside of London

To assess the impact of potentially closing the Canary Wharf office given these two additional considerations, we run the following workflow:

The steps in the workflow are as follows:

1. Use the Geocoder tool to convert the office postcodes into lat-long coordinates
2. Use the Isochrones tool to calculate the reachable area (‘isochrone’ or ‘trade area’) around each office by public transport, for a 90 minute maximum journey time, arriving at the office at 9am on a weekday morning

The results of this workflow are below:

The highlighted isochrone here corresponds to the reachable area for the Canary Wharf office. Comparing this to the reachable areas covered by the other offices we can see that by closing the Canary Wharf office we will lose some accessibility to the East of London.

However, the majority of the area served by the Canary Wharf office is also served by one of the four other offices, so we can be confident that closing it will not have too detrimental an impact on our ability to attract talent from outside of London, who want to commute by public transport.

To explore these isochrones in more detail, they can be easily exported into Tableau.

Describe the benefits you have achieved

The example use case outlined here demonstrate how some of the key questions involved in a back to work strategy can be answered simply and efficiently using Alteryx.

Without running this sort of location-based travel time analysis, Acme Corp would likely make less effective decisions, with a detrimental impact not only of the organisation as a whole, but on the individual employees themselves.

Research shows that relocation makes up 10% of the reasons for people leaving their jobs. Given that average staff turnover is estimated at around 15%, and that the cost of turnover per employee is roughly £30,000, a poor back to work strategy could see Acme losing 15 employees as a result (1,000 x 15% x 10%) at a cost of around £450,000 (15 x £30,000).

Add onto this the hidden costs of management time/distraction, on-boarding and training time, as well as wider staff morale, the savings generated through running this analysis could be extremely significant.

And even if Acme manages to retain most of its staff, longer commute times will still more than likely have a negative impact, both on the firm and the employees. Further research finds that employees commuting less than 30 minutes to work gain an additional 7 days' worth of productive time each year when compared to those with an hour or longer commute. And longer commutes have also been linked to higher rates of depression, financial concerns, and work-related stress.

With the right data and tools, businesses like Acme can simply make better, more informed decisions.

Related Resources

To see more examples of how travel time analysis can be used as part of an office location strategy, please see the case study and webinar below, produced by The Information Lab:

Case Study

Webinar

[sharsha315]

Good case study and nice presentation.

[jacob_kahn]

Awesomeness!