Home » Community » General Discussion » Mass Markup Tool Question
Search Search  
Show: Today's Messages    Show Polls    Message Navigator
Re: Mass Markup Tool Question [message #35176 is a reply to message #35173 ] Mon, 26 September 2011 18:27 UTC Go to previous messageGo to next message
avatar stonysmith  is currently offline stonysmith
Messages: 1865
Registered: August 2008
Go to my shop
Senior Member
moderator
stop4stuff wrote on Mon, 26 September 2011 18:20

I'm getting a bit lost here.

Q1) Is this tool a stand alone bit of software that anyone can use?
Q2) Will the 'tool' have an entry that will allow me to put in a % markup for all my models?

Cheers,
Paul


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.


Patience, Persistance, Politeness - the 3Ps will help us get us to Perfect Printed Products
Re: Mass Markup Tool Question [message #35177 is a reply to message #35175 ] Mon, 26 September 2011 18:28 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
tebee wrote on Mon, 26 September 2011 18:25

This is the great advantage of having this in CSV.

We can import that CSV into a spreadsheet, apply whatever formulas we want on it, then export the results to a new CSV and upload this back and change all our mark-ups rather than having to do each one individually.

That's 9351 numbers it's saved you keying in Smile


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.


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35178 is a reply to message #35174 ] Mon, 26 September 2011 18:29 UTC Go to previous messageGo to next message
avatar tebee  is currently offline tebee
Messages: 368
Registered: December 2010
Go to my shop
Senior Member
mctrivia wrote on Mon, 26 September 2011 18:22

........ Then copy all values in I and past value only over top of it.



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.
Re: Mass Markup Tool Question [message #35179 is a reply to message #35178 ] Mon, 26 September 2011 18:32 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
tebee wrote on Mon, 26 September 2011 18:29

mctrivia wrote on Mon, 26 September 2011 18:22

........ Then copy all values in I and past value only over top of it.



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.


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35180 is a reply to message #35178 ] Mon, 26 September 2011 18:36 UTC Go to previous messageGo to next message
avatar stonysmith  is currently offline stonysmith
Messages: 1865
Registered: August 2008
Go to my shop
Senior Member
moderator
tebee wrote on Mon, 26 September 2011 18:29

... I'm fairly sure the default is to export values only.


100% sure.. CSV supports values ONLY.

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


Patience, Persistance, Politeness - the 3Ps will help us get us to Perfect Printed Products
Re: Mass Markup Tool Question [message #35181 is a reply to message #35129 ] Mon, 26 September 2011 18:58 UTC Go to previous messageGo to next message
avatar stonysmith  is currently offline stonysmith
Messages: 1865
Registered: August 2008
Go to my shop
Senior Member
moderator
mctrivia wrote on Mon, 26 September 2011 15:37

... for my shop I will have 9351 lines(374*25+1).


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>


Patience, Persistance, Politeness - the 3Ps will help us get us to Perfect Printed Products
Re: Mass Markup Tool Question [message #35182 is a reply to message #35176 ] Mon, 26 September 2011 19:03 UTC Go to previous messageGo to next message
avatar stop4stuff  is currently offline stop4stuff
Messages: 3109
Registered: June 2010
Go to my shop
Senior Member
stonysmith wrote on Mon, 26 September 2011 18:27

stop4stuff wrote on Mon, 26 September 2011 18:20

I'm getting a bit lost here.

Q1) Is this tool a stand alone bit of software that anyone can use?
Q2) Will the 'tool' have an entry that will allow me to put in a % markup for all my models?

Cheers,
Paul


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.


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
Re: Mass Markup Tool Question [message #35183 is a reply to message #35182 ] Mon, 26 September 2011 19:14 UTC Go to previous messageGo to next message
avatar tebee  is currently offline tebee
Messages: 368
Registered: December 2010
Go to my shop
Senior Member
stop4stuff wrote on Mon, 26 September 2011 19:03


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

,
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......
Re: Mass Markup Tool Question [message #35184 is a reply to message #35182 ] Mon, 26 September 2011 19:16 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
thank you so much for telling me about vlookup. what would have been a nitemare turns out to be only

=IF(F2<=5,VLOOKUP(B2,Sheet2!A2:F18,IF(F2<=1.5,3,IF(F2<=3,4,IF(F2<=4,5,6)))),CEILING(I2*(1+VLOOKUP(B2,Sheet2!A2:G18,7))+5,1)-0.01)-I2


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.


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35185 is a reply to message #35184 ] Mon, 26 September 2011 19:17 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
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


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35186 is a reply to message #35183 ] Mon, 26 September 2011 19:19 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
tebee wrote on Mon, 26 September 2011 19:14

stop4stuff wrote on Mon, 26 September 2011 19:03


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

,
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......



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.


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35187 is a reply to message #35185 ] Mon, 26 September 2011 19:44 UTC Go to previous messageGo to next message
avatar stop4stuff  is currently offline stop4stuff
Messages: 3109
Registered: June 2010
Go to my shop
Senior Member
mctrivia wrote on Mon, 26 September 2011 19:17

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



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 Smile

[Updated on: Mon, 26 September 2011 19:44 UTC]

Re: Mass Markup Tool Question [message #35188 is a reply to message #35187 ] Mon, 26 September 2011 20:05 UTC Go to previous messageGo to next message
avatar Magic  is currently offline Magic
Messages: 1163
Registered: August 2008
Go to my shop
Senior Member
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).

[Updated on: Mon, 26 September 2011 20:06 UTC]


So many things to design, so little time...
Re: Mass Markup Tool Question [message #35203 is a reply to message #35188 ] Mon, 26 September 2011 22:03 UTC Go to previous messageGo to next message
avatar Magic  is currently offline Magic
Messages: 1163
Registered: August 2008
Go to my shop
Senior Member
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).


So many things to design, so little time...
Re: Mass Markup Tool Question [message #35211 is a reply to message #35203 ] Tue, 27 September 2011 01:04 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
There has got to be a better way then this:

=IF(ISNA(VLOOKUP(A2*10000+B2,'Special Case'!A$2:F$999,5,FALSE)),IF(G2="Dice",IF(F2<=5,VLOOKUP(B2,'Pricing Standards'!A$3:F$31,IF((F2/IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)))<=1.5,3,IF((F2/IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)))<=3,4,IF((F2/IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)))<=4,5,6))),FALSE)*IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE))-(VLOOKUP(B2,'Pricing Standards'!A$3:H$30,8,FALSE)*(IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE))-1))-0.01,CEILING(I2*(1+VLOOKUP(B2,'Pricing Standards'!A$3:G$31,7,FALSE))+5,1)-0.01),IF(G2="Rings",VLOOKUP(B2,'Pricing Standards'!A$33:F$60,3,FALSE)*IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),0)),VLOOKUP(A2*10000+B2,'Special Case'!A$2:F$999,5,FALSE))-I2


It got complicated because I know of know way to add variables:
ZZA=(F2/IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)))
ZZB=IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE))
ZZC=
VLOOKUP(B2,'Pricing Standards'!A$3:H$30,8,FALSE)*(IF(ISNA(VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE)),1,VLOOKUP(A2,'Bulk Items'!A$2:C$999,3,FALSE))-1)

=IF(ISNA(VLOOKUP(A2*10000+B2,'Special Case'!A$2:F$999,5,FALSE)),
	IF(G2="Dice",
		IF(F2<=5,
			VLOOKUP(B2,'Pricing Standards'!A$3:F$31,IF(ZZA<=1.5,3,IF(ZZA<=3,4,IF(ZZA<=4,5,6))),FALSE)*ZZB-ZZC-0.01,
			CEILING(I2*(1+VLOOKUP(B2,'Pricing Standards'!A$3:G$31,7,FALSE))+5,1)-0.01
		),
		IF(G2="Rings",
			VLOOKUP(B2,'Pricing Standards'!A$33:F$60,3,FALSE)*ZZB,
			0
		)
	),
	VLOOKUP(A2*10000+B2,'Special Case'!A$2:F$999,5,FALSE)
)
-I2


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35218 is a reply to message #35211 ] Tue, 27 September 2011 04:32 UTC Go to previous messageGo to next message
avatar christopherlowe  is currently offline christopherlowe
Messages: 138
Registered: April 2011
Go to my shop
Senior Member
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


please visit my blog at...
http://chrisshopofmodels.blogspot.com
Re: Mass Markup Tool Question [message #35219 is a reply to message #35218 ] Tue, 27 September 2011 04:35 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
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.


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35220 is a reply to message #35219 ] Tue, 27 September 2011 04:43 UTC Go to previous messageGo to next message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
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)


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/
Re: Mass Markup Tool Question [message #35223 is a reply to message #35220 ] Tue, 27 September 2011 05:49 UTC Go to previous messageGo to next message
avatar Magic  is currently offline Magic
Messages: 1163
Registered: August 2008
Go to my shop
Senior Member
Quote:

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

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?


So many things to design, so little time...
Re: Mass Markup Tool Question [message #35241 is a reply to message #35223 ] Tue, 27 September 2011 10:59 UTC Go to previous message
avatar mctrivia  is currently offline mctrivia
Messages: 808
Registered: September 2010
Go to my shop
Senior Member
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.


Follow me on twitter http://twitter.com/mctrivia or my blog at http://4ddice.blogspot.com/

Pages (2): [ «    1  2] 
   
Previous Topic:It Prints then it Won't
Next Topic:Greatest idea ever! (Cool for Halloween too)

Logo

Hello.

We're sorry to inform you that we no longer support this browser and can't confirm that everything will work as expected. For the best Shapeways experience, please use one of the following browsers:

Click anywhere outside this window to continue.