I am looking for a developer with experience with API’s, data management and excel/google sheets and formulas to help me with several different issues that my business is experiencing.

My business is a 3rd party marketplace seller focusing on selling on Amazon.com. Our business model involves acquiring price lists from distributors and reviewing them for profitability to resell on Amazon. These price lists are compiled in different formats based on the distributors, but often include the following information/data:

• Product name/description (Often written out in short hand)
• Product UPC (This information is not in the same format depending on the list or the vendor. Some UPC’s are missing a check digit, some do not have the correct number of 0’s in the front of the UPC, some have dashes in it, etc.) (Side note: all of this data can be formulated by
• Suppliers SKU/part number
• Number of items in a case pack
• Price per unit
• Price per case
• Product category

Once we receive these price lists; the business workflow for purchasing continues as follows:

The price list is edited (by myself) to reflect the following changes:

• The product UPC is edited to include the check digit and remove and dashes. It is also changed to make sure that the number of 0’s in front of the UPC is correct.
• The pricing is often changed as well. This is because most of the distributors in questions have a percentage markup based on the category of the item. The category is sometimes supplied, and sometimes we need to make assumptions based on the category. This markup ranges from between 6 to 20%. The mark up is then added into the original unit/case cost so we can see what our actually invoiced/landed cost is going to be. Sometimes we have to assume/figure it out on our own. The mark up categories are commonly grocery, healthy and beauty, general merchandise, etc.
• Once the price list is adjusted and finalized, the pricelist is then uploaded to a UPC Scraper (The UPC Scraper we use is called Scan Unlimited but there are many different ones available) that scrapes Amazon data to find the corresponding products and then runs calculations based on Amazon’s two fees for selling FBA (Fulfillment by Amazon). These fees are an Amazon referral fee and an Amazon fulfillment fee (pick and pack fee) the schedule for these fees is made available via a table on Amazon’s website (based on size and weight of the item and the category the item is listed in) and available via Amazon’s own API.
• The UPC scraper then analyzes the price lists for profitability by comparing the cost of the item with the current selling price on Amazon minus their fees.
• The data pulled from this UPC scraper is then filtered to then meet our buying criteria, which is based on the net profit after fees and profit margin. We also filter the products in this list based on the total number of estimated sales per month. The total number of estimated sales per month is a calculation based on the historical Best Sellers Rank (BSR) of the product and the category the product is listed in. This BSR data is available in the Amazon API, and the estimated sales per month is already made available and calculated via countless other apps and Google Chrome extensions.
• Once we set the filters for our buying criteria the products on that list are then analyzed one by one to review other metrics and data that may or may not be made available via either the Amazon API, or other 3rd party API’s (most notably Keepa). The other data that is review includes but is not limited to:
• Amazon consistently holding the buy box of the item (80% or more of the time in the last 30 days
• The brand is selling on the listing themselves.
• There being consistently only one buy box holder in the last 30 days (which means the brand more than likely has an exclusive agreement with that 3rd party seller)
• Another reason the data from the UPC Scraper is then reviewed manually after being filtered is because the UPC Scraper often misses key points due to the listing itself not being set up cleanly or correctly on Amazon’s platform. Most notably: if the item is a multi-pack (I.E: if the listing is actually for 2 units of an item instead of 1, the math for profitability will not be correct.) Unfortunately, since Amazon was not strict and had no real rules or regulations for creating listings on their platform for years, this is a common occurrence.
• When profitable products are found, they are then transcribed to a google sheet that becomes the basis for our Purchase Order. This google sheets includes all the relevant information needed to review the item including but not limited to:
• The date the item was reviewed
• The sell price at the time the item was reviewed
• Our cost per item.
• The total number of estimated sales per month
• How much inventory we would need to purchase for 30 days worth of inventory. (This number is the total number of estimated sales per month on that particular Amazon listing, divided by one plus the total number of other FBA sellers that are within 3% of the lowest FBA price. This is because we can assume that we will be an additional FBA seller at the lowest FBA price and that the buy box will rotate evenly between all FBA sellers within 3% of the lowest FBA offer price.
• Product UPC
• Supplier SKU/Part Number
• Total number of units per case
• How many total cases we want to order (most of our vendors only accept Purchase Orders in number of cases, not number of units)
• Total net profit
• Profit margin

Once all the information in the UPC scrape is reviewed, the price file is then reviewed manually by a virtual assistant/data entry specialist based in the Philippines. These Filipino VA’s are also the ones responsible for uploading the price lists into Scan Unlimited and reviewing the items.

A manual review is conducted because, as mentioned earlier, the data points made available on Amazon listings are often inaccurate and a simple search to cross-reference UPC’s on the price lists versus UPC’s on Amazon will miss a majority of the profitable products that are actually available. The UPC scrape also misses almost all of the multi packs and bundles that are available and a lof of the times those end up being our most profitable products. This manual review consists of 2 stages.

Stage 1: This is what we refer to as the competitor analysis. Here is an example of a sellers catalog: https://www.amazon.com/s?i=merchant-items&me=A3IQGL03Z0MNP5

• A random selection of larger Amazon sellers who sell in the same niche as us is reviewed and their Amazon catalog is searched by the brands that are available in the price list.
• Once a product is found on that competitive seller’s Amazon catalog, we then check to see if the same item is available in the vendors price list.
• The items are reviewed for profitability by inputting the cost from the vendor into a Google Chrome extension that shows us what the net proceeds on that particular listing are using the Amazon fees mentioned earlier.
• If an item is found to be profitable and meets all of our profit and competition criteria, the information is then transcribed into the same Google Sheet mentioned earlier.
• After reviewing the catalogs of several (usually 2-3 based on time constraints) of competitive sellers, the data is then reviewed using the final method of product research.

Stage 2: Amazon catalog search

• The very same process of searching a competitor’s Amazon catalog is then performed again, however, instead of searching a competitive sellers catalog, we search the entire Amazon.com catalog by brand and/or product to find any other listings that might be available.
• Any additional products found that meet our same criteria are then added to the same Google Sheet.

Once all three methods of the product research are performed (all done exclusively by Virtual Assistants in the Phiippines), the google sheet is then double checked by a member of our in-house buying team. They double check the data to make sure that no errors were made and to ensure that:

• The buy box is actually competitive and shared between multiple sellers.
• The brand is not a seller on the listing.
• The item will still remain profitable at the lowest consistent FBA price in the last 90-180 days.
• The total number of estimated units to purchase is on par with the data.
• The profitability figures are correct.
• The cost of the item is correct.
• The product on the price list matches the product on the Amazon listing (this is usually done by entering the UPC of the product from the price list into www.UPCItemDB.com)

When the item is approved for purchase, the row in the google sheet is marked in green, when it is disapproved for purchase, it is marked in red and notes as to why it was passed on is input into a cell in that row. Once finalized, that data is then transcribed by another Virtual Assistant based in the Phiippines into our inventory management software to create a formal Purchase Order. That software is called SKU Vault. Some information requires adjustment and manipulation to be placed into SKU Vault, such as:

• Any duplicate products (meaning the same exact SKU purchased for multiple different Amazon listings) is combined because vendors do not like to accept a purchase order with the same product listed multiple times.
• If the product has never been purchased before, a listing in our inventory management system is created so we can track it.

Once manipulated correctly it is then sent to the vendor.

As you can see by the notes above, our work flow can often be very messy and is very manual. I think this process leaves a lot of room for automation and optimization and I would like to the following issues and pain points to be addressed via a custom API or other automations/formulas.

Price Lists

There is a lot of manual manipulation done to the price lists before they are able to be reviewed by the UPC Scraper and the Virtual Assistants. I would like an automated method to perform the following functions. This can be done either via a google sheets formula/script or uploading the raw data and being able to download the corrected price file.

• Cleanly fix any erroneous UPC’s (UPC’s with dashes in them, UPC’s missing the check digit, UPC’s with an incorrect number of 0’s in the beginning of them.)
• Add any price mark ups to items based on category wherever possible.
• Compile the price lists uniformly so that all the columns contain the same data.
• Input the case cost and or unit cost when one of them is supplied, but the other is not.

Price List Comparisons

One common issue we run into since a lot of the larger distributors we purchase from have overlapping product catalogs (meaning they carry the same exact products) is determining which one of these vendors has the best price available on that particular product. This will also help prevent duplicate products from different vendors for the same Amazon listing. This leaves us with more inventory than we’re comfortable having due to cash flow and price volatility concerns and will sometimes cause us to overpay for inventory that we could have purchased cheaper from another supplier. I think possibly once the price files are all uniform a simple excel VLookUp or some sort of FTP service where we can upload the pricing files will be able to tell us which vendors carry the same products, and which one is offering the best price at the moment. However, one thing to keep in mind is that we are only interested in purchasing from the cheapest distributor when the product meets our purchasing criteria on Amazon. Generally speaking, less than 5% of a vendor's entire catalog will meet this criteria. I am not sure what the most efficient and effective way to resolve this issue would be.

Reviewing products we already have inventory of

Another issue that we face as we grow and scale is that we often end up re-reviewing products that we already have inventory of that has either been purchased from the same or a different vendor. I think the only way to resolve this issue would be to have some sort of formula or API hookup in the google sheet that will automatically flag any item input (most likely by ASIN our our SKU) in our Google Sheet that we already have inventory of in our Amazon catalog. If the product already exists in our catalog AND WE HAVE INVENTORY, I would like that entire row to display a message, or be flagged, etc. to prevent purchasing duplicate/excess inventory.

Data entry/Manual work

Once a profitable product is found, there is a lot of manual work and data entry that happens to transcribe that product into the Google Sheet. This information is already available via the Amazon API and the price list and should be able to be easily automatically transcribed into the price list instead of manually entered or copied and pasted. The product/vendor data can maybe be pulled using VLookUp form the pricefile and the data from the Amazon listing can be pulled from either the Amazon SP-API, the Keepa API or possibly another 3rd party Amazon API.

If needed, I also have an entire video training series that I created to help train our virtual assistants to perform all of the tasks outlined above and would be more than happy to share them upon your request.

I am looking for a developer who is well versed in API pulls, specifically the Amazon SP-API, and working within the confines of Google Sheets using formulas, VLookUp, etc. Experience using a third party Amazon sellers API such as Keepa is a HUGE plus.

Job Types: Full-time, Contract

Pay: $40.00 - $70.00 per hour

Schedule:

• 8 hour shift

Work Location: Remote

Please Note :
apexdining.ca is the go-to platform for job seekers looking for the best job postings from around the web. With a focus on quality, the platform guarantees that all job postings are from reliable sources and are up-to-date. It also offers a variety of tools to help users find the perfect job for them, such as searching by location and filtering by industry. Furthermore, apexdining.ca provides helpful resources like resume tips and career advice to give job seekers an edge in their search. With its commitment to quality and user-friendliness, Site.com is the ideal place to find your next job.