The United Parcel Service (UPS) is the world’s largest package delivery company, headquartered in Atlanta, GA. Mostly well known for its shipping capabilities, UPS utilizes a variety of transportation methods and services in order to deliver parcels efficiently both domestically and globally for individuals and corporations. Specifically, we worked with the UPS Global Customer Solutions or GCS team. The team focuses on the reporting of carbon emissions for UPS’s customers in the form of CIAs - Carbon Impact Assessments. The reports show the final calculation of metric tons of CO2 emissions over a period of time by mode of transportation and provide this information to help customers in a variety of ways.
The current carbon reporting process is predominately manual. The reporting process begins when a customer sends a request for a CIA to the team. The customer’s data is pulled through a variety of different time consuming methods from 6 different data sources. Each source represents a different internal business unit or subsidiary of UPS that contributes to the shipments of customer’s parcels both domestically and internationally. It is possible that a customer may have shipment data specific to just one, multiple, or all of the business units for one time period.
After the data is pulled, it is then manually cleaned and converted to a format that can be input into the calculator using a variety of tools. Once in the standardized format, the files are uploaded individually into the carbon calculator. When a file is uploaded, it enters the calculator’s queue. After successful upload of all data sources, the calculator will identify underlying data errors in the file that must be manually corrected and resubmitted into the calculator. After successful execution, the calculator outputs are compiled into a basic emissions report in PDF form and sent to the customer.
Currently, the carbon reporting process is predominantly manual. On average, the team spends 32 hours to complete one Carbon Impact Assessment. In the last year, the GCS team has seen a 250% increase in report requests from their customers. Because this is a time consuming process, the team is unable to keep up with rising demand. In addition to the lengthy reporting process, the team has received feedback from their customers regarding the level of detail in the final emissions report. Customers often ask questions such as, “How can emissions become a decision maker?” displaying the customers desire to transition to more sustainable business practices. Due to the current over-utilization of the UPS team, they do not have the capacity to sit down with each customer to determine how they can reduce their emissions.
The first step in streamlining the automation process was deciding which software to build the deliverable. We chose Alteryx, a data driven workflow software that is used to help automate processes through a user friendly interface, for the following reasons: the GCS team uses Alteryx in other business processes, a Python script or another raw source code requires a length audit and testing process with UPS’ IT Security team prior to any use, and Alteryx has built in capabilities for creating analytical applications.
Significant testing and time studies associated with the run-times of the application have been performed to determine the efficacy of the automation. Testing on real customer data showed significant reduction in time in the data preparation process. This implementation decreased data preparation for the external source by 98%, and the total pre-calculator process for internal sources by 25%. Additionally, the deliverable had a 95% reduction in time it takes for the finished files to be uploaded to the calculator - queueing time.
The goal of the recommendation report is to use the information the GCS team already has to give the customers more insight into their emissions habits without adding time to the team’s existing reporting process. To approach creating the recommendation report we began with a statistical analysis on existing customer data. The CEC template, the file uploaded into the carbon calculator, has dozens of variables that could potentially have an impact on carbon emissions. After meeting with the GCS team to learn an overview of how the calculator functions, we were able to narrow down the potential variable pool to those we felt would have the greatest impact.
One consideration in the creation of this excel reporting system was time, and we did not want to add a major amount of time to the overall emissions reporting process. The excel model was tested extensively on real customer data. The model was able to process the customer data as well as various sizes of test data without any errors or runtime deficiencies. This new report is able to greatly increase the amount of information a customer has access to, without increasing the amount of work for the GCS team.
The final automation deliverable comes in the form of an Alteryx Analytical Application. The application is built out with functionality to process data from all UPS Customer Solutions business units within our scope simultaneously, turning any labor time associated with intermediary steps in the data preparation process into limited computational time. The application has built-in functionality for outputting a unified CSV file containing all of the information input for a given study and complies with Carbon Calculator file size, data type, and formatting requirements for proper emissions calculation.
In order to address customers' desire to learn more about their emissions, the second deliverable is an excel model that utilizes macros and VBA to enhance the granularity of the report given to the customers. A statistical analysis of existing customer data determined what variables affect emissions, and in only sixty seconds, this deliverable triples the information available to customers. In addition to both deliverables, guidelines and standard operating procedure documentation was given to the client.
Value and Impact
Through these two deliverables, the value provided to the UPS team will be an approximate $310,000 reduction in annual cost of reporting. Additionally, value will be added through increased client satisfaction due to more granularity of carbon emissions reporting. This cost reduction is generated through a reduction time necessary to complete CIA reports. The time saved through these solutions will allow the UPS team to focus their attention elsewhere, providing more solutions for UPS customers, thus improving customer satisfaction and UPS as a whole.