Yusen Logistics Uses Alteryx to Improve Data Operations

[BowK]

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 roles and responsibilities are to provide reliable and clean data to business units, perform business-oriented analysis, as well as presenting business insights through our analysis and tools. We prepare and work on data daily using Alteryx. Today we will be going through four use cases that we have used Alteryx for. The first and second use case will demonstrate how we used Alteryx to improve ETL process and data validation flows. As for the third use case, you will learn how we’ve used Alteryx to rate our operational audit system, and lastly, we will show you how to extract data from different file types.

After further using Alteryx on a daily basis, it is we’ve experienced first-hand how truly user-friendly it really is. Without consolidated knowledge of IT techniques, it is hard for users to understand and learn, especially beginners. But with Alteryx, all different types of users can now use the built-in tools to perform data transformation and even predictive modelling!.

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 for ETL to manipulate the data obtained from our main Operation system. Since more data will come from different systems and departments, we try to provide a user-friendly tool to allow business users who don’t have an IT background to understand the data easily as to get them more involved in the preparation of the company data.

There 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 is separated into 3 parts.

Firstly, we find that communication between business users and IT developers is a time-consuming process. IT developers are responsible for preparing the data by extracting it from our Operation system using SQL, then blending the data in the BI tool using script after the business users raise their 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 extremely painful and lengthy.

Secondly, we would like to replace the ETL process flows with Alteryx to connect to different BI tools for further visualization. Now, as we have several BI tools used by different countries, the process will need additional time to convert the blended data to suit respective BI tools.

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

Lastly, we have to deal with a lot of data including operational, accounting, CRM data, etc. However, 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 tools 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

1. Data Extraction

2. Mapping Files Blending

3. Data Blending

4. Report Preparation

Describe the benefits you have achieved 

After the completion of this POC, we found that there are several obvious benefits. First, we are spending less time as we have eliminated the need to repeat a process twice - this workflow has 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 perfect as compared to what a BI tool can provide but it saves our time to generate key reports to our management.

Furthermore, before when business users found the discrepancy in the data

only IT developers were able to investigate  modify it.. Now, Business users can also investigate what has been built , all while learning more about the company data in a much more efficient way.

Finally, we found out our POC indeed increased our internal user satisfaction including management and colleagues from other department after we presented this POC. All of them agreed that scriptless is indeed vital for handling data in our company.

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

Alteryx makes error tracking easy and that is a powerful component of the platform. 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 is the difference. 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

For our top management, we’ve always wanted to assess the performance of our company. We extract different numbers and create multiple analysis and reports from them. They want to have accurate and credible reports to reflect the real situation of our company.

Meanwhile, in operations, their responsibility 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 impact 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 greatly affect the later part of our analysis.

Describe your working solution

Instead of getting the data and realising that it is wrong, we have added in Alteryx as monitoring role for our operation. We are able to extract the operation data from the database and then apply several 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 action to rectify the mistake.

In the pink container box 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 within 30 days maximum. 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 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 incorrect – this will then send the message to the operation system.

After that, we can create a note for our operators immediately. If there is any problem, they can take immediate action to minimize the cost of inputting the 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 will naturally occur. We always emphasize the importance of data quality. Without good data quality, we will 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 ow what they have input mistakenly. For any problems, immediate communications can be carried out. We can also directly change the operation data to reduce 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 customer prices in the quotation system and input the information back into our main system. We then have to manually check against both systems.

Since the charges of Sea transport Shipments are very complicated, 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

For the Quotation system, it provides the REST API connection service. Therefore, we can call the quotation directly with queries. For that, 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.

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 logic and made some mappings to align the master data between two systems.

Describe the benefits you have achieved

For our first trial with the report, we replaced 40% of the human checking with automation. With the use of Alteryx, part of the invoice can be checked. The invoice samples would be much smaller and fewer 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 extremely manual and time-consuming.

As we work in the Process Management team, our goal is to simplify and reduce 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

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 to reduce the manual work and the chance of overlooking the email.

Next, we have to extract the data and input into 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 the text so that it can be read in Alteryx.

MHT/HTML
It is rather simple and direct. We can use 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, is it the 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.

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 into Alteryx, we can further perform data blending for further actions.

The next step we dived further  by using Alteryx to write the XML file and then transmit the data back to our operation system. Even though Alteryx is not a formal tool for OCR, its high flexibility allows us to explore different new things and brings up infinite possibilities.

All in all, the reason why we need to have Alteryx POC is that we want to utilise all the data that we have. Data is a vital asset to our company. However, the data are siloed and  stored in a different system and it is hard to combine them together to visualize all things from end to end. Like an 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 to reduce manual labour in time to come and migrate into an automated future.

Truly, the most impressive words from Alteryx is “ Citizen Data Scientists”. We are glad to see Alteryx is moving step by step to making this come true. "As data becomes increasingly important, it is inevitable for us to touch multiple data sources. Learning how to handle such large amounts of data and various data sources at the same time is the key to succeeding in the future. In the past, we used to analyse data by recognising patterns from previous experiences. However, it is far from what we need in this modern age. Together, with the help of Alteryx - I believe everyone can and will become a “Citizen Data Scientist”.

[JoG]

Excellent article and thank you Yusen Logistics for sharing!