Click the link to use the template.
Introduction
In this template, we primarily focus on how to comprehensively evaluate traffic keywords from three perspectives through reverse lookup of competitor terms:
① Competitiveness,
② Relevance, and
③ Traffic Level.
We then build a professional Amazon keyword database using scientific methods tailored to different product stages and keyword selection needs.
For detailed explanations on how to derive the analysis of these three dimensions, refer to other specific dimension scoring templates within this document.
Dimension1：Competitiveness Score
Dimension2：Relevance Score
Dimension3： Traffic Score
This template primarily introduces how to conduct a reverse analysis on competing keywords, perform "traffic tier classification" for traffic-related keywords, and carry out relevant tier data statistical analysis. The analyzed data will enable the AI agent to provide keyword suggestions at different levels:
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.
Why is it necessary to use AI to assist with analysis?
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 AI Competitor Keyword Organization
Organize the recently reverse-checked and mined competitor keywords into a single Excel sheet with a "ReverseAsin" column.
prompt:
First, create a new folder named "Keyword_Reverse_Lookup_Modified" in the Intermediate/Intermediate/ directory. If it already exists, do not take any action.
Read the file under Competitor Keyword Reverse/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/Intermediate/Keyword_Reverse_Lookup_Modified/
Create a empty csv file name All_Competitors_Cattree.csvAll_Competitors_Cattree.csv underIntermediate/ The file shall with field name is "ReverseAsin 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.csvAll_Competitors_Cattree.csv
Step4. Traffic Score
Calculation: Appropriate Exposure Interval
prompt:
Read Intermediate/All_Competitors_Cattree.csvIntermediate
Filter out rows where 'Estimated Weekly Impressions'=0 or null , then prune the data by removing outliers.
Then use the formula to determine the optimal class interval: h=\frac{\text{Maximum Value} - \text{Minimum Value}}{1 + 3.322 \log_{10}(n)}
prompt:
1. Read/ Intermediate /All_Competitors_Cattree.csv Filter rows where 'Estimated Weekly Impressions' is not zero or empty
2.Define the bin edges using a bin width of 218 (Field 'Estimated Weekly Impressions').
3. Group by bins and calculate frequency and percentage
List all grouped frequency distribution tables and corresponding percentage data, as exemplified below:
Bin | Frequency | Percentage
4. Save frequency distribution tables named "Preliminary_Data_Grouping" in the / Traffic Levels/Traffic Levels/
Attention make sure the result is not a empty data table
prompt:
Read/ Traffic Levels/Preliminary_Data_Grouping.csv , If the 'frequency' is below 30, merge this group with the nearest group.
After merging the groups, save the new file in/ Traffic Levels/Traffic Levels/ with the name 'Data_Regrouping'.
According to the steps above, determine the intervals as follows:
No. Bin Frequency Percentage
1 [10031, 10249) 235 20.90%
2 [1093, 1311) 50 4.45%
3 [875, 1093) 67 5.96%
4 [657, 875) 112 9.96%
5 [439, 657) 133 11.83%
6 [221, 439) 262 23.31%
7 [3, 221) 265 23.58%
With these 7 bins, we can classify the traffic levels of this product's competitor keywords based on the projected weekly exposure into the following ranges:
Below 200
200-500
500-1000
1000-5000
Above 5000
Chart: View Competitor Traffic Distribution
prompt:
Read Intermediate/All_Competitors_Cattree.csvIntermediate/All_Competitors_Cattree.csv
1. Filter out rows where Estimated Weekly ImpressionsEstimated Weekly Impressions is not zero or empty.
2. Group the filtered data based on "Estimated Weekly Impressions" into the ranges:
below 200, 200-500, 500-1000, 1000-5000, and above 5000. Count the number of " Keyword PhraseKeyword Phrase " for different " ReverseAsinReverseASIN " within each group, which will be recorded as data F.
3.Create a grouped histogram for the Keyword Phrase performance data of different ReverseAsinReverseASIN with the following specifications:
Histogram requirements:
Y-axis: Values from data column F.
X-axis: Groups of "Estimated Weekly Impressions."
Within each X-axis group: Different " ReverseAsinReverseASIN " values should be represented by different colors. Eac ReverseAsin ReverseAsin should be displayed as side-by-side bars within the group.
Chart title: "Keyword Level Distribution."
Workspace: Label each bar with its value, using dark gray font color.
Chart colors: Use light pink, light blue, and light gray color schemes.
X-axis Y-axis: Both axes should be displayed with 1pt dark gray lines.
X-axis tick marks: Point down; Y-axis tick marks should point to the left.
X-axis title: "Traffic Keywords (Count)."
Note: Each subgroup within the X-axis should be displayed side-by-side, not stacked.
Finally, save the table with an additional field that writes the corresponding group name for eachKeyword Phrase. Name the table "Keyword_Level_Distribution.xlsx" in the Traffic Levels/Traffic Levels/ directory.
Significance Analysis: Examine Traffic Levels - Competition Differences
prompt:
Read / Traffic Levels/Keyword_Level_Distribution.xlsx focusing on two factors: 'Estimated Weekly Impressions' (A factor) and 'ReverseAsin' (B factor). Perform a randomized block design ANOVA on these factors.
Group 'Estimated Weekly Impressions' into ranges based on actual values, using grouping names such as below 200, 200-500, 500-1000, 1000-5000, and above 5000. Use the field name /Estimated Weekly Impressions Groupfor these groupings.
Calculate within each group, using the null hypothesis that there is no significant difference among different 'ReverseASIN' within each/Estimated Weekly Impressions Group
Perform ANOVA to calculate component variance, block variance, error variance, and F-value. If significant differences are found, also calculate ω and effect size. Record F-value and P-value regardless of significance, marking significance with '*'.
Compile the results into a table named /Exposure_Level_Difference_Analysisand save it in the / Traffic Levels/ folder.
prompt:
Read Traffic Levels/Keyword_Level_Distribution.xlsx and retain only the following columns: 'Keyword Phrase', 'Weekly ABA Rank', 'Monthly Searches', 'Monthly Purchase', 'Purchase Rate', 'Impressions', 'Clicks', 'PPC', 'Suggested bid', and 'Impression Category'.
Then, remove duplicate rows from the data.
Save the final result as Traffic_Analysis_File.xlsx in the Traffic Levels folder.
Step5.Competitiveness Score
Based on the suggested data fields and classifications derived above, we can construct a formula to calculate competitiveness.
Considering that competitiveness needs to incorporate traffic levels, the number of competitors, and market concentration, the formula should encompass these key elements.
Here, competitiveness represents the degree of market saturation and the intensity of competition.
We select the following representative fields to calculate competitiveness:
Estimated Weekly Impressions (traffic volume, traffic level)
Monthly Searches (search demand, traffic level)
DSR (Demand to Supply Ratio) (demand to supply ratio, competitiveness)
Sponsored ASINs (number of advertising competitors, competitiveness)
Click Concentration (click concentration of the top 10 ASINs, market concentration)
Additionally, we can appropriately include the flow value (the ratio of cost per click to product price) to measure the cost-effectiveness of each click, thereby influencing the calculation of competitiveness.
⭕️ According to this formula, a higher calculated competitiveness value indicates more intense competition. This is because:
· Data 1: High DSR with a low number of Sponsored ASINs:
This suggests that demand is relatively high compared to the number of competitors, potentially decreasing competitiveness. However, if the number of Sponsored ASINs increases, competition will intensify.
· Data 2: High Click Concentration:
This indicates that market traffic is primarily concentrated among a few ASINs, leading to more intense competition.
· Data 3: High Flow Value:
This means you need to pay a higher cost to acquire traffic, which usually signifies more intense competition.
· Data 4: High ratio of Estimated Weekly Impressions to Monthly Searches:
If weekly traffic is relatively high, it may alleviate some competitive pressure. Conversely, if this ratio is low, it may intensify competition.
prompt:
Read the file Intermediate All_Competitors_Cattree.csv
Process the field "PPC" to remove special symbols "$".
Create a new column "Historical Highest PPC Bid".
Determine the historical highest PPC bid for each "Keyword Phrase": find the highest value in the "Suggested bid" field (e.g., extract 1.21 from $0.73-$1.21) and store this value in the "Historical Highest PPC Bid" field.
Save the original file after processing.
prompt:
process the file and update the competitiveness score with the modified flow value calculation, follow these steps:
Read the File: Open Intermediate/All_Competitors_Cattree.csv
Create New Column: Add a new column named "Competitiveness".
Calculate Flow Value:
Determine the historical highest PPC bid for each "Keyword Phrase" using the "Historical Highest PPC Bid" field.
Compute the Flow Value with the formula:
[\text{Flow Value} = \left(\frac{\text{Historical Highest PPC Bid}}{\text{PPC}}\right) \times 100%]
Example calculation: For a historical highest PPC bid of 1.21 and PPC price of 0.97,
[\text{Flow Value} = \frac{1.21}{0.97} \times 100% \approx 124.74%]
Integrate into Competitiveness Formula:
Calculate Competitiveness using:
[\text{Competitiveness} = \left(\frac{\text{DSR}}{\text{Sponsored ASINs}} \times \text{Click Concentration} \times \frac{\text{Flow Value}}{100}\right) \times \left(\frac{\text{Estimated Weekly Impressions}}{\text{Monthly Searches}}\right)]
Filter and Clean Data:
After updating, retain only the columns: "Click Share", "Weekly ABA Rank", "Monthly Searches", "Monthly Purchase", "Purchase Rate", "PPC", "Competitiveness".
Remove duplicate rows, keeping only unique "Keyword Phrase" entries. The Final Data must keep this Field
Save the Processed Data: Save the updated data as "Competitiveness-Score" in the directory Competition Score
Note: Ensure no formatting issues occur during calculations, and implement robust error handling to prevent data processing errors.
Chart Explanation: As shown, green represents "Low Competition" keywords. To find those with slightly higher traffic within the green category,
look for suitable keyword scatter points from the outside inward (see the animation demonstration below).
prompt:
Read Competitiveness-Score.csv and use Plotly to create a 3D scatter plot following these steps:
Data Cleaning: Remove rows where "Monthly Searches" is below 1000 or missing, and where "Competitiveness" is 0 or missing.
Add Data Column: Add a "Competition Type" column based on "Competitiveness" values sorted from high to low, categorizing them as "High Competition," "Medium," and "Low Competition."
Create Chart:
3D Scatter Plot with Plotly:
Axis 1 (Y-Axis): "Competitiveness."
Axis 2 (X-Axis): "Monthly Searches."
Axis 3 (Z-Axis): "Keyword Phrase."
Plot Display: Show "Competition Type" on the scatter plot using colors:
"High Competition" in shades of red.
"Medium" in shades of blue.
"Low Competition" in shades of green.
Enhance Effects: Add 3D depth effects to give the plot clear 3D depth for improved visual appeal.
Optimize the Chart:
Optimization 1: Adjust the axes to allow most of the scatter points to fit well on the chart without making the grid too wide.
Optimization 2: Slightly disperse "Competition Type" on the axis for easier data observation, and slightly reduce the scatter circle size.
Optimization 3: Increase the distance between axis titles and axis data labels for better readability.
Save Processed Data: Save the processed original data file in / Competition Score/Competition Score/ ith the name"Competition_Type_Tagged".
Step6. Relevance
prompt:
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
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.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."
⭕️ 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 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/Relevance Score/ directory with the name "Cattree_Relevance_Score".
The following is our final analysis table for relevance analysis.
Step 7: Three-Dimensional Scoring Table for Amazon Keywords
Step 8: AI-Assisted Keyword Analysis
Prompt Reference
⭕️ Promt1 Reference：
As an Amazon business data analyst, combining the data and the results shown in the table for different ASINs with various exposure rates across different weeks, perform a grouped variance analysis. After analyzing these data, provide keyword recommendations specific to each level while keeping the original format of the “Keyword Phrase” in terms of capitalization and spacing. Make sure to consider the context of Amazon's A9 search algorithm and your competitive situation. Given that you are a small seller with limited advertising budget, a new Amazon store, and no substantial historical conversion data, here's the analysis and keyword strategy for listing your new product, a cat scratching post:
Table of Data Significance:
Group F_value p_value Significance
1 5000+ 0.030936723 0.992632612
2 1000-5000 1.161548136 0.325285125
3 500-1000 0.217471531 0.884217756
4 200-500 2.645704309 0.049172344
5 Below 200 0.119690479 0.948471114
⭕️ Promt2 Reference：
As an Amazon business data analyst, you would begin by examining the group variance analysis results for different ASINs under various weekly exposure rates. From this data, you would identify trends and patterns to recommend optimal keywords at each tier level. For this analysis, focus on keywords that are most relevant, followed by those that drive high traffic.
Here’s a suggested English title and five-point description for the product, assuming the role is to improve SEO and appeal based on the competitor’s structure:
Title:
Feandrea Cat Tree, 61-Inch Multi-Level Cat Condo for Indoor Cats with 5 Scratching Posts, Plush Perches and Caves, Hammock, Pompoms, Light Gray
Five-Point Description:
[Luxurious Perches and Caves] Enjoy two plush top perches and spacious cat caves, offering multiple cozy spots for relaxation—ideal for homes with multiple cats.
[Vertical Adventure Playground] With its multi-level design, this cat tower provides endless opportunities for cats to climb and explore, fulfilling their natural instincts.
[Quick and Simple Assembly] Setup is a breeze with universal screws and a straightforward structure, requiring just the included Allen hex key for one-person assembly.
[Interactive Entertainment] Two hanging pompoms with bells and two silent spares deliver endless playtime fun, stimulating your cats with engaging activities.
[Robust and Safe Design] Featuring a sturdy build and an anti-tip kit, this cat tree supports up to 5 cats (11 lb each) for safe and carefree playtime.
These points highlight key features and benefits to attract potential buyers, leveraging strong SEO elements for increased visibility.