How Amazon sellers can efficiently perform data cleaning and analysis: a detailed guide

In the operation work on the Amazon platform, data cleaning and analysis are crucial links. This article will combine specific operation steps to introduce in detail how Amazon sellers can effectively clean and analyze the collected data.

Data cleaning steps

First, after the third-party collector completes the crawling, we need to download the obtained form and extract the required key information from it. The final required data includes three columns: link parent ASIN, link shelf time, and major category rankings. In order to accurately extract the rankings of large categories, you can use the column sorting function of Excel to process the data. The specific operations are as follows:

1. Replace the ranked major category with #, such as “in Clothing, Shoes & Jewelry”.
2. Select the data in Excel, click the “Column” button, select the “Delimiter” radio button, and then click the Next button.
3. Set the separator symbol in the “Text Column Wizard – Step 2 of 3” dialog box, check the “Other” checkbox, and enter #. At this time, you can see that the ranking data has been separated separately. .
4. Click the “Next” button and complete the column splitting, then delete the data in other columns.

It is worth noting that for those data that still exist in the “Sports & Outdoors” category, you can consider deleting these links or analyzing them in a new table. In addition, in order to better manage data, you can create a new table (such as an Excel table named “A Keyword Selection”) and record the crawl date, ASIN, shelf time and other information in it.

When faced with large amounts of data, you can use the VLOOKUP function to improve efficiency. The specific steps are as follows:

1. Enter =VLOOKUP( in the cell corresponding to the date, Excel will prompt 4 parameters.
2. The first parameter is the data to be quoted, which can be the corresponding ASIN.
3. The second parameter is the search range. Open the captured data and select the ASIN and ranking columns.
4. The third parameter is the serial number of the search data, and the ranking here is the second column of the selected area.
5. The fourth parameter selects the matching type, enter FALSE or 0 to achieve precise search.

Data analysis skills

After completing the data cleaning, the next step is how to effectively analyze the data. First, use the COUNTIF function to count the number of times a product appears on the keyword search page in the past 14 days, and then exclude links that appear 4 times or less. Doing this can help us avoid misjudgments in later analysis due to retaining too much data, and it can also retain more potential new products.

Next, based on operational experience, if the ranking of the major clothing category is within 100,000, the link can ensure that orders are issued every day; if the product’s major category ranking is already within 5,000, the daily sales will be about 100 or more, which is very likely Hard to beat the competition. You can use the MIN function to analyze the best link rankings over the past 14 days and filter numerically to retain links ranking between 5,000 and 100,000.

In addition, even if the life cycle of this category is on the rise, the rankings of each product will still fluctuate up and down. Use the MIN function to calculate the lowest ranking value in the first cycle minus the lowest ranking value in the second cycle to understand how many positions the link ranking has increased. This method helps discover products with large ranking fluctuations, which may be potential hits.

Finally, considering the impact of the life cycle, many old links will rise again in ranking when the next sales season comes. You can sort the listing time in descending order to find links that have been listed recently. Through the above four steps, about 100 potential links can be screened out from thousands of links.

In summary, through effective data cleaning and analysis, Amazon sellers can better understand and predict market trends and provide strong support for product development.