Pending Price Changes, Want To Share How I Am Working This.

Discussion in 'Shapeways Shops' started by MadBikeSkills, Jan 15, 2019.

  1. MadBikeSkills
    MadBikeSkills Well-Known Member
    We all know about the price structure changes coming on January 31st. I am trying to make it as painless as possible for my shop so I decided I would work on a formula for recalculating all my markups and wind up with pricing that is both acceptable to me as well as following some of Shapeways shop owner suggestions.

    In my particular instance I have a minimum markup I want to set. It simply isn't worth my time if I cannot make a minimum amount even on small items. Next I want to make sure that all my pricing ends with the same fractional currency value since it is a psychological thing. Pleas note I only deal in USD. So I have come up with a way to accomplish both. I am going to show you an example using MS Excel, but I am sure other spreadsheets can do the same thing.

    So the first thing I do is download all the CSV files from Shapeways. It makes it easier if you already have them all in a folder on your PC rather than doing them one at a time. As an example, my shop requires that I download 4 CSV files for the complete inventory. Once I have them downloaded I can proceed to import the first file into Excel. After importing you should notice that column 'L' is the cost Shapeways collects from that particular model in that particular material, it is labeled 'base_price'. Column 'N' is the price you want to charge for your item and it is labeled 'total_price' in the CSV. So here is my example. I never want to make less than $5 USD on any given item, but I want to have at the very least a 35% markup. At the same time Shapeways suggests that your pricing end in .99 for the psychological consumer pricing effect.

    We are going to use two functions in Excel called INT and MAX. INT returns the integer only portion of the value. MAX compares two values and returns only the maximum of the two values. I am doing the formula completely in-cell so that I do not have to copy and paste multiple ranges into the spreadsheet. Row 2 is the first row that has actual model data, so in cell N2 I put in the following formula:

    =MAX((INT((L2+0.5)*1.35)+0.98), (INT(L2+5)+0.98))

    A bit more explanation is in order. the cost found in column 'L' is first increased by 0.50 then multiplied by the cost with the additional markup as a fraction of the cost. In my example above I want the price to be full price + 35% so I multiply my cost by 1.35. Adding the .5 before multiplying insures that when I drop the resulting decimal places I am still approximately +35%. The INT function takes the result and drops the decimal places, to which I add back .98. For example, say Shapeways charges 5.17 for an item, the first part of the equation would give you a price to charge of 7.98, but now I wanted to make at the very least 5.00 so I need a bigger markup for this model, this is where the MAX function comes in. So I take my formula which now looks like this =MAX(7.98, (INT(L2+5)+0.98)) and solve the back half. The back half of the calculation results in a total of 10.98, so now my equation looks like this, =MAX(7.98, 10.98). MAX will return the larger of the two values and will fill in 10.98 into cell N2.

    So you can see how this works on a higher priced item, I will illustrate it again with an item that Shapeways charges 19.49. The first value calculates to 26.98, the second expression calculates to 24.98. Now the equation looks like =MAX(26.98, 24.98) and places 26.98 in the 'total_price' column.

    Once I have copied the formula from cell N2 into all the subsequent cells in column 'N' the spreadsheet will recalculate all the pricing for you. Export the file to CSV then do the next one. Once you have all the files saved with the new pricing, upload them to your shop and automagically all your prices are set.

    I am not trying to say that the formula I provided is the only way to accomplish this, it is just a starting point. You may still have outliers that don't exactly fit your model pricing structure. Please if you have any suggestions on how to improve the formula or how to make it even easier, please feel free to comment.
    EvieL likes this.
  2. EvieL
    EvieL Well-Known Member
    Just wanted to say that as a former daily user of Excel, it makes me so happy to see this post.
  3. MadBikeSkills
    MadBikeSkills Well-Known Member
    Thanks @EvieL, I was dreading having to go through my entire shop and selecting the markup I wanted so I figured I would try this with my very rusty Excel skills.

    If you are not an Excel user, I tried the same formula in Google Docs Spreadsheet and it works as written.

    One thing to note the way I have written this formula, when you add the 0.50 it basically means that anything with a fractional cost 0.49 and under gets rounded down, anything 0.50 and over gets rounded up. You can change the addition if you want a different range, for example adding 0.75 will shift the rounding range down by 0.25.
    Last edited: Jan 17, 2019
  4. MadBikeSkills
    MadBikeSkills Well-Known Member
    I changed my formula a bit to better round up or down, it is now

    =MAX((INT(L2*1.35+0.5)+0.98), (INT(L2+5)+0.98))
    Last edited: Feb 2, 2019
  5. MadBikeSkills
    MadBikeSkills Well-Known Member
    Now complete, since I used a formula in combination with Excel, it was 8 minutes total to change 3200 prices.
  6. Nice work!

    I'm too much of a right brain person though to try to use your formula or even try to read your instructions. :p I have my shop closed, Universe Becoming, until Shapeways fixes the pricing. It's not that big of a loss though because my Shapeways shop slowed down to a very slow trickle. :D

    This is my first post in the forum from my new account that may be a shop I'm setting up just to try the new Etsy integration.
  7. MadBikeSkills
    MadBikeSkills Well-Known Member
    LWoF, not had to follow at all, and it works in Google Sheets as well as Excel. Here is the simplified logic step by step. MAX compares a list of values and only returns the highest one. Since the pricing CSV is in a standard format we can look at specific cells once it is imported. Column L starting at row 2 and downward are the Shapeways pricing, column N starting at row 2 is what you want to sell it for. So in column N we want to calculate what the Cost is and add a 35% markup. I used 35% as an example. That means we need to take column L and multiply it by 1.35. But we don't want odd number of cents; 1. because it just looks weird and 2. it leaves money on the table. So I add 50 cents, then take the whole number of that. Once I have the whole number I add 98 cents to the whole number and I have my first price. I also calculate using the same cost but this time adding just 5 dollars, taking only the whole number then adding 98 cents to the whole number. Now the MAX function compares the first and second price and the result is just the highest of the two prices. That way I a always guaranteed at least a 35% markeup but never less than 5 dollars. It is really simple.

    Anyway, that was the whole point of the exercise, I wanted consistency in my pricing, my entire store has all prices ending in $ 0.98 and I have it priced for a guaranteed minimum markup.
  8. Hmmm. . . I read it but I'm not able to follow along. :) I suppose if my life depended on it I could spend the effort to figure it out. :p

    Sounds pretty cools though overall! :D

    Sounds like you found a way to do a markup of all products in a shop via a percentage. That is something that has been highly requested over the years here in the forum.
  9. MadBikeSkills
    MadBikeSkills Well-Known Member
    Yep, it is a fixed percentage, but it relies on downloading the pricing CSV, and upload an updated pricing CSV. Most people would love to have just a percentage input field on the product page, but lacking that, I created this little one line spreadsheet function. Plus it was a way to make every price consistent within my Shapeways Store.

    Here was my thinking when I did this, If I have a model that costs 10 and I want to make 5 most people would just set the price at 15 and be done, 9.50 would be 14.50, etc, but then you have pricing that is all over the map, and if the model is 100 why should I only get 5. So my 10 item sells for 15.98, the 9.50 sells for 9.98, and my 100 sells for 135.98. That is what the formula does. it is pretty simple.
  10. This may also be extremely helpful for setting up variants on jewelry rings. Here's a discription of the current annoyence when setting up a new ring product. Let's say you have a ring that you want to offer in every quarter size from 0 to 16. That's 64 sizes. Now let's say you want to offer the ring in 10 of Shapeways' materials, that's a whopping 640 entries one needs to manually enter just for one ring. Also, for some materials, one cannot simply select a material and enter a blanket price for all of the sizes because, gold and platinum, for example, their making cost vary too drastically between each ring size.

    Another way your code could be useful is for making sales! Like, have the code reduce the price throughout the entire store by some percentage. Then one could use something like Winautomation to go in and add a line of text to each product description notifying customers of the sale and then after the sale concluded have it go back in and remove the line of text.
  11. MadBikeSkills
    MadBikeSkills Well-Known Member
    One thing that helps, is that the pricing wizard lets you download all materials, not just the ones you sell. In that CSV, column O has a value of not_for_sale or for_sale. If you want to enable in bulk you just change the value in column O to for_sale. Column J is either public or private. Those are really the only columns you should/could change, J, N, and O.

    I have over 300 items in my store, I have at the very least both Versatile and MJF PA12 enabled for every one, Almost 15,000 entries that I would have to change. You can see why I created the formula

    In your example its Valentines day and your normal markup is 30% and say you want to reduce everything by 10%, you could easily use either google sheets (online) or Excel (offline) to change the formula to reflect a 27% markup (30% - 10%(30%)).

    Since there is a limit of 250 products per CSV (my shop is 2 CSV files) I can tell you that after I downloaded both CSV files, the amount of time it took to create the formula, then copy it to every row, then save both files with the new prices was eight minutes. Download and upload was about 10 minutes total, so in less than 30 minutes I had all 15000 prices changed.
    Last edited: May 31, 2019
  12. UniverseBecoming
    UniverseBecoming Well-Known Member
    Interesting! Do you think you could make a tutorial for how to enter your formula in Excel?
    I was able to turn all of the private cells to public by using search and replace but I have no idea whatsoever how to run your formula.

    This is still James the Living Waters Of Life Guy. :)
  13. MadBikeSkills
    MadBikeSkills Well-Known Member
    Yeah easy peasy, I will try to outline it step by step, assuming you already know how to download the CSV files.

    Windows instructions:
    1. Double-click on the downloaded CSV file, the filename is usually in the form of UsernameYYYYMMDDHH_MM_SSCurrency.csv
    2. Office installs automagically associate .csv files with Excel, so Excel should open and your pricing data will appear in columns. excel01.png
    3. Do not touch row 1, it must remain intact without modification or else it won't import back into your shop properly.
    4. Move the cursor to cell N2
    5. You must type in the formula one character at a time, do not simply cut and paste it from here. Example will show a 30% margin and 5 dollar minimum markup and all resulting prices ending in $ .98.
      =MAX((INT(L2*1.30+0.5)+0.98), (INT(L2+5)+0.98)) excel02.png
      To change the markup, shown as 1.30 here, just figure out what you want and don't forget that the selling price has to be 100% + Markup%, so + 25% will be 1.25, + 50% will be 1.50, etc.
      To change the minimum acceptable markup, change the 5 in the second half of the formula to whatever your acceptable minimum is, for instance $ 3 minimum would be 3, Don't forget that Shapeways takes a 3.5% cut out of your markup as a handling charge, so maybe your $ 5 should be $ 5.19 (5.19 - 3.5% ~= 5 ).
    6. If you type it in successfully you will get no errors, now click the little check mark to the left of the formula you typed in. The formula in cell N2 will be replaced by the result.
    7. Now this next part is important because you want to change every price not just the first one. Right-click on cell N2 and select Copy from the context menu. An animated dash box should appear around cell N2
    8. Left-click on cell N3, then scroll to the last line in the spreadsheet and Shift Left-click on the last cell in column N, It should now have highlighted all the cells from N2..N????
    9. Put the mouse pointer somewhere in the now highlighted column and Right-Click.
    10. Select the icon under Past Options that has a little fx in it and Left-click it. It might take a second or two to display the results, but you have now made the entire column N recalculate the sell price using the formula you typed into cell N2
    11. The next step is important for re-uploading to Shapeways. Select File >> Save from the menu and the following warning will be displayed
    12. You actually want to select yes and lose the features, CSV files are simply text and excel will save the file exactly as displayed with all your new pricing data. Using Save will save it as the original filename it was downloaded under.
    I will assume at this point that you know how to upload the file back to Shapeways since you have mentioned that you already did this to bulk enable items in your store.
    Last edited: Jun 1, 2019
  14. UniverseBecoming
    UniverseBecoming Well-Known Member
    It worked! Thanks! :D