EDI Automation

Project Objective

The objectives of the project were to create a tool within the companies ERP (Enterprise Resource Planning) system that would parse orders received from retail customers out of electronic catalogs and create orders using the ERP’s product configurator. Then use that tool to build the relevant mapping to ensure at least 50% of orders are entered automatically without error.

Before

Before this project was rolled out, all orders received via EDI (Electronic Data Interchange) were printed out and manually entered by customer service. This required selecting the correct answer for over thirty questions with times to complete each line being as high as three to four minutes.

Since questions in both systems were not the same it was possible internayl order entry could take longer than the customer experienced in store. Out of sequence questions between system were causing errors in entry that required customer service to change values that were already entered.

With the volume of sales we were getting this way, automation really should have been implemented much earlier. There always seemed to be a higher priority item for leadership but luckily in this case customer service was heard and this automation was made a priority.

I had been discussing this possibility with my manager and team but we did not really get traction until the folks experiencing the pain were heard. Improvements like this should not require customer service to bring you a stack of orders saying that, "they are drowning in orders down here." This goes to show the importance of building a full business case for a change.

Outcome

The outcome was a new tool within the ERP system that was used to automate over 50% of orders.

Previously, a single order with five to six lines could take twenty minutes to enter but when automated could take fewer than twenty seconds. There is still a review component for each order but this is much faster than having to enter the entire order by hand.

My role

My role was the Product Owner.

My responsibilities

I worked with the sales team first to understand the pain points they were encountering when trying to enter orders that came from the retail catalogs. Once we had a documented business case I took the request to leadership and then to the ERP vendor for a quote.

The main driver here for the business is the time savings for customer service. The improved accuracy and additional time available to respond to customers were also significant factors in getting this approved.

I translated the business case into detailed technical requirements about how the tool would function, how orders would be processed technically and as a business process, and how the data contained in the tool could be manipulated and viewed.

After the work was approved the ERP vendor developer completed the initial build on the tool. There were a few reviews of progress on this tool which proved to be a positive collaborative process. Once it was testable I worked with the developer to validate the result was working as expected.

Two week sprints were used to load information into the tool and validate against existing orders. It took a few sprints to get enough information into the system and every type of entryway has slightly different parameters that needed to be taken into account.

Interior and Exterior door units use different language within the catalogs handing and jambs for example so there were some types of questions that required slight variations for automation to function. Luckily we hadn an import and export tool that would allow the dataset to be changed in Excel. Often we would have to make the same sort of change across multiple rows of the data and this made things much easier.

I worked with developers on how to add data and resolve minor issues with the vendor as they were encountered. At go-live the automation system only successfully entered about 20% but we worked in two week sprints using the most recent orders to continuously improve the setup.

It was determined a few times that either the retail or internal catalog needed to be changed so those change stories were worked into their respective sprint processes. We maintained both internal and retail catalogs but the retail catalog required about a month delay to the release once submitted. These changes once completed were still subject to their catalogs respective release schedules. This could mean a change to fix automation of a particular type of unit could have to wait months before the in-store update would be in production.

I provided weekly status updates regarding the current pass rate that also detailed the most common error types. This allowed stakeholders to see where the project was but also provided errors for developers to tackle in the next sprint.

Nearly a year and a half later we hit the 50% mark and completed the first major milestone of this project. The project remained ongoing as the final goal is to get the pass rate to as close to 100% as possible.

Technical Details

The automation used a data set that mapped a question answer pair in the internal catalog to one or more question answer pair found in the EDI of the order.

All entryway types (Exterior, Interior, and Commercial) and configuration types (Single, Double, etc.) lived in the same data set. This is important to consider as additional rows in the data would be required if the types or configurations overlapped or had competing possible answers.

Each retailer returned slightly different results in their EDI so we started by maintaining three unique datasets for the mapping. Eventually they all ended up using very similar data only requiring a few extra lines for one specific retailer.

Deliverables

The deliverable of the project were the tool within the ERP and the data used to map questions and answers from the order received from the electronic catalog and those contained within the product configurator in the ERP.

Ongoing

Post the initial 50% milestone we continued to update the mapping ofcourse but the main push was to further standardize the catalogs both internal and external.

There were slight differences in sizing between the internal and external catalogs so I worked with operations to confirm sizes and work any changes into upcoming releases. Slight differences in sizes can cause false positives in the automation reporting where it incorrectly sets a custom size. I believe most of the differences were within acceptable tolerances which is why they had not come up earlier. Sizing is incredibly important and the catalogs needed to have the right values.

There is a real possibility that a line on an order is automated but it is incorrect. This required customer service to alert us to when this is found and these defects were prioritized and added to upcoming sprints.

In the last few status reports the pass rate on order lines was hovering around 80%. Really would have liked to address more issues in the catalogs themselves which were preventing us from having a higher pass rate. The limitation here came down to priorities and the sharing of resources.

Lessons Learned

I think we did a good job of prioritizing high volume order types but we did not have order types without an internal catalog on our roadmap. These types of orders would always fail since customer service was building the BOM (Bill of Material) on their own. Building these missing pieces would require an operational standard and new sections of the internal catalog to be built.

Another item that was not accounted for is product offered in a retail environment that was not in the internal catalog. Automating orders we received really helped us call into question any instance where the catalogs did not agree. This allowed for some realignment of the product offering.

Initial metrics were written around full orders passing but after we got into it I think it made more sense to talk about lines passing. A single line on a twenty line order would cause it to fail for this metric which does not seem like an accurate representation.

Additionally, I would have been more granular with the metrics and prioritized greater pass rates by product type. Edge cases were really dragging our numbers down for the entire length of the project. If half of your orders are of one type and configuration with a pass rate of over 80% but you report an overall pass rate of less than 50% that is a confusing metric.

Lastly, the project failed to add a tool to help us test the impact of changes to the retail catalog on automation. The team needed to generate orders from the retail catalog's test environment and confirm these would flow into the ERP system. I did end up creating a Python tool to convert text into order format but this came far to late in the project. We shipped too many changes that required mapping updates post go-live. This could have been avoided if this integration testing was performed prior to release.

This is day 28 of 100 Days To Offload.