Alteryx Workflows and Power Automate save thousands of hours in a Global Restaurant Chain
Customer's Challenge
A top global restaurant chain’ restaurants assign employees individual shifts. Whenever employees can’t make their shift due to PTO or sick leave, the People Operations team must communicate that this shift is open so other employees can apply. Customers used spreadsheets, with manual reconciliations and calculations leading to human errors and many manual adjustments.
phData's Solution
The phData Analytics team created an Alteryx solution that centralizes all processing in three steps: standardizing the results and error-proofing the formulas manually typed into the spreadsheet.
Results
By developing an Alteryx solution, phData’s team reduced a process that took around 1,200 hours yearly to 180, saving almost half of an employee’s workday with this Alteryx automation. Also, due to the heavy audit on the process, the automation with Alteryx helped them standardize and error-proof all of the communications, ensuring compliance with regulatory demands.
The Full Story
To ensure the restaurants have sufficient staffing coverage, People Operations must communicate open shifts in advance so employees can apply to work. Shifts communicated must be posted in a permanent location because of audit requirements. They must also be sent with specific People Operations mailboxes.
An Inflexible Excel model with many different sheets and data from several different files leads to a time-consuming process. The process is run daily, on weekends, and on most holidays. As several files and formulas are needed, the execution can take 3 to 4 hours if done manually.
phData’s goal was to build automation to save time, limit human error, and improve performance when creating the files required for the Open Shifts communication process.
Why phData?
phData’s wide range of expertise on self-service analytic platforms initially attracted the client. The case was strengthened when phData’s expert, Alteryx Artisans, offered to deliver a solution within the required parameters using the resources that had already been purchased.
Building a Custom Alteryx Solution
As the company already used Alteryx for other areas, People Operations approached the team, explaining their process and asking if it could be automated.
After a few meetings to cover all of the requirements, the team decided to split the process into 3 steps:
The first step would be to create an Excel file with all relevant communication data, which could be done manually if needed.
The second step would be to prepare the PDF communications and a mailing file to be reviewed and updated before sending.
A flow in Power Automate to read the PDFs and mailing list and send emails to all restaurants and employees inside the communication regions.
Even though avoiding having an editable Excel spreadsheet was the best practice, People Operations needed this step because they often receive shift requests via email with no standardized formatting. To add those shifts to the communications, they needed access to the file in an easily editable format.
The shift communications can be divided into two categories:
- Ongoing Shifts: When an employee is terminated, the shift would be open for the taking of another employee or possibly a new hire.
- Temporary Shifts: When an employee is on a leave of absence or medical leave, the shift is temporarily open for others.
The first workflow prepares the Excel spreadsheet by reading data from the previous day’s communication and adding new schedules (if needed), employees in LOA, employees in SSR (Medical leaves), and checking the list of active and terminated employees for possible new terminations.
It then prepares an Excel spreadsheet with 8 sheets containing all of the data required to audit the process if necessary.
The second workflow reads the data prepared in the previous one and formats it in a PDF document and the mailing list that will be used by Power Automate to send out communications. Below, you can see the initial part of the second workflow, which just reads the data and sends it to a macro that prepares the PDF file.
Headers, dividers, and footers are created to prepare the PDF. All of those were made so restaurants and employees can check all of the needed information in an easy, tabular way.
The same data used in the PDFs is read to prepare the email files, and because of limitations on the emailing tool, groups of 500 employees or restaurants are made and separated to prepare the mailing list.
Finally, after reviewing all of the files, the People Operations team needs to go to the Power Automate website and run a flow that reads the mailing list and the PDF files and sends out emails based on the data.
Take the next step
with phData.
Learn how phData can help solve your most challenging data analytics and machine learning problems.
