Importing/Exporting Shipping Rates Spreadsheet

10 min read

Welcome #

These guidelines have been written for a training context in which a training manual will be the main delivery source for the educational experience.

Helpful Background #

This manual is written for an audience who may or may not be familiar with the Avetti Commerce software. You will be guided step by step on how to operate the software even if you are not comfortable with computers and how software tools are used. Understanding the Avetti Commerce Store Administration pages is very helpful knowledge to have prior to reading this manual.

Overview #

This document will show the steps of importing and exporting specific spreadsheets to different parts of the page, this document will show how each spreadsheet is used, and how each spreadsheet works and their purpose with the admin pages.

Shipping Rates #

The whole process of Shipping configuration can be done by importing values from a spreadsheet. Go to Settings | Shipping Rates and click the Export button at the bottom. On the status page, use the download link to download the Shipping.xls file locally on the computer hard drive.

Fig 1 – Importing Shipping Data using Spreadsheets

The first tab is used to define provider information such as provider code, Provider name, courier used by the provider, currency used by the provider, Offer code if exists, Weight type (Pounds, Kilograms), Markup, Handling and so on. Markup and handling are used for providers that use couriers that do not have a flat rate shipping option. In this case, the courier company will fix a base rate. And the mark up % can be set. So if courier rate is $10 and the mark up % is 0.1, total shipping charge would be $11.So $1 is the profit. Handling cost can be added to get more profit out of shipping. In the previous scenario, if handling is set to $2, total shipping charge would be $13. so the profit is $1 from mark up and $2 from handling. Column Option 8 is used to specify types of customers for which the provider will be active, such as business invoiceable, business non invoiceable and so on. Column Available should be set to YES to make the provider Available and Default can be YES to be the default provider.

Fig 2 – Importing Shipping Data using Spreadsheets

The second tab is used to define the shipping groups available and which one of them would be the default group.

Fig 3 – Importing Shipping Data using Spreadsheets

The third tab then associates shipping providers with shipping groups. Use the first column to input Shipping group name and the second column to specify a provider in that group. Position determines the order in which the providers will be displayed within the group. Set default to YES if the provider should be set to the default provider in the group.

Fig 4 – Importing Shipping Data using Spreadsheets

The Method tab is used to define all the shipping methods available. Use the shipping provider code column to specify what provider this method belongs to. Option 1 column has been deprecated. Option 2 column specifies the Prompt text. This is what the shopper will see on the store when he selects this method. Position sets the position among other methods on the store. The algorithm tab was included to define custom calculation methods. However, this feature has been deprecated.

Fig 5 – Importing Shipping Data using Spreadsheets

Rates tab is used to define all the flat rates on the store. Use the first 2 columns to specify what provider and method this rate belongs to. The remaining entries are as in the ‘Add/Edit Shipping rates’ admin page.

Fig 6 – Importing Shipping Data using Spreadsheets

Use the Courier package tab to define what packages are available to a courier. Input values the same as the Manage Courier Package page.

Fig 7 – Importing Shipping Data using Spreadsheets

The Shipping Package tab is used to associate packages with Shipping Providers. Property name and Property value columns can be used to set this package to be used only for shipping items with a particular property value ( Eg: Blue T-shirts).

Fig 8 – Importing Shipping Data using Spreadsheets

Use the Shipping From tab to input Warehouse details same as the Manage Warehouse Location admin page.

Fig 9 – Importing Shipping Data using Spreadsheets

The Ship To Tab is used to input information as on the Manage Ship To rules admin page.

*Note: When defining multiple ship to rules for the same warehouse, create multiple entries with the same Warehouse code.

;

Fig 10 – Importing Shipping Data using Spreadsheets

Use the Ship to Exceptions tab to define shipping exceptions are the same as the Manage Ship to Exceptions admin page.

Item Properties #

To export all item propertys into a spreadsheet, simple click the Export button located at the button page of the Settings | item Properties.

Fig 11 – Export Button

Once exported the spreadsheet will contain information like the figure below.

Fig 12 – Item property spreadsheet

To create a spreadsheet for an item property the spread will contain 5 columns:

Action: The action of which each new item property will be added but if the property already exists then instead edit that item property.

Category: The category said item property belongs to.

Facets Name/Code: The unique identifier of the item property this is how the property will be search for.

Facets Type: The type of item property, Property or Opition (each explained in Setting up Item Options and Creating Facets section of this document)

Postion: The position the item property is and in what order each property will be called for.

Once the spreadsheet as either been edited or created, click the Import button found on the Settings | Item Properties page to import the spreadsheet to the site.

Fig 13 – Import Button

Export/Import Customers #

To export customers from the Manage Customers page click the Export button if the customers were already exported recently then click the Status button to view recent exports and download the export you want by clicking the download link.

Fig 14 – Import/Export button

Once the Export button or the Download link is clicked the exportstomer.xls will download:

The spreadsheet is sections in 4 different sections, Customers, Address, Properties, Property Definitions:

Customer #

The first section of the spreadsheet is Customer inside there will be the basic information of the customer:

Fig 15 – Customer Spreadsheet customers

The first section is split into 14 different columns:

Action: The action the spreadsheet will take add if the customer doesnt exsit, edit if the customer does.

Login Name: The name the customer will use to log in

Login Password: The secert password used by the customer *Note this password uses a format to hide password from anyone viewing the spreadsheet – How does this work on a spreadsheet

Customer: Leave blank ?

Active: If the customer account is active, meaning if the customer account can be used or not.

Shopper Group: If the customer account belongs to a specfic group, like employee customers group, here is where selected group will be put.

First Name: The first name of the customer

Middle Name: The middle name of the customer

Last Name: The last name of the customer

Address Nick Name: The address nick name, this is used to search for the address with in the manage customers page

Hint Question: The question used for secondary authentication

Hint Answer: The answer to the hint question

*Note secondary authentication wont be active unless changes are made to template

Regist Date: The date the customer was registered

Email Address: The email address of the customer helpful for emails to be sent to the account or the account password was forgotten and new one must be created.

Address #

Fig 16 – Address 1/3

In first third of the second section of the page is the address. Split into 30 diffeernt coloumns the first 11 will be the following:

Login Name: The login name of the account connected to the address being created

Address Type: The type of the address being created Billing Address( If the customer buys an item this address is where the bill is sent) , Customer Address (Address of customer) , Shipping Address (The address to where the products are shipped) , Other Address ( is other addresses). * Note not all address are needed.

Phone Area Code: Area code of phone number, this isnt required

Phone: Phone number of the customer.

Phone Ext: Ext of the phone number if one is there

Day Phone Area Code: Area code of the phone commonly used in the day.

Day Phone: Phone number that is commonly used in the day more then likely a business phone number but not always.

Day Phone Ext: The phone ext of the phone being used daily.

Email Address: The email address of the customer with the address.

Fax: The customer fax number of the fax.

Fax Area Code: Area code of the fax.

Fig 17 – Address 2/3

In the second third of address section, the following 15 columns are explained:

Company: Name of the company related to the address

Company Type: ?

Address 1: The first address.

Address 2: The second address

Address 3: The third address

City: The city of the address

Province: The provice of the address

Another Province: If the province isnt a province or region

Zip/Postal Code: The zip/postal of the address

Country: The country of the address

Salutation: Greetings set for the user when they log in *Note changes must be made to use salutation

First Name: First name of the customer who has this address

Middle Name: Middle name of the customer who has this address

Last Name: Last name of the customer who has this address

Fig 18 – Address 3/3

In the finally part of the address section, the following 4 columns are explained:

P.O. box: The address po box if being used.

Title: The title of this address

Address NickName: The nick name of address to help find the address

Check Default: If this address will be the default for, billing address, shipping address etc.

Properties #

Fig 19 – Properties

The property section of the spreadsheet contains only 3 columns:

For information on Properties go to People | Customer Properties.

Login Name: The name of the customer account that will have selected property attached.

Prop Name: The name of the property being attached to the customer account.

Prop Value: The value of the property that will appear for that property.

Uploading Coupons from File: #

Create a comma separated (.csv) file. To create a list of coupons, they must be in the following format:

Code, Start Date, End Date, Count, Active, C Type, Shopper Group Name,

Code:The promotion coupon code.

Start Date: The start date of the coupon code in yyyy-mm-dd format.

End Date: The end date of the coupon code in yyyy-mm-dd format.

Count:Specify the Quantity of coupon codes, as per the Qty field above.

Active:Enable or disable the coupon code (0 for inactive, 1 for active)

C Type:Specify the type of coupon code (G, S, U, or O):

G: Limited Use Per Shopper Group
S: Store-wide
U: Limited Use per Customer
O: Single Use by Each Customer

Shopper Group Name (optional):Specify the customer shopper group name, if you chose the G: Limited Use Per Shopper Group coupon type.

Example .csv record coupon:

ImportCoupon1,2013-12-22,2013-12-24,10,1,G,Gold Customers,
This is an active coupon that lasts from December 22 to December 24, 2013. The code is ImportCoupon1, and there are only 10 coupons available for the group “Gold Customers”.

Example of the .csv file: