做SEO汇报，Excel表格没人看。我用Python做了几套可视化方案，把排名、流量、竞品数据变成直观的图表。这篇文章分享代码和最佳实践。

一、为什么需要可视化

数据本身不会说话，可视化让数据说话：

• 老板看不懂排名表格，但能看懂趋势线
• 团队记不住数字，但能记住颜色
• 客户不理解波动，但能理解对比

二、核心可视化方案

2.1 排名趋势图

import matplotlib.pyplot as plt
import pandas as pd
import sqlite3

def plot_ranking_trend(keyword: str, db_path: str):
    """绘制排名趋势图"""
    
    conn = sqlite3.connect(db_path)
    df = pd.read_sql("""
        SELECT date, rank FROM rankings
        WHERE keyword = ?
        ORDER BY date
    """, conn, params=(keyword,))
    conn.close()
    
    df['date'] = pd.to_datetime(df['date'])
    
    fig, ax = plt.subplots(figsize=(12, 6))
    
    # 绘制趋势线
    ax.plot(df['date'], df['rank'], linewidth=2, color='#2196F3')
    
    # 填充区域
    ax.fill_between(df['date'], df['rank'], alpha=0.3, color='#2196F3')
    
    # 反转Y轴（排名越小越好）
    ax.invert_yaxis()
    
    # 添加平均线
    avg_rank = df['rank'].mean()
    ax.axhline(y=avg_rank, color='red', linestyle='--', alpha=0.5, label=f'Average: {avg_rank:.1f}')
    
    # 样式
    ax.set_title(f'Ranking Trend: "{keyword}"', fontsize=16, fontweight='bold')
    ax.set_xlabel('Date', fontsize=12)
    ax.set_ylabel('Rank', fontsize=12)
    ax.grid(True, alpha=0.3)
    ax.legend()
    
    plt.tight_layout()
    plt.savefig(f'ranking_trend_{keyword.replace(" ", "_")}.png', dpi=150)
    plt.close()

2.2 竞品对比雷达图

import numpy as np

def plot_competitor_radar(metrics: Dict):
    """绘制竞品对比雷达图"""
    
    categories = ['Visibility', 'Top 3', 'Top 10', 'Content', 'Backlinks']
    
    # 数据
    our_data = [metrics['our']['visibility'], metrics['our']['top3'], 
                metrics['our']['top10'], metrics['our']['content'], metrics['our']['backlinks']]
    comp_data = [metrics['comp']['visibility'], metrics['comp']['top3'], 
                 metrics['comp']['top10'], metrics['comp']['content'], metrics['comp']['backlinks']]
    
    # 闭合图形
    angles = np.linspace(0, 2 * np.pi, len(categories), endpoint=False).tolist()
    our_data += our_data[:1]
    comp_data += comp_data[:1]
    angles += angles[:1]
    
    fig, ax = plt.subplots(figsize=(8, 8), subplot_kw=dict(projection='polar'))
    
    ax.plot(angles, our_data, 'o-', linewidth=2, label='Us', color='#2196F3')
    ax.fill(angles, our_data, alpha=0.25, color='#2196F3')
    
    ax.plot(angles, comp_data, 'o-', linewidth=2, label='Competitor', color='#FF5722')
    ax.fill(angles, comp_data, alpha=0.25, color='#FF5722')
    
    ax.set_xticks(angles[:-1])
    ax.set_xticklabels(categories)
    ax.set_ylim(0, 100)
    ax.set_title('Competitive Analysis', fontsize=16, fontweight='bold', pad=20)
    ax.legend(loc='upper right', bbox_to_anchor=(1.3, 1.1))
    ax.grid(True)
    
    plt.tight_layout()
    plt.savefig('competitor_radar.png', dpi=150)
    plt.close()

2.3 热力图：关键词×日期

import seaborn as sns

def plot_keyword_heatmap(keywords: List[str], db_path: str):
    """绘制关键词排名热力图"""
    
    conn = sqlite3.connect(db_path)
    
    # 获取最近30天的数据
    df = pd.read_sql("""
        SELECT date, keyword, rank 
        FROM rankings 
        WHERE keyword IN ({}) 
        AND date >= date('now', '-30 days')
        ORDER BY date
    """.format(','.join('?' * len(keywords))), conn, params=keywords)
    
    conn.close()
    
    # 透视表
    pivot = df.pivot(index='keyword', columns='date', values='rank')
    
    fig, ax = plt.subplots(figsize=(14, 8))
    
    # 自定义颜色：排名越好（数字小）越绿
    sns.heatmap(pivot, annot=True, fmt='.0f', cmap='RdYlGn_r', 
                cbar_kws={'label': 'Rank'}, ax=ax)
    
    ax.set_title('Keyword Ranking Heatmap (Last 30 Days)', fontsize=16, fontweight='bold')
    ax.set_xlabel('Date', fontsize=12)
    ax.set_ylabel('Keyword', fontsize=12)
    
    plt.tight_layout()
    plt.savefig('keyword_heatmap.png', dpi=150)
    plt.close()

三、自动化报告生成

def generate_weekly_report(db_path: str, output_dir: str):
    """生成周度SEO报告"""
    
    import os
    os.makedirs(output_dir, exist_ok=True)
    
    # 1. 排名趋势图
    keywords = get_top_keywords(db_path)
    for kw in keywords[:5]:
        plot_ranking_trend(kw, db_path)
    
    # 2. 竞品雷达图
    metrics = calculate_competitor_metrics(db_path)
    plot_competitor_radar(metrics)
    
    # 3. 热力图
    plot_keyword_heatmap(keywords[:10], db_path)
    
    # 4. 生成HTML报告
    html = f"""
    <html>
    <head><title>SEO Weekly Report</title></head>
    <body>
        <h1>SEO Weekly Report</h1>
        <p>Generated: {datetime.now().strftime('%Y-%m-%d')}</p>
        
        <h2>Ranking Trends</h2>
        {' '.join(f'<img src="ranking_trend_{kw.replace(" ", "_")}.png" width="600">' for kw in keywords[:5])}
        
        <h2>Competitive Analysis</h2>
        <img src="competitor_radar.png" width="600">
        
        <h2>Keyword Heatmap</h2>
        <img src="keyword_heatmap.png" width="800">
    </body>
    </html>
    """
    
    with open(f'{output_dir}/report.html', 'w') as f:
        f.write(html)

四、可视化最佳实践

1. 颜色：绿色=好，红色=差，黄色=警告
2. Y轴：排名图要反转（1在上方）
3. 标注：关键变化点加注释
4. 对比：永远有对照组
5. 简洁：一张图讲一个故事

可视化不是为了让图表好看，而是为了让决策更快。老板看一张图5秒就能知道要不要加大投入，比看10页表格有用得多。matplotlib + seaborn完全够用，不需要买Tableau。