How I Schedule Bulk orders in 2 minutes on Amazon

by

As a seller we have to schedule orders when they are ready to dispatch. At Amazon marketplace, there are several options for scheduling Easy Ship orders. Either they can be done manually or by bulk upload. In bulk upload a tab delimited file can be uploaded with required details like order id, SKU, date, time, weight and dimensions.

If there are few orders only say upto 40 then manual can also be used (manual can be used for even more orders but it would be time taking). When items are of different types i.e. having variable weight and shipping dimensions then manual take more time as seller has to check for each item what is weight and dimension. In manual, seller can create templates which can be used to provide item dimensions.

When items are different and more then bulk scheduling can come handy. There may be some easy to go tools available in the market which might be able to schedule orders easily and quickly but here I will be sharing tips on using Excel to schedule orders in bulk.

Seller should get the order list for unscheduled orders. Copy the list to excel tab (order_list). Sort the list by Promise Date, Order Id.

Two approaches can be used for Excel, the Simpler one and Advanced one.

Simpler one will involve some manual work also. With this multiple item orders and multiple quantity orders would require more manual effort.

  1. Create a tab (items) in excel
  2. Fill this with list of products that you sell
  3. Make columns as Item Id, Variant Code, Name, Weight, Dimensions
  4. Copy Order Ids from tab order_list as per Promise Date you want to schedule and paste in a new tab (schedule)
  5. Copy SKUs from tab order_list and paste in schedule tab
  6. Copy Promise Date (Remove Time from it) and past in schedule tab
  7. Provide Time Slot in schedule tab
  8. Use vlookup for SKU in schedule tab to get weight and dimensions from items tab
  9. Save file as tab delimited
  10. Upload to Amazon
  11. Check for processing status

Advanced bulk scheduling using Excel will do most stuff automatically and take care of many things. Before starting with the process of advanced scheduling, highlighting some features of it.

  • Days and date based scheduling can be done. Days based means how many days in advance to be considered. If today is 6th then how many days (based on Promise Date) of orders to be considered i.e. till 7th, 8th or more. Date based means (in the Days range provided) which particular promise date to be considered or all dates to be considered (when left blank)
  • SKUs can be marked as Skipped. Can be used when a SKU has to be scheduled later or manually
  • Multi-Quantity, Multi-item orders can be handled to some level
  • Order scheduling slip will have number of line items, unit pieces information to cross check and avoid mistakes
  • Orders scheduled in sequence of Date, SKU, Quantity so that orders with same SKU come together as per date. Packers can pack based on date and would be easy to pack when same SKUs are one after other
  • Item dimension and weight mismatch checking
  • Select order package dimension on the go. It is useful when order is multi-item, multi-quantity and package dimensions are not defined
  • Item count report, this report contains which item how many pieces would be required for packing. Packer can fetch required quantity of an item together
  • Order piece count report, this report contains information on how many pieces will go in an order. Packer can cross check and avoid mistakes
  • Buyer piece count report, this report shows if total pieces for a buyer is high, quantity of a particular item is high, how many orders/items buyer has returned recently
  • Buyer duplicate order report, this report shows if buyer has placed multiple orders for same item (in such a case one or more orders are generally returned as buyer might have ordered in error)
  • Advance days order packing can be done. As per new policies, orders can be scheduled only till one day before of promise date. It happens that for some promise dates number of orders are less and for other dates they are more. As orders can be scheduled only upto 1-day before this causes burden for packers. With advance days order packing, packers can pack orders in advance (without a schedule slip, as schedule slip will generate only 1 day before) by packing the orders from list and marking them with a sequence number. These sequence numbers will come on scheduling slips when the orders are scheduled. Packer will check sequence number on Scheduling slip and paste on matching sequenced parcel
  • Add SKUs/Items on the go. No need to add all the items on first use. Add item on the go when you receive order for first time. It will shows SKUs with issues like SKU not present, dimensions not available etc.
  • Sequencing of order scheduling is done based on number of pieces that has to go in an order. It makes it easy to identify and process orders having more pieces (this helps in avoiding mistakes as sometime packers send less pieces instead of actual to be send)
  • It can be checked if an order already considered/scheduled or not upto some level. It is useful when packing orders in advance
  • Define simple packs of an item with with same MPN Code and Name. It is useful for item identification while packing and getting right number of pieces for an item

Four excel files are being used for this (can be merged into one also)

  • 1st excel is for item management. Item name, dimension and other details are mentioned. This is kept as a separate file as it acts as item master and can be used to refer for other utilities
  • 2nd excel has the current order data and reports like item piece, buyer piece, buyer sku
  • 3rd excel is for final scheduling, order identifier list, order checking and order piece report
  • 4th excel has list of return orders

With this advance excel tool, we were able to:

  • reduce effort for scheduling
  • reduce errors in scheduling (package dimensions)
  • prevented missing of scheduled order files (packaging staff sometimes didn’t download the file or didn’t print after downloading and therefore didn’t know that orders are missing for packing)
  • packing speed increased (by having SKUs in sequence)
  • reduce error in sending wrong number of pieces
  • reduce processing duplicate orders of buyer (raise ticket for suspicious order)
  • identify buyer with return history (raise ticket for suspicious order)

Steps to be done

  • Request Unshipped Orders Report
  • Download this report (it is a .txt file) – Order List 1
  • Copy content from this file and paste in File 2 (order_amazon) in orders tab in B1
  • Sort data in orders tab based on Promise Date and Order Id
  • In settings tab provide value as 2 against field Number of Days
  • Let the value be blank for field Date
  • Go to pending tab and check if any records are having values in red
  • Copy SKU of such records and paste them in variant tab of File 1 (item_listing) in Column B. If pieces column (in pending tab) has a non-error value it means the SKU already exists in variant tab in item_listing
  • Copy these new Variant SKU values and paste in Item Id (parent item) column and MPN (manufacturer part number) column. Edit values in Item Id and MPN columns are per requirement
  • Sort variant tab based on Item Id and Variant (so that variants are together)
  • Fill details like Unit Set (optional), Set, Pack, Unit Count, Weight, Short Name, Item Type, Pkg Dim Item, MPN Name for each record in Variant tab
  • In pkg_dim_suggest tab define weight based Package Name (it is used when total order weight is more due to more items in order)
  • Add Package Name used in pkg_dim_suggest tab to pkg_dim_list tab with all details
  • Add more records in pkg_dim_list tab as per requirement based on products (it is used in variant tab to select a package dimension for an item)
  • After filling in details for variant tab, check in pending tab that there should be no records for Pick Date for which you are scheduling (there may be records in this tab but with with future Pick Date)
  • In Skip column (in variant tab), you can put value as 1 against an item if you want to skip it. Keep it blank if item is fine
  • Go to schedule tab and see if any record values are highlighted in red (for errors). If there are any such records then these could be due to high weight (which is not defined in pkg_dim_suggest tab). Define such package names as required
  • In column Volumetric Status of schedule tab, some record may have values. It shows that there is difference between Item Weight Bracket and calculated Volumetric Weight Bracket. You can ignore it if you feel it is all ok (it is just an alert)
  • In column A (pkg_dim_override), you can select package dimension to override auto populated valued (it is useful in multi-item orders)
  • Refresh pivot in item_pc, buyer_pcs, buyer_sku. Select data and print as PDF (selected data) of these 3 tabs
  • Copy data from schedule tab (column C to column M) and paste in input_list tab of File 3 (order_amazon_schedule)
  • Sort data in tab input_list based on pickup_date, lines, sku
  • Remove Blank records from Top
  • Copy data from column I to column K and paste in order_identity tab in columns B to D
  • check in column E (order count) if any value is more than 1 (it checks if order already considered)
  • copy data from column A to column H (input_list) and paste in amazon_orders_schedule tab in columns A to H
  • Select data in tab order_pc and print selected data as PDF
  • Save file
  • Go to tab order_amazon_schedule and save file as tab delimited (upload this saved file in Amazon Upload Order Related Files)
  • order_chk tab is used to check if orders already considered (useful when preparing orders for advance days). Copy Order List 1 data in order_chk tab in column B1. Column A will show status if order already exists in order_identity tab. Remove records from order_chk tab where status is record exists (i.e. order id is shown)
  • Copy pending records and paste in orders tab of order_amazon file (this step takes only those orders which have not been considered yet)

Leave a Comment