This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 05-01-201909:52 AM - edited on 06-19-201909:47 AM by ichand
Global Carrier Manager and Business Analyst
Seko Omni Channel Logistics
Chris McClellan, Adam Cama and Louisa Daniels
Overview of Use Case
Before purchasing Alteryx, our business practice was to use Tableau Prep to prepare our data for manual upload to Tableau Online. We had to change Excel files due to date format issues before adding the daily file to Tableau Prep and then manually running the workflow for upload into Tableau Online. This required a staff member to be trained up and available to do this 5 days a week, something no company wants to rely on. As we are a global eCommerce company, the need to have updated data as frequently as possible was a requirement so we could become a globally proactive team. As the data input grew, so did the time and issues with Tableau Prep. We ended up having to cut the data down by a third to ensure a successful upload daily, but this was still a time-consuming task, sometimes taking more than 2 hours to run through Tableau Prep. We needed a solution to help us create an automated, real time and reliable process. Then we were introduced to Alteryx!
Describe the business challenge or problem you needed to solve
The outcomes we needed to achieve were:
- Reliable and scale-able process for automating accurate data into our BI tool, Tableau: We wanted to be able to forecast volumes, so we needed to have access to as much data as possible to create usable forecasts throughout the year.
- Near real time data access for all global teams: We work with numerous global teams that all need access to accurate and up to date data at different times. This will affect the decision-making process and the ability to be on top of any supply chain blockers.
- Automated process: We needed to ensure that staff absences would not affect the process of updating data. Our data analysis team had some part time employees that could not sustain a 5-day a week uploading process. Holidays and sickness also affected this previously.
- Relevant insights: We wanted staff spending less time on updating data and more time analyzing data. We had spent so much time already on understanding our data and producing the reports required to highlight areas of improvement, however we did not have enough time/people looking at what had been produced.
- Minimal load on our SQL database: So any calculations done outside of this would assist here. Our system runs all our tracking portals for all clients, processing of all parcels and administration reports required for different parts of the business to run. We did not want the load of getting this data out of our system to affect any of this, as it would compromise the running of our core business.
- Data integrity and trust within our team: The line “If the data is correct” was a common phrase in meetings when looking at reports. The culture was starting to flat line with our analysis. We needed a process that our team could trust and give them more confidence to use the reports we were producing. We knew the data was correct, but the image of us having to always manually intervene with it, made people second guess this. Especially as so many errors of uploading were occurring and days of no upload.
- Addition of another database to data stream: We needed to incorporate the returns side of our business, which is in a different database. This was vital for our clients and us.
- Reducing the amount of time to upload: As we wanted to have this upload multiple times a day, we needed to get the upload down to less than 20 minutes so we could achieve this.
The supply chain teams within our business were relying on this data to make day-to-day decisions effecting performance and cost. The C-level management team required these reports to understand how the business was performing, and which areas of improvement we should focus on. The sales/account management team required reporting to view volumes, performance and the ability to showcase this to our clients. Both the operations and customer service teams needed visibility over the supply chain to be able to identify nonmoving parcels quickly, in order to reduce the noise and the amount of CS tickets within the business. Overall, we had each team within our business asking for this information in a way that they could access it quickly and easily.
Manual upload to Tableau Online
Tableau Prep upload time was 90+ mins for greater than 30 days data
Manual manipulation of Excel files to correct format of dates
Excel file e-mailed to user daily from SQL DB
Once a day solution to refresh the data
Ability to only upload the last 30 days’ worth of data – unstable on anything greater
30-60 minute load on system to produce Excel file and e-mail daily
Process involved: SQL DB → E-mailed to user → File manipulated → Upload new file into Tableau Prep → Flow run manually to Tableau Online.
Process before AlteryxAfter Alteryx
Automatic upload to workbooks
Live Connection to SQL DB
Alteryx upload 15-16 minutes for 90 days of data
No manipulation of files required
SQL code written to table, not an Excel file resulting on less load on the SQL DB
Time conversion macro created in Alteryx, reducing the load of the SQL DB
5-10 minute load on system to produce table for Alteryx
Ability allow SQL DB to be extended back to many months/years of as required (more work to be done on this to create static tables)
90 days’ worth of data refreshed every 2 hours
Process involved: SQL DB → Alteryx Flow → Snowflake → Tableau Workbooks → Published to Tableau Online.Process after Alteryx
Describe your working solution
Our solution involved using Alteryx and Snowflake to automate the integration of the data into Tableau Online. We are currently using Alteryx Designer and Alteryx Scheduler to enable us to achieve the above.
There are 2 workflows that run every 2 hours – one for delivery data and one for returns data. These databases do not need to be joined as all the information we need from the delivery side is brought into the returns side within the database itself.
Alteryx Designer picks up a table from our Azure SQL database and then uses background files that we have run through Alteryx to transfer them from Csv to a table in Snowflake – reducing the time of the workflow. It takes 15-16 minutes to run, minimizing the time by almost 600%.
We use this data in Tableau Online to report on performance within the supply chain, visibility over parcels within the supply chain and internal performances. We aim to incorporate CS (Zendesk) and IT (Jira) ticket information as well in the coming months.
As the data is available every 2 hours, we have a near real time view of how we are performing and where any issues are occurring. We have already seen a major decrease in nonmoving parcels and CS queries as a result of this visibility.
We have also started to use Alteryx within our accounts team to automate invoice reconciliation tasks. This is an ongoing task.
Zoom View of Date ConversionMain Data Source Flow
Returns Data Source Flow
Describe the benefits you have achieved
The impact on the business has been massive. We have seen time saved by employees that can now focus on analyzing the data, not uploading it, near real time visibility over our supply chain resulting in proactive actions from staff and less CS queries due to non and slow-moving parcels.
In terms of cost on the business, we are saving money indirectly through the resources of staff being used in other areas of the business. CS are able to spend more time on looking at what is causing noise in the business rather than being reactive on tickets and the team member who was uploading data daily is now in the reports and reporting to our internal teams any concerns to the supply chain that arise. We have also been able to add more calculations into the data, meaning we are able to report on more areas of the business.
Through analyzing the direct impact on time saved without manual upload, we gauge this to be at least $5,000 a year for the one employee alone. But we are aware that the indirect costs to the business are much higher. Overall the whole team are impressed by the outcome of using Alteryx within the business and we are now looking at other ways we can use this outside of Tableau.
We currently do a lot of manual collaboration and reconciliation with our carrier invoices so we are currently in the process of using Alteryx to minimize this time for the accounts team. With one carrier, we are now saving 3-5 hours a week and have the ability to reconcile to a higher level than we have previously meaning we are able to identify cost issues with the carrier or internal mistakes with carrier set ups. We still have so much more to incorporate Alteryx into our business.
How does Alteryx make you feel? Alteryx gives me the ability to do more tasks in less time, so I feel more productive which in turn gives me the chance to make changes within our team and company to benefit both employees and the business. Overall this makes me feel valued, empowered and accomplished. Alteryx allows me to offer our departments new ways to save time and money resulting in happier conversations and relationships with team members which makes me feel excited of the journey we are on with Alteryx.
What are you most excited about when it comes to the future of analytics? I am most excited about learning what is still to come. We have already seen so many new ways to collaborate and understand our data and the impact this has on decision-making, work life balance and overall understanding of what we have been doing for years. I cannot wait to see what new enhancements are still to come to make this easier and em-powerful to businesses, charity organisations and people.