Extensions‎ > ‎Product Matrix‎ > ‎

Productmatrix CSV Creation

Overview


This section covers the definition of a Productmatrix csv file, and runs through an example scenario to show how to create a relatively complex csv configuration.

 



 

Important Information about CSV manipulation


 
You need to use a template to work your rates into - download the template here. We recommend using a spreadsheet program such as Open Office or Excel to manipulate the rules.
 
Some key notes on using CSV files:
  • Do not change the order of the columns

  • Any value left empty should be an asterisk '*' which can mean anything.
    E.g. Price from * to 10 means from anything to 10. Country * means anything

  • A CSV should be delimited by commas, strings enclosed by quotes. Maintain the CSV format when you save

  • If on a Mac save as Windows file type

  • Country codes are in ISO-3166 format- click here for reference

  • Use Region/State codes for USA for example NY for New York - click here for reference

  • Upload the csv file by changing website scope under the shipping method configuration
If you are having issues with configuring your CSV file please also see the CSV Configuration and Troubleshooting Guide to diagnose.
 
Before you write your csv file it's really important you understand your requirements and what you are going to achieve.
 
Write your rules in pseudo code and try to break them down to the simplest form.
 
 

CSV Table Definition


 
The following columns are used in Productmatrix, follow the links on the field name to get further information:
 
Field Name Description
Country ISO3 Country code this rate applies to. Use ‘*’ for all. Multiple countries can be present on a single row (e.g. "GBR, FRA")

Region/State Destination State – use * for all, e.g. CA

City Destination City (generally not used) – use * for all

Zip/Postal code From Destination zip code. If using numeric ranges then set this to the number you wish the search to start from. If using pattern matching then set this to the pattern you require. See pattern matching for details

Zip/Postal code To Only used if you are searching for purely numeric postcode
ranges. Please ensure that “Use Zip code to/from range” is set, otherwise this column is ignored

Shipping Group Can be assigned to 1 or more products. Identifies the shipping rules for those products

Weight > Minimum weight that must be greater than of combined products in cart with the package id above

Weight <= Maximum weight of combined products in cart with package id above

Price > Minimum price that must be greater than of combined products in cart with the package id above

Price <= Maximum total price of products of package id identified above

Qty/Item >

Minimum quantity of items that must be greater than of combined products in cart with package id above.



 

Qty/Item <=

Maximum number of combined products in cart assigned to shipping group above.

For example you can say I'd like upto 10 BIKES to have price X, over 10 to have price Y

Customer Group Used to filter dependent on customer group at checkout (e.g. General/Retailer). If you do not need to change shipping prices based on customer group use * for this field.
Shipping Price Price of shipping

Algorithm Adds a lot of functionality to Productmatrix. See below for options on usage.  Unless you are doing something complex you can often leave this column empty.

Delivery Type

This is the label that will tell the end user the shipping
option, e.g. 1st Class, Courier, UPS Ground.

When multiple shipping groups are in the cart then the prices are combined based on the Delivery Type field. If unable to match delivery types across shipping groups then the rate is discarded.  See section on Delivery Types for more information and examples.

Notes Any notes you may wish to add. Best practice to add * if it's empty


Algorithm Column


 
The algorithm column allows you to specify advanced options around csv file management.  Before you start using these please take a look at the examples.
 
 Switch  Summary  Description  Syntax & Example  Further info

w=

Weight based price increase

Adds the specified amount for every unit of weight above the weight from field.

W=<weight threshold> @ <price>

 to add $1.50 per 1lb use: W=1@1.5

 surcharge for 0.5lb would be 0.5 * 1.5 = 0.75

Example

wc=

Weight rounded to the next highest full measurement.

Adds the specified amount for every unit to the next rounded to the next whole number of weight above the weight from field.

WC=<weight threshold> @ <price>

 to add $1.50 per 1ln use: WC=1@1.5

 surcharge for 0.5lb would be 1 * 1.5 = 1.5

Example

i=

Unit based price increase

Used to specify an additional per item rate. 

I=<price> Increase per unit added

E.g. to add $5 for every item added use: I=5

Example

im=

Quantity based price increase

Adds the specified amount for every unit of weight above the item from field.  

Im=<item threshold> @ <price>

to add $1.5 for every item use: im=1@1.5

Example

%=

Calculate shipping as a Percentage surcharge of the cart subtotal

To base the shipping rate on a percentage of the shopping cart subtotal.

You are also able to set a minimum charge for the shipping with this mechanism

%=percentage to charge

E.g. to set the shipping cost to 10% of the cart subtotal you would type:

%=10

To add a base amount on the percentage use the '+' symbol

E.g. %=10+20 means add 10% of shipping group cost + $20

Example

a=

Used for matching on residential/commercial addresses.

Unless you have a specific requirement for this please ignore.

Used in collaboration with the WebShopApps Residential Address extension.

Will filter rates according to if customer selects residential or commercial address

a=<address type> where address type can be either 'residential' or 'commercial'

e.g. a=residential will only show as a rate if the address type of the customer is residential. 

CSV Example

c=

Store a unique shipping method code in the database.

Unless you have a specific requirement for this please ignore.

Used to store a unique shipping code in the database for use with order/delivery management solutions.

Stored as field 'shipping_method' in sales_order/sales_flat_order.

You can also retrieve this value via the Magento API.

c=FDXGND

CSV Example


m=

Set the maximum shipping price.

 This is used to set the maximum shipping price. Could be used if you have multiple sets of post codes and multiple shipping groups.

m=99.99  CSV Example

instock=true

or

instock=false

Check if all items are in stock/out of stock. Unless you have a specific requirement for this please ignore.

Unless you have a specific requirement for this please ignore.

You could use this to offer express shipping if all items are in stock. Alternatively use it to offer delayed delivery if items are out of stock.

instock=true checks if all items are in stock

instock=false checks if there's any item that is out of stock.

CSV Example


alt=

Use alternative method when two or more items are in the cart.


If there is no common delivery method between two items in the cart then use the delivery method specified in the alt= algorithm.


alt=Standard

To use multiple alt's in one command line, use a comma delineated list

eg. alt=Standard,Ground,Worldwide

CSV Example


SHOWALL

Shows the method for all shipping groups even if not defined for that group.


If you wanted to offer collection for all items when product A is in the cart, you could use this algorithm. It will NOT work if a method has been explicitly excluded using "-1"

SHOWALL

CSV Example

setcart=true    Allows for a set price for all matching items    If there are products in the cart that match the criteria of this rule then the price will always be set at a fixed rate, this fixed price will not increase or decrease depending on the number of products in the cart. For example if 9 products are in the cart the price will still be set at the fixed rate for thre total cart. setcart=true CSV Example
 tracker=Allows you to specify a tracker number for a specific shipping method.
Used in collaboration with the Generic Tracker.

If you want to add a tracking number for a particular shipping method then this algorithm will link the shipping method with a Tracker.

Then in orders>shipments you can then select the shipping method and add a tracking number.


 tracker=tracker1 CSV Example
 
To use multiple algorithms use an ampersand '&' e.g. i=10&wc=1@0.5




CSV Example Walkthrough


We will go through a running example demonstrating different features of Product Matrix and how to implement different scenarios.

Note
: Any columns omitted are either empty or unimportant to the example shown, you will still need to have all columns present in your CSV file.
 
There are additional examples under the Examples section of Producmatrix.
 


Additional Cost Per Weight Threshold


 
Requirement 1: Provide Freight Delivery for any bulky product shipping to the USA costing $20 and $5 for every additional pound in weight.

Country Package Id Shipping Price Algorithm Delivery Type
USA bulky 20 WC=1@5 Freight Delivery



Addtional Cost Per Item Threshold & Stacking Algorithms


 
Requirements 2: Provide Standard Delivery for all other items in the USA costing $5 for items up to a weight of 50 pounds.
Requirements 3: For items from a weight above 50 pounds costs $10 with $2 for every additional item including $1.50 for every additional pound in weight.


Country Package Id Weight From Weight To Shipping Price Algorithm Delivery Type
USA * * 50 5 * Standard Delivery
USA * 50 * 10 I=2&WC=1@1.50 Standard Delivery




Free Delivery Past Certain Threshold


 
Requirements 4:Provide Free Delivery for any items over $100 and under 50 pounds weight.

Country Weight To Price From Shipping Price Delivery Type
USA 50 100 0 Free Delivery



Only Show Free Delivery / Exclude Delivery Type


 
The only problem with the above row is that it also includes Standard Delivery in the quote:
Free Delivery $0.00 
Standard Delivery $5.00 
Requirements 5: We want it to exclude Standard Delivery so we will copy the row and -1 to exclude Standard Delivery.
Country Weight To Price From Shipping Price Delivery Type
USA 50 100 0 Free Delivery
USA 50 100 -1 Standard Delivery


Now the quote is: Free Delivery $0.00

Asterisks as Wildcards & International Shipping


Requirements 6: Now we want to offer International Delivery for $30 to any other country:
Country Shipping Price Delivery Type
* 30 International Delivery




Filtering based on Product Type


Requirements 7: Include an In Store Pickup option for any product which can only be picked up from the store.

Country Package Id Shipping Price Delivery Type
USA only_pickup 0 In Store Pickup

This works for products assigned as only_pickup, but not rate will be capable if a product from any other group is in the cart.

 


The Result


To see the final CSV used in this tutorial - download the example and see below



Use our examples for many more real examples using Product Matrix.
 
 

Uploading Your New CSV File



To upload your new CSV file, please see here: Uploading a CSV file
ċ
MaximumPricevariesonMultiplePostcodeperline.csv
(2k)
Joshua Stewart,
Oct 4, 2011, 8:04 AM
ċ
ProductMatrix-Showall.csv
(0k)
Joshua Stewart,
Dec 7, 2011, 2:23 AM
ċ
SetCart.csv
(2k)
Unknown user,
Feb 11, 2013, 8:08 AM
ċ
Tracker-Example.csv
(0k)
michael@webshopapps.com,
Feb 15, 2013, 3:31 AM
ċ
alt_algorithm.csv
(1k)
Unknown user,
Dec 5, 2011, 1:26 PM
ċ
pm-example.csv
(1k)
Unknown user,
Dec 17, 2010, 4:09 AM
ċ
productmatrix-template.csv
(0k)
Unknown user,
Dec 15, 2010, 8:47 AM