AI 关键词挖掘工具：用 Python + 大模型自动生成 SEO 关键词矩阵和内容选题

pip install httpx pandas openpyxl python-dotenv

# 支持 OpenAI 兼容 API（智谱、通义、本地部署均可）
OPENAI_API_KEY=sk-xxx
OPENAI_BASE_URL=https://api.openai.com/v1
MODEL_NAME=gpt-4o-mini

import os
import json
import httpx
import pandas as pd
from dotenv import load_dotenv
from typing import List, Dict

load_dotenv()

API_KEY = os.getenv("OPENAI_API_KEY")
BASE_URL = os.getenv("OPENAI_BASE_URL", "https://api.openai.com/v1")
MODEL = os.getenv("MODEL_NAME", "gpt-4o-mini")

HEADERS = {
    "Authorization": f"Bearer {API_KEY}",
    "Content-Type": "application/json"
}

PROMPT_KEYWORDS = """你是一个SEO专家。根据用户提供的种子关键词，生成50个相关的长尾关键词。

要求：
1. 覆盖不同搜索意图（信息型、交易型、比较型、导航型）
2. 包含问答型关键词（如"怎么..."、"...是什么"）
3. 包含地域型关键词（如"北京..."、"...附近"）
4. 每个关键词附带搜索意图标签

请严格按以下JSON格式输出，不要添加任何额外文字：
[
  {{"keyword": "关键词文本", "intent": "informational|transactional|comparative|navigational", "category": "主题分类"}}
]"""

PROMPT_CONTENT_IDEAS = """你是一个内容策略师。根据以下关键词列表，为每个主题生成3个文章选题建议。

要求：
1. 选题要有差异化角度
2. 标注预估难度（低/中/高）
3. 标注建议字数范围

请严格按以下JSON格式输出：
[
  {{"topic": "主题分类", "ideas": [
    {{"title": "文章标题", "difficulty": "low|medium|high", "word_count": "1500-3000"}}
  ]}}
]"""


def generate_keywords(seed_keyword: str, count: int = 50) -> List[Dict]:
    """调用大模型生成关键词列表"""
    prompt = PROMPT_KEYWORDS.replace("{seed}", seed_keyword)

    response = httpx.post(
        f"{BASE_URL}/chat/completions",
        headers=HEADERS,
        json={
            "model": MODEL,
            "messages": [
                {"role": "system", "content": "你是专业的SEO关键词研究助手。"},
                {"role": "user", "content": f"种子关键词：{seed_keyword}\n请生成{count}个相关长尾关键词。"}
            ],
            "temperature": 0.8,
            "response_format": {"type": "json_object"}
        },
        timeout=30
    )
    response.raise_for_status()
    data = response.json()

    # 解析返回的JSON
    content = data["choices"][0]["message"]["content"]
    keywords = json.loads(content)

    if isinstance(keywords, dict) and "keywords" in keywords:
        keywords = keywords["keywords"]

    return keywords


def generate_content_ideas(keywords: List[Dict]) -> List[Dict]:
    """基于关键词生成内容选题建议"""
    # 先按 category 分组
    categories = {}
    for kw in keywords:
        cat = kw.get("category", "其他")
        categories.setdefault(cat, []).append(kw["keyword"])

    ideas = []
    for category, kws in categories.items():
        sample_kws = kws[:5]  # 每类取5个代表词
        prompt = f"""以下是"{category}"主题的关键词：
{', '.join(sample_kws)}

请为这个主题生成3个差异化文章选题建议。按JSON格式输出：
[
  {{"title": "文章标题", "difficulty": "low|medium|high", "word_count": "1500-3000"}}
]"""

        response = httpx.post(
            f"{BASE_URL}/chat/completions",
            headers=HEADERS,
            json={
                "model": MODEL,
                "messages": [
                    {"role": "system", "content": "你是专业的内容策略师。"},
                    {"role": "user", "content": prompt}
                ],
                "temperature": 0.7,
                "response_format": {"type": "json_object"}
            },
            timeout=30
        )
        response.raise_for_status()
        data = response.json()
        content = json.loads(data["choices"][0]["message"]["content"])

        ideas.append({
            "category": category,
            "ideas": content if isinstance(content, list) else [content]
        })

    return ideas


def export_results(keywords: List[Dict], ideas: List[Dict], filename: str = None):
    """导出为Excel和CSV"""
    if filename is None:
        from datetime import datetime
        filename = f"keywords_{datetime.now().strftime('%Y%m%d_%H%M%S')}"

    df = pd.DataFrame(keywords)

    # 统计各意图数量
    intent_counts = df["intent"].value_counts().to_dict()

    # 保存Excel
    excel_path = f"{filename}.xlsx"
    with pd.ExcelWriter(excel_path, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="关键词列表", index=False)

        # 写入意图分布表
        intent_df = pd.DataFrame(list(intent_counts.items()), columns=["意图", "数量"])
        intent_df.to_excel(writer, sheet_name="意图分布", index=False)

        # 写入内容选题
        if ideas:
            ideas_flat = []
            for item in ideas:
                for idea in item.get("ideas", []):
                    ideas_flat.append({
                        "主题分类": item["category"],
                        "文章标题": idea.get("title", ""),
                        "预估难度": idea.get("difficulty", ""),
                        "建议字数": idea.get("word_count", "")
                    })
            pd.DataFrame(ideas_flat).to_excel(writer, sheet_name="内容选题", index=False)

    # 保存CSV
    csv_path = f"{filename}.csv"
    df.to_csv(csv_path, index=False, encoding="utf-8-sig")

    print(f"✅ 已导出: {excel_path}, {csv_path}")
    return excel_path, csv_path


def main():
    seed = input("请输入种子关键词: ").strip()
    if not seed:
        print("❌ 关键词不能为空")
        return

    print(f"🔍 正在生成关键词（种子词: {seed}）...")
    keywords = generate_keywords(seed)
    print(f"✅ 生成了 {len(keywords)} 个关键词")

    print("\n📋 关键词预览:")
    for i, kw in enumerate(keywords[:10], 1):
        intent_map = {
            "informational": "信息型",
            "transactional": "交易型",
            "comparative": "比较型",
            "navigational": "导航型"
        }
        intent_cn = intent_map.get(kw.get("intent", ""), kw.get("intent", ""))
        print(f"  {i}. [{intent_cn}] {kw['keyword']} ({kw.get('category', '-')})")

    print("\n💡 正在生成内容选题建议...")
    ideas = generate_content_ideas(keywords)
    print(f"✅ 生成了 {sum(len(item['ideas']) for item in ideas)} 个选题建议")

    print("\n📁 正在导出结果...")
    excel_path, csv_path = export_results(keywords, ideas)

    print(f"\n🎉 完成！文件保存在: {excel_path}")


if __name__ == "__main__":
    main()

python keyword_miner.py