Tracking Undelivered Shipments through RPA with Microsoft Power Automate

Do you often send high-value shipments to B2B customers, only to later discover that they couldn’t be delivered? 

Undelivered shipments not only disrupt your supply chain but also damage your reputation and customer trust. Without a solid tracking system, handling these issues can turn into a full-time job.

For our client, this was a daily reality – until they decided it was time for a change. They needed an automation process for tracking packages along with hosting options.

This case study outlines the phases and implementation process of setting up a Robotic Process Automation (RPA) flow using Microsoft Power Automate to enhance operational efficiency and accuracy in logistics tracking. 

This article is for you if you often find your parcels turning into “undelivered shipments”.

Client’s Pain Points in Monitoring Undelivered Shipments

Our client, a prominent beauty company in California specializing in high-performance aesthetic products and the shipment of vials via UPS, faced significant challenges in monitoring shipments that are undelivered. 

Their logistics team was bogged down by a manual process that required a dedicated employee to spend over two hours daily to retrieve data, compute statistics, and prepare a report on undelivered product details for management review. 

This labor-intensive and error-prone approach led to delays and inaccuracies in reporting. To address this, the client sought an automation solution that met their enterprise security standards.

Bitcot’s RPA-Based Solution for Efficient Logistics Shipment Tracking

To enhance operational efficiency and accuracy in tracking shipments, Bitcot implemented a solution that automated the manual tracking of undelivered vials shipped by UPS. 

Our solution involves managing eCommerce shipment tracking across two separate sites: an FTP site and a tracking site. The FTP site houses the initial shipment data in an “outbound” folder. From there, the latest modified file can be retrieved, which contains shipment details but not the status.

To determine the shipment status, we import tracking numbers from the FTP file into a tracking site. This step allows us to fetch and update the status of each shipment by processing the data into CSV records and running SQL queries to match the tracking numbers. 

This process creates two types of sheets: a “master” sheet from the FTP site and a “minor” sheet from the tracking site, where the shipment status is updated accordingly.

The automation workflow generates three key reports daily to communicate them to the management:

• Initial Report (4 AM PST): Provides details on shipments scheduled for the day.
• Midday Report (11 AM PST): Updates on deliveries made or still in progress by mid-morning.
• End-of-Day Report: Tracks deliveries attempted throughout the day, including those that remain undelivered.

Reports are generated over three days, considering that shipments undelivered on the first day may require delivery attempts on subsequent days. By the end of the third day, the data is no longer considered viable for client use.

The entire solution has been built using Microsoft Power Automate, leveraging its ability to manage automated workflows and handle frequent updates seamlessly. SQL is used for querying and data management.

One of the main challenges faced during development was the evolving nature of the client’s requirements, which were not finalized until very recently. However, due to the flexibility of Power Automate, we were able to accommodate these changes smoothly and continuously update the workflows.

Infrastructure Requirements

• Power Automate Account: For building and running the automation flows.
• Microsoft Office License: For managing Excel files.
• UPS Portal Credentials: Required for logging into the UPS sites.
• Virtual Machine (VM): To host and run the automation processes.
• SMTP Credentials and Server Details: For sending email notifications.
• Licensing Requirements:
• 1. Unattended license if the automation flow is triggered from the cloud.
  2. Attended license if the automation flow is triggered manually.

Business Requirements

• Email Address: A designated email address was set up to handle notifications and reports generated by the automation processes. This ensured that all communications were streamlined and delivered promptly to the relevant parties.
• Flow Schedule Timing: The company established a predefined schedule for triggering automation flows daily. This consistent timing ensured that processes were executed efficiently, maintaining regular updates and operations.

RPA WorkFlow Automation That Simplifies the Messy Side of Tracking Packages

The RPA workflow for tracking packages was carefully developed to automate and streamline the entire process. 

We began by designing a system that extracts shipment data from UPS portals, processes it into usable formats, and integrates it with our tracking system. This involved setting up automated tasks to download, organize, and update package information efficiently.

Once the core automation was in place, we implemented error handling and reporting mechanisms to ensure accuracy and reliability. The workflow was configured to run at scheduled intervals, ensuring real-time shipment monitoring and updating shipment statuses. 

Effective package tracking relies on a well-structured folder system and strategic use of default files. Here’s a concise overview of our approach:

Folder Structure

• Downloaded Package File: Contains the packages.xlsx file obtained from the UPS web client site, which serves as the main data source for tracking and updating package information.
• Default Files: Includes essential templates and standard documents required for processing, providing a consistent framework for handling data.
• Error Logs: Stores error logs in text format used for troubleshooting and internal analysis, helping to identify and resolve issues encountered during data processing.
• Report: Houses the Undelivered Tracking Records.xlsx file, which tracks and details undelivered packages for reporting and further action.
• Re-Run Files: Keeps a copy of the packages.xlsx file designated for reprocessing over a period of three days, ensuring that any updates or corrections can be applied as needed.
• Blank Records: Contains records with missing tracking numbers, which need to be addressed to ensure complete and accurate package tracking.
• UPS Files: Includes CSV files split into batches of 100 records each, facilitating efficient processing and management of large volumes of tracking data.
• UPS Output Files: Stores the output files downloaded from the UPS tracking site, which contain updated tracking information and are used for further data updates and processing.

Default Files

• Sales Rep Mapping: Contains a mapping sheet that aligns sales representatives with their respective facilities, ensuring accurate and consistent reporting for sales and tracking purposes.
• Undelivered Tracking Records Format.xlsx: Serves as the final sheet that stores all records of undelivered packages, providing a comprehensive view of packages that have not yet been successfully delivered.

Task 1: Retrieve Package Data 

Download and Extract Project Files

• Download Zip File: Access SharePoint to download the project folder zip file which contains all necessary data.
• Extract and Clean Up: Extract the contents of the downloaded zip file. After extraction, delete the original zip file to save space and avoid clutter.
• Set Up Paths: Establish and configure all required folder and file paths to ensure smooth organization and access to the project files.

Access the UPS FTP Site

• Open UPS FTP Website: Navigate to the UPS FTP site at ftp2.ups.com to begin the process of accessing package data.
• Retrieve Encrypted Credentials: Obtain the encrypted login credentials from SharePoint to authenticate access to the UPS FTP site.
• Decrypt the Credentials: Convert the encrypted credentials into their usable form to enable secure login to the UPS FTP site.
• Log In with Decrypted Credentials: Use the decrypted credentials to log into the UPS FTP site and access the necessary folders and files.
• Navigate to the ‘Outbound’ Folder: Once logged in, locate and open the ‘Outbound’ folder where the relevant data files are stored.
• Sort Files by Date: Apply descending sorting based on the ‘Last Modified’ date to identify the most recent file in the ‘Outbound’ folder.
• Download and Move File: Download the file with the most recent date and transfer it to the ‘Client Download File’ folder for further processing.

Process the Downloaded File

• Open the Excel File: Access the downloaded Excel file to begin extracting tracking data.
• Locate Tracking Numbers: Find the column labeled “CARTON_TRKG_NBR” within the Excel file, which contains the tracking numbers needed.
• Copy Tracking Numbers: Copy all the tracking numbers from the identified column and save them into a new CSV file for processing.
• Split CSV File: If the CSV file has more than 100 entries, divide it into smaller batches of 100 records each to facilitate easier handling and import.

Task 2: Import Tracking Numbers and Download Results 

Log in to the UPS Tracking Portal

• Open UPS Tracking Website: Visit the UPS tracking website at www.ups.com/track to access the tracking and import functionalities.
• Get Encrypted Credentials: Retrieve the previously encrypted credentials list from SharePoint needed for logging into the UPS tracking portal.
• Decrypt Credentials: Decrypt the retrieved credentials to convert them into a usable form for logging in securely.
• Log In with Credentials: Use the decrypted credentials to log into the UPS tracking website.
• Click “Import Tracking Numbers”: Navigate to and click on the “Import Tracking Numbers” link to start the process of uploading tracking data.
• Navigate to Import Section: Go to the “Import Tracking Numbers” section to prepare for uploading the tracking number batches.

Import and Process Records

• Import First Batch: Upload the first batch of 100 tracking numbers from the CSV file to the UPS tracking portal and download the resulting output file.
• Update Packages.xlsx: Use SQL queries to update the packages.xlsx file. Integrate status and shipping type details from the output file corresponding to each tracking number.
• Update Sales Rep Information: Modify the sales-rep information in packages.xlsx by cross-referencing the output file with the SalesRepMapping.xlsx sheet. Use SQL queries to update the sales rep details based on reference numbers.
• Repeat Process: Continue the process of importing, updating, and downloading results for all remaining batches of tracking numbers in the CSV file.

Task 3: Update Package Data and Calculate Statistics 

1. Extract all undelivered records from packages.xlsx and store them in Undelivered Tracking Records.xlsx.

• Calculate total delivered records
• Calculate total undelivered records

Task 4: Update Delivery Attempt Details

Retrieve Package Information

• Open UPS Tracking Website: Visit the UPS tracking website at www.ups.com/track to access package tracking features.
• Search Package Details: Enter the tracking number to search for specific package details on the UPS tracking site.
• View Package Details: Click on the ‘View Details’ button to access information about the package, including delivery attempts and timestamps.

Update Records

• Scrape Delivery Information: Extract the delivery attempt and delivery time data from the UPS tracking details for each package.
• Update Records: Enter the scraped information into Undelivered Tracking Records.xlsx, ensuring each entry is matched correctly with its tracking number.

Task 5: Share the Report and Clean the Virtual Machine (VM)

• Share the report with statistics over email (shipment date, customer order number, facility name, facility address, city, state, ZIP code, facility, order quantity, tracking number, delivery status, carton quantity, lot number, lot expiration date, sales representative, shipping type, delivery attempt, and delivery time).
• Clean all the folders and files after completing the flows.

Choosing the Power Automate Subscription and Hosting Plan

Power Automate offers several subscription plans to cater to different automation needs. Here’s a concise breakdown of the available options, their pricing, features, and considerations.

Power Automate Subscription Options and Costs

1. Power Automate Premium

• Pricing: $15/Month
• Features:

• Unlimited cloud flows (DPA) and desktop flows (RPA) in attended mode.
• Ideal for automating processes that require user interaction.

• Limitations:

• Flows can only be executed in attended mode, meaning they require user interaction to run.

• Reference Link: View here.

2. Power Automate Process

• Pricing: $150/Month
• Features:

• Licenses a single “automation” bot for unattended desktop automation (RPA).
• Supports running desktop flows in unattended mode, allowing automation without manual intervention.

• Limitations:

• One Process license allows only a single bot and cannot run multiple cloud flows in parallel.

• Reference Link: View here.

3. Hosted RPA (Add-ons)

• Pricing: $215/Month
• Features:

• Provides robotic process automation (RPA) as a service using Microsoft-hosted machines.
• Supports unattended desktop flows and integrates with Azure for advanced scenarios.

• Limitations:

• Requires an existing Power Automate Premium License in addition to the Hosted RPA license.

• Reference Link: View here.

Hosting Options

1. Power Automate Process with Unattended License and a VM

This option focuses on licensing a single automation bot for unattended RPA. It requires a virtual machine (VM) to execute the flows, making it suitable for business-critical processes accessed by multiple users.

Pre-requisites:

• Power Automate Process License ($150/month)
• NinjaOne account for remote machine access
• MS Office license
• Virtual Machine
• UPS Portal credentials 
  1. http://ftp2.ups.com
  2. Tracking | UPS – United States
• SMTP credentials and Server Details

Considerations: This model is licensed per bot, which requires careful planning based on deployment scale and process automation needs.

2. Power Automate with Hosted RPA License 

Hosted RPA utilizes Microsoft’s infrastructure for scalable RPA capabilities, eliminating the need for on-premises hardware. It supports both attended and unattended automations and integrates with Azure for advanced scenarios.

Pre-requisites:

• Power Automate Premium Account ($15/month)
• NinjaOne account for remote machine access
• MS Office license
• UPS Portal credentials 
  1. http://ftp2.ups.com
  2. Tracking | UPS – United States
• Power Automate Hosted RPA License ($215/month)
• SMTP credentials and Server Details

Considerations: The Hosted RPA license includes access to one hosted machine and one unattended bot. It simplifies RPA setup and management but requires familiarity with Azure services. It’s ideal for rapid scaling of automation capabilities without significant infrastructure investment.

The Client’s Major Impact and Success With RPA for Shipment Tracking

Bitcot’s automation solution has redefined the client’s approach to automated shipment tracking. By transitioning from a labor-intensive manual process to a streamlined automated system, the client has achieved impressive gains across several critical areas. 

Streamlined Data Management

The client achieved a boost in operational efficiency by implementing a robust data management system. The precise extraction and organization of project files set a solid foundation for tracking. By effectively accessing and managing data, the client streamlined their data retrieval process, reducing delays and errors.

Efficiency Boost

By automating the tracking of undelivered vials, the solution reduced the time required for processing from over 2 hours to under 10 minutes per batch of approximately 400 records. This automation not only streamlined operations but also minimized the risk of human error, ensuring more accurate and timely updates.

Enhanced Accuracy and Reliability

The use of Power Automate to schedule and execute the tracking processes improved the accuracy of shipment records. With automated flows fetching and communicating results daily, the client experienced a boost in shipment monitoring efficiency. It also ensured that management received consistent and reliable updates.

Improved Reporting and Insights

The automation process enabled the client to generate comprehensive reports on undelivered packages. By extracting and analyzing data on delivered and undelivered records, the client gained valuable insights into their shipment performance. This detailed reporting helped in identifying and addressing issues promptly.

Final Thoughts

The way the beauty industry is booming, it’s no wonder that managing the logistics of these shipments can be a real challenge.

As B2B customers expect faster shipping and seamless experiences, keeping up with orders and deliveries is no small feat. Beauty brands must step up their game to meet these expectations.

For smaller businesses, manually tracking a handful of packages might be manageable. But large companies shipping thousands of vials daily simply can’t.

The result? Delays, data errors, unhappy customers, and a logistics team stretched to its limits.

But here’s the good news: by adopting automation and smart logistics solutions, beauty brands can turn these challenges into wins.

Whether you’re a small business or a large enterprise, Bitcot is here to support you in delivering an exceptional experience to your eCommerce customers. Drop us a line here.