Custom application - UPS shipping automation

Another example why sometimes it is a good idea to employ a custom programming regardless of the initial cost of the design and implementation.

Problem assessment.
Our client needed help with automating UPS shipping procedures. One of their partners generated retail orders that were to be shipped via UPS. Each order would be delivered directly to the customer. Because those orders were generated outside of the existing automated system, to process them orders would need to be entered manually into the UPS shipping software.

Solution.

To help automate the order shipping we built a custom application that would load and parse the source data and match the ordered items with an existing database records (item shipping weight was needed to complete the UPS import data). Using parsed records and the linked database catalog information the application creates a batch import file for the UPS shipping software. Because the created batch import file contains all the necessary information for the UPS software to process shipments, the import becomes a one step operation that results in a batch label print. The entire process (from the orders file to the shipping labels) takes 5-10 minutes.

Price tag.

The time required to complete this project was 10 hours of custom programming and testing. At $150.00 per hour the complete project cost: $1500.00

Return on investment.

In this particular case the cost savings came from the labor requirements reduction. On avarage it would take 8-10 labor hours to generate the shipping labels for a batch of 600 to 1000 orders. All of that labor had been reduced to under 10 minutes required to generate the labels with our custom application. The total cost for our client was ($18/h times 6 batches a month) ~$900.00 a month. The cost of developing a custom application had been recovered within the first two months after installation.

Conclusions.
The decision to chose a custom application was relatively easy to make, the cost savings calculation had been based on the hourl labor rated but in fact after installing the application our client experienced reduction in labor overtime that is paid at a higher rate. Using our custom application also provided addional benefits by improving the overall order turn around time and reducing shipping errors. Since the initial install we provided the client with two more updates that enabled them to process batch order (flash-sales) from two more online retailers. Because the processing flow is the same for all of orders, regardless of the order source, the cost of updating the application was minimal (2-3 hours). To add aditional order source we only needed to modify the data import specifications and UPS shipping account info.

Data parsing custom application "cdWorksKParser" review and cost analysis

This application is a perfect example why sometimes it is a good idea to employ a custom programming regardless of the initial cost of the design and implementation.

Problem assessment.
Our client turned to us for help with extracting inventory data from a text data file. The file contained lines of inventory data for the products in the client's catalog. Each line consisted of a product ID and multiple columns with inventory info. Sound simple, right? Well the problem was that each line could be about 100 columns long and the list of items was about 40,000 long. Our client needed only the item ID and the inventory info total for each of the items. Manual data extraction was not even an option. In the past the client hired outside data consultant to extract the data and create a report for inventory level updates.

Data sample.
.....................
DAAGAW401028    EA0000015Y0000000N0000000N0000000N0000009Y0000010Y0000000N0000000N0000015Y0000000N0000001Y0000001Y0000000N0.....................
DAAGAW402028    EA0000000N0000000N0000000N0000000N0000002Y0000002Y0000000N0000000N0000004Y0000000N0000015Y0000014Y0000000N0.....................
DAAGDMD13532    EA0000000N0000000N0000000N0000001Y0000000N0000260Y0000000N0000019Y0000150Y0000000N0000001Y0000027Y0000000N0.....................
DAAGDMD14532    EA0000022Y0000000N0000000N0000018Y0000047Y0000304Y0000000N0000012Y0000111Y0000002Y0000014Y0000006Y0000000N0.....................
.....................
x ~40K lines

Solution.

Build a custom application that would load and parse the source data and enable users to save  the resulting info as comma separated values (.csv) file. The output data set consists of two columns of data per item. Average time required to process ~30MB data file - about 20 minutes.

Price tag.

The time required to complete this project was 8 hours of custom programming and testing. At $150.00 per hour the complete project cost: $1200.00

Return on investment.

With the cost of outside consultant at about $300.00 per each data extraction our client limited the inventory reports to a weekly base. The monthly cost of running inventory reports on average between $1000.00 and $1500.00. The custom application investment paid for itself within the first month and the client changed to semi-daily report schedule.

Conclusions.
In this case the decision to chose a custom application was very easy to make and the savings could be easily calculated because of the outside subcontractor being used for data processing. We also found out from the client that the ability to update the inventory information more frequently resulted in fewer order cancellations due to item availability (when the item is reported "out-of-stock" the client's catalog is updated to reflect the status). From our experience we know that whenever there is a data processing step involving manual data manipulation, replacing that step with an automated process (custom or off-the-shelf) results in direct and indirect benefits. Time reduction, error eradication and workflow streamlining.