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

做SEO汇报Excel表格没人看。我用Python做了几套可视化方案把排名、流量、竞品数据变成直观的图表。这篇文章分享代码和最佳实践。一、为什么需要可视化数据本身不会说话可视化让数据说话老板看不懂排名表格但能看懂趋势线团队记不住数字但能记住颜色客户不理解波动但能理解对比二、核心可视化方案2.1 排名趋势图importmatplotlib.pyplotaspltimportpandasaspdimportsqlite3defplot_ranking_trend(keyword:str,db_path:str):绘制排名趋势图connsqlite3.connect(db_path)dfpd.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,axplt.subplots(figsize(12,6))# 绘制趋势线ax.plot(df[date],df[rank],linewidth2,color#2196F3)# 填充区域ax.fill_between(df[date],df[rank],alpha0.3,color#2196F3)# 反转Y轴排名越小越好ax.invert_yaxis()# 添加平均线avg_rankdf[rank].mean()ax.axhline(yavg_rank,colorred,linestyle--,alpha0.5,labelfAverage:{avg_rank:.1f})# 样式ax.set_title(fRanking Trend: {keyword},fontsize16,fontweightbold)ax.set_xlabel(Date,fontsize12)ax.set_ylabel(Rank,fontsize12)ax.grid(True,alpha0.3)ax.legend()plt.tight_layout()plt.savefig(franking_trend_{keyword.replace( ,_)}.png,dpi150)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]]# 闭合图形anglesnp.linspace(0,2*np.pi,len(categories),endpointFalse).tolist()our_dataour_data[:1]comp_datacomp_data[:1]anglesangles[:1]fig,axplt.subplots(figsize(8,8),subplot_kwdict(projectionpolar))ax.plot(angles,our_data,o-,linewidth2,labelUs,color#2196F3)ax.fill(angles,our_data,alpha0.25,color#2196F3)ax.plot(angles,comp_data,o-,linewidth2,labelCompetitor,color#FF5722)ax.fill(angles,comp_data,alpha0.25,color#FF5722)ax.set_xticks(angles[:-1])ax.set_xticklabels(categories)ax.set_ylim(0,100)ax.set_title(Competitive Analysis,fontsize16,fontweightbold,pad20)ax.legend(locupper right,bbox_to_anchor(1.3,1.1))ax.grid(True)plt.tight_layout()plt.savefig(competitor_radar.png,dpi150)plt.close()2.3 热力图关键词×日期importseabornassnsdefplot_keyword_heatmap(keywords:List[str],db_path:str):绘制关键词排名热力图connsqlite3.connect(db_path)# 获取最近30天的数据dfpd.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,paramskeywords)conn.close()# 透视表pivotdf.pivot(indexkeyword,columnsdate,valuesrank)fig,axplt.subplots(figsize(14,8))# 自定义颜色排名越好数字小越绿sns.heatmap(pivot,annotTrue,fmt.0f,cmapRdYlGn_r,cbar_kws{label:Rank},axax)ax.set_title(Keyword Ranking Heatmap (Last 30 Days),fontsize16,fontweightbold)ax.set_xlabel(Date,fontsize12)ax.set_ylabel(Keyword,fontsize12)plt.tight_layout()plt.savefig(keyword_heatmap.png,dpi150)plt.close()三、自动化报告生成defgenerate_weekly_report(db_path:str,output_dir:str):生成周度SEO报告importos os.makedirs(output_dir,exist_okTrue)# 1. 排名趋势图keywordsget_top_keywords(db_path)forkwinkeywords[:5]:plot_ranking_trend(kw,db_path)# 2. 竞品雷达图metricscalculate_competitor_metrics(db_path)plot_competitor_radar(metrics)# 3. 热力图plot_keyword_heatmap(keywords[:10],db_path)# 4. 生成HTML报告htmlf html headtitleSEO Weekly Report/title/head body h1SEO Weekly Report/h1 pGenerated:{datetime.now().strftime(%Y-%m-%d)}/p h2Ranking Trends/h2{ .join(fimg srcranking_trend_{kw.replace( ,_)}.png width600forkwinkeywords[:5])}h2Competitive Analysis/h2 img srccompetitor_radar.png width600 h2Keyword Heatmap/h2 img srckeyword_heatmap.png width800 /body /html withopen(f{output_dir}/report.html,w)asf:f.write(html)四、可视化最佳实践颜色绿色好红色差黄色警告Y轴排名图要反转1在上方标注关键变化点加注释对比永远有对照组简洁一张图讲一个故事可视化不是为了让图表好看而是为了让决策更快。老板看一张图5秒就能知道要不要加大投入比看10页表格有用得多。matplotlib seaborn完全够用不需要买Tableau。