Keyword grouping is one of those foundational SEO tasks that sounds manageable in theory—until you find yourself staring at a massive spreadsheet containing 12,000 unorganized search queries. Attempting to manually categorize each term or fill out a “group by intent” column row by row is an exhausting sink of time that quickly leads to human error and inconsistency.
Traditional manual clustering simply does not scale for modern search search engine optimization. Even worse, basic rule-based grouping relying on exact keyword matching falls short. Rule-based systems frequently miss semantic overlap between phrases that express identical search intent without sharing a single word. To build scalable content strategies that satisfy real user intent, SEO professionals need a more sophisticated, automated approach.
To solve this, an efficient python-based clustering pipeline utilizes TF-IDF vectorization alongside HDBSCAN—a density-based clustering algorithm. This workflow handles noisy datasets, processes thousands of queries in minutes, and produces structured, actionable topical clusters. You can access the open-source script directly on GitHub.
The Problem with Modern Keyword Clustering
Keyword clustering serves as the ultimate catalyst for topic generation and content planning. Rather than handing content creators disconnected briefs built around individual target queries, clustering allows SEO teams to group semantically related queries into unified, coherent topics. This approach ensures that a single piece of content addresses a comprehensive range of related search intents, directly matching how modern search engines evaluate topical authority.
Structuring your site around well-defined topic clusters delivers significant algorithmic and operational benefits:
- Stronger Semantic Relationships: Grouping terms reveals how topics intersect, allowing you to map out logical content hubs.
- Enhanced Topical Authority: Producing complete coverage across a cluster demonstrates deep expertise to search engines.
- Optimized Internal Linking: Grouped keywords clearly define parent-child URL hierarchies and contextual internal linking paths.
- Broader Visibility: A single targeted landing page optimized for a topic cluster can rank for dozens or hundreds of long-tail variations.
Despite these clear advantages, constructing automated pipelines introduces two major technical hurdles: data preprocessing and algorithmic topic clustering.
Preprocessing the Keyword Data
Raw keyword exports sourced from databases or SEO toolkits are notoriously noisy. They often contain misspellings, non-ASCII characters, irrelevantly short queries, and high-frequency stop words. Cleaning tens of thousands of rows manually inside a spreadsheet editor is virtually impossible.
Python offers the ideal solution for this challenge. By leveraging data manipulation libraries, you can build an automated preprocessing pipeline that ingests raw query files, strips out noise, normalizes text variations, and standardizes formats at scale with zero manual intervention required after initial setup.
Clustering the Topics Without Predefined Constraints
When analyzing large keyword datasets, you rarely know how many topical clusters exist before exploring the data. This inherent unpredictability makes popular machine learning algorithms like K-Means clustering a poor fit for SEO data. K-Means forces you to define a specific number of clusters (the k value) prior to running the analysis. If you guess wrong, you end up with either overly broad umbrella clusters or fragmented groups that obscure meaningful insights.
To overcome this limitation, combining TF-IDF (Term Frequency-Inverse Document Frequency) vectorization with HDBSCAN (Hierarchical Density-Based Spatial Clustering of Applications with Noise) yields far superior results.
TF-IDF converts text strings into mathematical feature vectors. It does this by evaluating term importance across the dataset—assigning heavier mathematical weight to distinctive contextual terms while down-weighting generic words that appear across many queries. Once converted into these vector representations, the data is passed to HDBSCAN.
HDBSCAN is a density-based algorithm that discovers natural groupings based on spatial proximity without requiring you to guess the total number of clusters ahead of time. Crucially, HDBSCAN excels at handling noise. Instead of forcing every outlier query into an ill-fitting cluster, HDBSCAN flags unclassifiable terms with a -1 label.
This noise identification is invaluable for SEO workflows. Search query exports frequently contain highly obscure, one-off long-tail keywords that lack strong semantic connections to broader topics. Isolating these outliers prevents them from diluting the quality and coherence of your primary topical clusters.
Sourcing High-Volume Keyword Lists via BigQuery
Before executing any clustering logic, you need an un-sampled keyword list. While exporting query data directly from the Google Search Console (GSC) user interface works, the web UI caps exports at just 1,000 rows and frequently applies data sampling on higher-volume sites.
If your organization exports Google Search Console performance data directly into Google BigQuery, you have access to complete, un-sampled query records. Extracting your target dataset simply requires a straightforward SQL query against your GSC BigQuery schema.
By pulling directly from BigQuery, you can gather months of historic impressions, clicks, and queries across your domain without hitting standard interface export limits. Once the query results are retrieved:
- Export the result set as a CSV file.
- Isolate the dedicated search query column.
- Save the output as a plain
.txtfile containing a single keyword query per line.
If you do not currently have a BigQuery integration established, standard CSV or TXT exports from Google Search Console, third-party SEO platforms, or internal analytics databases will still work fine. The Python script only requires a flat text file containing one query per line.
Refactoring and Prompting AI for Script Optimization
Developing custom automated tooling used to require hours of manual scripting and debugging. By leveraging generative AI assistants, you can rapidly refactor legacy code, optimize library dependencies, and enhance overall output quality.
When using AI tools to assist in building or refining Python scripts for data science and SEO pipelines, specific prompting strategies yield significantly better operational code:
1. Request Tunable Parameters over Hardcoded Values
Parameters like minimum cluster size and density sensitivity perform radically differently depending on your dataset volume. Clustering a targeted 200-keyword list requires much lower minimum threshold settings than analyzing a global dataset of 50,000 queries.
When prompting an AI to generate or update your code, explicitly instruct it to extract key configuration settings—such as min_cluster_size and cluster sensitivity—into clear, adjustable variables at the top of the script. This design structure lets you tweak parameters across multiple iterations without risking damage to the core vectorization and clustering logic.
2. Specify Your Execution Environment
Standard Python scripts designed for terminal execution often rely on command-line argument parsers like argparse. However, the most convenient workflow for digital marketing teams often involves cloud-hosted interactive notebooks like Google Colab.
By prompting the AI with a specific target environment—such as requesting a Colab-native Jupyter notebook—the AI automatically restructures the workflow. It incorporates Colab-friendly file upload widgets, implements graceful fallback error handling around environment-specific imports, and integrates interactive visualization libraries like Plotly to visually chart cluster density and distributions directly within your browser.
Step-by-Step Code Execution and Output Analysis
The core execution model of this Python clustering tool remains lean and efficient. It transforms raw text files into cleanly organized topic clusters through a straightforward execution process.
Step 1: Input and Automated Text Cleaning
You begin by uploading your .txt file containing target queries into the notebook runtime. The preprocessing module immediately takes over, performing multi-stage string cleaning:
- Stripping out non-ASCII, non-English, or special control characters.
- Filtering common global stop words that offer no semantic value.
- Normalizing whitespace, lowercasing, and removing accidental duplicates.
Step 2: Vectorization and Parameter Tuning
Once the text is cleaned, the script converts terms into weighted numerical vectors using TF-IDF. At this stage, you can fine-tune the clustering behavior using the configurable parameters:
min_cluster_size: Controls the absolute minimum number of queries required to form a distinct topic group. Lower values create smaller, highly granular sub-topics, while higher values create broader core categories.- Cluster Sensitivity: Controls how aggressively HDBSCAN evaluates density borders when assigning queries to neighboring groups.
Step 3: HDBSCAN Clustering and Auto-Labeling
HDBSCAN processes the weighted vectors and constructs dense cluster groups. Once clustered, the tool automatically evaluates the top TF-IDF scoring terms within each individual cluster to generate a representative, descriptive label for the overall topic group.
Step 4: Structured Data Export
Finally, the tool compiles the output into a structured, multi-tab Excel spreadsheet formatted for immediate strategic review:
- Cluster Summary View: A high-level overview detailing each identified topic cluster name, total query count within the group, and representative keyword samples.
- Granular Keyword Breakdown: A comprehensive row-by-row table mapping every input keyword directly to its assigned cluster ID, complete with calculated scoring metrics and outlier flags.
Maximizing AI and Machine Learning in SEO Strategy
While machine learning models perform the heavy lifting of mathematical calculation and pattern detection, high-level strategic decisions remain firmly in the hands of the SEO professional. Choosing TF-IDF over dense semantic word embeddings (like BERT or OpenAI embeddings) offers a distinct advantage in keyword clustering: TF-IDF is extraordinarily fast, lightweight, and preserves distinct lexical boundaries critical for query analysis without requiring expensive GPU infrastructure.
Generative AI accelerates developer velocity by taking over repetitive tasks—such as updating boilerplate code, wiring external data processing libraries together, and creating customizable user interfaces. Combining AI coding assistance with custom algorithmic logic results in a highly tailored tool perfectly tuned to your specific workflow needs.
The resulting Python clustering tool provides a lightweight, highly customizable solution capable of processing thousands of unorganized search queries in seconds. While automated outputs should always be vetted by editorial expertise before mapping out final site architectures, this tool completely eliminates hours of tedious manual spreadsheet work, providing content and SEO teams with an optimized starting point for topic-driven keyword research.