After the third-party collector completes the crawling, download the original data to get the table. The data finally needed are the three columns of link parent ASIN, link listing time, and major category ranking. At this time, you need to extract the major category ranking in field 6, that is, the content after #, which can be completed using Excel’s split function.
First, replace the major category after the ranking with #. Here is the clothing category, that is, “in Clothing, Shoes & Jewelry”.
Secondly, select the data in Excel, click the “Split into columns” button, select the “Delimiter” radio button, and click the Next button.
Set the separator in the “Text to Columns Wizard – Step 2, Total 3 Steps” dialog box, check the “Other” checkbox, and enter #. You can see that in the data preview, the ranking data has been separated separately. Click the “Next” button to complete the split, and delete the data in other columns.
At this time, there are still a few data in the “Sports & Outdoors” outdoor category that have not been separated. To ensure the consistency of data comparison, you can delete such links or analyze them in a new table. Create a new table (corresponding to the Excel table named “A Keyword Selection”, please download and view according to learning needs), the first three columns respectively record the crawling date, ASIN, and shelf time, and the first row is the link ranking of the crawling date. Fill in the corresponding data captured every day into the table, and you can sort out the ranking change trends of most products under the keyword search of “tunic tops folwomen”.
Since the amount of data can reach tens of thousands, ordinary manual search will be very cumbersome, so you can use the vlookup function to match the data captured every day to the table. The specific operation is as follows: First, enter “=vlookup(” in the cell corresponding to the date, and Excel will prompt 4 parameters.
The first parameter is the data to be referenced. You can directly select the corresponding ASIN here, or enter “B2” and enter “,”. Note that this is an English comma, not a comma for the Chinese input method.
The second parameter is the search range. Open the captured data, select the ASIN and ranking columns, and the function will match according to the ASIN column when searching.
The third parameter is the sequence number of the searched data. Note that the number of columns here is not the default number of columns in Bxcel, but the column number of the search range. The ranking here is the second column of the selected area, so enter “2”, and the ranking column data can be matched when searching.
The fourth parameter is to select the matching type. Because you need to find the ranking accurately, enter “FALSE” or “0”. Enter “TURE” or “1” for fuzzy search. Finally, complete the right bracket “)” to get the formula “=VLOOKUP(B2,partially captured data!E:F,2,0)”. Fill in other cells downward to get a table.
1 Link rankings fluctuate periodically, with peaks on Wednesdays and Thursdays and lows on weekends, which is consistent with operational experience.
2 Even links with relatively high rankings may not appear in keyword search rankings every day.
Taking into account the periodic changes in links and the delay in data capture, we can take a 7-day cycle and take data from 2 cycles for comparative analysis. Later operators can also select multiple cycles for comprehensive comparison.