This is free to download keywords research and content brief template that we often use to create content based on specific topics or themes. We call it an advanced topical authority keyword research strategy. Using this template and strategy correctly will not only lead to high rankings in SERP, driving quality organic SEO traffic, but also help solve your audience’s problems.

The data source will be the free Keywords Research Tool from Google Ads Keywords Planner, so anyone can easily use this template. Just note, you should be familiar with Google Sheet Query and some Google Sheet formulas to modify this template.

Download below, or continue reading for more explanation.

Things You Need to Know and Learn to Use This Google Sheet Template

To use this template, you should understand the following, or master these if you want to modify and extend this free SEO template:

1. Named Ranges
2. Google Sheet Query
3. Google Sheet Formulas such as IF, SUM, ARRAYFORMULA, ISERROR, SEARCH, QUERY

Sheet Component Explanation

1. Raw Data

This sheet contains data from Google Ads Keywords Planner. Ensure that the data model and structure match this template if you intend to use different data values from the example provided. For scalability and ease of use, I have named this range “RawData” with the range ‘Raw Data’!A:Z, which includes all data columns.

2. Modifier

This sheet features five columns, which I refer to as modifiers: Brand Modifier, Informational Modifier, Navigational Modifier, Commercial Modifier, and Transactional Modifier.

1. Brand Modifier: A list of words that represent a brand.
2. Informational Modifier: A list of words that represent informational search intent.
3. Navigational Modifier: A list of words that represent navigational search intent.
4. Commercial Modifier: A list of words that represent commercial search intent.
5. Transactional Modifier: A list of words that represent transactional search intent.

Each modifier column has its own named range for scalability purposes, allowing you to change the ranges without altering the main formula, which I will explain shortly.

3. Branded KW, Informational KW, Navigational KW, Commercial KW, Transactional KW Sheet

This sheet contains lists of keywords grouped by search intent using the formula in the K column (Search Intent):

=IFS(
SUM(ARRAYFORMULA(IF(ISERROR(SEARCH(QUERY(InformationalModifier, "Select B ",0), A2)), 0, 1))) > 0, "Informational",
SUM(ARRAYFORMULA(IF(ISERROR(SEARCH(QUERY(NavigationalModifier, "Select C ",0), A2)), 0, 1))) > 0, "Navigational",
SUM(ARRAYFORMULA(IF(ISERROR(SEARCH(QUERY(CommercialModifier, "Select D ",0), A2)), 0, 1))) > 0, "Commercial",
SUM(ARRAYFORMULA(IF(ISERROR(SEARCH(QUERY(TransactionalModifier, "Select E ",0), A2)), 0, 1))) > 0, "Transactional",
TRUE, "Informational"
)

To display the results based on each search intent, use the following formula for Branded KW:

- Branded KW: =QUERY(RawData,"SELECT A,C,D,E,F WHERE J=TRUE ORDER BY A ASC",1)
- Informational KW: =QUERY(RawData,"SELECT A,C,D,E,F WHERE K='Informational' AND J=FALSE ORDER BY A",1)
- Navigational KW: =QUERY(RawData,"SELECT A,C,D,E,F WHERE K='Navigational'",1)
- Commercial KW: =QUERY(RawData,"SELECT A,C,D,E,F WHERE K='Commercial'",1)
- Transactional KW: =QUERY(RawData,"SELECT A,C,D,E,F WHERE K='Transactional' AND J=FALSE",1)

4. Content Brief Section

In the content brief section, I name the categories as follows: Informational KW Content Brief, Navigational KW Content Brief, and so on.

For this example, I have created only one content brief section titled “Informational KW Content Brief.”

To display the data, you can use the following formula:

=QUERY(InformationalKWList, "SELECT E, B, A, F ORDER BY F", 1)


Ensure that you assign named ranges first for the list of Branded KW, Informational KW, Navigational KW, Commercial KW, and Transactional KW Sheets.

Also, don’t forget to make it static by copying all data and using Paste Special -> Values Only. Since during the work, this content brief section will need some changes and topic grouping optimization.

5. Statistics Section

This section’s purpose is to understand the potential of the data we have. The content can vary based on the specific case study you need. For example, in this sheet, the statistics display how many search queries we have in homeschooling topics, grouped by branded keywords and search intent, and also the potential search volume that could be generated by this topic.

That’s it! I hope this free SEO template for keyword research is useful for your needs. I hope you understand how to use it. If not, I might create an e-book that explains it more clearly in Bahasa Indonesia, and a more advanced version with a video explaining how to use it. What do you think? Leave your thoughts in the linkedin comment section.