Automating Price List Data Processing: A Game Changer for Amazon Sellers

One area where eCommerce businesses often get bogged down is in the manual processing of price sheets from suppliers, which can take forever and lead to mistakes. 

Imagine the hassle of manually managing a massive supplier price list, sorting through endless spreadsheets, and entering data by hand. Tragic, right? And if an error slips through, it can lead to a whole new set of headaches.

This was precisely the challenge faced by one of our clients, who approached us to develop an automated solution to streamline their price list management process. 

It’s a relief that we’re lucky to live in a time where most data processing can be automated.

It’s because there’s now a simpler solution in hand. It’s called “robotic process automation” and it’s clearly something every eCommerce company should give serious thought to if they plan to scale with greater efficiency.

Client’s Pain Points in Data Entry and Processing

Our client is a well-known eCommerce wholesaler in the United States who operates an Amazon store offering a wide range of industry products. They source these high-quality products from suppliers and then rebrand them under their own label, thereby offering a unique selection to their customers.

However, as their business grew, so did the complexity of managing their inventory and pricing. This led them to explore automated data processing solutions to streamline the extraction and management of price sheets.

They faced significant challenges with the manual extraction and processing of price sheets from their suppliers – a task that was both time-consuming and prone to errors. This inefficiency began to impact their ability to maintain accurate pricing and manage their Amazon store effectively. 

To address these issues, they approached us with the goal of automating this process.

To really get what this case study is about and fully grasp its insights and impacts, let’s start by understanding price sheets.

A price sheet is a document provided by suppliers that lists the products they offer along with their selling prices. 

The goal of the price sheet is to give the client all the necessary information to:

• Decide on Purchases: Determine which products to buy from the supplier.
• Set Selling Prices: Establish the right selling prices for products to ensure competitiveness and profitability.
• Evaluate Profitability: Calculate potential profit margins and returns on investment for each product.

In a nutshell, the price sheet is a tool that helps the client make informed purchasing and pricing decisions to maximize profits. 

The client works with multiple vendors who supply them with product pricing information. These vendors are essentially different sources of product prices, and each provides pricing data for their own products.

Before the start of this project, the client used to manually process these sheets, inputting the data into SmartScout to check and compare prices, which is a time drain.

Automating this process makes it faster, more accurate, and more scalable.

The Initial Manual Process

1. Receiving Price Sheets: The client was manually obtaining price sheets (PDF/Excel) from their suppliers, which included brand names, ASIN numbers, UPC numbers, selling prices, MSP, etc.

2. Brand Search: The client would navigate to the “Brands” section in SmartScout, search for the supplier, and search their product list. Various filters would be applied manually to narrow down the search, like setting the product’s rank to under 250k and keeping the Amazon In Stock Rate at a maximum of 30%.

 3. Exporting and Uploading: Once the client found the products they’re interested in, they would export the data and manually upload it to a UPC scanner.

 4. Extracting Product Details: Using the UPC scanner, the client would pull up detailed information about each product from the list of ASINs provided by SmartScout.

5. Checking Each Detail: The client would then click on each detail to view a product page on Amazon, manually extracting information from these pages.

6. Creating the Output Sheet: After gathering all the necessary details, the client would enter the information into a sheet. This sheet would be used to list the products on Amazon Seller Central.

This was the overall concept the client was working with, using manual efforts and labor.  The entire process of updating a single price sheet could take one to two days, which was far from ideal in the eCommerce landscape where quick updates can make a significant difference.

When the client reached out to us, we aimed to create a streamlined flow to handle these tasks automatically, providing the desired results in less time.

Automating the process of handling price sheets helps achieve the following:

• Save Time: Reduce the time spent manually entering and verifying data.
• Increase Accuracy: Minimize errors in data entry and calculations.
• Improve Decision-Making: Provide timely and accurate data for better business decisions.

Bitcot’s Automated Data Processing Solution

Our first step was to thoroughly analyze the client’s requirements and understand each part of the manual processes that were in place. 

To address the client’s challenges, we developed an automated workflow using Microsoft Power Automate’s RPA capability.

To automate the client’s entire process, we designed a sophisticated workflow that integrates seamlessly with Amazon’s systems and the client’s existing tools. The solution automates every step, from receiving price sheets from suppliers to updating the data on Amazon. 

Here’s what the data automation process looks like:

Automated Data Collection Process

We automated the process of receiving price sheets from suppliers. This data is then extracted and prepared for further processing.

The automated flow starts when the client selects the file from the vendor and clicks the run button on Power Automate. 

Automated Data Extraction 

Once the flow is initiated, it automatically extracts details from the file and pulls necessary data from Amazon (in the standard format given by the client). A new spreadsheet is created to organize data specific to the client.

The data extraction involves both reading from the price sheets and retrieving data from Amazon.

SmartScout Integration

The automated workflow enters the extracted data into SmartScout, eliminating the need for manual data entry. This includes:

• Opening SmartScout and navigating to the Brands section.
• Searching for brand names.
• Applying predefined filters: Rank (<250k) and Amazon In Stock Rate (Max 30%).
• Running the UPC scanner and entering the seller’s name to pull data.
• Sorting data by ranking from low to high.

Data Mapping

The automated system selects products and fills in the required details from the price sheet on the Amazon detail page (into the plugins AZInsight and Keepa). Key data points extracted include:

• 30 Day Ranks
• Supplier cost
• 30 Day Buy cost
• ROI
• Profit Margin
• Estimated Sales Per Month (RevSeller)
• Close Seller

Additionally, data from the last 90 and 180 days is also included to provide a comprehensive view.

Final Data Extraction

All the data from the Amazon detail page and the price sheet are now extracted and mapped.

The automated flow checks if more products are available, and if so, continues the extraction process. If not, it generates the final output sheet.

We use the plugins AZInsight and Keepa to extract details from Amazon.

AzInsight

Once the buy box cost is entered, for example, 130.77, and the selling cost is set to 10, the powerful algorithms process this data to calculate the profits and margins.

The system automatically computes the profit margin based on the given costs and assesses how the ROI is impacted. This allows the client to gain detailed insights into financial performance and make data-driven decisions.

It also handles multiple variables like the sales estimate, average cost, and hierarchical structure of products, where a main product (e.g., a shirt) serves as the parent ASIN and different sizes or colors are categorized as child ASINs. 

The tool empowers the client further by analyzing historical data, BSR (Best Seller Rank), price drops, current market conditions, and different fulfillment options (FBM and FBA). This level of insight allows sellers to optimize their pricing strategies and enhance their competitive edge.

Keepa

Relevant data is extracted from the ‘Data’ tab, specifically from the ‘Product Details’ sub-tab.

Keepa combines various metrics and data points to give a detailed analysis of the product’s market performance.

On the left, it contains foundational product details, including the title, category, product codes (ASIN, UPC), and specific seller information like the Buy Box seller and the lowest FBM seller. It also includes information on the last price change and when the product was first listed.

The right side of the image is rich with analytical data, showcasing the product’s sales rank both current and historical, average price points over various timeframes, and the number of reviews. 

The tool tracks the product’s buy box statistics, including who is holding the buy box and the stock levels. Additionally, it highlights pricing trends, showing how the product’s price has varied over time, and provides insights into the product’s performance across different sellers and fulfillment methods.

Output Sheet Generation

Once the data has been mapped and extracted, the system consolidates it into a final output sheet. This sheet includes product mappings, ASINs, current rankings, supplier costs, selling prices on Amazon, Buy Box costs, ROI, profit margins, monthly sales projections, etc.

The sheets also indicate whether the product is sold by Amazon, another vendor, or the brand itself. 

Final Review and Updates

The generated output sheets are emailed to the client for review. Following this, the data could be automatically updated on the Amazon platform.

How We Solved Key Challenges in Automatic Data Processing

Data Consistency

To keep the client’s data consistent, we standardized the data extraction process based on the client’s specific requirements. By establishing clear guidelines and formats, we were able to minimize discrepancies and maintain uniformity across all datasets. 

This way, we not only enhanced the reliability of our data but also streamlined the extraction process, making it more efficient and error-free.

Filtering and Sorting Efficiency

Managing large datasets can be tricky, especially when it comes to filtering and sorting data accurately. To tackle this, we applied specific filters tailored to the client’s needs. This approach enabled us to handle extensive data volumes efficiently, ensuring that only the most relevant and accurate information was processed. 

By optimizing our filtering and sorting techniques, we significantly improved the speed and precision of our data processing system, ultimately delivering high-quality results to our client.

Communication and Reporting

Clear communication is key to any successful project. We ensured this by maintaining strong client relationships and ensuring project success. To achieve this, we implemented a robust communication strategy that included generating comprehensive output sheets and sending timely email reports to the client. 

These detailed reports kept our client in the loop, providing them with a clear understanding of the project’s progress. Our straightforward communication approach helped build trust and made sure everyone was on the same page.

By addressing these challenges with innovative solutions and a client-centric approach, we have successfully delivered consistent, accurate, and efficient pricing automation services.

RPA: The Engine That Drove Our Project Forward

Automation is a transformative asset for this project, revolutionizing the way data is processed and managed. By automating key stages of the workflow, we’ve significantly boosted efficiency across the board. 

Previously, manually applying filters to each product was time-consuming, taking one or two days per sheet. Within these two days, the data would be outdated because the rank changes every second in Amazon. With automation, this task now takes only one hour, or even less, depending on the number of items in the sheet. 

Accuracy is another big win. Manual data handling often leads to errors, whether through misinterpretation or typos. Leveraging tools that automate pricing into your price sheet data processing can streamline updates and adjustments, ensuring that pricing remains accurate and consistent across all channels.

Automation ensures that data is extracted, processed, and mapped with precision. This reliability reduces the risk of costly mistakes and ensures that the information used for decision-making is both accurate and up-to-date.

Moreover, automation streamlines processes by seamlessly integrating various systems and tools. 

Our client no longer has to manually coordinate between different platforms like SmartScout and Amazon. Instead, the automated system ensures that data flows smoothly from one stage to the next, enhancing overall productivity and reducing the likelihood of disruptions.

Automated workflows also follow set rules and procedures, ensuring that every piece of data is handled in the same way each time. This uniformity leads to predictable results and reliable data.

From a cost perspective, automation has led to significant savings. By cutting down on manual labor, our client has minimized operational costs associated with data entry and management. This not only lowers expenses but also frees up resources that can be better utilized elsewhere in the organization.

How Our Client Has Thrived and Grown Since We Began Our Collaboration

With the new automated system:

• The client selects a vendor file, and the automation takes over.
• All relevant details are extracted and compiled into a ready-to-use output sheet.
• The client receives the same comprehensive data much faster.

The implementation of the RPA solution truly transformed the client’s business. Here’s how it made a difference:

Time Efficiency

• Reduction in Manual Effort: The client drastically reduces the time spent on extracting, processing, and updating price sheets. What used to take one to two days per price sheet can now be done in a fraction of the time.
• Faster Updates: The system enables quicker updates to product listings on Amazon, ensuring the client can respond rapidly to market changes like competitor pricing adjustments or supplier updates.
• Parallel Processing: The client can handle multiple price sheets simultaneously, further speeding up the process compared to the manual, sequential approach.

Increased Accuracy

• Minimized Human Error: The system reduces the risk of manual data entry errors, ensuring more accurate pricing and product information on Amazon.
• Consistency: Standardized data extraction processes ensure uniformity across all datasets, maintaining reliable and consistent information.

Enhanced Data Management Process

• Improved Data Management Process: Streamline every step from collecting price sheets to updating information on Amazon.
• Handle More Data: Efficiently manage larger volumes of price sheets by using tools that automate data processing, allowing for better scalability.

Scalability

• Handling Larger Data Volumes: The automated process can efficiently manage larger volumes of price sheets and product data, allowing the client to scale operations without increasing manual workload.
• Adaptability: As the client’s business grows and they add more suppliers, products, or data points, the automated system can be easily adjusted or expanded.

Improved Decision-Making

• Timely Data: The client provides timely and accurate data, enabling the client to make better-informed decisions regarding purchasing, pricing, and inventory management.
• Data Integration: The system integrates data from multiple sources (like SmartScout and Amazon) and presents it in a consolidated format, giving the client a comprehensive view of their business metrics (e.g., profit margins, sales projections) to inform better decisions.
• Profit Optimization: By having accurate and up-to-date information, the client can better evaluate profitability, set competitive prices, and maximize returns on investment.

Operational Efficiency

• Streamlined Workflow: By automating the entire workflow, from receiving price sheets to updating Amazon listings, the process becomes more efficient. This frees up the client’s staff to focus on higher-value tasks rather than repetitive manual data entry.
• Enhanced Reporting: Automated generation of output sheets and communication reports ensures the client has a clear overview of the data and the updates made, contributing to better operational oversight.

Competitive Advantage

• Faster Market Response: With automation, the client can update their product listings on Amazon more quickly than competitors who may still rely on manual processes. This speed can be crucial in maintaining competitive pricing and availability.
• Optimized Listings: Consistently accurate and up-to-date product listings can lead to better visibility, higher sales rankings, and ultimately increased sales.

Final Thoughts

Since we began our partnership, the client’s operational improvements have been nothing short of remarkable. 

What once took days of manual effort is now completed swiftly and accurately. This means our client can receive updated price sheets, process data, and make informed decisions much faster than before.

Our targeted strategies and advanced data process automation solutions have led to significant positive shifts in outcomes. Our client has experienced smoother operations, enhanced efficiencies, and measurable gains in performance. 

This automation project has ultimately provided our client with improved profitability and a stronger competitive position in the market.

If you’re facing similar challenges in your eCommerce operations or want to explore automation solutions, shoot us a message to learn how we can help transform your processes and drive efficiency.