keyword-clustering

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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:
```python
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:

| Label | Meaning | Signal words |
|---|---|---|
| `Informational` | User wants to learn | what is, how to, guide, tutorial, definition, examples |
| `Navigational` | User wants a specific site/brand | brand name + login/sign in/pricing |
| `Commercial` | User is comparing options | best, top, vs, review, alternative, comparison |
| `Transactional` | User wants to act/buy | buy, download, get, free trial, sign up, hire |

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:
| Column | Description |
|---|---|
| Cluster ID | Numeric cluster number |
| Cluster Name | Descriptive topic name |
| Keyword | The validated keyword |
| Search Volume | Monthly search volume from DataForSEO |
| KD | Keyword difficulty (0–100) |

From Infrasity-Labs/dev-gtm-claude-skills, MIT.

More from Infrasity-Labs/dev-gtm-claude-skills