# 手把手教你用Excel分析网站流量（实例讲解） 随着运营精细化发展，如何通过品牌沉淀的数据挖掘出更多优化可能，是每个运营、产品甚至技术的必修课。这篇文章将主要阐述我是如何通过发现问题、提出猜测、验证猜想和事件归类这四个方面，分析官网流量数据并找出问题的。（文中出现的工具有 CNZZ 后台和 Excel2013） 零丨数据背景 这次以某个朋友的网站作为演示数据，选取了 2016 年 7 月 25 日到 2016 年 8 月 7 日，分别是 2016 年第 31 周和 32 周两周的数据，选择这两周数据是因为第 32 周该网站刚刚关闭了付费广告的投放，所以网站流量出现了巨大差异，比较典型，如图示： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684895943704/f12e5f30-0184-43dc-8b50-b48392ba165b.jpeg align="left") 壹丨发现问题 在图右下选择 “更多指标”，选取当前核心指标，举例取 PV、UV、平均访问时长、跳出率；图中部 “时” 改为 “天”。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684895949948/0455602f-8175-435a-a8c3-bd28ca748760.jpeg align="left") 然后我们通过观察图上部分的对比数据，提出疑问： 1\. 为什么两周流量数据大面积变差？怎么找出是哪一天或者哪个栏目哪个小时的原因？ 2\. 为什么独立访客（UV）和新独立访客（NUV）差值都是 4.5 倍左右，而浏览次数（PV）差值却只有 1.5 倍？ 3\. 为什么两周的 UV 走势（橙线）类似，但 PV 走势（蓝线）却在 7-26 和 7-29 两天出现谷值和峰值？ 贰丨提出猜测 目前我们已知最大的变化就是第 31 周开了付费广告，而第 32 周关闭，关闭时间是 8-1 号当天几点呢？一到凌晨就关了还是下班才关？网站负责人说 “好像是 8-1 号早上”，分析师不相信 “好像”，只能通过数据去验证。 这时候我们能提出的合理猜测是（猜测问题 1=d1，下同）： d1：两周流量数据大面积变差，是因为关闭了广告投放，但具体是哪个小时和栏目通过 CNZZ 展示出的数据无法直观得出结论，需要分析具体数据。 d2：UV 和 NUV 差值相近，可能这周的数据增长多数来自新独立访客，而着陆页对新访客的吸引力不强，所以大多新访客没有产生更多点击行为，这也说明，为什么第 32 周的跳出率升高。 d3：7-26 对应 8-2，7-29 对应 8-5，分别出现的谷值峰值原因在 SEO 日记录表中无记录，暂时无法给出猜测，只能查看具体数据。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684895954089/b3be0490-abf8-485b-8542-6fad707adb6b.jpeg align="left") 【网站日志中只记录了 8-1 关闭付费推广】 提出猜测之前先问问网站负责人，网站近期有没有改版或变动，有没有忘记记录的事项，其他部门有没有做过什么线下活动推广等等，合理的猜测来自于网站日志记录的已知条件和分析师的经验。 叁丨验证猜想 在 Excel 中打开 CNZZ 记录的两周访问明细（因私密原因将主[域名](https://cloud.tencent.com/act/pro/domain-sales?from=20065&from_column=20065)修改为我的微信 ffeels），按如下猜测具体分析： 1\. 具体什么时候停的付费广告？ 在日期中筛选第 32 周数据（8-1~8-7），在 “页面来源” 中筛选被标记的付费来源链接（该站标记的是 “ipinyou”），确定，结果如图。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684895963377/1be49836-193c-4a14-b243-91606937bdea.png align="left") 最后一条带有付费标记来源时间是 2016-8-1 9:56:43，得出负责人是在周一上午上班后 10 点左右关闭的付费广告投放。 2\. 在这次流量变动中，关闭付费广告带来了多大的影响？ 全选 7-25~8-7 访问明细数据，新建透视表。行放 “页面来源” 和 “受访页面”，列放 “周数”，值计数放 “IP”，观察两周整体数据对比，发现自然流量 + 付费流量差值为 7141-2745=4396，再筛选付费链接流量差值，即可得付费广告影响。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684895983767/e2d855c9-28c7-4516-b790-45f4d9944428.png align="left") 分别在 “页面来源” 和 “受访页面” 两个字段处使用标签 “不包含” 筛选掉付费标记 “ipinyou”，如图： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684895988573/cfe5447f-786c-4317-bbac-fab0c6e6f941.png align="left") 现在我们得出结果第 31 周和第 32 周的付费流量差值分别为 7141-3834=3307，2745-2593=152，这就是开关付费广告为网站流量带来的具体影响数。那么网站流量变化全因是付费流量开关引起的吗？通过上图的计算结果，我们知道并不是，排除掉付费流量，我们仍然存在 3834-2593=1241 的自然流量差值，是什么原因引起的呢？ 3\. 自然流量中有多少是自己公司的用户？ 询问网管了解公司的网线有没有被分成多条，公司所有主机目前对应的 IP 地址或 IP 段是多少。因为选择的时间久远，已经无法得知当时该公司的内部 IP 段是多少，故忽略该步骤。 4\. 自然流量的差值是由哪些页面在哪几个时段引起的？ 添加字段 “日期” 和 “小时” 到行中，选择降序排列，值显示方式调整为对比 32 周的差异，选中值选在区域，更改条件格式→项目选区规则→前 10 项，填充粉色，重复该步骤选择最后 10 项，填充黄色，最后效果如图。 （其中粉色代表该页面 31 周比 32 周多出来的数据，如 “直接访问书签” 31 周来源流量比 32 周多 127；黄色代表该页面 32 周比 31 周增加的数据，如 “[http://study.ffeels.com/dujia/gushisudi”32](http://study.ffeels.com/dujia/gushisudi”32) 周来源流量比 31 周多 321） ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896016291/d244b7dc-961e-4408-8711-e3ab543171d9.jpeg align="left") 得出正值相差超过 200 的页面有 “[http://fof.ffeels.com/”、“http://fof.ffeels.com/help”、“http://fof.ffeels.com/capacitymis”，负值超过](http://fof.ffeels.com/”、“http://fof.ffeels.com/help”、“http://fof.ffeels.com/capacitymis”，负值超过) 200 的页面有 “[http://study.ffeels.com/dujia/gushisudi”。](http://study.ffeels.com/dujia/gushisudi”。) 分析的步骤相同，我们以 “[http://fof.ffeels.com/help”](http://fof.ffeels.com/help”) 页面为例，为什么这个页面 32 周比 31 周多 267 次？一次将 “IP”、“访客新老属性”、“受访页面”、“地区” 等字段放进行中，日期处降序排列如图： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896033130/8be83379-f47d-4ef8-9c21-99d11fdd24f7.png align="left") 从 “[http://fof.ffeels.com/”、“http://fof.ffeels.com/help”、“http://fof.ffeels.com/capacitymis”](http://fof.ffeels.com/”、“http://fof.ffeels.com/help”、“http://fof.ffeels.com/capacitymis”) 三个页面可以发现，三个页面的流量都来自 7-29 号早上 6 点。 同时，我们发现了一个可疑的 IP 字段，两天都有 “222.16.42.\*\*\*”，看看这个 IP 段是什么鬼，于是在 “IP” 字段筛选出 “222.16.42.\*\*\*” ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896037124/01aa1dd0-3ced-4b89-b972-1bb275d795fd.jpeg align="left") 有意思的是，这个 IP 段仅在第 31 周的每天的早晨 6，7 点间活跃，如图： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896047907/0a56f0c5-67ce-45d1-b588-135fe85ff86e.png align="left") 因此得出结论，31 周比 32 周多出来的自然流量差是由 “[http://fof.ffeels.com/”、“http://fof.ffeels.com/help”、“http://fof.ffeels.com/capacitymis”](http://fof.ffeels.com/”、“http://fof.ffeels.com/help”、“http://fof.ffeels.com/capacitymis”) 三个页面在 2016 年 7 月 29 日早晨 6-7 点间引起，来访用户均为新访客，且这些页面均不为内容页，访问时间也不规律，目前条件缺失，只能推断出人为＞机器，放入事件库，再观察。 5\. 独立访客（UV）和新独立访客（NUV）差值都是 4.5 倍左右，而浏览次数（PV）差值却只有 1.5 倍，是否因为付费广告着陆页不符合用户体验，如果是，那么新老访客分别输出了多少 PV？ 按 “新老访客属性” 字段统计得出，31 周和 32 周新老用户分别贡献流量 7141 和 2745，约等于浏览次数（PV）值。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896079233/bc98c9f4-e361-475f-8d2e-439d4880321e.jpeg align="left") 然后我们分别筛选新老用户的流量值，老用户流量值 2915 和 1895，如图： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896110619/69a3baec-90f2-4538-876a-a3a8e6b078f5.png align="left") 新用户流量值 4226 和 850，如图： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896115571/c2bd6838-50c2-407f-977a-a46c20ea9414.png align="left") 最终我们发现，整站流量周变化 7146/2745=2.60；老用户流量周变化 2915/1895=1.54；新用户流量周变化 4226/850=5.00。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896136551/cab110dd-623c-4667-83fe-5a383ca55460.jpeg align="left") 新用户 PV 数变化≈两周新独立访客变化数，所以我们可以得出结论，第 31 周数据增长多数来自新独立访客，推测是着陆页对新访客的吸引力不强，或是投放目标人群不精准。（还可以通过受访页面数据的付费链接跳出率分析得出是哪个页面最差，对应改进，不细讲，留给读者思考） 6\. 流量趋势中 7-26 对应 8-2 出现了流量谷值，是否是单一页面引起的？ 对比 7-26 和 8-2 的流量，我们发现，是因为 8-2 当天整站的流量全部降低，并非单一页面引起。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896144122/e29b970d-5e37-4b16-a370-2621a1e2a483.jpeg align="left") 那为什么 8-2 当天会出现整张流量下降的情况呢？当我带着这个诡异的现象再次询问网站负责人时，他想了一会儿说：“哎呀，不好意思，我忘记告诉你了，8-2 号台风 “妮妲” 来了，公司放假一天。” 哈哈，抓到一个忘记记网站日志的。我们来通过新老用户流量变化核实一下。 新用户流量变化如图，平滑过渡： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896147855/e1d56df0-569a-4900-afa6-96d55dafd218.png align="left") 老用户流量变化如图：8-2 号当天流量断崖下跌，确实是老用户引起的整站流量降低。企业员工的访问量占了自然流量的一大部分啊。 ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896152539/6fc46dd2-856b-4883-8f01-a7f5255f1aaf.png align="left") 综上所述，提出的猜测我们都已经验证。 在整个过程中，大家应该发现了，所有的分析逻辑都是从大到小，从最开始的整体流量趋势，找到对应是哪个周，哪一天，哪个小时，哪个栏目，哪个页面出了问题。通过已知的记录提出合理的猜测，然后通过数据验证猜测。过程中并没有高深的技巧，只要有一颗问到底的心。 在示例中，很多人想当然的认为 32 周相比 31 周流量大幅度降低是因为关闭了付费广告，不再继续分析，那就会疏忽一个很大的问题 —— 整体流量下降，不代表所有栏目的流量都下降，比如图示： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896156602/de03aba2-600c-4fb2-a7cb-137f89c81d1b.jpeg align="left") 为什么 32 周这个 “[http://study.ffeels.com/dujia/gushisudi”](http://study.ffeels.com/dujia/gushisudi”) 页面反而在整体流量下降的情况下大幅度增长？不写过程了，直接给结论，是因为 8-4 号当天 9:28 开始，技术对这个页面设置了内容采集，自动从其他站抓取内容，每隔一分钟发布一次，证据如图： ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1684896159698/33bde7dc-5bfd-4111-82b3-0325682c0aaa.png align="left") 肆丨事件归类 通过 Excel 进行数据分析，我们可以发现很多问题，甚至是有一次同事使用流量宝刷流量，被我戳穿了... 这次的分享只列出了一些常见的分析方法和逻辑，旨在让大家感受一下 Excel 在数据分析中起到的作用。 对于分析师来说，什么叫 “事件归类”？说得通俗些就是积攒的 “经验”。比如每到节假日，网站流量会怎样变化，公司什么宣传对流量提升影响最大，一旦停止广告投放网站的真实流量来自哪里等等，把这些经验归类记录在笔记中，久而久之就从初学者成长为高级分析师。但是话说回来，总会有你通过 Excel 猜不到，分析不出的问题，比如模拟人行为的爬虫，设置不同 UA，不定时抓取等等。遇到暂时不能解决的问题，存在 “难题库”，总有个契机会让你灵光乍现，解决它们的。 最后要说的就是，Excel 作为最大众的数据分析工具，门槛低、功能强，性价比超高。只要你保持旺盛的求知欲，再加上一点点软件操作技巧，人人都能是数据分析师。