SEO keyword analysis

Sample of keyword analysis briefing:
Optimized Project Briefing: SEO Keyword Analysis for a Graffiti Website (Extended with Keyword Magic Tool Filtering)
Objective:To conduct a detailed keyword analysis for a graffiti website, focusing on products and graffiti-related categories. The analysis will leverage SEMrush data, especially utilizing the Keyword Magic Tool, and will be structured in an Excel format, ensuring that specific columns are populated for effective SEO and content strategy.
Background:The graffiti website seeks to be a leading platform for both graffiti enthusiasts and professionals. With a range of products and resources, it's essential to optimize for search to attract the right audience and drive conversions.Website: ----------
Scope of Work:
Keyword Research & Analysis:a. Use SEMrush, particularly the Keyword Magic Tool, to extract and analyze keywords for the following categories:
b. The keyword structure should be: [keyword] + [shirt/hoodie/sweater]. For instance, "dog shirt" or "metaverse hoodie". c. Explore a wide range of categories, avoiding any that are copyrighted or trademarked [example: "no under armour full zip hoodie" or "nike hoodie". Examples include animals (like dogs, cats), concepts (like metaverse, sustainability), colors, patterns, and more. d. Ensure that the keywords align with the graffiti/streetwear brand image.
Keyword Filtering:a. Search Volume: The keyword must at least have 30 monthly search volume, cpc of under 0,99, keyword difficulty of below 50. Number of results can't be higher than 100. Stick to the following categories Shirt Hoodie Sweatshirt Implement sub categories based on the overaching categories you find.
Analysis & Reporting:a. Organize the keywords in a structured manner, preferably in an Excel.b. For each keyword, provide the following metrics: Keyword, Search Volume, CPC, KD. c. Highlight keywords that present the best opportunities based on the criteria and potential alignment with the brand.
Recommendations:a. Based on the keyword analysis, suggest potential design ideas or themes for t-shirts, sweaters, and hoodies. b. Identify any emerging trends or niches that the brand can capitalize on in the future.
Using SEMrush's Keyword Magic Tool:a. Start Broad: Begin with a broad keyword related to graffiti (e.g., "hoodie") see example in raw unfiltered export.b. Filter Results:
Search Volume: Filter keywords based on a minimum and maximum search volume to target terms that have a significant number of monthly searches.
Keyword Difficulty: Set a range to find keywords that are feasible to rank for, considering the website's current domain authority.
CPC: If considering paid search, filter out keywords that have an excessively high CPC.
Word Count: Filter for long-tail keywords (e.g., 3+ words) which often have clearer user intent and can be easier to rank for.
SERP Features: Identify keywords that trigger specific SERP features like featured snippets, which can offer additional visibility.
Competitive Density: Filter out keywords that have a high competitive density, indicating many advertisers are bidding on this keyword.
Include/Exclude Keywords: Use these filters to specifically include or exclude certain terms or phrases. c. Explore Keyword Groups: The tool will automatically group related keywords. Dive into these groups for more specific keyword ideas.d. Use Questions Feature: Identify questions people are asking related to graffiti. This can provide content ideas.e. Analyze SERP Features: Check which keywords trigger special search results like featured snippets or local packs. Targeting these can provide additional visibility.f. Export Data: Once you've identified potential keywords, export the data to Excel for further analysis.
The keyword must at least have 30 monthly search volume, cpc of under 0,99, keyword difficulty of below 50. Number of results can't be higher than 100. Stick to the following categories Shirt Hoodie Sweatshirt Implement sub categories based on the overaching categories you find. Implement new categories and sub category's if needed.
Current ranking keywords use the vlookup method to implement in the keyword analysis and add a category and sub category. For new keywords add the keyword, search volume, cpc, KD, category and sub category.
Excel Report Creation:a. Populate the provided Excel template with the data gathered.b. Ensure data is organized and formatted for easy analysis and interpretation.c. Provide a summary of findings, pinpointing areas of opportunity and potential content strategies.
Content Recommendations:a. Based on the keyword analysis, propose content topics or optimizations that can drive organic traffic.b. Identify content gaps and recommend strategies to address them.
Using the =VLOOKUP Method in Excel:The VLOOKUP function in Excel is used to search for a value in the first column of a table range and return a value in the same row from a specified column. This is especially useful when you want to gather data from one tab and display it in another.
Steps to use VLOOKUP:
Identify Your Lookup Value: This is the value you want to search for in the first column of your table.
Specify the Table Array: This is the range of cells that contains the data you're searching through.
Column Index Number: This is the column number from which you want to pull the data. For example, if your table has three columns and you want data from the third column, you'd use "3".
Range Lookup: Typically, you'll use "FALSE" to find an exact match.
Example:=VLOOKUP(A2,Sheet2!A1:D10,3,FALSE)This formula will look for the value in A2 on the current sheet, search for it in the first column of the range A1:D10 on Sheet2, and return the value from the third column of the same row.
Timeline:
Day 1-3: Keyword extraction and initial analysis using SEMrush and the Keyword Magic Tool.
Day 4-5: Deep dive into keyword metrics and populating the Excel sheet.
Day 6: Drafting content recommendations.
Day 7: Review, refinement, and finalization of the report.
Resources Available:
SEMrush access, with a focus on the Keyword Magic Tool, for keyword data extraction.
Excel template for keyword analysis.
Approval:Upon completion, submit the Excel report and content recommendations for review. Feedback will be provided within 48 hours.
End of Optimized Briefing (Extended with Keyword Magic Tool Filtering)