Overview of Weight-Based Shipping Follow

If you are looking to set up your rate card for the first time, please start here.

 

Introduction

There are two basic methods of charging shipping for physical merchandise orders in Topspin:
  • Flat Fee Shipping means that an item will ship for a specified flat rate. Multiples of an item in the same order will result in the shipping rate being multiplied by the quantity ordered. For example: A CD has a flat shipping rate of $5 in the US. My fans who order will have it shipped anywhere in the US for $5. If they order two copies, shipping will cost $10; 4 copies will incur a shipping charge of $20, and so on. Flat-fee shipping allows you to designate different rates for different countries as well. If my rates are $5 to the US, $10 to Mexico, and $50 to Djibouti, then these are the rates that will be applied for customers purchasing from those locations (location is determined by the shipping destination specified by the customer).
  • Weight-Based Shipping means that the shipping rate will be calculated primarily upon the basis of the cumulative weight of the items being shipped. Weight-based shipping can be used to take advantage of efficiencies in shipping costs (using the above example, it does not cost $20 to ship 4 CDs under most circumstances). If it costs $5 to ship up to 1 pound, and $7 to ship up to 1.5 pounds, then four 5-ounce CDs will ship for just $7, as their cumulative weight would be 1lb, 4oz. Weight-based shipping can also take into account specific handling costs for initial and additional order items (this will be covered in additional detail below).  
 
Pricing Model
 
Topspin's weight-based shipping model is designed to be flexible worldwide and to allow you to use any carrier you may wish to use. It's also designed to be flexible enough to allow you to give yourself any needed padding and margin on shipping fees that you may need (more on that later). 
 
First, it's most important to understand the components of a weight-based shipping charge. There are three main charges that sum up to be the shipping charge:
  • Shipment Cost - this is the cost that is at the intersection of three points: The shipment method you use (e.g., UPS Ground, UPS 3 Day Select, UPS 2nd Day Air, UPS Next Day Air), the sum of the weights of the items in the shipment, and the destination country (or destination postal code in the US & Canada, should they be supplied).
  • Per-Order Fee - this fee is assessed to all orders in the weight-based shipping model. It's also referred to as the "open order" or "first item" fee. It may also be called the "handling fee"
  • Per-Item Fee - this fee is assessed to all items after the first item in the weight-based shipping model. For instance, if you are selling a bundle that includes a CD, a T-shirt and a charm bracelet, you will be assessed an item fee (for the CD, the first item in the order), and 2 additional-item fees (the T-shirt and the charm bracelet). This is also called an "additional item fee". 
When a user's cart includes offers that are all priced using a weight-based model, the simplest form of the calculation is used:
 
(Shipment cost based on sum of weights of all items) + order fee + ((total number of items - 1) * additional item fee) 
 
For example, an artist have five offers (weights of the items are provided in parentheses):
  • CD (8 oz)
  • T-shirt (12 oz)
  • Deluxe Package (vinyl and t-shirt, not bundled, packed or kitted in any way - 2.5 lbs)
  • Digital Download (n/a)
  • Vinyl Package (22 oz)
A fan's cart contains:
  • 2x CD (8 oz per CD)
  • 1 T-shirt (12 oz per shirt)
  • 1 digital download (n/a)
  • 2 Deluxe Package (vinyl and t-shirt, not bundled, packed or kitted in any way - 2.5 lbs)
  • 1 Vinyl Package (22 oz)
The charge would be calculated as follows:
 
Weight = (8oz * 2) + (12 oz) + (2lb 8oz * 2) + (22oz) = 130 ounces (digital downloads do not have shipping costs)
 
Total items = (2 CD, 1 T-Shirt, (2 Vinyl, 2 T-Shirt), 1 Vinyl) = 8 items
 
S&H Fee = Shipping cost for 130 ounces + per-order fee + (per-item fee * 7)
 
Remember, the item fee is always charged for every item AFTER the first item - so it's number of total items (8) minus 1 (the first item) – 7 items. 
 
In the case of a mixed cart – weight-based items and flat-fee items – the weight-based calculations are performed without counting the weight or item count of flat-fee-based items. Once the weight-based total is calculated, the flat-fee charges are then added on top.
 
Shipping Cost
As noted earlier, the shipping cost is determined by what method is being used, where the package is going, and how much the package weighs.
 
Topspin places no restrictions on number or type of shipping methods - you may have as many as you desire. A shipping method is a unique service offered by a carrier. A carrier is business like FedEx, DHL, UPS, USPS, etc. Ship methods, again, may be (for example) UPS Ground, UPS 2nd Day Air, UPS Next Day Air, USPS First Class Mail, etc. You may have any variety and combination of any service. 
 
Many people refer to their shipping as zone-based. Topspin's rate cards are calculated specifying ranges of zip codes and country codes. This is the exact same method that underlies the zone-based cards of shipping carriers. In the Building A Rate Card section, this will be reviewed in depth. 
 
Per-Order Fee
The Per-order fee is assessed once per order. It is only assessed once on an entire order, regardless of how many items are in the order.
 
Per-Item Fee
The additional-item fee is assessed for every physical item after the first item in the order. Any individual spin may incur multiple per-item fees (e.g. two separate shirts in one spin would incur one per-item fee; a cart with one spin of two shirts and another spin of a single CD would be two additional item fees: the second shirt and the CD.) 
 
If a user's shopping cart contains a mixture of flat- and weight-based shipping spins, the per-item fee will NOT be assessed on the flat-fee spins (as those spins' pricing represents the final "all-in" cost to ship). 
 
 
Building Your Rate Cards
 
This section will take you through building a sample rate card shipping from a fictional warehouse in Westwood in Los Angeles, CA. 
This warehouse will ship up to 10 lbs on any single order domestically and internationally via UPS. 
It will offer additional service via USPS for lightweight items (< 1 lb) 
It will only be priced in US Dollars.
 
Getting Started: Finding the Rate Cards
The zip code of our fictional warehouse in Westwood is 90025. 
The first step is to get the UPS rates in the US for Westwood.
We will go to http://www.ups.com/content/us/en/shipping/cost/zones/continental_us.html to get the rates, and enter 90025 as the ZIP. This will begin a download of a file called 900.xls (900 as it is the first 3 digits of 90025.) This file gives us the shipping zones for our warehouse, defined as an individual set of ZIP ranges. 
 
After that, you need to get your UPS rate card - the cost it will cost you to ship. UPS offers two rate cards on http://www.ups.com/content/us/en/shipping/cost/zones/continental_us.html - we will use the Daily Rate and Service Guide, because our fictional warehouse has daily pickup service. The download we'll get is daily_rates.xls. 
 
It's important to note that you should consult with the shippers that you intend to use to determine the accurate rates and service levels. This walkthrough makes presumptions to help illustrate the process of setting up a rate card.
 
A Note About US Postal Codes
In these examples, you will see that zip codes are commonly expressed as three digit numbers, dropping the final two numbers. This is the standard practice to denote a range - for instance, a range described as "300-301" is actually "All zip codes, inclusive, from 30000-30199". Aside from some special cases (predominantly Alaska and Hawaii), you will not see US postal codes on these rate cards expanded out to the full five-digit number.
 
 
 
DOMESTIC RATE CARD
 
 
Step 1: Preliminary Work
Open Excel and start a new spreadsheet. Call it "domestic_rates.csv" or something similar - we're going to build the rate card to paste into the Topspin rate card format. 
Across the top, your columns should be:ZIP START, ZIP END, TRANSIT DAYS MIN, TRANSIT DAYS MAX, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. 
We'll be using whole-pound increments up to ten pounds (which is why you entered 1, 2, 3, 4, etc as column headers). 
Make sure you've got 900.xls open as well as daily_rates.xls. 
 
Step 2: Building The ZIP Ladders And Zones For Domestic Delivery
This will be a time-consuming step which is important to get right. It is strongly recommended that you take breaks every few minutes and proofread your progress. 
This step will be taking the postal code ladders from the "Dest ZIP" column in the postal code and inserting them into the ZIP START and ZIP END columns of your temp_rates.csv worksheet. 
 
Looking at the first destination in 900.xls, we see the range is "004-005" which actually means 00400 to 00599. In the ZIP START column, type in 00400. In the ZIP END column, type 00599. Repeat this for the 006-007 range, putting 00600 in the ZIP START column and 00799 in the ZIP END column. You will see that 009 does not have a range. This means that the ZIP codes should be 00900 in ZIP START and 00999 in ZIP END. 
 
1.png
 
Entering ZIP code ranges. Note that excel does not display the leading zeroes on zip codes such as 00400 or 00799 - they will be present in the uploaded CSV.
 
Continue to work through the ZIP code range until you have completed all of the rows on the first table. (Row 101, 988-994 in the 900.xls file). 
 
When you've completed this, before tackling the next set of numbers below the tables, we'll associate zones, which will be a huge time saver for future steps. All you will do is go back to the top of your document, and the top of the 900.xls document. Select the values in the table under the second column, "Ground", to the end of the table, and paste them into the column beneath "1". You should see that you now have one corresponding zone number for each row you've created in your zip ladder (as pictured). If you don't, something is wrong and you should double-check your entered data. 
2.png
 
Pasting in UPS zone data with our ZIP code ranges. There are 93 zip code ranges in the upper table, and our paste had 93 rows - we know we have the right number of rows so far. 
 
The next step will be to deal with the special cases in Alaska and Hawaii. Though there are lots of codes here, this is comparatively easy and you can cut and paste to make this section go more quickly. Our ZIP START and ZIP END values will be the same because the rates are only quoted for single codes.  
 
3.png
 
Alaska and Hawaii use single-ZIP code ranges. Note that the ZIP START and ZIP END columns are identical. 
 
Proceed through each section, copying and pasting each section column by column.
 
After finishing each section, make a note of that section's zone in the "1" column on your working spreadsheet. For the first section of rates to Hawaii in our 900.xls file, these are zone 44, so we'll enter 44.
 
4.png
 
Associating zone data with our Alaska and Hawaii zones. 
 
Continue working through the remaining sections for Hawaii and Alaska, making note of their zones as you finish a section. By the time you are finished, your card is likely to be over 500 rows. 
 
SAVE YOUR FILE! Make sure to save it as a CSV. 
 
At this point, you can close the 900.xls document as you won't be needing it for the remainder of this exercise. 
 
Step 3: Zone Pricing
 
Now you will refer to the daily_rates.xls (or other appropriate rate document). Flip to the UPS Ground tab. You will see a table showing Zones and Rates by weight. 
 
5.png
The UPS Ground tab showing rates to each zone, by pounds.
 
We're going to go back and populate the actual rates into our rate card. Select the values in the zone 2 column down to 10 lbs (6.86) Copy them to your clipboard. Now, switch to the file you've been building and search for the first occurance of zone 2. You will put your cursor in the cell for that row, in the "1" column. Then go to the edit menu and choose "paste special", and click "transpose" - 
 
6.png
 
Use "Paste Special" and the "Transpose" checkbox to paste a vertically-oriented row from the UPS Ground rate card into your horizontally-oriented Topspin rate card.
 
this will paste the vertically aligned columns from daily_rates into our horizontal aligned columns in the rate card we're building. Don't worry about the formatting. 
 
7.png
The results of "Paste Special" with "Transpose" checked.
 
Search for all zone 2 columns in your document and paste as described above.
 
Once you have completed zone 2, continue on, pasting for zones 3-8, and then 44-46 as applicable. This again may take some time and breaks are highly recommended to ensure accuracy. However, if you've followed instructions to this point, you should be doing well. 
8.png
 
Working through the zones, one at a time. Zones 6 & 7 have not been completed on this worksheet.
 
Step 4: Domestic Rate Card Cleanup And Final Formatting
 
You've completed the hardest part of the rate card setup, and now it's time to cleanly format this rate card for importing. The first thing you should do is provide a transit time guideline. Our fictional discussion with our shipper told us to estimate 4-6 days for transit in the US, so our domestic rate card will have "4" in the TRANSIT DAYS MIN column and "6" in the TRANSIT DAYS MAX column for all rows.
 
9.png
Adding transit times to our rate card.
 
The next step is to add the additional information required for the Topspin system to read our rate card. 
Go to cell A1 (should read "ZIP START") and from Excel's "Insert" menu, choose "Columns". Do this twice, so that "ZIP START" is now in column C1. 
In the first column, enter "CURRENCY". 
In the second column, enter "COUNTRY". 
 
Since our rate card is in US Dollars, we will be entering USD for all our rows. Other values that are allowed are:
CAD = Canadian Dollar
AUD = Australian Dollar
EUR = Euro
GBP = UK Pound Sterling
JPY = Japanese Yen (note: Japanese Yen is not decimalized, and therefore all values should be whole numbers - 3500 is valid, 3499.99 is not)
 
We will next add the country code, so that Topspin knows which country these rates apply to. Our country code in the Country column will be "US". 
The country codes you need to refer to are the ISO-3166 country codes, which you can view at http://www.iso.org/iso/english_country_names_and_code_elements
ONLY these codes will work, there are NO VARIANTS in the system. 
 
Step 5: Rate Card Upload
You've got a complete csv rate card now! Save it as domestic_rates_final.csv or any other name you'll remember. Go to http://app.topspin.net/account/fulfiller/ and scroll down to the rate card section. Choose "Add New Rate Card" and give it a name. This name will be visible to fans, so make it something that will make sense to the average consumer. In this case, "UPS Ground" is a great choice. 
Give it a code you'll recognize in the "CODE" box for any orders exports. We'll name it "UPS-G" for UPS Ground. The weight system should be set as pounds, and we'll leave everything else blank. Choose the file and upload it, and Topspin will process your rate card. For larger rate cards, this may take a few minutes. 
 
If you've done everything as described here, you should have no problems with the upload. Click the "test" button and choose a few sample zip codes and weights to make sure the shipping rates are as you expected. 
 
Congratulations! You've got your first rate card in the system. The others that we'll do are all smaller. 
 
CANADIAN RATE CARD
 
If you've successfully uploaded a domestic rate card as above, you will not have much problem with the Canadian rate card. It works very much like a domestic US rate card. The only thing that may take some time to get in a rhythm with is the Canadian postal code system which mixes letters and numbers. Canadian postal codes are formatted as ANA NAN where A=alphabet and N=number. The "lowest" possible postal code is "A0A 0A0" and the "highest" possible postal code is "Z9Z 9Z9". A-Z, 0-9. If you can count to 10 and you know your alphabet, this won't be too hard. 
 
Canadian rate cards will again use three-character codes, much like the US cards. Once again, they count from the left, so a range of "A0A-A1C" would actually be "All postal codes from "A0A 0A0 to A1C 9Z9" (0A0 being the "lowest", like 00, and 9Z9 being the "highest", like 99.) 
 
Step 1: Preliminary Work
Again, we need to go to UPS's site and get the appropriate zone chart for Canada. We're actually going to get the daily rates for the world, which we will use again. (A reminder: Make sure to discuss your service needs with whatever shipper you're using! They will be able to provide the right rate guidance for you.) 
This file is a 6 meg PDF download, available at http://www.ups.com/media/en/daily_rates.pdf
 
Next, we'll get Excel set up again. Start a new spreasheet and call it Call it "canada_rates.csv" or something similar - we're going to build the rate card to paste into the Topspin rate card format. 
Across the top, your columns should be: ZIP START, ZIP END, TRANSIT DAYS MIN, TRANSIT DAYS MAX, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. 
We'll be using whole-pound increments up to ten pounds once again. 
 
As a reminder, our fictional warehouse is in Los Angeles, CA 90025 (specifically Westwood), which we'll keep in mind when we build this card.
Go to page 69 in the PDF (as of this writing), and scroll to the Canadian rates - it will start with Newfoundland. We'll be using "UPS Standard to Canada" as our level of service. When we look at the table, we can see that our origin state (CA) for the service level places all of Newfoundland in zone 56. 
 
Since we're working from a PDF, the flow won't be as copy-paste friendly and regular breaks are highly recommended to ensure accuracy. 
 
Step 2: Building Postal Code Ladders and Zones for Canadian Delivery
 
Start entering the rates. Newfoundland's first rate is A0A-A0Z. In your first row under ZIP START, you'll enter "A0A 0A0". In the row under ZIP END, you'll enter "A0Z 9Z9" (everything from A0A through A0Z inclusive). Next will be A1A-A1N. Enter A1A 0A0 (think of "0A0" like you would "00" on a US card) in ZIP START and A1N 9Z9 in ZIP END ("9Z9" is like "99" on a US rate card.)
 
Continue and complete Newfoundland. At this point we'll actually enter the zone information - 56 - in the appropriate rows under the column with the header "1". This will help us with our pasting of rates later. Continue on through the provinces, making sure to enter the zone information as you go.
 
10.png
Entering Canadian postal codes. Newfoundland has been completed with zone data, Nova Scotia is nearly completed. Note that the codes end in "0A0" and "9Z9" instead of "00" or "99"
 
When you've competed British Columbia, you'll see that Nunavut, the Northwest Territories and the Yukon have no zone data for our service level. This means that UPS Standard To Canada does not have service to either of these three provinces. You may elect to offer another Canadian service level (UPS Saver for instance), but for the purpose of our example we will simply not have service to these provinces. Instead of keying in the postal code ranges for these three territories, we will leave them blank. 
 
11.png
UPS Standard to Canada service is not offered to Nunavut, Northwest Territories, or Yukon, so do not enter the postal code ranges.
 
At this point, save your document, take a step away and clear your eyes: you likely need it after entering all the postal codes.
 
Step 3: Zone Pricing
 
Again, we will open the daily_rates.xls file we were using in the domestic service example (available at http://www.ups.com/content/us/en/shipping/cost/zones/continental_us.html). Again for our example, we're using the "Daily Rates", but you should contact your shipper to understand the appropriate level of service. In the daily_rates.xls file, switch to the "UPS Standard (EXPT)" tab to see your Canadian rates. We'll start with Zone 56 in this case since there are a lot of destinations in Zone 56.
 
12.png
The rates for UPS Standard to Canada.
 
Select the cells under the "56" column from the row corresponding to 1 lb to the row corresponding with 10 lbs and copy it to your clipboard. Now we will paste this into our first row for Zone 56. When you put your cursor in the cell that says "56" in your working file that you built in step one, use Excel's Paste Special command. Check the "Transpose" box and your rate will paste in correctly. 
 
13.png
Use "Paste Special" and the "Transpose" checkbox to paste a vertically-oriented row from the UPS Standard to Canada rate card into your horizontally-oriented Topspin rate card.
 
If you want to make the next several rows easy, once you have pasted, copy the horizontally aligned contents you pasted to your clipboard. Now, move down and paste over every row that corresponds to Zone 56. 
 
14.png
Working through the zones, one at a time. Zones 52, 53, and 55 have not been completed on this worksheet.
 
When you have completed this, move on to the next zone - Zone 55 - and paste its value onto all corresponding rows like you did for Zone 56. Continue until there are no more rows that need to have zones pasted. You may not end up using all zones - don't be concerned. As long as you've correctly assigned zones to your rows and pasted in the appropriate pricing, you have nothing to worry about.
 
Step 4: Canadian Rate Card Cleanup and Final Formatting
You've completed the hardest part of the rate card setup, and now it's time to cleanly format this rate card for importing. The first thing you should do is provide a transit time guideline. Our fictional discussion with our shipper told us again to estimate 4-6 days for transit to Canada, so our Canadian rate card will have "4" in the TRANSIT DAYS MIN column and "6" in the TRANSIT DAYS MAX column for all rows.
 
15.png
Adding transit times to our rate card.
 
The next step is to add the additional information required for the Topspin system to read our rate card. 
Go to cell A1 (should read "ZIP START") and from Excel's "Insert" menu, choose "Columns". Do this twice, so that "ZIP START" is now in column C1. 
In the first column, enter "CURRENCY". 
In the second column, enter "COUNTRY". 
 
Since our rate card is still in US Dollars, we will be entering USD for all our rows. We do not want to enter CAD (Canadian Dollars) because we are selling in US dollars but  are shipping to Canada.
 
We need the country code, so that Topspin knows which country these rates apply to. Our country code in the Country column will be "CA" for Canada, not California! 
The country codes you need to refer to are the ISO-3166 country codes, which you can view at http://www.iso.org/iso/english_country_names_and_code_elements
ONLY these codes will work, there are NO VARIANTS in the system. 
 
16.png
A view of the completed Canadian rate card - note the country code and currency.
 
Step 5: Rate Card Upload
You've got a complete csv rate card now! Save it as canada_rates_final.csv or any other name you'll remember. Go to http://app.topspin.net/account/fulfiller/ and scroll down to the rate card section. Choose "Add New Rate Card" and give it a name. This name will be visible to fans, so make it something that will make sense to the average consumer. In this case, "UPS Standard to Canada" is a great choice. 
Give it a code you'll recognize in the "CODE" box for any orders exports. We'll name it "UPS-C" for UPS Canada. The weight system should be set as pounds, and we'll leave everything else blank. Choose the file and upload it, and Topspin will process your rate card. 
 
If you've done everything as described here, you should have no problems with the upload. Click the "test" button and choose a few sample zip codes and weights to make sure the shipping rates are as you expected. 
 
If you've followed all the instructions to this point, you have the US and Canadian rate cards in the system, which are the two most time-consuming cards to build. We only have two left: Worldwide and our USPS light-weight card.
 
WORLDWIDE RATE CARD
 
You might be completely sick of postal codes at this point and unable to distinguish letters from numbers. If so, you ought to take a break for an hour. 
 
The good news with is that you won't need to worry about postal codes anymore! At this point, we'll be building a rate card for international service. Topspin does not use postal codes outside of the US, so you will just be looking at the rates to serve countries around the world. Fortunately, this is generally how UPS works as well, so there's not much work to do. 
 
Step 1: Preliminary Work
Once again, we will be using UPS for rates. We'll be using the same UPS rate document we were using for Canada, so there's not a new download. As always, a reminder: Make sure to discuss your service needs with whatever shipper you're using. They will be able to provide the right rate guidance for you.
The file, in case you lost the link, is a 6 meg PDF download, available at http://www.ups.com/media/en/daily_rates.pdf
 
We will set up Excel, but this time we're going to set it up slightly differently: since we do not use ZIP codes, we're going to actually omit those from our table for ease of building this document. Start a new spreadsheet and call it "world_rates.csv" or something you will remember. 
 
Your columns should be:COUNTRY, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. 
 
You'll notice that we've omitted ZIP START and ZIP END, as well as TRANSIT DAYS MIN and TRANSIT DAYS MAX. Those will actually be necessary when we upload the final document, but since we won't be using them right now, we're omitting them for ease of setup.
 
We'll be using whole-pound increments up to ten pounds once again. 
 
As with the last rate cards, our fictional warehouse is in Los Angeles, CA 90025 (specifically Westwood), which we'll keep in mind when we build this card.
 
We will be using UPS's Worldwide Saver rate, which does not require us to know if our warehouse is shipping from the East or West of the US. It also has a much broader service area. Worldwide Saver countries and zones begin on page 64 of the daily_rates.pdf file.
 
Step 2: Building Zones for Worldwide Delivery
 
The process for entering rates is much simpler. Each country on the list has its name (e.g. Chad) and its ISO-3166 country code (TD). You will be entering the country code. To speed up future work, you'll also want to enter the zone code (in the Saver column) after entering the country code. 
 
Starting on the first row, you'd enter AF and 405 (Afghanistan, Zone 405). Then AX and 403 (Aland Islands, Zone 403). Continue working through the document. You may see special characters referring you to footnotes - do not enter these! You should only be entering two-letter codes. 
 
Also, skip Canada since you entered its rates in the previous rate card.
 
17.png
Entering two letter country codes and their Saver zone.
 
As you go through, you will see many duplicated country codes. Don't worry about them - it's easier to just enter them for now and let Excel remove them. 
 
 
Step 3: Removing Duplicate Entries
 
Once you've finished entering all countries, our next step is to remove the duplicate rows. Select columns A and B which should contain the country codes and zones. 
 
  1. In Excel, click the "Data" menu, choose "Filter", and select "Advanced Filter". Excel may give you a message about column headers - just click "OK". 
  2. On the dialog box that comes up, choose "Filter the list, in-place" and "Unique records only". Press "OK". Your list will be filtered, but you are not done.
  3.  18.png
  4. Filtering your rate card - "in-place" and "Unique records only" have been selected.
  5. Once your list has filtered, copy the results to your clipboard. From Excel, click "Data", then "Filter", and select "Show All". The full list will show up again. From the edit menu, select "delete". From the dialog that pops up, choose "Move cells up". 
  6. Now, put your cursor in cell A1. Paste from the clipboard - you will have your de-duplicated list! Save your file now if you haven't. 
 
Step 4: Zone Pricing
19.png
The UPS Worldwide Saver rates.
 
Once more, we will open the daily_rates.xls file we were using in the domestic service example (available at http://www.ups.com/content/us/en/shipping/cost/zones/continental_us.html). Again for our example, we're using the "Daily Rates", but you should contact your shipper to understand the appropriate level of service. In the daily_rates.xls file, switch to the "UPS WW Saver (EXPT)" tab to see your Worldwide rates. We'll start with Zone 401 in this case since there are multiple destinations in Zone 401.
 
Select the cells under the "401" column from the row corresponding to 1 lb to the row corresponding with 10 lbs and copy it to your clipboard. Now we will paste this into our first row for Zone 401. When you put your cursor in the cell that says "401" in your working file that you built in step one, use Excel's Paste Special command. Check the "Transpose" box and your rate will paste in correctly. 
 
If you want to make the next several rows easy, once you have pasted, copy the horizontally aligned contents you pasted to your clipboard. Now, move down and paste over every row that corresponds to Zone 401. 
 
20.png
Filling in Worldwide Saver rates. Zones 405, 406, 407 and 408 have not been completed.
 
When you have completed this, move on to the next zone - Zone 402 - and paste its value onto all corresponding rows like you did for Zone 401. Continue until there are no more rows that need to have zones pasted.
 
Step 5: Worldwide Rate Card Cleanup and Final Formatting
Now that you've completed adding the rates, you're ready to take your final steps to prepare this rate card for import. Since the card was structured slightly differently, we will have a different set of columns to add this time around.
 
First, add a column to the left COUNTRY column, called CURRENCY. Once again, we are selling in US Dollars, so we will enter USD here.
 
To the left of the "1" column, add four columns: ZIP START, ZIP END, TRANSIT DAYS MIN, TRANSIT DAYS MAX.
We will leave ZIP START and ZIP END empty since we are not using postal codes for worldwide rates. 
We will next enter "4" in TRANSIT DAYS MIN and "6" in TRANSIT DAYS MAX, as advised by our shipper.
 
Now that you've completed that, save your rate card - it's time to upload!
 
21.png
The finalized worldwide rates. Note that no postal codes have been entered.
 
Step 6: Rate Card Upload
You've got a complete csv rate card now! Save it as world_rates_final.csv or any other name you'll remember. Go to http://app.topspin.net/account/fulfiller/ and scroll down to the rate card section. Choose "Add New Rate Card" and give it a name. This name will be visible to fans, so make it something that will make sense to the average consumer. In this case, "UPS Worldwide Saver" is a great choice. 
Give it a code you'll recognize in the "CODE" box for any orders exports. We'll name it "UPS-W" for UPS Worldwide. The weight system should be set as pounds, and we'll leave everything else blank. Choose the file and upload it, and Topspin will process your rate card. 
 
If you've done everything as described here, you should have no problems with the upload. Click the "test" button and choose a few sample zip codes and weights to make sure the shipping rates are as you expected. 
 
Congratulations, you've now got worldwide service and have completed a lot of work. Take a break - the last rate card is easy and won't take long at all. 
 
LIGHTWEIGHT RATE CARD
 
The final card is one to allow low-cost shipment of items under one pound. This will be a quicker rate card to build and have vastly fewer rows than we've seen in any previous rate card, but it will introduce some new concepts. This lightweight rate card will use US Postal Service rates - you can close all the documents you've had open to this point. This rate card will have First-Class service to the US, Canada and Mexico. 
 
Step 1: Preliminary Work
This time we will be using US Postal Service for our rates. First we will look at the domestic table at http://pe.usps.gov/text/dmm300/Notice123.htm#wp1011092 which shows the First-Class rates for the US. We will use "Package" for our rates. 
 
22.png
USPS Domestic Package Rates
 
Again, we will set up Excel. This time we will set up all columns - we won't be saving much time by not having them in. 
Start with CURRENCY, COUNTRY, ZIP START, ZIP END, TRANSIT DAYS MIN and TRANSIT DAYS MAX.
 
First-Class mail is a flat rate, so we won't need to worry about the point of origin - the service is one price anywhere in the country.
 
However, our weight increments are different. We will need to enter our weights in fractions since we're dealing with ounces (below a pound). 
To save yourself time and confusion, here are the fractions for each ounce below a pound:
0.0625, 0.125, 0.1875, 0.25, 0.3125, 0.375, 0.4375, 0.5, 0.5625, 0.625, 0.6875, 0.75, 0.8125, 0.875, 0.9735, 1
Use these numbers any time you need to use a fraction of a pound.
 
Paste those numbers into the column headers of your file. 
 
 
Step 2: US Rates
The first step will be to set up our countries. We're providing rates to three countries - the US, Canada and Mexico. We'll enter the following data on row one:
Currency: USD
Country: US
Zip Start & Zip End: Empty (This is an important concept: if you want to do "all zip codes", you don't enter 00000-99999, you leave it blank, like you did in the worldwide file)
 
You will enter "1.22" in the "0.0625" (1 oz) column. Then, enter "1.39" in the "0.125" (2 oz) column. Continue until you've entered the rates up to 13 oz (service is not offered over 13 oz) - so you will leave the columns blank starting with the 0.875 column.
 
23.png
Entering Package rates into the rate card. The one- and two-ounce prices have been entered; the three ounce price is being entered.
 
Step 3: Canada & Mexico Rates
 
International First Class rates can be found at http://pe.usps.gov/text/dmm300/Notice123.htm#wp1118064
 
Scroll down to the "Packages" rates. Enter a new row in your spreadsheet:
 
Currency: USD
Country: CA (ISO-3166 two-letter country code for Canada)
Zip Start & Zip End: Empty (Again, "all postal codes" should be represented by blanks here. Entering A0A 0A0 - Z9Z 9Z9 will cause problems here)
 
Enter 1.23 in the 1 oz column, and continue to 8 ounces (0.5). Note that the next increment is 12 ounces, or 3/4 of a lb. (0.75). You will actually enter the rate for 12 oz in the columns from 0.5625 through to 0.75 - because the rate for 9, 10, 11 and 12 ounces are the same rate. 
 
24.png
Rates for 9-12 ounces are the same - make sure to enter the 12 ounce rate in the cell for 9, 10, 11 and 12 ounces.
 
You will do the same thing again for the rates over 12 ounces - enter the 16 ounce rate for every weight starting at 0.8125 up to 1. 
 
25.png
Rates for 13-16 ounces are the same - make sure to enter the 13 ounce rate in the cell for 13, 14, and 15 ounces as well as the 1 pound cell.
 
Next, we'll do the rates for Mexico. Again, start a new row, and enter in the cells:
Currency: USD
Country: MX
Zip Start & Zip End: empty
 
Enter 1.23 in the 1oz column, 1.79 in the 2oz column, and so on until you have reached 8oz. 
 
For 9oz-12oz, you will enter the 12oz rate.
 
26.png
Rates for 9-12 ounces are the same again - but higher priced. Make sure to enter the 12 ounce rate in the cell for 9, 10, 11 and 12 ounces.
 
From 13oz to 1lb, you will enter the 16oz rate, as you did with Canada.
 
Step 4: Transit Time And Final Prep
You've completed the hard part, and now you can simply add the transit time. Our shipper advised us to expect 3-5 delivery days in the US, so enter "3" in TRANSIT DAYS MIN and 5 in TRANSIT DAYS MAX for the US row. Internationally, we were advised to expect 7-14 days, so we will enter "7" in TRANSIT DAYS MIN and "14" in TRANSIT DAYS MAX for the CA and MX rows. 
 
27.png
The final rate card for Lightweight service.
 
Now that you've completed that, save your rate card - it's time to upload!
 
Step 5: Rate Card Upload
 
You've got a complete csv rate card now! Save it as firstclass_final.csv or any other name you'll remember. Go to http://app.topspin.net/account/fulfiller/ and scroll down to the rate card section. Choose "Add New Rate Card" and give it a name. This name will be visible to fans, so make it something that will make sense to the average consumer. In this case, "UPS First Class" is a great choice. 
Give it a code you'll recognize in the "CODE" box for any orders exports. We'll name it "USPS-FC" for USPS First Class. The weight system should be set as pounds, and we'll leave everything else blank. Choose the file and upload it, and Topspin will process your rate card. 
 
If you've done everything as described here, you should have no problems with the upload. Click the "test" button and choose a few sample zip codes and weights to make sure the shipping rates are as you expected. If you test the rate card for the US, you'll see no rate return for 14 ounces and above in the US, and nothing over 16oz (1lb) in Canada and Mexico. No rates will be available outside the US, Canada and Mexico. 
 
You've done it! All four rate cards have been set up and you have a solid basis for a basic shipping scenario. If you wanted to add addititonal services (expedited domestic and international services), you could just use your existing rate cards as a basis, copy the new zone info in and paste the rates. 
 
There's More Than One Way To Skin The Cat
If you wanted to have a simpler format for this rate card, you could actually split into two services - e.g. US domestic (and not include any weight tiers above 13oz) and a First Class International (CA and MX) card and omit the duplicate rates between above 8 oz and above 12 ounces. 
 
The important takeaway is that all weights are "up to but not over", from the previous weight - so in the international card, the gap between 8oz and 12oz would be interpreted as the same as if we'd entered rates at every point on the ladder. This same logic would work for full pounds - so if you had weights for 1-10lbs, and then at 15, 20, 25 lbs after that, you could simply enter 10,15,20 as your weight columns instead of 10, 11, 12, 13, 14, 15, 16, etc.
 
 
Other Helpful Information
 
kg/g works like lb/oz
 
If you are using the more sensible metric system, the concepts don't change. Kilograms are full numbers (1kg is entered as "1"). Grams are fractions of the kilogram. If you wanted to enter a weight ladder from 100g up to 1kg in 100g intervals, you would enter your weight columns as:
0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1
And then make sure to select "kg/g" on the weight model during upload.
 
28.png
A rate card from 100 grams to 1 kilo in 100 gram increments. 
 
Currency Codes
Currency codes, which go in the Currency column, use ISO-4217 currency codes. You may find an explanation of the ISO-4217 at http://en.wikipedia.org/wiki/ISO_4217
Topspin currently supports six currencies:
USD = US Dollar
CAD = Canadian Dollar
AUD = Australian Dollar
EUR = Euro
GBP = UK Pound Sterling
JPY = Japanese Yen
(note: The Japanese Yen is not decimalized, and therefore all values should be whole numbers - 3500 is valid, 3499.99 is not)
 
More About Currency
To allow artists to sell in multiple currencies, you must supply rates in those other currencies. Topspin does not allow mixed currencies at checkout. If your rate card mirrored this example and only had one currency, e.g. USD, artists would not be allowed to sell in Euros, Canadian Dollars, etc. because of a currency mismatch. 
 
You may supply rates in other currencies, and they can be in the same rate cards you completed above. You do not need one rate card for US in US Dollars, one rate card for US in Japanese Yen, etc. You can have one rate card containing rates in USD, AUD, GBP, JPY, CAD and EUR. You may wish to include a small percentage buffer on your rates to account for currency fluctuations as well as the conversion from the transactional currency to your currency that you are paid in. 
 
One example case that's worth discussing is if you fulfill most frequently to the US and Canada. You may want to have rates in USD and CAD - that way artists who natively sell in CAD can still ship to the US, provided you have shipment rates to the US given in CAD as well as USD. Likewise, pricing in USD would allow artists who natively sell in USD to ship to Canada. 
 
Currency's complexity can be understood somewhat by framing it with the question, "Which artists do I want to enable to use me as a fulfillment partner?" If you're dealing with artists who primarily sell in a given region, say, the US, you may only need to provide USD currencies but allow worldwide shipment. Similarly, if you'e working with a Japanese act, you may only need to provide rates in JPY, but you may want to allow global shipping for increased sales. However, if you want to allow a large worldwide artist to sell to fans worldwide in their native currency, you ought to consider pricing your rates in all currencies.
 
Pricing Flexibility: Protecting Your Margin
This guide shows the steps necessary to enter the prices that your shipper has quoted to you. There are a few ways in the weight-based shipping model to ensure that you margin to cover your overhead:
  • Inflate raw shipping costs - so that you are charging more than your cost to ship. You may elect to simply add a flat fee on top of your cost, or multiply by a percentage. Another simple way to do it is to "shift" your table a step or two – charging the 2 lb price for 1 lb, the 3lb for 2lb, and so on.
  • Set up your per-order/per-item fees to work with your business - if you find your fulfillment is a lot of single-item orders, you may want to raise your per-order fee slightly and decrease your per-item fee. Likewise, if you're doing multi-item orders, it might be wise to slightly increase your per-item fee and reduce your per-order fee.
  • Roll customs fees into your shipping cost for international shipping - It's not uncommon to add an additional surcharge to the rate for international shipments to cover the cost of customs fees. Simply add these to the prices on the rate card directly (Topspin does not have a "customs fee" in its pricing model) and you'll easily cover your cost. 
  • If you're pricing in international currencies, remember to double-check your exchange rates - Currencies fluctuate regularly, and while they're not likely to make a sudden jump outside of a exchange rate window, they will drift in one direction or another over time. Make sure you're not losing money on exchanges or charging fans too much (and discouraging transactions, lowering your bottom line). 
Avoiding Fraud By Not Allowing Shipment To Some Countries
If you have a policy of not allowing shipment to countries to avoid fraud or lost shipments (e.g. Nigeria), you can enforce this policy in a few easy ways. 
  • For worldwide cards, do not use a "*" catch-all in the country code area to signal "all-world" rates.
  • Do not enter rates for that country specifically - if you wanted to not ship to Niger, you would not enter the NE country code or any rates for it.
  • When uploading, use the "Select Excluded Countries" control. Open the tree and place a check in the boxes you do not want to ship to
This will eliminate the ability to ship to territories you do not fulfill to. 
 
Cutting Deals: Artist-Specific Rate Cards
 
If you have a special arrangement with an artist or management company to provide special rates, you can select the artists a rate card applies to.
  1. When uploading a rate card, under the "Select Artists" box, deselect the "Default" option.
  2. In the box that opens up, place a check by all artists that should have use of the rate card you are uploading. 
The artists will only use the rate cards you upload - they will not use the "Default" cards and the artist-specific cards. They will only see the cards that have been flagged for their use.
 
Wildcards - a shortcut for simpler cases.
 
If you want to enter rates for the US, Canada, and Mexico (for instance), and wish to have one flat rate worldwide, you can do that. You will need to use the wildcard.
Simply enter the rates for the countries you want to provide rates for, and make sure to use their country ID. 
 
Here is a quick example to show how you would expand the lightweight rate card to use wildcards and allow service worldwide for first-class package.
 
We'll refer again to the first class international rate card for packages at http://pe.usps.gov/text/dmm300/Notice123.htm#wp1118064
29.png
 
The USPS First Class rate card for packages, including pricing for international shipment to zones 3-5 and 6-9. 
 
This rate card has two sets of international zone pricing: zones 3-5 and zones 6-9. Since we want to cover everything, we will offer service at the most expensive rate - the zone 6-9 price, to ensure that we are not losing money. 
In the CURRENCY column on a new row, enter "USD"
In the COUNTRY column, enter "*" (just the asterisk). The asterisk is the "catch all" or "wildcard", and means "all countries". 
Leave the ZIP START and ZIP END columns empty.
Put 11 in the TRANSIT DAYS MIN column, and put 21 in the TRANSIT DAYS MAX column - our shipper advised this as the best estimate.
 
30.png
Proper use of the wildcard within your rate card. 
 
Once again, as before, start entering weights in this row, starting with one ounce (1.44), and continuing to 8 ounces.
 
31.png
Entering the first set of weights. 
 
Enter the rate for 12 ounces in the 9, 10, and 11 ounce columns, since the price for 9-11 ounces is the same as the price for 12 ounces.
 
32.png
Rates from 9-11oz use the 12oz rate.
 
Enter the rate for 16 ounces in the 13, 14, and 15 ounce columns, since the price for 13-15 ounces is the same as the price for 16 ounces.
 
33.png
Rates from 13-15oz use the 16oz rate.
 
Save and upload - you've got worldwide rates in a flash, courtesy of the wildcard!
 
Generally, when using the wildcard, you will want to use one of the more expensive shipping rates to ensure that you're not losing money.
 
You can use a maximum of one wildcard per currency - so you may have one wildcard for USD, one for CAD, and so on in your rate card.

Troubleshooting
 
"Your Cart Is Too Heavy"
This error is one that fans will encounter in one of two scenarios. 
  1. The fan's cart is heavier than the maximum allowed weight on any rate card. If it's possible to find out how many items the fan was purchasing, it can indicate if this was the problem. For instance, if your weight card only goes up to 25 lbs and the fan has enthusiastically added products that total up to a weight of 27 lbs, it will not be possible for them to check out. There are two possible ways to remedy this:
    • Advise the fan to split the order into multiple orders. This is the easiest solution for the fulfiller, but if this problem is encountered frequently, it may be advisable to...
    • Expand rate card coverage. We went up to 10 lbs in our example for brevity; but it's advisable to cover up to at least 25 lbs, if not higher. UPS rate cards, for instance, go up to 70 lbs domestically. A higher maximum weight will reduce the frequency of this error. 
  2. The fan's geography is not covered by rate cards. This has been seen on rare occasion and is not as easily recognized. In one case, there was a ZIP code range that was supposed to cover codes from 33000-39999. However, a fan with a ZIP code of 36326 was reporting the "Too Heavy" error with a weight of 1.5 lbs. This weight was over the maximum
Have more questions? Submit a request and our team will be able to help get you sorted.
Powered by Zendesk