Click the link to use the template.
Introduction
In this template, we introduce how to score the "relevance" of traffic keywords for reverse lookup of competitor terms:
Relevance Score - A larger numerical value indicates that the keyword is associated with a greater number of the same competitor ASINs on the first page of Amazon search results for the queried keyword.
Note: A relevance score of 100 does not mean that the keyword is associated with exactly the same ASINs as the queried keyword;
rather, it indicates the keyword is associated with the most common ASINs in the natural ranking on the first page of Amazon search results.
By downloading data from Seller Spirit and using Kimichat to create a "Competitor Keywords - Basic Word Type Analysis Table," we can help build our own "Keyword Database," reverse check competitors ➕ large model semantic analysis ➕ keyword scoring system = high-quality "Competitor Keyword Database."
⛓️💥 📊 Click to visit SellerSprite
First, conduct three-dimensional scoring for keywords:
"Relevance";
"Competition";
"Traffic Volume".
Finally, let the AI understand the information accurately based on the processed competitor data, and provide specific advertising phrases and listing keyword suggestions - plan & recommendations.
Step 1: Create some new empty folders
prompt:
Batch create six empty folders, named "Traffic Levels, Relevance Score, Competition Score, Intermediate, Competitor Keyword Reverse , Category Products"
⭕️ Did it succeed? Remember to upload the files to the folder to start processing!
👆🏻 Remove irregular data line structures and keep only the data starting from the column headers (including the column fields).
Step 2: Upload the files
File download entry:
: Search for keywords directly on Amazon (👈🏻 Click to go to Amazon and check more than 5 competitors)
(Use one of the largest core keywords to find as many related keywords as possible)
(Based on category Node ID + core keyword, find the sales situation of category competitors)
Amazon's advertising phrases and listings should focus on high-traffic keywords, but it's not always better to choose the keywords with the highest traffic.
Only keywords that are highly relevant and have an appropriate level of competition can lead to better exposure. Many people often overlook this point.
In the following sections, we will use this business template and the code scripts generated by the AI model to conduct automated business analysis and initially build a keyword database for a brand-new product.
Step 3.1 AI Competitor Keyword Organization
Organize the recently reverse-checked and mined competitor keywords into a single Excel sheet with a "relevance" column.
prompt:
First, create a new folder named "Keyword_Reverse_Lookup_Modified" in the Intermediate/ directory. If it already exists, do not take any action.
prompt:
Read the file under Competitor Keyword Reverse. Each file insert a column in the first column . Then fill the first column of each file with the corresponding portion of the file name.
Specifically, extract the part of the file name between the second "-" and either the "(" or the next intermediate "-". Fill this extracted portion into the "ReverseASIN" field.
For example, for the file "ReverseASIN-US-B00T0C9XRK(2400)-20241023.xlsx", the part to be filled is "B00T0C9XRK". Note that you only need this middle part "B00T0C9XRK".
Once filled, Remove the first line of data from each file .save these files to Intermediate/Keyword_Reverse_Lookup_Modified
prompt:
Create a empty csv file name All_Competitors_Cattree.csv underIntermediate/ The file shall with field name is "Reverse Asin Keyword Phrase Click Share Estimated Weekly Impressions Keywords type Conversion Keyword Distribution Organic Share Sponsored Share Organic Rank Organic page Updated Sponsored Rank Sponsored page Updated Weekly ABA Rank Monthly Searches SPR Title density Monthly Purchase Purchase Rate Impressions Clicks Products DSR Sponsored ASINs Click Concentration Most Clicked 3 ASINs Concentration PPC Suggested bid TOP 10 Products",
Then read all excel/csv files underIntermediate /Keyword_Reverse_Lookup_Modified write into the All_Competitors_Cattree.csv
prompt:
Read Intermediate/All_Competitors_Cattree.csv , andCategory Products/KeywordMining-US-cat tree(1995)-Last-30-days.xlsx. Match the two files based on the fields 'Keyword Phrase' = 'Keyword'.
Write back the corresponding 'Relevancy' data fields to Intermediate/All_Competitors_Cattree.csv .
Then save the resulting files as a new file in the Relevance Score/Relevance Score/ folder, naming it 'Cattree_Competitor_Keywords_Relevance_Score'."
As shown, we have already organized the 'Relevancy' field into our needed 'Relevance Data
Step 3.2: Relevance Distribution Chart
Read the relevance table to examine the general distribution of all related keywords within different 'Relevancy' ranges for competitors. If the data is not highly irregular,
we will use keywords with a 'Relevancy' score greater than 50 for precise targeting.
(Irregularity is defined as when more than 50% of competitor keywords have a 'Relevancy' score above 50, or if the count is fewer than 10.)
Below is a data example for illustration.
prompt:
Read the Relevance Score/Cattree_Competitor_Keywords_Relevance_Score.csvRelevance Score/Cattree_Competitor_Keywords_Relevance_Score.csv ， First, delete the data rows with "Estimated Weekly Impressions" equal to 0 to prune the data and remove outliers. Then, plot a bar chart using "ReverseASIN" as the dimension.
X-axis: Relevancy, with each 10% as a data bin.
Y1-axis: Original "Estimated Weekly Impressions" data, without any additional calculations.
Y2-axis: Keyword Phrase count.
Workspace:
Bar Chart: Corresponds to Y1-axis data. Place bars for different "ReverseASIN" side by side on the X-axis within each bin, without stacking. For multiple "ReverseASIN", show multiple bars. Label the data with the cumulative "Estimated Weekly Impressions" and "Keyword Phrase" count, along with the percentage of the cumulative "Estimated Weekly Impressions" relative to the total for that "ReverseASIN."
Line: Corresponds to Y2-axis data, showing the average Keyword Phrase count under different Relevancy for all "ReverseASIN." Use different colors for different "ReverseASIN."
Step 3.3: Competitor Keywords Relevance Tagging
⭕️ Below are the relevance levels: high, medium, and low. Please adjust based on the general percentile and keyword judgment from above.
The general standard is to categorize the top 50 keywords as high relevance.
Reference Example:
If 'Relevancy' is greater than 50, mark 'Relevance' as high.
If 'Relevancy' is between 20 and 50, mark 'Relevance' as medium.
For other keywords, mark 'Relevance' as low.
Note: Focus on high relevance for the initial stage of new products, while medium relevance can be considered later. When selecting keywords for a new product,
traffic level is one of the dimensions to consider but not the primary or sole consideration, especially for CPC and advertising keywords.
Read the relevance data.
For each keyword, categorize the 'Relevance' as high, medium, or low based on the criteria:
High relevance: 'Relevancy' > 50
Medium relevance: 20 < 'Relevancy' ≤ 50
Low relevance: 'Relevancy' ≤ 20
Use the categorized data for further analysis and decision-making.
prompt:
Load the Relevance Score/Cattree_Competitor_Keywords_Relevance_Score.csv and Add a new column named "Relevancy Type" in the data.
Type 1: Set "Relevancy Type" to "High" if "Relevancy" is greater than 50, or if the "Keywords type" field contains "Highly searched", "High-ranking", or "Long-tail".
Type 2: Set "Relevancy Type" to "Medium" if "Relevancy" is less than 50 and greater than 20.
For all other keywords, set "Relevancy Type" to "Low".
Delete rows where "Estimated Weekly Impressions" equals 0 or empty.
Save the modified data as a new file in the Relevance Score directory with the name "Cattree_Relevance_Score".
The following is our final analysis table for relevance analysis.