Alter.Nation
Community news, customer stories, and more!
The Story
During the height of the covid pandemic, Keyrus UK decided to cancel the lease of the UK office because all of our employees were working remotely, and the office was not being utilised. A question came from the Managing Director on where the office should be located when we could meet in person after all restrictions were lifted? The management at Keyrus UK said given we are a data consultancy and have a number of talented people surely, we could answer this question internally.
The first step was to work with the HR department to obtain colleague addresses and their preference for traveling to work. We knew that the Keyrus UK office would be in a coworking space such as WeWork or The Office Group (TOG), so we drew up a list of all the coworking providers.
Alteryx was a perfect tool to solve this challenge and do all the heavy lifting for the analysis. After a list of 7 coworking office companies was provided, Alteryx was able to web scrape those websites to gain all of their locations within London, a total of over 150 locations. An additional benefit of web scraping these sites was being able to retrieve supplement information such as the different office spaces that could be rented and the prices. This information would further help with the decision-making process when looking at the locations.
Webscraping in Alteryx
Once the office locations have been web scraped with their address details, Alteryx was then utilised to call a number of the Public transport and Transit APIs where colleagues used a mix of car and public transport to travel from their home addresses to each of the potential 150 locations. The API calls were each made into an Alteryx Macro to easily integrate into the Alteryx workflow. To keep the results fair and consistent, we set the same departure time for all colleagues during peak rush hour to get as close to a real-life situation as possible. Once I got the results of the travel time for each colleague and location, I then worked out the standard deviation for that location. The rationale for this was to see how dispersed the travel times were. A lower standard deviation meant that the journey to that office was closer to the mean for all colleagues. This meant that we did not find an office location that really suited our colleagues, who lived in or near central London, and reduced their travel time by a few minutes, while causing a huge increase in travel time for our colleagues who lived further out.
A key challenge for this analysis was the scale--for each location, every colleague’s journey needed to be calculated. Due to the 150 locations to examine, this meant thousands of permutations of journeys needed to be analysed. Thankfully using Alteryx, a single workflow was able to build up the thousands of API calls, then successfully call the API and parse the JSON response into a tabular format ready for further analysis and ranking. I could have scaled down the analysis and reduced the number of locations; however, I was curious to see if we could analyse 150 locations in a single workflow.
While analysing colleagues’ journeys to the office, I also ran a similar analysis on travelling to client offices, as I wanted to ensure it was easy to travel to their offices. This is important for our business development managers and consultants, so this was factored in, and the standard deviation of these travel times was also worked out for these journeys. I took the results of both analyses and averaged out the results to give a final score for each location.
Colleague and Client Office Location Analysis
Once Alteryx had web scrapped the 7 coworking office providers, then successfully called and parsed the API responses, I generated two outputs. The first was a static PDF of the top 10 locations from the analysis for the management to view at a quick glance. This output was generated by the native reporting tools of Alteryx. The second output was a Tableau dashboard with data for all the locations plus the different office spaces and price data. The dashboard allowed the management team to explore the data further, enabling them to increase the number of locations to review and filter on a number of parameters.
Mock Up Tableau Dashboard – Office Locations
The Impact
So where did we end up after all of this analysis? Well, we stayed in the previous building, but we are on a different floor now and got another location in Canary Wharf. We kept the previous building as a post-box and were able to negotiate a good deal on the lease when things began to open up in the UK. However, the analysis showed that we were in the right location, as a number of the top locations were in the same part of London. The addition of the second office in Canary Wharf was also strategic, being convenient for colleagues to travel to and close to our clients’ offices.
The benefit of this analysis was that a single consultant was able to analyse hundreds of locations using tools such as Alteryx and Tableau and provide insights to the management in a number of days. We were able to be agile and did not incur an extra cost when performing this analysis. Plus, the ability to retrieve additional information, such as office space and pricing information, helped the management team when it came to negotiating the new lease. The thrill of solving a challenge like this with so many locations was also great to see and know is a possibility with these tools.
❝
The benefit of this analysis was that a single consultant was able to analyse hundreds of locations using tools such as Alteryx and Tableau and provide insights to the management in a number of days.
❞
This is the first article in a new series on the Alter.Nation blog--Alteryx Impact Stories. If Alteryx has been a game-changer for you, pitch us your story! You can fill out the submission form here.
