Crackerjack-IT Blog

QuickBooks, CommerceHub and UPS API case study

June 1, 2022 by

Integrating CommerceHub with QuickBooks and UPS to automate process flow

One of our customers came to us with a convoluted, 42-step process to get orders from CommerceHub (CHub) into their ERP (QuickBooks) and at the same time, integrate UPS' Shipping Label API. Anyone who has integrated anything to QuickBooks will tell you that it's a finicky platform to which to write data. Our crack process automation team took up the challenge and turned the 42-step process into an easy-to-use order processing system. The process went from 42 manual steps to 1 manual step. Order shipping went from 1+ week(s) to minutes.

Here's how they did it.

Existing inefficient process

The new automated import process

This customer had zero automation, hence the 42-step process that was in place. Below is the process as it previously existed.
  1. Log into CHub and manually download the orders for the day
  2. Open the packing slip file
  3. Print the PDF of the packing slips (a single PDF with all the packing slips was provided)
  4. Put order file into the “Download” folder
  5. Open order file in Excel
  6. Format the file so the columns are in the correct position for later (you’ll see why down below)
  7. Save the file as a .CSV (Comma-Separated Value) file for archival purposes
  8. Copy all the lines to the clipboard except the header line
  9. Paste the lines into an existing .CSV file that is formatted with extra columns necessary
  10. Set column W to CP and column X to SHP
  11. Update column H to format as a Zip Code (need this for the shipping software)
  12. Add package weights manually in column L (there can be thousands of orders here so imagine the task)
  13. Update column K with the shipping service level necessary to get that product to the customer to fulfill the SLA
  14. Save the .CSV file
  15. Close the original .CSV file and do not save changes
  16. Open all the packing slips saved from step 4
  17. Validate that the packing slips are in the same order as the orders .CSV file that was created in step 9-13
  18. Close the packing slips
  19. Open a pre-defined .CSV file used to upload into UPS Worldship
  20. Alter column G to be MM/DD/CCYY
  21. Save this file as an order export .CSV file in an outbound order location
  22. Delete the header
  23. Save the modified file as an outbound order confirmation .CSV file in a different folder
  24. Open the order export .CSV file that we just created in step 19
  25. Delete the data but leave the header
  26. Save the file
  27. Open the dated order confirmation file
  28. Go back to the CHub order stream site
  29. Select File -> Uploads -> Select the order confirmation file we created in step 23
  30. Click on “Needs Invoicing”
  31. Select all orders on the page
  32. Submit the file and then wait for it complete
  33. Log out of CHub
  34. Open the Invoice file from the CHub download
  35. Minimize this for now
  36. Open the Order Export .CSV file we created
  37. Add a column of sequential line numbers to end of column list
  38. Add a header and footer with the file name
  39. Perform a print preview and change to landscape and set to “Fit on one sheet” and print
  40. Pull the UPS .CSV file into Worldship
  41. Print out all open orders
  42. Hand-collate the packing lists that were printed in step 3 with the outputted shipping labels from Worldship

As you can imagine, this process took quite some time. This customer routinely received order files with over 3000 orders in them. Hand-collating 3000 packing slips and shipping labels was a full-time job and sometimes required additional assistance to complete on time. Shipping orders could take a week or more with that volume.

We presented this customer with a customized version of our OrderIQ software. In addition, we directly integrated with CHub’s SFTP site and built automated scripts to handle file communications as required by CHub. The only step that requires user interaction is step 2. All other steps are either scheduled or occur once the user presses the print button.

The new process now looks like this
  1. Scheduled batch file executes (entire step is scheduled and automated, requiring no user-intervention) performing the following steps:
    • Download XML orders using WinSCP and a SFTP script file
    • Call a custom program that we wrote to:
      • Determine service levels based on state data entered in OrderIQ
      • Assign weights based on data entered in OrderIQ for their parts
      • Import data into QuickBooks as an Invoice
    • Generate an order acknowledgement XML file (in EDI speak this is a 997)
    • Upload the order acknowledgement file to CHub via SFTP with WinSCP and another script file we built
  2. The user logs into OrderIQ – This is the only step that requires human intervention
    • The user sees all the orders that were downloaded and integrated today in one easy to use screen
    • The user checks the checkboxes next to the orders they wish to print and ship
    • The user clicks the print button on the page and waits for the orders to come out on their printer
      • The automation for this step queues the orders up to be picked up by a scheduled process
  3. The scheduled process runs and for each order it uses UPS’ Shipping API to create a shipping label
      • The combination Packing List and Shipping Label is printed out, no longer requiring hand-collation here
      • The order is marked as shipped in OrderIQ
  4. Create the Invoice and ship confirmation files to send to CHub via SFTP using WinSCP
  5. In the evening another automated process kicks off to submit shipped data to CHub as invoices:
      • Orders shipped today that haven’t been marked as invoiced and pulled and an invoice file is generated and sent to CHub via SFTP
      • The order is marked as Invoiced in OrderIQ so that it will no longer pull up
Conclusion

The customer can now process thousands of orders per day with just a single selection and click of a button.  Gone are the days of hand-collating and processing times of weeks to turn out large order quantities. This is just one example of how our automation team was able to automate multiple moving parts into a single cohesive process.  If you're facing a daunting automation task like this or just want an analysis of how much faster we can make your process, let us know, we're LIVE to improve efficiency for customers.