Keyword Clustering

Keyword Clustering Skill

Turn a raw keyword list into a structured, validated cluster map delivered as a downloadable Excel file.

What This Skill Does

1. Accepts keywords from any input format
2. Validates each keyword via DataForSEO — fetches search volume, KD, CPC
3. Filters out keywords with 0 or null search volume (dead keywords)
4. Clusters the validated keywords by topic + search intent
5. Outputs a formatted, downloadable Excel file

Step 1: Input Handling

Detect which input format the user provided and extract the keyword list.

Pasted list

If the user pasted keywords directly in chat (one per line, comma-separated, or numbered), extract each keyword into a clean array. Strip numbers, bullets, extra whitespace.

CSV or Excel upload

The file will be at /mnt/user-data/uploads/. Read it with pandas:

import pandas as pd

# CSV
df = pd.read_csv('/mnt/user-data/uploads/filename.csv')

# Excel
df = pd.read_excel('/mnt/user-data/uploads/filename.xlsx')

# Identify keyword column: look for columns named 'keyword', 'keywords', 'query', 'term', 'search term'
# If ambiguous, pick the first text column or ask the user
keyword_candidates = ['keyword', 'keywords', 'query', 'term', 'search term']
keyword_col = next((col for col in df.columns if col.lower() in keyword_candidates), df.columns[0])
keywords = df[keyword_col].dropna().tolist()

Google Sheet URL

Use web_fetch to fetch the sheet as CSV (append /export?format=csv to the base URL). Parse with pandas.

Cap at 500 keywords per run. If input exceeds 500, tell the user and process the first 500, or ask which subset to use.

Step 2: DataForSEO Validation

Use the dataforseo_labs_google_keyword_overview tool to validate all keywords and fetch metrics. Batch in groups of 100 to stay within API limits.

Required fields to extract per keyword:

• search_volume — monthly searches (Google)
• keyword_difficulty — KD score (0–100)

Filter rule: Drop any keyword where search_volume is 0, null, or missing. These are dead keywords.

Tell the user upfront: "Validating [N] keywords via DataForSEO. This may take a moment..."

After validation, report:

• Total keywords submitted
• Keywords that passed (have search volume)
• Keywords that were dropped (zero/null volume) — list them so the user can see what was removed

If DataForSEO is unavailable or returns an error: Tell the user "DataForSEO validation failed — I'll cluster the full list but cannot validate search volume or provide KD/CPC data." Then proceed with the full unvalidated list and skip the volume/KD/CPC columns in the output.

Step 3: Clustering

Cluster the validated keywords only using a two-axis approach:

Axis 1: Topical Cluster

Group keywords by shared topic/theme. Use the root concept to name the cluster.

Rules:

• Each cluster should have a 2–5 word descriptive name (e.g., "Email Marketing Tools", "Python Data Analysis")
• Aim for clusters of 3–8 keywords. Prefer more clusters over fewer — if a topic can reasonably be split into two distinct subtopics, split it
• Do NOT over-merge: keywords that target different angles, audiences, or content types should be in separate clusters even if they share a root word
• If a topic is very broad, always create sub-clusters (use a parent + child naming convention, e.g., "SEO – On-Page", "SEO – Technical")
• Assign each cluster a numeric Cluster ID (1, 2, 3…)

Axis 2: Search Intent

For each keyword, assign one of the four standard intent labels:

When intent is ambiguous, pick the most likely based on the full keyword phrase. Do not leave intent blank.

Clustering Logic

Use your understanding of keyword semantics. Group keywords that:

• Share the same core topic or product area
• Would logically appear in the same content piece or site section
• Target the same audience stage

Do NOT group purely by shared word (e.g., don't put "best email marketing software" and "email marketing statistics" in the same cluster just because they share "email marketing" — one is Commercial, one is Informational, and they serve different pages).

Step 4: Build the Excel Output

Read the xlsx SKILL first if available. Use openpyxl for formatting.

Sheet 1: "Keyword Clusters" (main output)

Columns in order:

Sorting: Sort by Cluster ID ascending, then by Search Volume descending within each cluster.

Formatting:

• Row 1: Bold header, dark background (#1F2D40), white text, center-aligned
• Alternate cluster groups with light row shading to visually separate clusters (#F2F2F2 every other cluster group)
• Freeze top row
• Auto-fit column widths (min 15, max 40)
• Number format for Search Volume: #,##0
• KD: plain integer

Sheet 2: "Cluster Summary"

One row per cluster:

Sort by Avg Search Volume descending — highest-opportunity clusters first.

Sheet 3: "Dropped Keywords"

List all keywords removed at the validation step:

If no keywords were dropped, add a single row: "No keywords were dropped."

Step 5: Save and Deliver

# Save the workbook
output_path = '/mnt/user-data/outputs/keyword_clusters.xlsx'
wb.save(output_path)

Then run recalc if formulas are used:

python scripts/recalc.py /mnt/user-data/outputs/keyword_clusters.xlsx

Use present_files to deliver the file to the user.

After presenting the file, give a short summary in chat:

• Total keywords clustered
• Number of clusters created
• Number of keywords dropped
• Top 3 clusters by average search volume

Edge Cases

• Duplicate keywords: Deduplicate before validation. If duplicates exist, mention it.
• Non-English keywords: DataForSEO supports multi-language. Cluster by semantic meaning. Note the language.
• All keywords dropped: Tell the user all keywords had zero volume. Offer to cluster them anyway without validation.
• Single keyword input: Tell the user clustering requires at least 10 keywords to be meaningful.
• Very large input (200+ keywords): Warn that DataForSEO batching may take 2–3 minutes. Proceed automatically.