alteryx Use Cases

Learn how you can leverage Alteryx in your organization.
Announcement | Are you an advocate for Alteryx? Check out our Advocacy.Amplified program to get recognized for your evangelism!

How Yusen Logistics Uses Alteryx to Improve the Data Operations

Community Lead
Community Lead
Created

logos_YBHDCNRXWVG36DAQW326-5733012f.png

 

 

 

 

 

 

 

Overview of use case

Yusen Logistics is a world-leading provider of contract logistics and international freight forwarding, creating solutions ranging from standalone operations to global supply chains. With regional headquarters in Europe, Japan, East Asia, Oceania, and the Americas, Yusen Logistics is a team of over 24,000 people working in 45 global markets to deliver seamlessly connected supply chain solutions to our customers. Our aim is nothing but simply to be your preferred Supply Chain Logistics Company.

 

We are Eric Wong and Linda Lo and we are data analysts under Process Management Department in Yusen Hong Kong Global Headquarters. Our role and responsibility are to provide reliable and clean data to business units, perform business-oriented analysis and provide business insights through our analysis and tools. We prepare and work on data daily using Alteryx. Today we are going to show you 4 use cases that we have been using Alteryx to improve the ETL process flows, data validation flow, to rate audit, and to extract data in different file types.

 

After further using Alteryx, we can feel the user-friendliness of this tool. Without consolidated knowledge of IT techniques, it is hard for users to understand and learn from beginnings. Now Alteryx can allow all users to use built-in tools to perform data transformation which needs to use script to do before.

 

 

Use Case 1: Improving the ETL Process Flow


Describe the business challenge or problem you needed to solve

 

Currently, our company is using the BI tool as an ETL tool for the data obtained from our main operation system at the same time. Since more data will become from different systems and we try to provide a user-friendly tool for allowing other business users who are not from IT background can also understand data easily and enable them to involve more in the preparation of company data. It is no doubt that a professional ETL tool is required for us to move the ETL process. Therefore, we extract necessary data and perform ETL to create excel reports from end to end for the accounting department as POC to evaluate the performance of Alteryx.

 

We are trying to solve the data preparation and blending problem for our company data. It separates into 3 parts.

 

Firstly, we find that it is quite time consuming for communication between business users and IT developers in the current process. IT developers are responsible to prepare the data by extracting it from our Operation system using SQL and then blend the data in the BI tool using script after business users raise requirements to them. It is quite common and normal that there is always some misunderstanding between business users and IT developers. Hence, this process is always time-consuming.

 

Secondly, we want to replace the ETL process flows to Alteryx in order to connect to different BI tools for further visualization. Now we have several BI tools used by different countries and it needs additional time to convert the blended data to suit other BI tools.

All company business users and IT developers encountered this problem day by day. As a result, we would like to enable business users to involve in the data preparation and blending part in order to reduce the total time required. Besides, we would like to have a tool which can export into a different file format which can allow users to connect to different BI tools for further usage in a convenient way.

 

Lastly, currently, we have a lot of data on hand including operational data, accounting data, CRM data, etc. Nevertheless, having the data stored in BI only but not data warehouse or data lake disabled us to make full use of data. We cannot easily connect all data due to the limitation of BI tool such as API, other applicable and machine learning, etc.

 

Describe your working solution

 

Data: The data comes from our operation system and various excel mapping tables.

Alteryx offerings: Designer and Server

Platforms or technologies: Blended data is stored in Azure which allows other users to connect the result directly as well as generate key excel reports for the accounting department

Deployment: The workflow is created and published to the gallery in which the daily schedule has been set up. We use diverse BI tools and excel spreadsheets to visualize the data.

 

Overview of ETL

BowK_0-1580998329035.png

 

  1. Data Extraction

BowK_1-1580998329042.png

 

  1. Mapping Files Blending

BowK_2-1580998329054.png

 

  1. Data Blending

BowK_3-1580998329063.png

  1. Report Preparation

BowK_4-1580998329079.png

 

Describe the benefits you have achieved

 

After the completion of this POC, we found that there are several obvious benefits. First, we spend less time to do the same thing by comparing this workflow with all the steps I need to take to provide the same result in our traditional ETL method. Although the report appearance may not be as good as a BI tool can provide but it saves our time to generate key reports to our management.

 

Furthermore, unlike before, only IT developers can be able to find out the reason behind and then modify it when business users find the discrepancy of the data. Now Business users can also look into what has been built before and know more about the company data in a more efficient way.

 

Last but not least, we find out our POC indeed increased our internal customer satisfaction including management and other department colleagues after our presentation of this POC. All of them agree that scriptless is really vital for handling data in our company.

 

In the beginning, we started from a simple workflow to combine 2 data sources into one with some ETL. Then we find that it is not difficult to start. I can always use less than 10 tools to perform the result.

 

BowK_5-1580998329089.png

 

After further using Alteryx, I can feel the user-friendliness of this tool. Without consolidated knowledge of IT techniques, it is hard for users to understand and learn from beginnings originally. Now Alteryx can allow all users to use built-in tools to perform data transformation which needs to use script to do before. Besides, error chasing ability is powerful for Alteryx. Users can see those warnings which they may not easily find in other tools. Moreover, most tools have the input and output windows which allow users to see the difference after applying each tool. It is extremely important to have this function in Alteryx. Then users do not need to run the same workflow many times to find out where the difference happens. For me, using Alteryx is like using excel for doing data analysis.

 

 

Use Case 2: Improving the Data Validation Flow

 

Describe the business challenge or problem you needed to solve

BowK_6-1580998329092.png

 

For our top management, we always want to assess the performance of our company. We extract different numbers and do a lot of analysis and reports to them. They want to have accurate and credible reports to reflect the real situation of our company.

 

Meanwhile, for operators, their job duty is to fill in shipment information to the operation system. But during the process, there are no reports and measurements to monitor them in real-time. Hence, not much direct impacts will be brought to them since they are not linked with the potential consequences. If the data input by them is not correct, it will affect the later part of analysis.

 

 

Describe your working solution

 

BowK_7-1580998329096.png

Instead of getting the data and realised that it is wrong, we have added in Alteryx as monitoring role for our operation. We can extract the operation data from the database and hence apply some validation workflows to check the data quality. If there are any problems, we can import some error messages or even change the data directly. In this case, the users can be aware of the data input errors immediately and take actions on it.

 

BowK_8-1580998329102.png

 

For the pink part of the workflow above, it is the validation part. We apply some business logic to check the data. e.g. For Air shipment, we must issue a Master Airway Bill on or before the Estimated Time of Departure by at most 30 days. The wrong ETD/ MAWB will lead to a wrong settlement between sister branches or customers. It also affects the throughput calculations. We check how large is the gap between the two dates. If the day difference is greater than 30 days, we consider it as “Mismatch”.

 

To show the message of “Mismatch”, we need to export an XML file to our eAdaptor so as to transmit the information to our operation system. Therefore, in the blue part of the workflow above, we wrote a loop to create XML files with those shipments that are wrong to send the message to the operation system.

 

BowK_9-1580998329105.png

 

After that, we can create a note for our operators immediately. If there are any problems, they can take actions to minimize the cost of inputting wrong data.

 

 

Describe the benefits you have achieved

 

We need a lot of human inputs and entries in the operation system. During the process, human errors involved. We always emphasize the importance of data quality. Without good data quality, we need a lot of KPIs, audits, and investigations in the later stage. It is difficult to reflect the real situation in time. More importantly, it is difficult to trace back what the operator has done wrong.

 

With the new way, operators can now receive the messages when they are editing the shipment data. It is much easier to trace back why they have input wrongly. For any problems, immediate communications can be carried out. We can also directly change the operation data to reduce the manual work.

 

 

Use Case 3: To Rate Audit

 

Describe the business challenge or problem you needed to solve

Nowadays, we enter the Quotation information into one system which is not linked with our operation system. For users, they have to check the price to customer in the quotation system and input back the information to our main system. We have manual checking against both systems.

 

Since the charges of Sea transport Shipments are very complicated, therefore, we hope to automate part of the general process to enhance the efficiency of checking procedure. We have developed a workflow to audit the invoice in our operation system against the quotation system.

 

Describe your working solution

BowK_10-1580998329110.png

 

For the Quotation system, it provides the REST API connection service. Therefore, we can call the quotation directly with queries. Hence, we make use of Alteryx to form the queries and make use of JSON Parse tool to download the JSON data. After that, we can parse the output to multiple tables and store them. We also make it into a loop to extract multiple records.

 

 

BowK_11-1580998329118.png

 

 

After getting the data from API, we can now compare the quotation data (blue part) with our operation systems’ data (red part). Inside the green part, we applied our business logics and make some mappings to align the master data between two systems.

 

Describe the benefits you have achieved

For our first trial with the report, we can replace 40% of the human checking by machine. With the use of Alteryx, part of the invoice can be checked. The invoice samples would be much smaller and less working hours will be allocated for manual check. Hence the efficiency is greatly increased.

 

Use Case 4: Extracting data in different file types in Alteryx

 

Describe the business challenge or problem you needed to solve

In our company, we always received bookings from different customers and shippers. They provided all kinds of file types (word, pdf, mht, html, jpg, png…) for our operators and they have to manually input the information back to our operation systems. The process is manual and time consuming.

 

We work in the Process Management team and our goal to simplify and reduce the manual work as much as possible. From receiving the emails from our customers to capturing the essential information and to update our system YUNAS, we would like to give it a try in Alteryx!

 

Describe your working solution

 

BowK_12-1580998329127.png

 

Firstly, we have extracted the emails related to the booking and put them into a central folder for future use. It can be scheduled every day so as to reduce the manual work and chance of overlooking the email.

 

BowK_13-1580998329170.png

 

 

Next, we have to extract the data to Alteryx. Unlike loading Excel or Database. Alteryx cannot directly read those files. To tackle various types of files to make our goal happen, we have different approaches to read the data inside.

Word Documents
The first step would be using the Run Command Tool to run a batch file. It is used to change the documents in text so that it can be read in Alteryx.

MHT/HTML
It is rather simple and direct. We can use the dynamic input to execute.

PDF
Since Alteryx does not have any tool to extract the information inside PDF directly. We have used a tool in the Gallery and use the R package for reading the PDF. Then the PDF can be read in Alteryx directly.

JPG/PNG
For images, same as PDF, we cannot directly import them to Alteryx. To read Images, we have to use the technology of Optical Character Recognition (OCR). We use the R packages to read the images. For this part, you may also use the market OCR tools and import back to Alteryx for data transformation or blending.

BowK_14-1580998329177.png

 

 

Describe the benefits you have achieved

 

It has been a great trial of reading different file types to Alteryx. The data is not limited to Excel or database but also extend to documents and images. Alteryx can help to perform the OCR function which is quite a fun and new experience. After getting the data to Alteryx, we can further perform data blending for further actions.

 

The next step we would further go deep would be using Alteryx to write the XML file and hence to transmit the data back to our operation system.

 

Even though Alteryx is not a formal tool for OCR, but its high flexibility allows us to explore different new things and brings up possibilities.

 

 

All in all, the reason why we need to have Alteryx POC is that we don’t want to waste our data. Data is a vital asset of our company. However, all data are stored in a different system and it is hard to combine into together to visualize all things from end to end. Like ERP system, the ultimate goal is to connect everything into one single platform for better management. Marketing data, accounting data, CRM data, operational data, financial data can be linked up together to show a clear picture to us on how to improve our business. Alteryx allows us to have ample attractive functions like sending emails and xml export function. Finally, we hope we can reduce human works and migrate into an automated future.

 

On the other hand, the most impressive words from Alteryx is “City Data Scientists”. We are glad to see Alteryx is moving step by step to make this becomes true. As data is becoming more and more important nowadays, it is evitable for us to touch multiple data sources. How to handle such large amounts of data and various data sources at the same time is the only key to succeed in the future. In the past, people always analyse data based on their understanding of recognized patterns from past experience. However, it is far from what we need in this new century. We should get data insights rather than the traditional method to analyse data. With the help of Alteryx, I believe everyone can become “data scientist” in the era of data analytics.

 

Comments
Alteryx
Alteryx

Excellent article and thank you Yusen Logistics for sharing! 

Labels