当前位置：首页 > news >正文

SEO数据可视化：用Python做让老板眼前一亮的报告

news 2026/5/25 12:10:20

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

一、为什么需要可视化

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

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

二、核心可视化方案

2.1 排名趋势图

importmatplotlib.pyplotaspltimportpandasaspdimportsqlite3defplot_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 竞品对比雷达图

importnumpyasnpdefplot_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 热力图：关键词×日期

importseabornassnsdefplot_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()

三、自动化报告生成

defgenerate_weekly_report(db_path:str,output_dir:str):"""生成周度SEO报告"""importos os.makedirs(output_dir,exist_ok=True)# 1. 排名趋势图keywords=get_top_keywords(db_path)forkwinkeywords[: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">'forkwinkeywords[: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> """withopen(f'{output_dir}/report.html','w')asf:f.write(html)