Mass Markup Tool Question

Paul, I would not call this a "tool". It is simply a file of all the items and materials in your shop. There are NO calculations performed by the "tool".

You can load the file into Excel, Google Docs, many other programs and make any changes [to the markup] that you wish. You then convert the file back to CSV format, and upload the file to Shapeways. Your shop markups are updated based upon the information you've recorded in the file.

You can apply any level of complexity the Markup calculation that you have the desire or skills for. This new thing Nancy is offering is simply a way for you to get the background data, and then a method for mass-updating the Markups in your shop.

Yes CSV is great and now that I know about vlookup this format is not so bad. though the first line is headers so 9350 numbers I don't have to type in.

I will write a app to add a set percent markup to everything and make available to all.

You won't need to do this as you going to have to export as CSV (I presume) and I'm fairly sure the default is to export values only.

Just tried it. You are correct. 1 step less and no reason to write the app now. To simple.

100% sure.. CSV supports values ONLY.

(unless you pull some of the really esoteric tricks that I use. <grin>)

Another small note.. I'm making a rather rash assumption here, but.. if you only want to change the price on a couple of models or materials, then the file you UPLOAD only needs to contain the rows you are trying to change. The rest of the items in your shop [should] not be affected.

We'll need to see the official instructions on Wednesday, but that's what I'd expect.

Sorry Nancy, but, yes: the programmers WILL need to do a little documentation. <grin>

ah, i get it now, cheers stony.

I think I'll stick to just applying my $ value markup as I release a model for sale.

Thanks again,
Paul

Of course the other nice thing you can use this for is just to extract all you current markups into a spreadsheet and run an analyse on them to check there are OK.

If you are really ambitious you could cross-reference it with the shop sales spreadsheet and see if your most popular items had for example a low mark-up or were in a particular price range.

I can see us needing a new section on this forum for spreed sheet tutorials......

thank you so much for telling me about vlookup. what would have been a nitemare turns out to be only

Sheet 2 contains my lookup info for pricing. This give me set prices under 5cc and variable if above based on a different percent per material. so I.E. WSF is 9.99 if under 3CC.

What are the material IDs? Iknow the following:

6 White Strong & Flexible
62 WSF Polished
25 Black Strong & Flexible
59 Dark Grey Strong & Flexible
58 Indigo Strong & Flexible
45 Red Strong & Flexible
Alumide
23 Stainless Steel
38 Antique Bronze Glossy
Antique Bronze Matter
39 Gold Plated Glossy
31 Gold Plated Matte
53 Silver
54 Silver Glossy
High Gloss Black Glass
High Gloss White Glass
Milky White Matte Glass

A spread sheet tutorial will be a must. Look at the formula I am using on mine. It does some pretty powerful things once you know how to use it.

the material Id values are all in the edit page source code for a model with all materials available, and can be found by searching the page for 'data-material-id' ... that much I know

In attachment you will find the kind of formula I will use (I will have to choose the appropriate values to stick to my currrent prices).
Basically you take the Shapeways price add a fix value or a percentage or both, then round to the next multple of something and then substract a small amount like 1 cent to have $x.99 for instance.
The formula is also based on VLOOK (called RECHERCHEV for me, I hope you will see the formula in you own language)

Example for an object that Shapeways sells me $1.48:
- as it is between $0 and $9.99, I would add 30% (total $1.924)
- then add a fix fee of $3 ($3.924)
- then round this number to the next multiple of $1 ($4)
- then substract one cent ($3.99)

for $11.00
- as it is between $10 and $24.99, I would add 20% (total $13.20)
- then add a fix fee of $4 ($17.20)
- then round this number to the next multiple of $2 ($18)
- then substract 10 cent ($17.90)

Once again these are just examples
You can modify all the cells in yellow (the orange ones are calculated and the green ones are for your information only).

To show how powerful such a formula can be, here is another example with negative values.
A negative percentage allows you to change the direction of the curve (here I used -100% to make 2 plateaux at $9.99 and $19.90), and a negative fixed value allows you to make the curve continous (try to change the -$5 to $0 to see what happens).

There has got to be a better way then this:

It got complicated because I know of know way to add variables:

looks great for now... i assume that in the future upload information will be increased to include comments descriptions and other what-nots...

also- are the object numbers exclusive to each shop? if they are not and somehow they get changed- won't that mess up someone elses shop? just checking...

c

object numbers are exclusive. Try clicking on and replacing the object number in the url with a random number. You can find some interesting things that way. You will get 404 error if model is hidden.

Assumptions:
1) If I upload a CSV file containing only a sub set of models in my shop prices for these models only will be updated.
2) If I set markup to a value less then 0 then the product will not be available in that material. It will not be made available for $0 markup.

If these 2 assumptions are true then the following options would be really nice:
1) Download Unavailable material: Checkbox default off
2) Download Changed Data Only: Checkbox default off
3) Upload minimum Markup: Textbox default $0.01

1) would be nice because 90% of the time I have already set what materials I want things to be available in and just want to update these. When I want to see all materials my workflow would be completely different so having this as a checkbox defaulting off instead of just being always on would be nice. Always off is not an option because it removes way to many uses for CSV.

2) Any time you put out a new material or change the pricing scheem of a material or I upload a new model it would be nice If there was a simple way to get all these together in 1 csv by themselves. Ideally you would keep track of last time csv was downloaded and generate everything new/different from that time when checked. This is much lower priority from number 1 since new materials we can sort by material type, and new models we can sort by model id but for the less advanced excel users(and us lazy ones) this would be a big bonus.

3) Mistakes happen. And your system does not report sales if the markup is $0. So if markup is less then 0 set to unavailable and if less then a specific threshold set to the threshold. Default to $0.01 just so your system will report the sale and if you want to be more fancy save each shop owners prefered minimum markup but for a start set it to $0.01 so at least we get an email saying you made a penny which is a big clue we made a mistake and need to fix something. This minimum should also apply to new materials added(though I would prefer you set new materials to not available)

Unless you want to make a macro in Visual Basic, a variable in Excel is a cell:
- instead of assigning a formula to a variable you put it into a cell
- instead of refering to a variable in another formula you refer to the cell where you put the formula
Makes sense?

yep I did not think there was a way. Will just keep a copy in notepad that can be replaced to get formula. the bottom version is much easier to read.