Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Working from home and not being able to meet in person at social events underscored the need at Keyrus UK for more interactions between colleagues. Fortunately, I had made a Coffee Roulette Alteryx application in my previous company so I pitched the idea I could make a Coffee Roulette application for Keyrus UK. The idea was welcomed, and I was able to quickly make the application and has since been adopted into three groups at Keyrus.
Overview
The Coffee Roulette solution at Keyrus is an Alteryx scheduled workflow hosted on our Alteryx server and runs once per week and utilises Snowflake as a data warehouse and emails the participants who have been paired up.
The Alteryx Workflow
The workflow has two inputs: a list of current employees and the teams they sit in. The second input is a list of previous matches that is used later in the process to filter out records.
To pair up colleagues each week I create a list of potential matches that can be feed into an Iterative macro to generate the parings. This is done by appending the data together to get a total combination of pairings, which I can then reduce in the remainder of the workflow. The first step to reduce the parings is to remove matches between the same individual. The next step is to concatenate the two names into a new field and tokenize the word characters then sort alphabetically and combine or concatenate. This means I can easily remove duplicates as regardless of the order of the names in this field will always be the in the same order with this process.
For the previous matches I do a similar process with concatenating the name fields and use a join tool, as a filter to remove these records from the list of potential matches.
Once I have removed the previous records from the list of potential matches, I assign a random number to each paring. If the pairings are both in the same team, I then increase the number assigned randomly just to make these parings less likely and increase the chance of the pairings, being in different teams. I then remove any duplicates using the field where I concatenated the letters of both names and then sort by the random number ascending. This step prepares the data before it’s fed into the Iterative macro by removing duplicates, previous history and then randomly assigning an order for the macro to process the data.
Inside the Iterative Macro it takes the first record and then removes the names of the paring from the remaining records and then Iterates the process and repeats until there are no remaining records.
Once the macro produces a list of this week’s Coffee Roulette Matches, the remainder of the workflow prepares the data to email the matches and inform them who they have been matched with. Also, the workflow emails anyone who has not been matched plus emails myself and HR a backup of the previous matches.
Conclusion
The Coffee Roulette solution at Keyrus is a fully automated solution which is helping us keep in touch with colleagues during these unprecedented times. This solution was always designed to make this process as easy as possible requiring as little human touch points. Therefore, partnering Alteryx and Snowflake has meant the data can be maintained and stored and the processing of the matches and dissemination of those matches are in handled in this one solution. The team input is the one source of data that occasionally needs updating by a human. This is done by an Alteryx App to streamline the process and makes the update as easy as possible. Due to speed it took to implement Coffee Roulette at Keyrus UK and the positive results from those Coffee Chats it wasn’t long before other groups at Keyrus asked for their own versions.
Banner image by Pricilla DuPreez
