PDA

View Full Version : Thousands of Products upload with options help



ryanb4614
07-10-2012, 07:43 AM
I have worked with zen-cart and now using magento... I am exploring all options so my customers have the best experience on my store.
I list 15,000 products on my website. My distributor give me a simple excel spreadsheet that I run a macro script to adjust the categories, images, and all other column so it can easily upload to my store. But one issue I always have been avoiding is that there are multiple attributes for the same product What I have been doing is normally uploading all the different attributes as a separate product, but this leads to the customer having a hard time locating the correct color or correct size. For example, if I sell a dog crate

Crate comes in multiple sizes
18 inch
23 inch
30 inch
42 inch
Fish filter
100 series 59.99
200 series 79.99
300 series 89.99
etc all different sku number, prices and weights. They have them on the spreadsheet but they are in each of their own rows. Are there any solutions out there for this? I am sure the major chain stores don't have people listing one product at a time and adding the attributes. There has to be a simpler solution.

Harold Mansfield
07-10-2012, 10:35 AM
I don't have a solution to your problem, but major chain stores DO have full time people constantly updating their website. One of the problems I have with my clients is that they are constantly trying to compare other websites from much larger companies that not only started with a larger budget, but continue to employ a full time staff.

And sites like Amazon allow authors to create their own page ( I just found this out). So I assume retailers have simular privileges, and they probably have a team of their own that does nothing but web marketing.

So don't beat your self up trying assuming that they have some kind of automated system that one guy runs. That is not the case. Not even close.

vangogh
07-10-2012, 12:49 PM
^ What Harold said. I'll add a couple of things. First is that while companies like Amazon can hire employees to handle more data input, keep in mind their system is custom made. They aren't using Zen-Cart or Magento or any other readily available system. Their site is custom made for the kind of things they want to do. If you can afford it, you can likely have someone develop a custom system for you that would make things easier. You aren't going to find a ready made solution.

Second do you really need 15,000 products? How many of those 15,000 actually sell? I don't know your site, but based on your description here it sounds pretty generic. Why would someone buy from you as opposed to all the other sites with the same list from the same distributor. Why not focus on a smaller subset of those 15,000 products instead? You'd have a much easier time uploading everything and you could create content around the subset of products to make them stand out from everyone else. Be the go to company for a few products and then start expanding the products you offer.

MyITGuy
07-10-2012, 05:29 PM
Sounds like you need to setup "Configurable Options"
Magento - Knowledge Base - Tutorial: Creating a Configurable Product - eCommerce Software for Growth (http://www.magentocommerce.com/knowledge-base/entry/tutorial-creating-a-configurable-product/)

billbenson
07-10-2012, 06:23 PM
^ What Harold said. I'll add a couple of things. First is that while companies like Amazon can hire employees to handle more data input, keep in mind their system is custom made. They aren't using Zen-Cart or Magento or any other readily available system. Their site is custom made for the kind of things they want to do. If you can afford it, you can likely have someone develop a custom system for you that would make things easier. You aren't going to find a ready made solution.

Second do you really need 15,000 products? How many of those 15,000 actually sell? I don't know your site, but based on your description here it sounds pretty generic. Why would someone buy from you as opposed to all the other sites with the same list from the same distributor. Why not focus on a smaller subset of those 15,000 products instead? You'd have a much easier time uploading everything and you could create content around the subset of products to make them stand out from everyone else. Be the go to company for a few products and then start expanding the products you offer.

Steve, my manufacturer gives distributors a price list with about 1500 products in it. Some products come in XS, Small, Med, Large, XL, XXL, and XXXL for example. The manufacturer doesn't put all the sizes in there and each size has a unique part number. Over the years, every time I run into a part number that isn't in the price list, I call the manufacturer for the XXL for example, get the part number and price. I then enter that into my personal db.

My personal database now has about 4500 products in it. Of course when a new price list comes out, it only has the 1500 items in it so I guess when doing price updates to the database for the items not in the published list. To make matters worse, they give us an excel price list and printed price list which often conflict or have different information. This is coming from a 1.2 B company! I'm told there are about 13,500 different products commonly sold.

About 50% of my customers know the product part number. I would LOVE to have all 13,500 products on my site. Do you realize how much telephone time that would save me!

@ the OP - I think having a different part number is probably the easiest way for the small business. As for the different attributes, I would add them as links in the body of each add.

I didn't have a chance to look at the link MyITGuy posted, so maybe it has a better solution?

vangogh
07-11-2012, 12:17 AM
Bill I've seen your price list and your database. :)

I think I've mentioned to you before the same thing I said above. I know you'd like all 13,500 products in your database, but realistically how many of those will you sell? You're either going to have to spend time or money getting all those products into the database. Is what you would spend worth how much you'd really earn back?

Keep in mind anyone can get the same list of products as you and sell them the same as you. You're all using the same product images and mostly the same descriptions provided by the manufacturer. There's little to set one business apart from another. Why not take a small chunk of all the products and build something exceptional around them? Stand out so much for one set of products that you dominate the industry for those few products. Then add another chunk of products and do the same. Then another and another. That seems like the better strategy to me.

MyITGuy
07-11-2012, 12:42 AM
I would LOVE to have all 13,500 products on my site. Do you realize how much telephone time that would save me!

Out of curiosity, whats stopping you from listing all 14K products? If it's a formatting issue feel free to PM/Email a sample of the file as well as what it needs to be converted to for import.

If it's pricing not being available, then couldn't you list the basic information along with a "Quote" button instead of a "Buy" button to prevent some of those calls?

billbenson
07-11-2012, 04:26 AM
Out of curiosity, whats stopping you from listing all 14K products? If it's a formatting issue feel free to PM/Email a sample of the file as well as what it needs to be converted to for import.

If it's pricing not being available, then couldn't you list the basic information along with a "Quote" button instead of a "Buy" button to prevent some of those calls?

The issue is I don't have access to the information on the 14k product information. I get the same 1500 product price list update every year as does everyone else.
I actually have more products on my site than the manufacturer does. It's not for proprietary reasons, its because the marketing department doesn't talk to customer service who doesn't talk to engineering who doesn't talk to whoever puts out the price list.


then couldn't you list the basic information along with a "Quote" button instead of a "Buy" button to prevent some of those calls

That's a good point. But it goes back to the full features of of the inexpensive carts out there. I have constantly updated my MySQL db with every quote I do. But my quote db is cryptic and someone needs to make pretty pages out of my db to put it on the site.

But perhaps that isn't necessary. I have thought of putting a quick quote generator using my database. Enter a product, quantity, and email and they get a quote. That's all a purchasing agent really wants anyway.

To be clear, the reason I have my separate db from the cart is I can save data the cart doesn't like costs which most carts won't. I also add in there information so I can easily do annual price updates. Remember, of the 4500 products, the company only gives me info on 1500. What do I do about price updates on the stuff that isn't in the annual price list.

It's messy as a one man show!

billbenson
07-11-2012, 05:13 AM
Bill I've seen your price list and your database. :)

I think I've mentioned to you before the same thing I said above. I know you'd like all 13,500 products in your database, but realistically how many of those will you sell? You're either going to have to spend time or money getting all those products into the database. Is what you would spend worth how much you'd really earn back?

Keep in mind anyone can get the same list of products as you and sell them the same as you. You're all using the same product images and mostly the same descriptions provided by the manufacturer. There's little to set one business apart from another. Why not take a small chunk of all the products and build something exceptional around them? Stand out so much for one set of products that you dominate the industry for those few products. Then add another chunk of products and do the same. Then another and another. That seems like the better strategy to me.


13,500 products in your database, but realistically how many of those will you sell

For clarification: I have 4500 products in my db of the 13..5k products and there are 1500 products in the price list.

The problem, Steve, is I need to service all my customers, whether he needs a $5 part from a system or wants to buy a 10k product. If it is the 10k product, I can put out a quote in 5 minutes in most cases. If it is the case of the $5 part, I spend 30 minutes on the phone with the manufacturer trying to figure out what the part is and make no money. It's hard to abandon the $5 quote jobs though, since I have had occasions when they come back to me and say ok, we are just going to buy a whole new system for 5k or whatever. Bottom line, it costs a lot of time.


Keep in mind anyone can get the same list of products as you and sell them the same as you. You're all using the same product images and mostly the same descriptions provided by the manufacturer. There's little to set one business apart from another. Why not take a small chunk of all the products and build something exceptional around them? Stand out so much for one set of products that you dominate the industry for those few products. Then add another chunk of products and do the same. Then another and another. That seems like the better strategy to me.

That is what I would describe as a different business model. And a valid one. However, the competition goes off the 1500 product list and usually puts up a few hundred. This is true for both large distributors like Grainger and small operations like me. None of them will have the extra 3000 products in their db that I have gathered over time.

Also, the subsets of products you suggest I push are just that. Subsets of an interwoven product line. I can't just push product A, because they will need product B and C to make it work. But product B and C also have very diffeerent applications that often don't require product A. You really can't split this apart unless you go down to the piece parts (nuts and bolts).

I see a selling stratagy as having all the products (I can) there with prices. The purchasing agent gets a request for 17 of part number xyz from engineering and my contact. They don't usually price search, the purchasing agent just buy's it. Sometimes I've done a lot of preselling. Sometimes

Hope I didn't say anything stupid here. It's 5am and I couldn't sleep...

vangogh
07-12-2012, 03:30 PM
That is what I would describe as a different business model.

Oh I agree. It is a different business model I'm describing. I'm suggesting it could be just as profitable a business model with a lot less of the work involved. I understand if you don't have every product listed then you won't be able to sell to everyone. But do you need to? Isn't it possible you could sell less products to more people and overall sell just as many products as you do now? Focusing on the subset would allow you to more easily include all those $5 parts needed for the subset of products and create the content specific to the subset of products to better stand out.

Once you become the go to source for Product A, you can then build out the same for Product B and so on. Again I realize it's a different business model or perhaps simply a different strategy, but different doesn't mean worse or better.

Even with your current set up why not focus on improving some product lines instead of worrying about including everything else the manufacturer makes. Get one line in order and move on to the next. You won't have every product on the site right away, but you don't have them there right now anyway.

billbenson
07-12-2012, 11:17 PM
Oh I agree. It is a different business model I'm describing. I'm suggesting it could be just as profitable a business model with a lot less of the work involved. I understand if you don't have every product listed then you won't be able to sell to everyone. But do you need to? Isn't it possible you could sell less products to more people and overall sell just as many products as you do now? Focusing on the subset would allow you to more easily include all those $5 parts needed for the subset of products and create the content specific to the subset of products to better stand out.

Once you become the go to source for Product A, you can then build out the same for Product B and so on. Again I realize it's a different business model or perhaps simply a different strategy, but different doesn't mean worse or better.

Even with your current set up why not focus on improving some product lines instead of worrying about including everything else the manufacturer makes. Get one line in order and move on to the next. You won't have every product on the site right away, but you don't have them there right now anyway.

In a way I do Steve. I get emails and phone calls just because of the brand name. My phone messages are transcribed, usually reasonably well. I can not answer my phone and look at the message to see if it's worth my time. I don't really want to bother with a sale under $200 unless things are really slow. I scan my emails and email messages of transcribed calls and prioritize them by the likelihood of closure and the dollar amount of a possible sale. However, not answering the phone looses orders. A lot of people won't leave a message and it could be for a large order. Also, a lot of people will call with a question about some small piece part, but also need an expensive product.

A good clerk who could screen the calls would be a good way of dealing with this.

As you know, my product is an unusual one. There are only two companies in the world with the full product lines. There are plenty of other distributors out there that just sell particular parts of their product line. They tend not to be very knowledgeable in the products as well. So I carved my niche by becoming an expert on the products and selling the full product line. This strategy is more evolutionary than planned.

Since I will never have the full 13.5k product database anyway, part of this issue is moot. I do have the 4500 product database instead of the 1500 product one they give us because I either quoted or sold the product. One of the things I've thought about for the new site is rather than put the rest of the products on the site is add a quote generator to the site. Enter your name, phone, email, product quantity and part number and you get an instant quote emailed to you.

As I've mentioned before, a very high percentage of my customers already know the part number. A large part of my sales strategy is to get an email quote to my prospect. Now he has my contact, model numbers etc. He may be an engineer or some field guy and just forward it to purchasing. At that point it's a numbers game.

ryanb4614
07-13-2012, 07:07 AM
As for my products I don't "have too" list all these products. But I sell general pet supplies so there around thousands of products. But as mentioned above I have for example bowls that come in different sizes same with cages, apparel, etc. I would say more than 3/4 of my products come in a different color or size. But my distributor doesn't have the ability to provide me with a spread sheet of the products with variations. They just list every product they carry on the spread sheet. That is what I usually upload when I am done configuring the file to meet my formatting requirements for the upload. I do remove quite a bit of product but, I still have multiple products that have different sizes. But I cannot see going through thousands of products to add the different attributes. I would think that there would have to be an easier solution because all the other websites out there have a simple dropdown box with the different options available for that specific product.

ryanb4614
07-13-2012, 07:10 AM
Out of curiosity, whats stopping you from listing all 14K products? If it's a formatting issue feel free to PM/Email a sample of the file as well as what it needs to be converted to for import.

If it's pricing not being available, then couldn't you list the basic information along with a "Quote" button instead of a "Buy" button to prevent some of those calls?

I have done this when I first started out but its impossible to do. I run weekly updates of quantity available and any price changes.These products must be able to be imported into my store.

vangogh
07-16-2012, 11:31 AM
@Bill - That all makes sense and I'm definitely not trying to suggest I know more about your business than you. You seem to be doing pretty good with it. If you need to have all those products listed then you should list them. The quote generator makes sense, especially if your customers will know what they're looking for in advance.

@Bill and Ryan - If you both do want to include as many products as possible on your sites I think the solution is to set things up on your site so data entry is as easy as possible. You may not be able to automate every aspect of this, but if something is developed to make manual entry easy you could still add all the products to your sites. Automate what you can and with the rest either make it a habit to spend X hours each week working data entry or hiring someone for those X hours.

billbenson
07-16-2012, 12:40 PM
OsCommerce, Zen Cart and others have programs that allow you to edit product information including descriptions, categories, prices etc in excel and then do a bulk upload. It surprises me that Shopp doesn't have that. It's really a necessity for sites with a lot of products.

Ryan has a much more difficult situation than I do. It sounds like he needs to change pricing or edit products on a weekly basis. I'm assuming he is getting different products from different suppliers. That's a lot of constant maintenance.

MyITGuy
07-16-2012, 05:41 PM
I have done this when I first started out but its impossible to do. I run weekly updates of quantity available and any price changes.These products must be able to be imported into my store.

Not impossible...you just have to find the right tool/process.

For example, instead of importing the new list through your platform, why not go directly to SQL. If you're just updating quantity and pricing this shouldn't be an issue.

If this isn't an option, then you should look to see if the configurable options can be set during import and have a process populate this field as needed. It may not catch all of them off the bat, but after some time you would get near 100% accuracy.

billbenson
07-16-2012, 09:15 PM
Jeff, I have a wordpress site with the shopp plugin for ecommerce. There are some batch insert tools out there, but none I've found any good reviews of. If I can't find something I like, I want to write something. My plan is to put them all in a miscellaneous category with crude descriptions (the descriptions that are in my personal quote database), and as I have time to rewrite them and make them pretty, move them to the proper category via the admin panel. That way, if someone searches for a product, they can find it and buy it.

So if I do need to write my own program to upload probably 2500 products or more, I need to know all the tables that are affected by an insert.

I would like to go through the admin panel and insert a product and then go to the database and see which tables were affected. Do you know how to find out which tables were affected on an insert? Is there a php command that will do this?

vangogh
07-19-2012, 12:20 PM
It surprises me that Shopp doesn't have that.

It doesn't surprise me. Not everyone has that many products they want to upload. I would think most ecommerce set ups have a lot less so the need isn't there for most. Clearly it is for some, but not most.


Do you know how to find out which tables were affected on an insert?

Why not just look through the database. There aren't a lot of tables in there and some are clearly not about products. If I remember correctly product information went into 3 maybe 4 tables.

billbenson
07-19-2012, 01:00 PM
Why not just look through the database. There aren't a lot of tables in there and some are clearly not about products. If I remember correctly product information went into 3 maybe 4 tables.

That's true, but a programmer once showed me a real easy way. I think it was with phpmyadmin but I'm not sure.

MyITGuy
07-19-2012, 01:54 PM
J
I would like to go through the admin panel and insert a product and then go to the database and see which tables were affected. Do you know how to find out which tables were affected on an insert? Is there a php command that will do this?

You may find this helpful, although your host may have to run the commands:
MySQL :: MySQL 5.0 Reference Manual :: 5.2.2 The General Query Log (http://dev.mysql.com/doc/refman/5.0/en/query-log.html)

Otherwise you can take a look at the PHP code itself, or just review the tables...can't be to difficult to decipher if needed (Let me know if you would like some assistance).

billbenson
07-19-2012, 11:53 PM
Thanks Jeff. That seems a lot more difficult than Vangogh's approach since only a couple of tables are affected. I was more interested in the method I was shown a few years back that was real easy. Since all of these will be inserts, it should be easy to see by doing an insert from the admin panel and looing for addition ID rows in the tables.

Thanks again though.

vangogh
07-20-2012, 05:38 PM
Bill when I was uploading data to the new site, what I did was look through the Shopp tables inside WordPress to see which needed the info. I think there are 3 maybe 4 tables. product, price, catalog, and categories. I looked at the spreadsheets I exported from your database (I forget how many there were), looked at the how the spreadsheets needed to be set up to be imported into the Shopp tables, and then converted from your old to the new.

Mostly that involved copy a column from spreadsheet A to spreadsheet B. The most difficult part was making sure I could tie each row in the column together through a common set of values in a column in both. I did it manually since it was all a one shot thing for me, but I'm sure the whole thing could be automated. Once you have the spreadsheets set up the way Shopp wanted it was a simple matter of importing them through phpMyAdmin.

If I remember correctly another difficulty was that your data has quotes and other html stored in side that threw off the spreadsheets so I had move certain things around. I think it was because some of the data stored was what had to be used as a field separator on export.

If you go through the process once manually of converting what you get from the manufacturer to what Shopp needs, you should be able to figure out how to automate for the next time.

billbenson
07-20-2012, 10:01 PM
Well there are two things that are going to make this easier. One is they will all go into one category and there is limited information. Category, short and long description, and price. I'll put the same description for the short and long descriptions. That should do it.

It still stays down in the priority list because I'm overly busy with the existing site (spend a 12 hour day answering emails and the phone) and I worry about change affecting SERPS.

There is even a shopp utility that is supposed to import. It's buggy from what I read. The problem with some of these utilities is they try to be all things to all people which makes the software overly complicated. I might as well write something that just works for what I need.

vangogh
07-26-2012, 11:22 PM
I'm not sure why a single category and duplicate descriptions would help. I do know it wouldn't lead to the most helpful content on the site.

billbenson
07-26-2012, 11:55 PM
As we discussed it places all of the products on the site. The ones that haven't been made pretty would appear under misc and would appear in a search or be available for online purchase. As the products under the category are edited they would be placed in the proper category.

vangogh
07-27-2012, 05:26 PM
I can understand. I guess I just don't think it's all that hard to categorize them when they first go up on the site. It's more work, but it's not that much more work. But yeah, tossing everything into a single category will be easier. As long as you do then continue to move things into more appropriate categories and work on the copy so it's not a quick short description from the manufacturer for every product.