Valentine’s Day AusPost Express Post

Process for creating Valentine’s Day AusPost Express Post file:

1. Go to orders in Shopify

- Add in delivery date e.g. "14-2-2020", filter on Express Delivery, plus select only unfulfilled and those paid, partially paid, plus partially refunded

- Choose export 50+ orders – this will get sent to [email protected] or email use to log into Shopify

- Save this file in Aus Post e.g. G:\Shared drives\Brewquets\Delivery\Australia Post - BREWQUETS\2020\2. February 2020\Valentine's Day Delivery\EOD 10-02-20 where EOD represents the point you will use the file to e.g. End of Day 10-02-20

2. Open file and delete any orders after end point e.g. if End of Day 10-02-20 then delete orders placed 11-02-20 (you will do these later).

3. Create a new column to the left of Column A ‘Name’ and call it Name also. In cell A2 enter the formula =Concatenate(AQ2,” “,B2) where AQ2 equal shipping state and B2 equals order number e.g. NT #10279. Double click on the bottom right edge of A2 so this drags it down for all cells.

4. Once done copy column A and ctl+alt+v (paste special) into column B choosing values. Delete column A.

5. Insert a column to the right of column R Lineitem name and call this Weight. Sort on Lineitemname.

6. In weight apply weights based on product type e.g. dozen/hampers = 5, long stem =3.

7. Pay attention if Line item is more than 1 as will need to increase the weight e.g. if 2 then double the weight.

8. Sort file on Name A-Z and save.

9. Open AusPost Macro

10. Browse file and select state you want to do and click process

12. Change Parcel type to Express – for first then cntl+c cell and cntrl+v for all other cells changing to Express.

13. Check Delivery comments and make sure these are all ok.

14. Open CSV file used at 10 and copy from weights making sure that the order numbers match-up.

15. Cntrl+alt+v these into Parcel 1 weight choosing paste special values

16. Create Parcel CSV – rename to include the time period it covers.

17. Upload to AusPost and update all invalid addresses as per normally AusPost data.

18. Sort A-Z then select all orders for a specific state and Labels – Save this file, this way you have a label file that is just for that specific state allowing you to focus on just orders for that state.

19. Repeat for each individual state that you are doing a cage for e.g. NSW, QLD, VIC, Other

21. Print using send as file to printer

20. If you are continuing to add to the file unassign once labels have been done and use the filter choosing only those assigned to work out which labels to print each time.

Suggest for future years there is a separate location from Express Brewquets as this means the above can start days earlier rather than waiting for the Express Brewquets location to be empty in AusPost.

Packing

Valentine’s Day – had cages for NSW / VIC / QLD / Other – these were labelled up to be clear where parcels needed to be put.

Focus was on doing one state at a time to reduce any chances of error

Mass Fulfill updates

Prior to using Mass Fulfill for Valentine's Day delivery notifications the delivery email was updated. Updated before setting to shipped at 9am on 13th and removed from delivery email after all orders shipped out:

Valentines Day Deliveries: February 14th deliveries (for postcodes within the Australia Post Express Network) are dispatched on the 13th February using an overnight delivery service.

When getting file ready to be Massfulfilled it will have state + order number e.g. NSW #12345 – the state coding needs to be removed.

To do this add an extra column to the left of order ID.

The use Text to Column filtering on space.

Copy order number and past back into Order ID overwriting state.

This is now ready to be used for Macro with Mass Fulfill.