Youtube video thumbnail
Posted: Sun Dec 22, 2024 5:06 am
In the example above, all else being equal, there may be a better opportunity for traffic optimizing for bicycle accidents than for bus accidents, or creating a legal settlement calculator (3,970 searches) compared to creating content around legal settlement “estimates” (10 searches). By including the number of keywords with that ranking, you can also indicate how much content may be required to address all of the users’ needs.
A category of related keywords with only 3 keywords in the category can be satisfied with just one vietnam phone number sample piece of content, while a category with 978 different keywords will likely need a much broader strategy.
To automatically calculate and update these numbers, you need to understand Excel a little, but this saves a lot of time, especially if you are updating the keyword list and don't want to manually recalculate everything.
In the image above, I used a formula in cell B4 to sum the number of queries per month for all terms marked with the value "Bicycle" in the Accident Type column on the Keyword Plan tab. Here it is:
=SUMAR.SI.CONJUNTO('Plan de palabras clave'!$B$2:$B$1697, 'Plan de palabras clave'!$J$2:$J$1697, B3)
Basically, this formula looks at all the values in cells J2 through J1697 (the entire column J) on the 'Keyword Plan' tab of the file and if the value in that column is equal to B3 (in this case "Bicycle"), add the value from the same row to the total, except for column B.
image.png
The "Number of Keywords" on the Keyword Categories tab is just a count of how many are in that "category" on the Keyword Plan tab. For example, cell B5 below:
image.png
The formula in that cell is:
It says: Look at column J of the "Keyword Plan" tab and count how many times the content in B3 ("Bicycle" again) appears.
If you still feel lost with Excel formulas, check out this video for a quick explanation:
img-semblog
7. Action plan for your keyword research
Using all of the data above, you can begin to build your plan to go after those groups of terms that you are currently not ranking well for. Start with the biggest search opportunities that are also tied to high-priority keywords. As you put your keyword plan into action, assign your most important keywords to your current pages, if those pages exist, then decide how you could improve those pages or try to attract links to those pages to improve ranking.
For some keyword groups you may not have pages on your website, but you can certainly start building your content plan to create new pages within the main part of your site or create new blog posts, whitepapers, videos, etc. I’ve even worked with some e-commerce sites that started changing their products and suppliers after learning the relative importance and opportunities of various product categories by doing keyword research.
A category of related keywords with only 3 keywords in the category can be satisfied with just one vietnam phone number sample piece of content, while a category with 978 different keywords will likely need a much broader strategy.
To automatically calculate and update these numbers, you need to understand Excel a little, but this saves a lot of time, especially if you are updating the keyword list and don't want to manually recalculate everything.
In the image above, I used a formula in cell B4 to sum the number of queries per month for all terms marked with the value "Bicycle" in the Accident Type column on the Keyword Plan tab. Here it is:
=SUMAR.SI.CONJUNTO('Plan de palabras clave'!$B$2:$B$1697, 'Plan de palabras clave'!$J$2:$J$1697, B3)
Basically, this formula looks at all the values in cells J2 through J1697 (the entire column J) on the 'Keyword Plan' tab of the file and if the value in that column is equal to B3 (in this case "Bicycle"), add the value from the same row to the total, except for column B.
image.png
The "Number of Keywords" on the Keyword Categories tab is just a count of how many are in that "category" on the Keyword Plan tab. For example, cell B5 below:
image.png
The formula in that cell is:
It says: Look at column J of the "Keyword Plan" tab and count how many times the content in B3 ("Bicycle" again) appears.
If you still feel lost with Excel formulas, check out this video for a quick explanation:
img-semblog
7. Action plan for your keyword research
Using all of the data above, you can begin to build your plan to go after those groups of terms that you are currently not ranking well for. Start with the biggest search opportunities that are also tied to high-priority keywords. As you put your keyword plan into action, assign your most important keywords to your current pages, if those pages exist, then decide how you could improve those pages or try to attract links to those pages to improve ranking.
For some keyword groups you may not have pages on your website, but you can certainly start building your content plan to create new pages within the main part of your site or create new blog posts, whitepapers, videos, etc. I’ve even worked with some e-commerce sites that started changing their products and suppliers after learning the relative importance and opportunities of various product categories by doing keyword research.