Advanced Order Fulfillment Tutorial

[ACE99]

Let's face it, most of us love the selling part of ecommerce, but we hate order fulfillment. Who wants to sit in their backoffice and stuff boxes with products? It's much more fun to sell and market those products IMHO. Afterall, that's why most of us started a website in the first place. We thought we could sell something and be good at it, not because we were shipping experts.

 

Order Fulfillment is one of the most difficult and expensive parts of ecommerce. If your manufacturers ship for you, then you're lucky. However, if you have no other choice but to ship yourself, then I imagine you've run into lots of headaches and problems. Perhaps we can solve some of them here.

 

The following solution increased our order fulfillment speed by 500% or more. What once took our company 5 hours to do can now be done in just 1 hour, and the accuracy has increased while our mistakes have almost disappeared.

 

I'm no operations or order fulfillment expert, but we did a lot of research into the problem and here's what we came up with.

 

I hope you guys can improve upon this system, fill in the gaps, or maybe come up with something even better.

 

BTW, you're going to have to know some coding to do this right. If you know PHP and MySQL, then you'll figure out the hard parts. The rest is cake.

 

 

Here's how it works:

 

 

1.) Install a barcode contribution that adds a barcode to every invoice (and packing slip, if you'd like). The barcode contains the order_id. We used the osBarcode contribution.

 

2.) Install a batch printing contribution that can print several orders at once and change the order statuses for all printed orders to "processing". It works even better if with just one click you can have the program print all new pending orders, and change them all to "processing". We used the "Batch Printing without Frames or PDF" contribution.

 

2.5) Designate a computer in your office or warehouse (or bedroom ;-)) as the "shipping computer station". Make sure it's running Windows XP. On this computer we're going to install the programs mentioned below, a barcode scanner, a UPS scale, and a UPS label printer.

 

3.) For UPS: Install WorldShip 7.0 or 8.0 and set up an ODBC connection with your osC database.

You're going to have to download an ODBC driver for MySQL from the MySQL web site.

 

4.) For USPS: Install Endicia's Galaxy Ship software and set up a database connection with your osC database. Search for Endicia Galaxy Ship in Google.

 

5.) Set up both Galaxy Ship and WorldShip to post back data into a table called "ups_usps_import" (or whatever you want to call it) in the osC database for each shipment, with info like the order_id for the shipment, it's tracking #, price, weight, etc. This is really easy to do. These programs were designed to do this.

 

6.) This is the only difficult part: Write a small program in the osC admin that goes through this data posted from Worldship and Galaxy Ship, and that marks all the orders statuses for those orders as "shipped", and even stores their tracking #'s in the osC database so customers can see them when they view their accounts online!

 

7.) Buy a Symbol LS4000 barcode scanner (a really good scanner, for a good price). As a bonus, buy an "Intellistand" for the scanner that basically holds the sanner so that it is always on and scanning a beam constantly on your desk. Now you won't have to pick up the scanner every time you want to scan an invoice. The scanner is always searching for a barcode, and now you just put your packing slip or invoice barcode under the scanner and cha-ching!

 

8.) Get UPS to subsidize you with a free label printer and a free digital scale (we chose the Toledo scale), which your UPS rep is always happy to do.

 

9.) Connect the barcode scanner and scale and label printer to your "shipping computer" on which you have installed Worldship and Galaxy Ship.

 

Now you've got a high tech shipping dept for about $450, plus the Galaxy Ship monthly fee of about $30.

 

 

 

Now here is typical day in the shipping dept.:

 

1. First, print out your new orders with your nifty batch printing module. Every order invoice/packing slip has a barcode. Every order printed is automatically changed to "processing".

 

Now we've got our stack of orders and packing slips.

 

2. Next, on your shipping computer, double click both Worldship and Galaxy Ship so that they are up and running. Make sure in UPS Worldship that you go to UPS Online Connect > Keyed Import and select the osC database so that you're connected. Galaxy Ship will connect to the osC db immediately when the program starts up. The scale, barcode scanner and printer should be on and hooked up.

 

3. If you've got one employee (you), then they have to pick, pack and ship orders one at a time. If you have two employees, have one of them do the picking and one do the packing. Have he picker prepare the orders with the right products so that all the packer has to do is box them up.

 

4. Once the packer has boxed an order, he or she places that box on the scale and scans the packing slip or invoice (whichever slip you didn't place in the box) with the barcode scanner.

 

Two amazing things will happen:

(1) the weight for the package is immediately put into Galaxy Ship or Worldship, depending on which application you have activated in Windows XP. Galaxy Ship will automatically convert the weight to pounds and ounces for USPS.

(2) The scanned barcode will instantaneously bring up that order's shipping address and data right from the osC database. You never have to touch the keyboard!

 

5. Press enter and a label is printed.

 

When you're done shipping everything, run the little "end of day" program that you created in the osC admin that goes through all of the shipment data that UPS worlship and Galaxy Ship posted back to your osC database in the ups_usps_import table that you created. This little program should scan the table and mark all the orders that were shipped as "shipped" in the orders_status table in osC, email a tracking # to your customer and make this tracking # available from the customer's online account. Sorry, you're going to have to know some coding to do this.

 

 

That's it! Hopefully this will give some of you busy store owners some ideas on how make your shipping easier and a heck of a lot faster.

 

You can set up this whole system in approx. one week. When it's all done you should be able to ship one order with one barcode scan and two mouse clicks. Now we're talking!

[Dreven07]

Ace,

 

What was the exact model of the scanner you bought? Is it this one:

 

Symbol LS 4000i Handheld Scanner (Undecoded, with DB9 Cable)

 

Did you have to do anything other than plug it in and it was recognized by Worldship?

[Dreven07]

Ace,

 

I found the model that would suit us best. Symbol LS4007i Handheld Scanner (USB Interface). I think your shipping system sounds like a great idea. I belive we are going to give it a try.

 

I have one more question. How did you get UPS to give you a free Mettler Toledo Scale? I called them up and they directed me to the MT site where the scale costs $425.

[ACE99]

We got the corded LS4000 and I cant remember the exact #. All I know is that it works together with the intellistand. UPS worldship and galaxy ship should work just fine with this scanner. For UPS you're going to have to ask for a rep to come out and visit with you. Don't call UPS and do this over the phone. You've got to speak with a UPS rep in person.

[radders]

We doubled our fulfillment speed and cut down on errors with a simple set of digital scales ?20, a label printer ?70, the Batch Print Center contribution (free), and Royal Mail Smartstamp (?5 per month). We looked at using a barcode scanner but still not sure what more that could add as we don't use UPS much. Any ideas to further speed this up much appreciated.

 

1) Orders with status 'processing' are printed out once a day and picked / collected from the warehouse.

 

2) The combined invoice and packing slip with shipping and returns labels is attached to the package.

 

3) All packages are weighed and the weight written on the package

 

4) Smartstamp postage stamps are printed using a Dymo 310 labelwriter and affixed to each package

 

5) A list of orders is printed from osc and each package is ticked off on this list as it is put in the sack for the postman to collect.

 

6) Back to batch print center to update the order status to 'dispatched'.

 

Of course what really takes the time is not the sending of the regular orders but any orders that need to be spilt for shipping and particularly those pesky returns and refunds :-)

[ShoppingMantra]

Hi,

Thank you so much for these sugestions.I installed the barcode and its working finr. I installed the Batch Printer also but it was giving errors,I tried with a lot of batch Printing contributions but nothing was fine.

Can you give me the link of a proper and error free batch printing programe.

Thanks a lot

Rishit Khandelwal

[shaunam]

Ace,

 

I found the model that would suit us best. Symbol LS4007i Handheld Scanner (USB Interface). I think your shipping system sounds like a great idea. I belive we are going to give it a try.

 

I have one more question. How did you get UPS to give you a free Mettler Toledo Scale? I called them up and they directed me to the MT site where the scale costs $425.

 

Hi,

 

I'm wondering if you've implemented the process in ACE99's previous post and if you'd be willing share the code from step 6. The part where it updates the orders?

 

Thanks

[Dreven07]

Hi,

 

I'm wondering if you've implemented the process in ACE99's previous post and if you'd be willing share the code from step 6. The part where it updates the orders?

 

Thanks

 

 

Sure, I have it packaged up and ready to go. The install instructions are not as detailed as some and I modifed a CRE Loaded version of osCommerce when I wrote the code so I am not sure how much that will throw you off. The line numbers will definatly be off. If you want it and have any questions just e-mail me at [email protected].

[yosc]

Does anybody use scales to weigh their products not just for shipping - ie products sold by weight, where the gross price is a function of the weight? I'm looking for some digital scales that could be integrated with osc, and any tips / recommendations / how-to's would be appreciated.

 

Ideally, what I would like the system to do is have the item from the order weighted on USB-connected scales, have the weight input into osc, modify order with the exact weight (I have only approximate weights in my system), update the price for the order, and print out a label with product weight / price info (which is essentially what they do in supermarkets, the only addition is to be able to auto-update the order info in osc). does anybody know of any comparable implementations or how hard / labor-intensive it could be to write custom code for that?

 

thanks for any input

[Priest]

6.) This is the only difficult part: Write a small program in the osC admin that goes through this data posted from Worldship and Galaxy Ship, and that marks all the orders statuses for those orders as "shipped", and even stores their tracking #'s in the osC database so customers can see them when they view their accounts online!

 

First off, I want to say THANKS Ace99 for the great tutorial. Next I want to ask if anyone has created the code for the #6 part? This is the only part I'm having trouble with. If anyone has it, or can help out, please let me know!

 

Thanks,

Priest

[ATXsigns]

Wow, that's like a dream come true. Just need code, somebody! :)