公众号历史文章数据采集与Excel分析实战：以罗辑思维为例-平芜编程栈

1. 项目全景：从“看文章”到“看数据”

1.1 这个项目到底在做什么

罗辑思维这个号，是我公众号观察系列里第一批纳入样本的。前后筹备了一周，把2025年发布的874篇文章全部抓下来，整理成Excel，包含标题、发布时间、链接、阅读数、点赞数、推荐数、分享数、留言数。做完之后发现一个很有意思的数字：10万+的文章有147篇，整体爆文率接近16.8%。

这个项目本质上做的是一件事：把公众号从“内容产品”变成“数据样本”。很多做运营的朋友每天在看文章、转文章，但对“这个号到底发了什么、哪些内容真正跑出来了、用户在看什么之后愿意互动”这些问题，基本上是凭感觉。我这几年的经验是，感觉会骗人，数据不会。把一年的发文记录拉出来，排个序、分个层、算个比例，很多运营上的门道一下就清楚了。

这篇长文想把整个流程完整交代一遍：数据怎么抓、字段怎么定义、Excel怎么生成、统计结果怎么看，以及我在这个过程中踩过的坑。适合三类人：做新媒体运营的、搞数据分析的、纯粹想研究头部公众号内容节奏的。我默认你懂一点Python，不太懂也没关系，代码可以直接抄，每一步为什么这么做我会讲清楚。

1.2 为什么选“罗辑思维”这个观察样本

选样本这事，很多人不重视，觉得随便找一个号就能跑通流程。实际上样本选得好不好，直接决定后续分析有没有意思。我当时挑罗辑思维，主要看中几点。

第一，更新频率够高。这个号基本保持日更，有些天甚至会发三到四篇，一年下来能凑出874篇有效记录。样本量大了之后，做时间分布、标题规律这类分析才有统计意义。如果找一个周更的号，一年才五十篇，做出来的观察报告很薄，也看不出什么趋势。

第二，文章形态足够杂。有长文、有短观点、有书摘、有商业案例分析、也有知识付费产品的推广文案。形态杂意味着阅读数、点赞数、留言数这些指标分化明显，分析时能看出不同内容类型之间的差异。我手动翻的时候感觉篇篇都挺有道理，但数据拉出来之后才发现，有些类型的文章阅读量离10万+差得很远。

第三，10万+文章的比例足够高，又没高到失去区分度。147篇10万+意味着这个号确实有稳定的大众触达能力，但剩下727篇没有到10万+，这给对比分析留出了空间。如果全都能到10万+，反而没法分析“什么内容更容易爆”。

说白了，选样本的标准就三条：有量、有差异、有分层。满足这三条，哪怕你选的是一个垂直领域的万粉小号，同样能做出一份有价值的观察报告。

1.3 为什么坚持导出Excel而不是只写分析代码

有人可能觉得，数据分析直接用Python打印结果就行，为什么非要导成Excel？我做这个项目时从第一天就定了规矩：最终交付物一定是一份Excel文件，而且要做得让不懂代码的人也能直接用。

一个很现实的原因是，运营团队里不是每个人都愿意跑命令行。把数据导成Excel之后，同事可以自己筛选“阅读数大于5万的标题有哪些”，可以拖一个数据透视表看“哪个月发得最多”，甚至可以直接把某一列复制进汇报PPT。我见过太多分析项目，Python脚本写得漂漂亮亮，但分析结果只在工程师的终端里，业务人员根本够不着，最后项目就烂尾了。

另一个原因是Excel本身在几千行的数据规模下非常好用。很多人搜过“Excel函数公式大全”，也收藏过不少技巧，但真到处理八百多行数据的时候反而觉得 Excel 不够用。这个认知是错的。874篇文章构成的二维表，用数据透视表、条件格式、自动筛选处理起来非常顺手，完全没必要上数据库。先导出Excel，让数据“落地”，后续要导进BI工具、接入RPA，或者转成CSV喂给其他程序，都有回旋余地。

2. 数据采集方案：怎样拿到公众号历史文章

2.1 三条获取文章列表的路径

要拿到一个公众号的历史文章，市面上的做法大致分三类，我的建议是优先级从上往下排。

第一类，公众号自己的合集/专辑页。微信公众号后台推出“合集”功能之后，运营者会把同主题的文章归到一个专辑里。专辑页有一个公开的URL，顺着它能拿到一连串的文章标题、封面、摘要和发布时间。这个路径最干净，数据是结构化的，而且不需要登录就能看到前几十篇。罗辑思维这个号内容体系性强，合集页给整个采集流程省了很多事。

第二类，第三方新媒体数据平台。新榜、清博这类平台都已经把公众号历史文章目录化，提供了按账号查看文章列表的功能。它们的字段比原始页面更规整，有些还直接给出了阅读数、点赞数等指标的估算值。但问题也很明显：非会员能看到的数据量有限，字段精细度不够，而且不同平台的口径可能不一致。用这类平台做交叉验证没问题，做主力数据源不太够。

第三类，搜狗微信搜索。这是老牌入口，直接搜公众号名就能看到最近十篇文章，还附带阅读数和点赞数。但搜狗的限制非常严格，翻页困难、反爬明显、数据深度有限，只适合临时查一篇文章，不适合批量拉全量数据。我试过用它跑整个流程，跑了一半就放弃了。

实际执行时我走的是“合集页拿文章列表 + 文章详情页拿互动指标”的组合路。先拿到“有哪些文章”，再逐篇去取“每篇表现如何”，两段数据用文章链接作为主键关联，最后合成一张宽表。这个思路对任何公众号都通用，不管对方有没有开合集，你只需要找到它的文章入口就行。

2.2 字段拆解：阅读数、点赞数、推荐数、分享数、留言数到底指什么

采集之前必须先把字段定义搞清楚。不然后面统计出来数字对不上，自己都不知道错在哪。我这套Excel里最终落地的字段有八个：文章标题、发布时间、链接、阅读数、点赞数、推荐数、分享数、留言数。

文章标题和链接好理解，直接从文章页HTML里能抓。发布时间也好办，文章页有一串Unix时间戳，转换一下就是标准日期时间。这里注意一个坑：标题里可能带换行和多余空格，直接进Excel会显得很乱，后面清洗阶段要专门处理。

阅读数，对应文章页下方的“阅读”数字。这里有一个全行业都知道的特殊规则：超过10万之后，页面只显示“10万+”，不再显示精确数字。所以我在Excel里单独加了一列“是否10万+”，用逻辑值标记清楚，方便后面筛爆文。文章详情接口如果返回精确数字，当然直接填进去；如果只返回“100001”这种占位符，我就统一按照10万+处理。

点赞数，页面上的“点赞”按钮对应的数值。这个指标在不同时期有过变化，早期是“点赞”，后来改成“在看”，最近又改成“推荐”，所以在旧文章和新文章之间做跨期对比时，要留意按钮名称对应的字段到底是谁。我这套表格里把“点赞数”和“推荐数”分开列，避免混在一起算不清楚。

推荐数，也就是现在文章底部的“推荐”按钮数据。微信改版之后，用户可以把文章推荐给自己的朋友，这个动作比单纯的点赞重一些，接近早期的“在看”。接口返回的字段名在不同公众号上可能不一样，我自己的处理原则是：以自己抓包看到的返回值为准，在Excel里用固定列名，同时保留原始字段备注说明。

分享数，指用户把文章转发给好友或朋友圈的次数。这个值不直接在页面正文展示，但接口能拿到。分享数是衡量内容“主动传播力”的关键指标，比阅读数更能说明问题——一篇被分享很多但阅读一般的文章，往往意味着它在小圈层内的精准传播。

留言数，就是文章留言区的评论总条数。留言需要作者精选后才会公开，所以留言数高一方面说明讨论热烈，另一方面也说明运营者在评论区互动上花过心思。留言数这个指标我自己在统计时单独用了接口数据，没用页面肉眼数，因为有些长文评论能有好几百条，人工数根本数不过来。

2.3 合规与频率控制：数据采集的底线

这一节是我每次讲公众号采集都必须强调的，不是因为场面话，而是我确实见过有人因为不管不顾地爬，最后账号被封、数据白抓。

先说合规底线。公众号文章页、合集页本质上是公开内容，我把它们的标题、时间、链接和展示出来的互动数据记录下来，用于个人观察和分析，这属于对公开信息的整理。但如果把目光转向用户头像、昵称、评论者个人信息，性质就变了。这个项目的原则非常明确：只采集文章自身的数据，绝不采集任何用户个人信息。留言数我取的是“有几条留言”，不是“谁留了什么言”。

再说频率控制。微信公众号对非登录状态下的接口请求是有风控的，短时间高频请求很容易触发验证码甚至是IP限流。我自己的经验是，同一IP下请求间隔不要低于1.5秒，并且每次请求之间加一个随机延迟，把“看起来像人手点击”的节奏模拟出来。整个874篇跑完，我大概用了两个多小时，中间还主动停了几次，这个速度完全够用，没必要贪快。

第三个建议是做断点续采。文章列表分页拉到一半断网、程序崩了、电脑睡眠，都是现实会遇到的场景。我一开始没做检查点，断了就得从头来，特别浪费时间。后来改进成每抓完一篇文章就往本地文件里追加一行，就算中途崩了，下次启动时先读一下已经抓到的链接集合，跳过这些再做增量。这个做法后来帮我省了不少事。

3. 数据处理与Excel导出：从杂乱JSON到规整表格

3.1 数据清洗的标准动作

抓下来的原始数据不能直接写进Excel。接口返回的JSON和页面解析出来的文本，多多少少都有脏东西，不清理的话后面的透视表、排序、筛选全都会出错。

清洗流程我一般固定做四步。

第一步是去重。文章链接是唯一主键，我从合集页拿到的列表和补采的详情数据里可能重复出现同一篇文章，直接用链接去重，保留信息最全的那一条。参数拼接顺序不同的URL需要先做归一化，否则同一个链接因为参数顺序不对会被当成两条记录。

第二步是格式化时间。接口返回的发布时间是一个十位或十三位的时间戳，我用datetime模块转成“2025-03-15 08:30:00”这种标准格式，同时拆出一列“日期”和一列“时刻”，方便后面按日汇总和按小时分析。这一步很多人偷懒不做，结果Excel里全是数字串，图表都画不了。

第三步是标题清洗。标题里的换行符、制表符、首尾空格全部清掉，再把标题长度单列统计出来。后面分析的时候，我要用标题长度和阅读数做简单对比，看是不是短标题更容易爆。这个分析虽然粗糙，但确实能看出一些倾向。

第四步是数值字段的兜底处理。阅读数、点赞数这些字段有可能返回空值或字符串“None”，我把它们统一转成0，同时保留一列“是否缺失”作为诊断标记。宁可明明白白地知道这条数据没抓到，也不要让空值混进平均值计算里，把整体数据带偏。

清洗完之后我会做一次全量巡检：看看总共多少条、缺失值占比多少、时间范围是否覆盖全年、有没有明显的异常值，比如某篇文章阅读数是隔壁文章的几百倍。巡检没问题，才进入Excel导出阶段。

3.2 用Python三件套实现Excel导出

整个项目我只用了三个Python库：requests抓取、pandas处理表格、openpyxl写Excel。这三个库加在一起，已经能把这件事从头做到尾。

先看抓取部分的核心逻辑。文章详情接口和公开页面返回的数据，最终会汇总成一条条记录，追加到一个列表里：

import requests import time import random from datetime import datetime HEADERS = { "User-Agent": "Mozilla/5.0 (iPhone; CPU iPhone OS 17_0 like Mac OS X) " "AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 " "MicroMessenger/8.0.40(0x18002833) NetType/WIFI Language/zh_CN", "Referer": "https://mp.weixin.qq.com/" } def fetch_article_meta(url): # 文章详情页解析：标题、发布时间、链接 r = requests.get(url, headers=HEADERS, timeout=10) # 实际解析需要从HTML中提取 var msg_title、ct 等字段 # 这里省略正则细节，把解析结果作为字典返回 return { "标题": "示例标题", "发布时间": "2025-06-18 08:00:00", "链接": url, } def fetch_read_stats(biz, mid, idx, sn, token): # 阅读数、点赞数、推荐数、分享数、留言数 api = "https://mp.weixin.qq.com/mp/getappmsgext" params = { "__biz": biz, "appmsg_type": "9", "mid": mid, "idx": idx, "sn": sn, "is_ok": 1, "f": "json", "token": token, } r = requests.get(api, params=params, headers=HEADERS, timeout=10) return r.json()

这里有两个提醒。第一，微信对UA（User-Agent，访问者浏览器标识）是有要求的，直接拿普通浏览器的UA去访问文章页，大概率被拒。我同事踩过一次坑，明明链接在微信里能正常打开，用requests一访问就返回403。后来把UA改成微信内置浏览器的格式，问题就解决了。第二，阅读数等互动数据并不在文章页正文里，而是要调getappmsgext这个接口，参数里的__biz、mid、idx、sn必须从文章链接或页面源码里解析出来，token也有时效，过期之后要重新想办法获取。

抓完之后，把records装进pandas的DataFrame，再做一次最后的清洗，然后写Excel：

import pandas as pd from openpyxl import load_workbook from openpyxl.formatting.rule import CellIsRule from openpyxl.styles import PatternFill df = pd.DataFrame(records) # 判断是否为10万+文章 df["是否10万+"] = df["阅读数"] >= 100000 # 按月统计 df["发布时间"] = pd.to_datetime(df["发布时间"]) df["月份"] = df["发布时间"].dt.to_period("M") monthly = df.groupby("月份").agg( 总发布数=("标题", "count"), 爆文数=("是否10万+", "sum") ).reset_index() with pd.ExcelWriter("罗辑思维_2025观察.xlsx", engine="openpyxl") as writer: df.to_excel(writer, sheet_name="文章总表", index=False) monthly.to_excel(writer, sheet_name="月度发布统计", index=False) df[df["是否10万+"]].to_excel(writer, sheet_name="10万+清单", index=False) # 给文章总表加条件格式 wb = load_workbook("罗辑思维_2025观察.xlsx") ws = wb["文章总表"] green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid") ws.conditional_formatting.add( "J2:J875", CellIsRule(operator="greaterThanOrEqual", formula=["100000"], fill=green_fill) ) wb.save("罗辑思维_2025观察.xlsx")

这段代码跑完之后，Excel里会有三个Sheet：文章总表、月度发布统计、10万+清单。总表里每一行是一篇文章，月度统计里每个月一行，10万+清单则单独列出所有爆文。三个Sheet对应三种看数据的视角，日常用的时候不会混。

3.3 让Excel更耐用的三个细节

导出Excel只是及格线，真正好用的表格还需要再做三个细节处理。我第一次做出的表格给同事用，对方反馈是“能用，但不好用”，问题就出在细节上。

第一，日期一定要设置成Excel能够识别的格式。直接写入字符串“2025-06-18 08:00:00”，Excel不一定把它当成日期，筛选时无法按年月分组。我的做法是在写入前用pandas把它转成datetime类型，这样Excel会识别为真正的日期，自动筛选和透视表都能按年月层级展开。

第二，链接要带跳转动作。文章链接光放在单元格里，别人要看还得复制粘贴到浏览器，很麻烦。我通常会在Excel里加一列“文章跳转”，用公式=HYPERLINK(A2,"打开原文")，这样读者单击就能跳转。这个细节特别有效，我上次发给运营同事，对方第一反应就是“这个太方便了”。如果你不喜欢公式，也可以直接用openpyxl给单元格加超链接属性，效果一样。

第三，加一列“是否10万+”，并用条件格式把爆文行标出来。10万+的界限是100000，条件格式选“大于或等于”，填充绿色，整行都亮起来。这样一打开Excel，哪些文章跑出来了，眼睛一扫就知道，根本不用再排序。我自己看数据时，绿油油的一片就是月度的爆发时段，灰扑扑的一片就说明那段时间内容整体没有破圈。

还有一个容易被忽略的细节：Excel里长数字，比如链接URL，容易被Excel自动识别成科学计数法，看起来很别扭。解决办法是在写入之前给这一列设置单元格格式为“文本”，或者干脆在URL前面加一个单引号。我用的是openpyxl读取后设置列格式，这一招属于“查了半天才发现的坑”，现在直接写出来省得你再踩。

4. 2025年数据盘点：874篇与147篇背后的信息量

4.1 总量与发布时间规律

874篇全年文章，平均到365天差不多每天2.4篇。这个量级说明罗辑思维的内容生产不是“偶发式”的，而是有明确排期、有稳定产能的。具体到一天之内发出几条，拉开Excel就能看到节奏：大部分天数是两到三条，偶尔有四条的爆发日，也有极个别天只发了一条甚至没发。

发布时间分布是一个很有趣的维度。我按“小时”统计了一遍文章发布时间，发现这个号的发文时间高度集中在几个固定窗口：早上七点到八点一条，中午十二点左右一条，晚上六点到八点一条。这种“早中晚三段式”排期，我判断是为了覆盖通勤、午休、睡前三个阅读高峰。推送时间本身就是运营策略的一部分，很多用户就是习惯了在某个固定时间点收到它的文章，久而久之形成了打开习惯。

这里有一点值得做公众号的朋友参考：如果你也想稳定出爆文，不必追求每篇文章都十万加，更重要的是让读者在固定时间看到你。数据里有大量阅读数在几千到两三万之间的文章，它们虽然没爆，但保持着账号的日常活跃度。真正撑起这个号基本盘的，恰恰是这些“不爆”的文章带来的稳定触达。

4.2 10万+文章占比与特征观察

147篇10万+文章，在874篇总量里占比约16.8%。如果把全年分成四个季度来看，会发现这147篇并不是均匀分布的。我在Excel里用透视表粗略看了一眼，明显有几个月份集中出现了大量10万+，还有一些月份几乎颗粒无收。这种月度差异通常和内容主题、热点事件、产品推广计划强相关，值得单独列出来做深挖。

我再做了一个很简单的特征对比：把10万+文章和全部文章的平均点赞数、平均推荐数、平均留言数分别算了一遍，发现爆文的互动指标几乎全面高于非爆文。这个结果不意外，但有一个细节很有意思——有些文章阅读数刚好还在五六万，但留言数已经超过了部分10万+文章。这说明小爆文不一定输在讨论热度上，破圈和深度互动是两个维度。运营上如果只盯阅读数，很容易错过评论区里那些真正高粘性的反馈。

标题长度我也顺手统计了一下。不是想做什么严谨的结论，但数据确实显示出一种倾向：10万+文章的标题普遍比非爆文更短、更直给。倒不是说长标题一定不好，而是头部账号的内容盘子足够大，短标题在信息流里被完整看到的概率更高，损失的信息更少。这个观察对标题党没什么帮助，但对老老实实做内容的人是有参考价值的。

4.3 从数据反推运营节奏

874篇文章的数据，如果只看总量就太浪费了。我做这份Excel的核心目的，是想反推出这个号的运营思路。

比如说，把“文章发布时间”和“是否10万+”放在一起看，能隐约看出早间时段发出的文章，爆文比例比晚间略高。这个现象我推测和早间用户阅读行为有关：早上大家愿意花时间看一篇有深度的文章，晚上则更偏向于轻松的内容。当然这只是相关，不是因果，要验证还得再做更细致的内容分类。

再比如说，“推荐数”和“留言数”同时偏高的文章，往往对应一些有争议性或者有现实意义的话题。有些内容阅读量中等，但评论数和推荐数远远超出平均值，说明它在核心读者群中引发了强烈的情绪共鸣。运营者完全可以依据这类指标去调整后续选题方向，不必只盯着流量天花板。

这个Excel对我来说最大的价值，是它把“罗辑思维到底在靠什么保持影响力”这个问题变得可以回答：高频、稳定、固定时段触达，内容在长短之间切换，爆文和常规内容交替，评论区保持活跃。这些结论从外部看可能只是模糊的印象，但一旦落到Excel里，就变成了可以追溯、可以量化、可以作为对标参照的运营策略。

5. 常见问题与排查经验：踩过的坑一次说清

5.1 抓取时拿不到阅读数怎么办

这个问题我几乎每次做采集都会遇到，公众号的互动数据接口有一定概率返回空值。排查方向按顺序来：

第一步，检查UA是否模拟了微信内置浏览器。普通浏览器UA访问文章详情接口，微信后台经常拒绝，返回的JSON里没有read_num字段或者直接报错。这个问题最容易踩，也最好修。

第二步，确认token是否过期。token是调用getappmsgext接口必需的凭证，有时效性，有效期过了之后接口会返回错误码。我的做法是在主流程里加了token刷新逻辑，一旦检测到特定错误码就暂停任务，重新获取token再继续。

第三步，确认文章链接中的参数配对是否正确。__biz、mid、idx、sn这四个参数必须来自同一篇文章，混搭会出现“文章不存在”之类的提示。我当时还遇到过一个情况：合集页拿到的链接和详情页拿到的链接，参数排序不一致，导致匹配不上，后来统一用原始链接作为唯一标识，问题就解决了。

第四步，如果以上都没问题，大概率是阅读数已经超过10万，接口返回的值为空或为占位符。这个时候不用纠结，直接在Excel里标记为“10万+”，程序上不要让它影响整体入库。

5.2 Excel导出后乱码、列错位、链接失效

Excel导出环节也有不少细节问题。第一个坑是CSV编码。如果直接用Excel打开CSV文件，中文会乱码，因为CSV默认是ANSI编码，Python写出来的往往是UTF-8。解决办法有两个：一是写CSV时指定encoding="utf-8-sig"，二是干脆不用CSV，直接用pandas的ExcelWriter输出真正的xlsx格式，这样根本不存在编码问题。我的选择是后者，少一个坑就少一份烦恼。

第二个坑是数字被Excel自动转成科学计数法。文章链接这种长串字符，在Excel里很容易变成7.89E+17这种样子。解决办法就是把链接列设成文本格式。我上面代码里写Excel的时候，其实是可以用excel_writer的格式化选项预先处理的，如果你用openpyxl做二次处理，记得把链接列单元格的number_format设为"@"。

第三个坑是超链接失效。用=HYPERLINK()公式生成的链接，如果目标地址里带了&或者?这样的特殊字符，Excel有时会解析出问题。我的经验是，链接如果来自微信文章页，原样写入问题不大，但如果是从抓包工具里复制、带了转义符的，要先做一次URL解码再写入。

5.3 频率限制与账号风险：别等封号才后悔

最后一定要讲频率限制。公众号的风控不是只针对登录态，对匿名接口请求也有一整套识别逻辑。我同事曾经图快，把请求间隔压到0.2秒，结果跑了不到一百条就被提示操作频繁，之后整个IP段一段时间内都没法正常访问文章页。这种代价太大了。

我的做法是：同一个IP下，单篇文章的接口请求间隔不低于1.5秒，再在这个基础上加随机延迟，把间隔控制在1.5到3.5秒之间。整个874篇跑下来，虽然时间多花了一点，但全程没有触发风控，数据一次性采集完整。如果你要采多个公众号，更要注意控制总请求量，不要在同一天内跑完所有目标账号，拆成几天分批跑更稳妥。

还有一件事，我强烈建议每次采集都做增量备份。每抓完一篇就在本地记录一行，不要等全部结束再一次性入库。我当时第一次做的时候没有这个思路，跑到第四百篇时电脑自动更新重启，前面抓的数据全丢了。后来改成增量追加模式，就算中断，重启后也能从上次断掉的地方接着跑。