Introduction

This document takes you through the steps required to upload a courier rate sheet to the FreightExchange platform. These steps cover both adding and updating rates - and rates both that are publicly available to brokerage customers and customer's own accounts.


This version is intended for internal use as it goes into more extensive technical detail. An end-user version of this document is also available.

Pre-requisites

To use these instructions you will need the following:

  • Access to the rate management screen as a FreightExchange admin user.

  • A full understanding of how courier rates work, especially regarding the rates you are uploading.

  • Access to Google Sheets or Microsoft Excel.

Steps to complete the rate sheet template

The courier rate sheet template has five tabs which must be completed:


  1. Courier

  2. Rates

  3. Zones

  4. Services

  5. Categories

1. Completing the Courier tab

Here's an example of a completed Courier tab:


Column

Details

Internal E-mail

This address is used to identify the carrier in the system, although almost no messages are sent to it. It must be unique for each carrier. If you are updating rates for an existing carrier you must use the existing email address for that carrier.

Name

This defines the name of the carrier throughout the system. Please ensure this is accurate and correct as it is visible to users in many places.

Fuel Levy

Fuel levy in decimal points. Example above shows 6%. As a further example, a fuel surcharge of 14.5% would be entered as 0.145. It is possible to change the fuel surcharge at any time after uploading.

Vol. Div. (Volumetric Divisor)

Some carriers will provide you with a "cubic" value instead of the divisor. Cubic essentially means how much per m3 of freight will be charged minimally even if it is lighter.


Example: Vol div. 3000. Freight is 100x100x100cm and weighs 200kg. If going by KG rate, it is still going to be charged as a 333kg as the minimum charge for a m3 is 333kg.


Example 2: Vol div. 3000. Freight is 40x40x40cm and weighs 2kg. Going by the formula provided below, the chargeable weight is 22kg (rounded up from 21.33kg).


Cubic   Vol. Div

500 -> 2000

333 -> 3000

250 -> 4000

200 -> 5000

167 -> 6000


Calculation method for vol divisor, trial and error method: (100x100x100) / vol. divisor (3000, 4000 etc)        


Calculation method for chargeable weight:

(L x W x H in cm) / vol divisor = chargeable weight in KG

                                

The answer you get will be equivalent to "cubic". If divided by 3000, it will give you 333 etc.    

Owner

If the rates are for a customer's own account you must enter their company ID in this column. If the rates are intended for brokerage customers to buy (they are publicly available) then leave this column blank.

Currency

The currency code that this carrier sets their rates in. Use the three character code, e.g. AUD, USD.

Alias

The alias name of the courier company being used. Please refer to the table below for the aliases to be used. If there is not an alias for the carrier you are uploading you are unable to proceed.


Carrier Company Name

Alias

CRL ExpressCRL_EXPRESS

TNT

TNT

Toll Express

TOLL_EXPRESS

Toll IPEC

TOLL_IPEC

Toll Priority

TOLL_PRIORITY

Toll Air Express

TOLL_AIR_EXPRESS

Allied Express

ALLIED_EXPRESS_AOE - Interstate Road Express & Pallet services

ALLIED_EXPRESS_AET - Local Courier Normal & Courier VIP services

Northline

NORTHLINE

DXT

DXT

Direct Freight

DIRECT_FREIGHT

Niche

NICHE

FedEx

FEDEX

Australia Post

AUSPOST

Border Express

BORDER_EXPRESS

Couriers Please

COURIERS_PLEASE

Direct Couriers

DIRECT_COURIERS

Fastway

FASTWAY

HiTrans

HITRANS

Startrack

STARTRACK

TFM

TFM

    

2. Completing the Services tab

A carrier can have one or more services which are entered on the services tab. If a carrier has multiple services you should add each one in a separate row.


Not all carriers have multiple services so you will need to understand the proposed rates to see if this is the case or ask the carrier for further details. Whilst it varies between carriers, examples of services might be road, premium etc.


Below is an example of a completed Services tab:

Detailed instructions:

                                       

Column

Details

Internal Name

This is the name by which the service is known to internal (i.e. FreightExchange) users and should be descriptive so that it is easy for users to identify each service. Each service in the upload sheet must have a different internal name.


Must be all in CAPS and also underscores MUST be included.

Display Name

This is the name for the service that will be displayed to customers who can see carrier/service names in their quote results. It must therefore be descriptive to an external user.


If you are updating existing rates for a carrier it is ESSENTIAL that the entries in the display name column match the existing entries as otherwise the upload will fail.

ExternalId

If this service maps to an integrated carrier service, you MUST place the correct CARRIER SERVICE CODE here.

Example 1:

The CARRIER SERVICE CODE for TNT Road Express Service is 76.


Example 2:

The CARRIER SERVICE CODE for Couriers Please 500g, 1kg, 3kg & 5kg satchels are PS0 PS1 PS3 PS5 respectively.


You must obtain the correct codes from the carrier if these are not known.

Origin Country

Two digit "country of origin code". Whilst the column is named origin country it can really be interpreted as the country where the rates are based. For Domestic rates this column indicates the country in whcih the service operates. For Export rates it indicates the country from which the service operates. For Import services it indicates the country to which the service operates.


View the list of country codes.

Service Type

One character code, either:


D=Domestic

I=Import

E=Export

ShipmentType

One character code.

N=Non-Documents

D=Documents.


If left blank will default to N.


This field defines whether the rate applies to a 'documents only' shipment (eg letters) or anything. This is very important for international services since Document shipments undergo a different process and are charged differently.


This column has no use for domestic services and can therefore be set as N for those cases.

PerItemCalculation

Whether the rates apply per consignment or per item. For example, Fastway charges for each item, whereas TNT charges the total of all items.


Generally this will be TRUE if entering an ITEM or PALLET rate, FALSE if entering a KG rate.

Service Category

For brokerage (public) rates this column is used to categorise services from different carriers into a common category. The list of values can be found in TB_EXTERNAL_SERVICE_NAME. Setting the correct value is important to present quote results appropriately to brokerage customers.


For customer BYO rates this column can be populated either from the same table or, if the customer has defined their own service categories, from the values in TB_COMPANY_SERVICE_CATEGORY.

BookingCutOff

This is the carrier cut-off time in HH:MM format. i.e. the time after which the carrier will not accept any new pickups for the day.


Enter 23:59 if unknown.

DeliveryTargetTime

Enter to 17:00. This is the time by which the carrier generally completes deliveries. Itmust be entered in HH:MM format.

PalletQtyDecision

This column is required only services priced on a per pallet basis and specifies how the pallet quantity is determined based on pallet dimensions. For non-pallet service, enter one of the below values as you can't leave this field empty.


The acceptable values for this column are:

  • REJECT: Do not provide a quote

  • ADDITIONAL: Charge a full additional pallet for every oversize part

  • PRORATA: Pro-rate the pallet spaces


This field is very important for services priced on a per pallet basis so please refer to the detailed description below and consult the carrier for the correct value to use.

TimeDistCalculation

The method used to calculate time and distance. This value is required only for services where the rate is calculated by reference to the distance between locations or the time taken to travel between them.


Leave it empty, if it is not a time based service.  

The acceptable values for this column are: 

  • DEPOT_TO_DEPOT,

  • DOOR_TO_DEPOT, 

  • DOOR_TO_DOOR, 

  • DOOR_TO_DOOR_RETURN


This field is very important for services priced on a time or distance basis, so please refer to the detailed description below and consult the carrier for the correct value to use.

TimePrecision

For services where the rate is calculated based on the time taken to travel between locations this is the number of minutes in a block of chargeable time.


For example if a carrier bills per 15 minutes enter 15.


This value is required only for the time-based services. Leave it empty, if it is not a time based service.                  


3. Completing the Zones tab:

As the name suggests, the Zones tab is where we define the list of zones for each service and the locations each contains. This is a very important step in ensuring rates are calculated correctly.


Every carrier will define their own set of zones and should have supplied this information with the rate sheet you received. Typically, if a carrier offers multiple services they will have the same zones, and indeed a carrier's zones will be common to all their customers. This means that you will repeat the zones for each service you added in the Services tab.

It is quite common for a carrier's zones to not cover an entire country, so don't worry if that is the case

Below is an example of a completed Zones tab:


Zones can be uploaded in several ways.


The way shown here is that each postcode has its own line. Duplicate zone names and description are fine as long as postcodes do not duplicate. For e.g. any booking leaving from 4000, 4010 or even 4051 will be considered as under BNE. It is highly recommended to use the same name for both zone name and zone description so you do not mix it up.

Service name MUST be the same as internal name in services tab.


Column

Details

Service Name

This field must match the Internal Name from the Services tab

Zone Name

The carrier defined name for the zone then use this. For example could by Sydney or SYD depending on the carrier.

Zone Description

The carrier defined description for the zone then use this. For example could by Sydney Metro or Sydney. It is quite common to make Zone Name and Zone Description have the same values.

Country Code

2 character country code for the zone. For example, if the zone is is Australia, the CountryCode will be AU.

Initial Post Code

The initial postcode for the zone. For example, if the zone called SYD covers post codes 2000 to 2100, the initial postcode will be 2000

Final Post Code

The final postcode for the zone. For example if the zone called SYD covers post codes 2000 to 2100, the Final Post Code will be 2100

Suburb

This is rarely used. Populate this field when the carrier has suburb specific zones.


How zone lookups work

It is also important to understand that the system finds the zone for a given location using a three stage lookup:

  1. Find a zone where country code matches, the post code is between the initial and final post codes and the suburb matches.

  2. Find a zone where country code matches, the post code is between the initial and final post codes and the suburb is blank.

  3. Find a zone where the country code matches

The lookup stops as soon as it finds a matching record. This logic allows a somewhat common scenario where a main town is in a different zone to more outlying suburbs in the same post code. This is illustrated below:

Service Name

Zone Name

Zone Description

Country Code

Initial Post Code

Final Post Code

Suburb

ROAD

MT_ISA

MOUNT ISA

AU

4825

4825

MOUNT ISA

ROAD

QQ5

QUEENSLAND ZONE 5

AU

4825

4825


Here we are defining a zone called MT_ISA which contains only the suburb called Mount Isa. All other suburbs in post code 4825 will be considered to be in the QQ5 zone.

4. Completing the Rates tab:

The Rates tab is where the actual rates offered by the carrier are added. This sheet uses our own internal representation of rates and will therefore not exactly match how you receive this information from the carrier. Indeed every carrier will present rates in a different way so you must be able to understand the rates that the carrier is presented and then be able to "translate" these into our standard format. 

It, hopefully, goes without saying that accuracy is extremely important when carrying out this work.


Usually rates are provided between two zones, although there may not be a rate between every possible combination of zones. 


It is beyond the scope of this article to provide instructions on how every single carrier sheet is translated into our format so it is essential to develop expertise if you are tasked with carrying out this work. Below, details are provided on each column in our template which will hopefully provide assistance in completing the sheet correctly.


You can upload rates for multiple services in the same file, but you must ensure that the Service Name column is matched to the correct Internal Name value from the services tab for each service as the rates will be most likely be different for each service.


Below is an example of a completed Rates tab:


Detailed Instructions:


Column

Can be left blank?

Details

Service Name

No

Must be identical to the INTERNAL NAME in the services tab.

Origin Zone Name

No

The corresponding zone name in zones tab. E.g. BNE. Must match the Zone Name in the Zones tab

Destination Zone Name

No

The corresponding zone name in zones tab. E.g. BNE. Must match the Zone Name in the Zones tab

Unit of Measure

No

Unit of Measure used to calculate rates. The acceptable values for this column are

  • KG

  • ITEM

  • PALL

  • VOLUME

  • TIME

  • DISTKM

Basic Rate

No

Basic rate charged for consignment. This should be provided by the carrier and maybe referred to as a Consignment Fee, Basic Rate or Basic Charge.

Basic Quantity

No

This indicates the number of "units" that the Basic Rate includes and should be included in the carrier rate sheet. For KG (weight) based rates it is common for the basic rate to either include a number of kilos or none so you would enter either the number of kilos or zero as applicable. The carrier may also refer to this as the "first X kgs".


Some examples:

The rate is $14.50 for the first 20kg - enter 20 in this column.

The consignment rate is $8.50 - enter 0 in this column.

The rate is $12.90 up to 20kg - enter 20 in this column.

Supplementary Basic Rate

Yes

If the carrier charges a different basic rate for items after the first enter it in this column. This is not a commonly used method and typically applies only to carriers who charge per item.

Supplementary Basic Quantity

Yes

The number of units included in the supplementary basic rate - this should be completed using the same logic as the Basic Quantity column.

Additional Rate

No

This is the rate that applies per unit after the Basic Quantity amount. This would typically be referred to as the per kilo or per pallet rate by a carrier, or shown as the additional rate.


Some examples:

The rate is $8.50 plus $0.85 per kilo - enter 0.85 in this column.

Additional Quantity

No

The number of units covered by each Additional Rate. This is most commonly 1 but could vary.


Taking the example of $8.50 plus $0.85 per kilo again, you would enter 1 in this column.

Min Price

No

The minimum price that can be charged. Not all carriers will have a minimum charge and this should not be confused with the Basic Rate. Some carriers will specify a basic charge plus an additional rate and then also have a minimum price. In this case the rate cannot be lower than the minimum price. In other words if the result of the usual rate calculation is less than the minimum price then the minimum price will apply.


If the carrier does not specify a minimum price you should put zero in this column as it cannot be left blank.

Max Quantity

No

Maximum units that this rate applies to. If the carrier does not specify a maximum then it is acceptable to default to 22 for pallets and 99999 for KG rates.


This column is particularly important where a carrier has tiers (or weight breaks) in their rates. This scenario allows a carrier to (for example) have a different per kilo rate for heavier shipments. You will also end up with multiple rows with the same origin and destination zones but different values in Max Quantity.


A weight based example:

$8.50 plus $1.12 per kilo up to 300kg

$8.50 plus $1.07 per kilo over 300kg

You would have two rows with the same origin and destination zones. In the first row, Additional Rate would be 1.12 and Max Quantity would be 300. In the second row, Additional Rate would be 1.07 and Max Quantity would be 99999. In both rows, Basic Quantity would be 8.50.


A pallet rate example:


1-3 pallets $100 per pallet

3-5 pallets $90 per pallet

5+ pallets $80 per pallet


The max quantity should be 3, 5 and 22 respectively. If no maximum number of pallets is specificed by the carrier you will need to use your judgement when setting the upper limit. As an example, a standard semi-trailer can accommodate 22 pallets so this might be a suitable limit.

Valid From

No

Put today's date (i.e. the day you are uploading). This way, tech can expire any old rates for yesterday's date

Valid Until

No

Put an expiry date far away, if uploading rates in 2020, put 31/12/2021.

Max Length

No

Enter the maximum allowable length for freight as specified by the carrier, in centimetres. Not all carriers will specify this information in which case you should use your judgement based on the general profile the carrier handles.


For example if a carrier will handle most types of freight you might enter 640. However if they specialise in smaller cartons, then 150 might be a more sensible value.


If the carrier handles pallets exclusively you will enter the same value in this column and Max Pallet Length.

Max Width

No

Enter the maximum allowable width for freight as specified by the carrier, in centimetres. Not all carriers will specify this information in which case you should use your judgement based on the general profile the carrier handles.


For example if a carrier will handle most types of freight you might enter 240. However if they specialise in smaller cartons, then 150 might be a more sensible value.


If the carrier handles pallets exclusively you will enter the same value in this column and Max Pallet Width.

Max Height

No

Enter the maximum allowable height for freight as specified by the carrier, in centimetres. Not all carriers will specify this information in which case you should use your judgement based on the general profile the carrier handles.


For example if a carrier will handle most types of freight you might enter 240. However if they specialise in smaller cartons, then 150 might be a more sensible value.


If the carrier handles pallets exclusively you will enter the same value in this column and Max Pallet Height.

Max Pallet Weight

No

Maximum pallet weight accepted by the carrier. Applies only to pallet rates. You must get this information from the carrier. Different carriers have different restrictions but rarely above 1000kg.

Max Pallet Length

No

Maximum Pallet Length accepted by the Couier. Must be provided by carrier in cms. Standard pallet dims are 120x120cm.

Max Pallet Width

No

Maximum Pallet Width accepted by the Courier. Must be provided by the carrier

Max Pallet Height

No

Maximum Pallet Height accepted by the Courier. Must be provided by the carrier

Max Weight

No

Maximum CONSIGNMENT weight, can be used where there is a maximum weight per consignment for units of measure other than KG

Max Item Weight

No

Maximum weight of any ITEM that will be accepted on this rate. Currently only used for TIME and DISTKM rates.

Max Item Volume

No

Maximum volume in M3 of any ITEM that will be accepted on this rate.

Apply based on actual weight

No

Indicates whether the Max Item Weight field is based on the actual or chargeable weight. Set to TRUE if it is based on the actual weight.

5. Completing the Category tab:

The Category tab allows you to link the rates you are uploading to specific types (or categories) of freight. If the rates you are loading are applicable to general freight then there is no need to complete this tab, but it must still be present with the header row.


If you are limiting the rates to specific categories it is important to note that only customers who have the show_load_categories field in the TB_COMPANY table set to true.



Column

Details

Service

Must be identical to the INTERNAL SERVICE name in the Services tab.

Category

Check short description for category below.

Subcategory

Check short description for sub-category below.


Category

Category Short Description

Sub-category

Sub-category Short Description

Pallets & Packages

PPS

Palletised or Packaged Freight

PPF

Pallets & Packages

PPS

Parcels & Small Packages

PSP

Vehicles

VEH

Vehicles

VEH

Vehicles

VEH

Motorcycles

MCL

Vehicles

VEH

Boats / Yachts

BYS

Haulage

HAU

Heavy Haulage & Machinery

HHM

Haulage

HAU

Building & Industrial Materials

BIM

Removals

REM

House & Office Removals

HOR

Removals

REM

Household Goods

HHG

Containers

CON

Containers

CRS

Livestock

LIV

Livestock

LVS

Bulk

BUL

Bulk Grain & Feed

HGF

Special Handling

SHG

Art

ART

Special Handling

SHG

Electronics

ELC

Refrigerated

REF

Frozen

FRO

Refrigerated

REF

Chilled

CHI

Other Freight

OFS

Hay

OFR

Other Freight

OFS

Bulk

BLK


The data for the table above can be generated using this SQL statement.

SELECT lc.name as `Category`, lc.short_description as `Category Short Description`,

lsc.name as `Sub-category`, lsc.short_description as `Sub-category Short Description`

from TB_LOAD_CATEGORY lc INNER JOIN TB_LOAD_SUB_CATEGORY lsc ON lc.id = lsc.category;