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.
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. Completing the Courier tab
Here's an example of a completed Courier tab:
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:
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.
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:
Find a zone where country code matches, the post code is between the initial and final post codes and the suburb matches.
Find a zone where country code matches, the post code is between the initial and final post codes and the suburb is blank.
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:
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:
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.
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;