The Final Step (in Automating our Order Processing, that is)

One of the features of our shopping cart that I’ve touted over and over again is that we’ve automated our back-end shipping process.  With a few clicks we can print invoices and shipping labels while the accounting and inventory are being done automatically.  Whether it’s 5 or 50 orders, we can process them (packing excluded of course) in essentially the same amount of time.

In reality though, there was one manual step left in the process:  picking a box size for every order.  FedEx requires a weight and a box size to return a shipping label.  We have each product weighed in the database so passing along a weight wasn’t very hard.  The box size thing is a bit more complicated, so when developing the site we just put a drop-down menu on the shipping page and whomever was processing the orders would do their best to guess at a box size for each and every order.  Needless to say, on a busy Monday morning this could take a while.

Last week I finally got around to tackling the problem.  Like I tend to do for most things, I challenged myself to come up with an accurate solution by doing as little work as possible.  Here’s what I decided to do:

• Input into the database the length, width, height and volume (L x W x H) of every box that we have in stock, which is something like 25 different variations.
• Record the same dimensions for every product that we offer, treating every product like a rectangle (thus simplifying things a whole lot).  So for example, a bottle with a diameter of 3″ and height of 9″ would simply be treated as a 9″ x 3″ x 3″ cube.
• When entering both, enter the length as the largest dimension, the width as the second largest, and the height as the third largest.
• Have the system determine length, width, height, and volume constraints:
  • It takes the largest L/W/H of the items in the package and makes those the constraints, which is why inputting them in order is important.  Take for example that same 9″ x 3″ x 3″ bottle and a 4″ x 4″ x 1″ applicator pad.  The constraining dimensions would be that the box has to be at least 9″ x 4″ x 3″.  If the dimensions weren’t in decreasing order, this wouldn’t be consistent and would’ve made the programming more complicated.
  • It adds up all of the volumes of every item in the package to determine the volume constraint.
• It picks the smallest box that is larger than the L/W/H constraints and has a total volume that is greater than the volume constraint.

Here’s a visual example:

In this instance, the wax measures 5.25″ x 5.25″ x 4″ for a volume of 110.25 cubic inches.  Our smallest box is 10″ x 4″ x 4″.  That has a volume of 160 cubic inches, but since the width dimension of the product is greater than the width of the box, the system bumps the product up to the next box, which is the 6″ x 6″ x 6″ one shown with a volume of 216 cubic inches.

Pretty sweet huh?  The whole thing only took me a few days to implement, including measuring all of the several hundred products we sell (which I did twice because my dumb ass shift+deleted the original spreadsheet prior to uploading it to Google Docs.  My friend Paul got me doing this in college, absolute worst habit ever and it’s so hard to break).

To be quite honest, these are the types of projects that really pump me up.  They take a little bit of my engineering problem solving skills and a little bit of my programming skills, and they result in a quantifiable gain for the company (in this case, time).  The system is already more accurate than we were and will save us anywhere from 10 minutes to 30 minutes a day.  For a small company like us, cutting out a relatively large step in an already well-refined process is a big deal.