The Basics

The Keyword Clusterizer Google Sheets Add-on will help you quickly and efficiently organize large data sets of Keywords into lexical clusters.

Installing the KWC

We developed the Keyword Clusterizer as a Google Sheet Add-on so that you can manage your SEM projects the way you want to, using the same built-in Google Sheet functionality that you already love and know.

You can install the KWC Google Sheet Add-on from the Google Workspace Marketplace here: https://workspace.google.com/marketplace/app/keyword_clusterizer/614914266143

After Installing, you will see the Add-on appear in your Google Sheet “Extensions” menu item.

Importing Data Sources

To import your keyword data from various sources such as Search Console, SEM Rush, Moz, KeywordTool.io, or ahrefs, follow these steps:

1. Create a Google Sheet and organize your imports: Start by filling a Google Sheet with the data imported from your preferred keyword tools. We suggest creating one tab for each data source to keep things organized.
2. Export data as CSV from your chosen data source: From your favorite data source, export the keyword data as a CSV file.
3. Import CSV into the Google Sheet: Import the CSV file into your Google Sheet. It is recommended to create a separate tab for each data set from each data source.
4. Utilize the Keyword Clusterizer: The Keyword Clusterizer tool will consolidate and visually represent all your keywords, blending the data from multiple sheets into a coherent overview.

By following these steps, you can import keyword data from different sources and use the Keyword Clusterizer to view and analyze all your keywords in one unified representation.

Mapping your Data to the KWC

The mapping step in the Keyword Clusterizer involves assigning relevant metrics to each keyword. You will find a new Tab called - Data Master Sheet - that is automatically created after your first Keyword Clusterizer Run.

In your - Data Master Sheet - You will find all your metrics in one place. You know need to label three different kinds of Metrics :

1. Choose your Keyword Data (1 Column - Mandatory)
2. Choose your Volume Metric (1 Column - Optional)
3. Add Any Extra Metric you want (Up to 5 Columns - Optional)

How to Map Your Metrics

Keyword Data:

• This column contains the keywords or search terms that you have selected for analysis. It serves as the primary input for the mapping step.
• It is important to choose keywords that are relevant to your business or website to ensure accurate analysis and insights.
• Example Metrics you can use for “Keyword Data” : Keywords, Search Terms, Negative Keywords.

Volume Metric:

• The volume metric represents the metric you want to use as volume for each keyword.
• Example Metrics you can use for “Volume Metric” : Impressions, Clicks, or Search Volume.

Extra Metrics:

• The additional metric columns, labeled as "Extra Metric," allow you to include supplementary metrics attached to your Keywords
• You can choose up to five extra metric columns to accommodate various metrics of interest.
• In your spreadsheet, some of the extra metric columns are populated with data, while others are marked as "Skip."
• Example Metics you can use for “Extra Metric” : You can use anything, Intent, Competition, URLs, CPC, Keyword Difficulty.

Running the Clusterizer Again:

• You must rerun your Clusterizer after you have selected your metrics.
• By rerunning the clusterizer, you can refine your keyword groups and gain a deeper understanding of keyword relationships.

Changing Metrics:

• You can revisit the mapping step anytime to modify or update the metrics associated with your keywords.
• This flexibility allows you to adapt your analysis based on new data or evolving priorities.

By following the mapping step in your keyword analysis process, you can organize and analyze your keyword data effectively. Assigning relevant metrics to each keyword enables you to uncover valuable insights for search engine optimization, content creation, and marketing strategies.

Hot, Cold, and Maybe Tabs

To organize your keywords, follow these instructions:

1. Move keywords to the Hot, Maybe, or Cold tabs: You can assign keywords to the Hot, Maybe, or Cold tabs for categorization. This action will take effect during the next run of the process.
2. Category level selection: If you choose to move a category, the entire category and its associated keywords will be relocated to the designated tab.
3. Subcategory level selection: If you opt to move a subcategory, the entire subcategory along with its keywords will be moved to the desired tab.
4. Keyword level selection: If you select an individual keyword, only that specific keyword will be moved to the designated tab, leaving the rest of the category or subcategory intact.

By following these instructions, you can effectively organize your keywords by moving them to the appropriate tabs at the category, subcategory, or keyword level.

Apply Conditional Formatting

The "Apply Conditional Formatting" functionality in the Keyword Worksheet (KWC) allows you to automatically apply conditional formatting to the current sheet. This feature enhances the visual representation of your keyword clusters by changing the colors of cells based on specific criteria. It is particularly useful when working in real-time with clients, as it improves the aesthetics and readability of the KWC.

How to Apply Conditional Formatting:

Accessing the Functionality:

• Go to the main menu of the KWC.
• Look for the "Apply Conditional Formatting" option.

Running the Function:

• Click on the "Apply Conditional Formatting" option to initiate the process.
• The functionality will start applying conditional formatting to the current sheet.

Color Coding of Clusters:

• After the conditional formatting is applied, the sheet will display color-coded cells for your keyword clusters.
• Typically, the colors used are blue, red, and yellow to represent "Hot," "Cold," and "Maybe" clusters, respectively.

Improved Visualization:

The color-coded cells make it easier to visually identify and differentiate keyword clusters at a glance. This enhanced visualization helps you and your clients quickly understand the distribution and classification of keywords.

Real-Time Collaboration:

When working with clients in real-time, the application of conditional formatting adds a professional touch to the KWC. The visually appealing layout makes it easier for clients to follow and engage with the keyword analysis process. Utilizing the "Apply Conditional Formatting" functionality in the KWC enhances the readability and aesthetics of your keyword clusters. By color-coding the cells based on the "Hot," "Cold," and "Maybe" categories, this feature allows for quick and intuitive comprehension of keyword groupings.

Advanced Functionality

Here we talk about the advanced stuff

Forced and Irrelevant Clusters

To control the behavior of the Clusterizer in future runs, you can utilize forced and irrelevant categories. Here's a clear explanation:

Forced Categories: You can force the Clusterizer to create specific categories in future runs. A forced category must be a single word. By designating a category as forced, you ensure its inclusion in subsequent runs.

Irrelevant Categories: The Clusterizer will exclude irrelevant categories from future runs. Irrelevant categories consist of single words and are considered designations for keywords that are deemed irrelevant. Any keywords that would have been assigned to an irrelevant category will be distributed among other existing categories.

You can manage forced and irrelevant categories in two ways:

1. Forced and Irrelevant Categories Tab: You can access and modify your list of forced and irrelevant categories in the dedicated "Forced and Irrelevant Categories" sheet.

2. Green and Red Check Boxes: By clicking the green box next to a category, you save it as a forced category, instructing the Clusterizer to include it in subsequent runs. Conversely, clicking the red box next to a category designates it as irrelevant, causing the Clusterizer to exclude it from future runs. Keywords within the irrelevant category will be redistributed among other categories.

By following these instructions, you can effectively manage forced and irrelevant categories to shape the behavior of the Clusterizer in future runs.

Negative and Positive Keywords

Please ensure that you add appropriate keywords to the designated columns. If a keyword in the "Hot," "Maybe," or "Cold" tab matches a negative keyword, it should be moved to the "Cold" column. Similarly, if a keyword matches a positive keyword, it should be moved to the "Hot" column. This action should be performed only once, during the next run.

In the event of a conflict between a negative keyword, a positive keyword, and a "Move To" instruction, the priority order is as follows:

1. Negative keyword
2. Positive keyword
3. "Move To" instruction

Please adhere to the established Google Ads matching rules:

• Use brackets [...] for an exact match: Only keywords that are identical to the expression within the brackets will be matched.
• Use quotes "..." for a phrase match: Keywords that contain the expression within the quotes, in the same order, will be matched.
• Use no quotes or brackets: Keywords that contain all of the words in the expression, regardless of the order, will be matched.

*It's important to understand that when the Keyword Clusterizer (KWC) utilizes your positive or negative keywords once, those keywords will be highlighted in a gray cell background and will not be considered in future runs. This is done to prevent mistakenly including positive and negative keywords that should no longer be part of your clusters. If you wish to include those keywords again, you can remove the gray cell background at any time, and the KWC will treat them as if they were being used for the first time, reevaluating their impact on the clusters.

Google Permissions

The Keyword Clusterizer requires certain permissions to effectively utilize your data. Here's a clearer explanation:

1. View and Manage Permissions

To enable the Keyword Clusterizer to access and manage your sheet, you will need to authorize it. This authorization allows the Clusterizer to retrieve your keyword data from your Google sheet and write the sorted data back into the same sheet.

2. Connect to an External Service

Additionally, you will need to authorize the Keyword Clusterizer (KWC) to connect with an external service. The processing of your data is handled by our clustering script, which is hosted externally. Your data is collected and sent to our clustering algorithm, where it is sorted. Finally, the formatted, visually enhanced data is written back into your Google sheet.

By granting these permissions, the Keyword Clusterizer can effectively utilize your data by accessing and managing your sheet, as well as connecting with our external clustering service to process and enhance your keyword data.