PDA

View Full Version : Price Update On an ecommerce site



billbenson
09-29-2008, 04:24 PM
The below might help some people using ecommerce sites and particularly those using things like Yahoo Stores where you don't really have access to your database.

I have an ecommerce site with over 2000 products. The manufacturer comes out with a new price list. Some prices doubled, some stayed the same, some went up a bit, some went down. Some aren't on the new price list. It's hard for me to believe a manufacturer would put out a price list that doesn't have all the products and doesn't state what the new products are.

I have to have the prices current or I could loose money. I had to do this on a weekend as there are few customers on weekends. I went to bed at 6am monday morning. I have a headache.

I currently use oscommerce for the my site. I would like to change, but thats a different thread. I did something that technically you shouldn't do. I wrote a table that has part number, weight, price, cost, short description, long description, image name, category, etc. for each part number I have. When I get a new part number, I enter that into that table. The reason you shouldn't do this is now you have pricing, descriptions etc appearing in two different places in your database. Which one is correct?

I also use that table for a quote generator I wrote.A lot of times people call in for a quote or place an order. I use that table as the master price list and generate quotes from that.

So the new price list has:


descriptions that may be worse than the ones I already have
new products
price changes
obsoleted products
omitted products
new prices


So, how do you deal with this. Personally, I made a table (for the Yahoo etc users, keep it in excel) that contains all the pertinent info. I first write a program that updates this table with the new info. I then write programs to use this table to update the information on products scattered through the site. There are currently 72 tables, many of which are ones I made.

I'm not sure if this is the best approach, or even a good approach, but it at least allows me to do updates. For products that aren't in the new price list, I just have a program that bumps them by 5%. I also have editing programs for those so I can easily modify that price and cost if I find the price was not a good one. That is all outside oscommerce, so it is transportable to other e commerce solutions.

vangogh
09-29-2008, 08:52 PM
I guess it depends a little on how you get the new price list and how many items usually change each time the list updates. If it's only a few that change I might just enter them manually, but based on your sleepless night, I'm going to assume a lot changes.

The details will depend on the format of the price list from the manufacturer, but as a basic process I would grab the data from the manufacturer, look to convert it into a format for the osCommerce tables and set something up automatically.

Look in the osCommerce db for the table with the prices. My guess is it's one table with a product id and a price and there would be another table with product id and product name. Hopefully that's all the tables you need, but it depends on the info that needs updating.

To update the tables you just need some sql to update the right ids with the right prices. The problem is the ids in osCommerce likely won't match the ids from the manufacturer. What I think it will all come down to is taking the info from the manufacturer (hopefully you can get it in XML or some other format that's easy to grab the data) and writing sql statements to update osCommerce.

billbenson
09-30-2008, 10:33 AM
The price list comes in hard copy and excel. They don't match. Costs are not included in any oscommerce tables. The descriptions in the price list are cryptic with bad acronyms. I don't want to write over existing descriptions that I have changed over time. My solution was to write one master table that has all the info and programs to allow me to update existing items in the various tables ie update price where part#=xyz. I also have a program that lists out the new parts and info to insert into Easy Populate which allows you to add new products in bulk to the standard oscommerce tables.

vangogh
09-30-2008, 06:17 PM
I was afraid that's how the info would come. Sounds like you worked out a decent solution to updating the tables at least.

orion_joel
10-02-2008, 12:56 AM
This is primarily the reason why i have never gone ahead with a full online store for my business, while i could absolutely do many sales through it if i put in the time and effort to set it up i think that it would be an absolute nightmare to keep up to date.

Most of the suppliers i have (i use 3 to 4 regularly, about another 6 for specific products) If i went with every product from all of them, i would have 100,000+ products with probably a 50% overlap, and none use even a similar product code method, one uses numbers the other uses a letter number combination. Even just one product category i tried to do it for, has about 4000 products and leaves so much guess work when i was trying to work out how to set it up for easy updating.

billbenson
10-02-2008, 01:10 PM
Its also an example of the opportunities out there. While some manufacturers are internet savvy, its rare. I did a site for a product a while back. The only documentation the manufacturer would provide was a 100 page pdf price list. I spent 2 weeks putting that pdf price list in an excel sheet and then on a shopping cart. Our competition just put the pdf file on their web sites. I did some good business on that site, then they cut their margins.

The point being, if you can get it into a spreadsheet, you can upload it to an ecommerce site pretty easily. Your competition might be lazy and there lies the opportunity. Depending on the product, it could be a clerical project you could farm out. In my case its a technical product that would be pretty hard to outsource.

BillR
10-02-2008, 02:27 PM
This sounds like an excellent business opportunity for you Bill.

Contact the manufacturer. Explain their price list is hard to use for current customers and could drive away their re-sellers. Then offer - for a low fee - to construct a "good" format to deliver the data in for their customers.

Don't charge less than $125/hour. I do this crap all the time on high end customer service systems. I charge between $140 and $200 per hour.

billbenson
10-02-2008, 03:45 PM
Interesting angle, Bill. The problem is for my main product line, they won't even do it for their internal people. Think about this. They have probably 35k different products including all the nuts and bolts. Probably 20k products that should realistically be in the price list. Kinda stupid not to put all products in the price list though. They only have images for about 30% of the products.

By doing that, it forces them to have twice the customer support personnel that they need.

Selling the value of an up to date website as your main catalog to replace the above is not easy. Company executives usually don't get it. They think a web site should cost $500 and marketing the site never enters their minds. I run into this frequently even in hi tech in silicon valley. If they can't get it....

I think there is more money in marketing products via the web than teaching others how to do it and charging them $125 per hour to do that.

Eventually that will change, but somebody correct me if I am wrong, I don't believe web marketing is really part of the curriculum for an MBA today?

vangogh
10-02-2008, 03:47 PM
Even better would be to make the data available in several formats. .xls, .csv, SML, SQL, etc.

Once you have it one of the above it becomes much easier to transfer it to the other formats and you could still display things in a PDF for anyone who still wants that.

BillR
10-03-2008, 01:49 PM
Even better would be to make the data available in several formats. .xls, .csv, SML, SQL, etc.

Once you have it one of the above it becomes much easier to transfer it to the other formats and you could still display things in a PDF for anyone who still wants that.

Or just build a web services gateway that can be consumed :)

billbenson
10-03-2008, 10:31 PM
Don't follow you bill. Can you give some more detial as to what you mean?

Bill to Bill :)

orion_joel
10-04-2008, 12:13 AM
One of my suppliers i have found does offer a downloadable csv file. The only problem is that they offer a simple version for free, which i do not think includes any graphic association information and only a short description, as they also offer a product image file which has all the filename's matched to the product code.

However they also offer a paid for version (something like $200 per month) which include's the only extra's a long description (which is almost essential for about 5-10% of their products) and graphic link information for all their product's. For the unfortunate part i do not find that the cost of this access is actually matching to the value. I really do not think that it would be worth that much to me.

Additionally another one of my suppliers offer's a pre-created skinable online store which includes a majority (maybe 85%) of their products. The few times that i have looked at one's that others have paid for they often do not include some of the newest products, even a week after they have been included in the actual wholesaler's own online ordering system. The price of this system i beleive is about $7,000 plus an ongoing monthly fee.

I think that a good number of manufactures are using this price lists and related thing's as a very good revenue stream. While there wuld be i admit a good investment to initially connect it in with their systems and set it up, ongoing once setup their maintenance time should be quite minimal.

billbenson
10-04-2008, 12:56 PM
That's interesting. Something else I've seen happen a couple of times is manufacturers adding a cart to their own site and firing distributors and internal sales people that manage distribution networks. I think you will see more of that as manufacturers become more internet marketing savvy.

orion_joel
10-04-2008, 08:04 PM
That is interesting, many of the mainstream computer manufactures did the same thing, and continue to have it there, i guess mostly to compete directly with companies like Dell. To some extent some for a time even pulled out of offering a partner program to the reseller network. However almost all have while keeping their own online presence, realized the value of the reseller network and have put renewed efforts into it.

At the same time everyone of the distributors that i deal with often, has their own online shopping cart, however it is restricted to distributors only. Really their is nothing to stop them opening it up to everyone, except for the impact it would have on the market, and while they would gain probably thousands of new customers, they are not the type of customer they want. For a distributor to open to the public in an online method like this, it may help them cut back on internal sales staff and such. But it creates a new problem, generally retail customers are a lot more demanding and at least in Australia there are different laws dictating how a sale on a business to business basis differs from business to retail. So for the most part in Australia i think they are happy to deal with the resellers and let the resellers deal with the public, makes their life a lot easier.

billbenson
10-04-2008, 10:16 PM
Early in my career HP was a very popular company for engineering graduates to work for. Probably still are. Graduates were willing to work for thousands less to work for HP.

I went to work for a small company manufacturing telecom switching equipment. At the time there were 7 regional telcos in the US which was our market. So, we had 7 prospect customers.

We hired a marketing manager from HP. He was a MBA trained to do things the hp way. There was one big industry trade show each year. We always had a booth. We didn't get any real prospects at the trade show, but we had to be there. When you only have 7 customers, you don't need to do a lot of prospecting at trade shows. Only the executives who you already knew went to the show anyway. Mostly wining and dining was the reason to be there.

So what the guy hired from HP did is set up 3 tiers of people in the booth and got a large booth. The first tier were the pre pre qualifiers. Most wore dresses. They steered people to the prequalifiers who in turn sent prospects to private booths maned by sales people. The HP guy was either fired or left within a year or so. He didn't get us any new prospects.

Point being, while some companies may be able to bypass distributors and sell direct via the web, I think it will be a long time until executives in companies know how to use it effectively. And that's ignoring the fact that web marketing is in its infancy and is very fluid. Also distributors frequently provide a VAR service which a large company like HP or Dell just can't do. Look whats happened to Dells support rating in recent years using off shore tech support. Now they are selling directly through WalMart.

Its something the internet marketers need to be aware of when putting time and resources into marketing products. It's really nothing new though. A small distributor was given a product line 20 years ago, put a bunch of resources into selling the product line only to have the manufacturer yank the product from them. And in most cases, its difficult for you to know the internal politics of a company. Or if they just hired a MBA from HP to manage marketing and sales for baby toys?

orion_joel
10-05-2008, 12:16 AM
Bill, while i cannot say i have heard of it specifically what you mention i would almost expect happens very very often.

It probably is the downfall of many small growing companies hiring someone that is capable of what they need, but to short sighted or blinded by a previous corporate career that leaves them with blinkers on to what is really needed. Probably not because they are not capable of what they need to do, but more so because they have been preconditioned for X number years in the corporate environments like HP to deal with customers in one way only.

Again i actually worked for one company that had a distribution deal pulled from them with no explanation and nothing more then a faxed letterhead with just a couple of sentences. From the time of that letter the company in question would not answer questions deal with support request or anything from the company i was working for. It happened again with another supplier as well, only they were a little more friendly on the break, however in both cases the company i worked for had been putting a lot of time and effort into distributing the products and was really growing the business for both but had the plug pulled. Why they never could get those reasons from either company.

Relating it all back to the actual topic of this thread. The reliance on companies that will make these sort of short sighted decisions, for something like a price list that you can adapt to use in an ecommerce environment can be bad news. Depending on the method they provide it you could spend a couple of weeks working it into your website, only to have them make a change or no longer offer it, you could effectively waste a lot of time, and in some cases money to only have to rework it over and over again at the whim of a corporate giant who does not have to answer to anyone on it.

NewEstore
10-22-2008, 02:16 AM
oscommerce is good. I will try. I am a small business owner. I received an email to advice me to set up a free Ecommerce shopping cart in fastcommerce.com (http://system.fastcommerce.com/). I want to set up 5 different online stores at least to promote my small apparel store. How is fastcommerce free ecommerce cart? or any other easy ecommerce system recommendations?

vangogh
10-22-2008, 03:05 AM
I've never used it, but I just took a quick look. They have a free option, but it seems a little limited for a full ecommerce store. If you don't plan on selling many products or having too much traffic the free stuff could be a good deal.

Otherwise you're likely going to want to grow at which point you'll have to pay at least $30/month and maybe $100/month depending on your needs. I have a hunch everything you'll get at those prices could easily be gotten for less money and with greater flexibility and control of the site for you. There are a variety of open source ecommerce solutions that will cost you nothing. Your expense will be a domain and hosting.

Hosting an application myself would be my preference, but from the little I've seen fastcommerce looks like it could be good. Hard to know with certainty since I've never tried it, but you could always try the free version as a test to see if you like the system. The worst case is you may spend time you could have spent elsewhere.

billbenson
10-22-2008, 01:07 PM
If your thinking of oscommerce, I would recommend zen cart instead. They are very similar, but oscommerce is a fairly old table based program. It's better to start with a newer css based design. I have several oscommerse sites.I'm converting them to zen as I have time.

orion_joel
10-22-2008, 08:22 PM
While i have worked on an setup stores using zencart as well as some that use cubecart, the one i prefered was i think called x-cart which was a commercial one coming in at about $200 per license i think. I can only think that it was the layout and how things worked that i preferred, however for the price it is a decent investment, considering there will probably be some customization and design required to get it working for you how you want it to work. Which for the most part will probably also cost some money.