These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Sunday, December 11, 2011 Price / Attribute Harvester

Executive Summary

For my final project, I decided to develop an attribute & price harvester for a company called Oxspring Paul. Oxspring Paul is a product distribution company that specializes in e-commerce and media based sales channels. They primarily deal in watches. As part of their business, they have to manually collect attribute & pricing data on new watch products that they start to sell in order to create product profiles. In addition, they perform regular pricing checks on items currently in their inventory to ensure optimal marketplace competitiveness. Currently, to collect these two data types, an employee will manually look up each item on to harvest the available attribute data and also establish a marketplace pricing benchmark on initial product build. To maintain accurate pricing data, it is required that employees cycle through the entire existing inventory and retrieves pricing data on a regular basis. Collecting attribute & pricing data represents a very large amount of employee labor hours. With nearly 2000 new unique products being processed per year and over 4000 current SKUs that must be price checked regularly, it is estimated that new attribute & price data harvesting requires about two full-time employees. In addition, the pricing portion is just not being done as well as it should be done. Ideally, a price check would be performed on every item, every day. Oxspring Paul would be lucky if pricing data was updated on every item once a month.

In developing the attribute /price harvester, I had the following goals:

1. I wanted the harvester to be capable of operating on just one input, the Amazon unique identification number or ASIN. I chose this identifier because Oxspring Paul can easily get a list of the product ASINs of every item they sell on Amazon. In further developments, I would like to build out a version that can find an ASIN based off just the manufacturers model number.

2. Collect the following pricing attributes: List Price, Price, Buy Box Price, Buy Box Shipping, Buy Box Total Price, New Market Competitors, New Market Price, Amazon Sales Rank, Rank Category and Selling Status. In addition, I wanted it to collect the following product attributes: Brand Name, Model Number, Part Number, Model Year, Item Shape, Display Type, Clasp, Metal Stamp, Case Material, Case Diameter, Case Thickness, Band Material, Band Length, Band Width, Band Color, Dial Color, Bezel Material, Bezel Function, Calendar, Special Features, Movement, Water Resistant Depth and Dial Window Material Type.

3. I wanted the harvester to be able to diagnose what type of page (status) it was receiving from Amazon and process it accordingly. Amazon products pages are formatted in five possible statuses: In Stock, Available from These Sellers, Currently Unavailable, Only Available at These External Websites and Oops. The formatting of the page is designated based on the current selling status of the product or in the case of the Oops format, the lack of a product with the ASIN used.

4. Lastly, I wanted the program to save the sheet of data as its own .csv file in a folder (Data) based on the current location of the workbook. This .csv file would later be automatically processed by a SQL based system that would import and analyze the data.

No comments:

Post a Comment

Blog Archive