手把手教你用EXCEL分析流量波动原因（实例讲解）

通过数据分析，我们可以解决一系列问题。最近网站流量突然降低了，但我什么操作都没做，为什么会这样？……诸如此类。 前言   随着运营精细化发展，如何通过品牌沉淀的数据挖掘出更多优化可能，是每个运营、产品甚至技术的必修课。这篇文章将主要阐述我是如何通过发现问题、提出猜测、验证猜想和事件归类这四个方面，分析官网流量数据并找出问题的。   （文中出现的工具有CNZZ后台和Excel2013）       这次以某个朋友的网站作为演示数据，选取了2016年7月25日到2016年8月7日，分别是2016年第31周和32周两周的数据，选择这两周数据是因为第32周该网站刚刚关闭了付费广告的投放，所以网站流量出现了巨大差异，比较典型，CNZZ后台流量如图示：  

          在图右下选择“更多指标”，选取当前核心指标，举例取PV、UV、平均访问时长、跳出率；图中部“时”改为“天”。  

      然后我们通过观察图上部分的对比数据，提出疑问：   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日记录表中无记录，暂时无法给出猜测，只能查看具体数据。  

网站日志中只记录了8-1关闭付费推广

  提出猜测之前先问问网站负责人，网站近期有没有改版或变动，有没有忘记记录的事项，其他部门有没有做过什么线下活动推广等等，合理的猜测来自于网站日志记录的已知条件和分析师的经验。       在Excel中打开CNZZ记录的两周访问明细（因私密原因将主域名修改为我的微信ffeels），按如下猜测具体分析：   1、具体什么时候停的付费广告？   在日期中筛选第32周数据（8-1~8-7），在“页面来源”中筛选被标记的付费来源链接（该站标记的是“ipinyou”），确定，结果如图。  

      最后一条带有付费标记来源时间是2016-8-1 9:56:43，得出负责人是在周一上午上班后10点左右关闭的付费广告投放。   2、在这次流量变动中，关闭付费广告带来了多大的影响？   全选7-25~8-7访问明细数据，新建透视表。   行放“页面来源”和“受访页面”，列放“周数”，值计数放“IP”，观察两周整体数据对比，发现自然流量+付费流量差值为7141-2745=4396，再筛选付费链接流量差值，即可得付费广告影响。  

    分别在“页面来源”和“受访页面”两个字段处使用标签“不包含”筛选掉付费标记“ipinyou”，如图：  

        现在我们得出结果第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周来源流量比31周多321）  

      得出正值相差超过200的页面有“http://fof.ffeels.com/”、“http://fof.ffeels.com/help”、“http://fof.ffeels.com/capacitymis”，负值超过200的页面有“http://study.ffeels.com/dujia/gushisudi”。   分析的步骤相同，我们以“http://fof.ffeels.com/help”页面为例，为什么这个页面31周比32周多267次？一次将“IP”、“访客新老属性”、“受访页面”、“地区”等字段放进行中，日期处降序排列如图：  

      从“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.***”  

  有意思的是，这个IP段仅在第31周的每天的早晨6，7点间活跃，如图：  

    因此得出结论，31周比32周多出来的自然流量差是由“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）值。  

点击可查看大图   然后我们分别筛选新老用户的流量值，老用户流量值2915和1895，如图：  

      新用户流量值4226和850，如图：  

      最终我们发现，整站流量周变化7146/2745=2.60；老用户流量周变化2915/1895=1.54；新用户流量周变化4226/850=5.00。  

          新用户PV数变化≈两周新独立访客变化数，所以我们可以得出结论，第31周数据增长多数来自新独立访客，推测是着陆页对新访客的吸引力不强，或是投放目标人群不精准。（还可以通过受访页面数据的付费链接跳出率分析得出是哪个页面最差，对应改进，不细讲，留给读者思考）   6、流量趋势中7-26对应8-2出现了流量谷值，是否是单一页面引起的？   对比7-26和8-2的流量，我们发现，是因为8-2当天整站的流量全部降低，并非单一页面引起。  

      那为什么8-2当天会出现整张流量下降的情况呢？当我带着这个诡异的现象再次询问网站负责人时，他想了一会儿说：“哎呀，不好意思，我忘记告诉你了，8-2号台风“妮妲”来了，公司放假一天。”哈哈，抓到一个忘记记网站日志的。我们来通过新老用户流量变化核实一下。   新用户流量变化如图，平滑过渡：  

    老用户流量变化如图：8-2号当天流量断崖下跌，确实是老用户引起的整站流量降低。企业员工的访问量占了自然流量的一大部分啊。  

      综上所述，提出的猜测我们都已经验证。   在整个过程中，大家应该发现了，所有的分析逻辑都是从大到小，从最开始的整体流量趋势，找到对应是哪个周，哪一天，哪个小时，哪个栏目，哪个页面出了问题。通过已知的记录提出合理的猜测，然后通过数据验证猜测。过程中并没有高深的技巧，只要有一颗问到底的心。   在示例中，很多人想当然的认为32周相比31周流量大幅度降低是因为关闭了付费广告，不再继续分析，那就会疏忽一个很大的问题——整体流量下降，不代表所有栏目的流量都下降，比如图示：  

      为什么32周这个“http://study.ffeels.com/dujia/gushisudi”页面反而在整体流量下降的情况下大幅度增长？不写过程了，直接给结论，是因为8-4号当天9:28开始，技术对这个页面设置了内容采集，自动从其他站抓取内容，每隔一分钟发布一次，证据如图：   

        通过Excel进行数据分析，我们可以发现很多问题，甚至是有一次同事使用流量宝刷流量，被我戳穿了……这次的分享只列出了一些常见的分析方法和逻辑，旨在让大家感受一下Excel在数据分析中起到的作用。   对于分析师来说，什么叫“事件归类”？说得通俗些就是积攒的“经验”。比如每到节假日，网站流量会怎样变化，公司什么宣传对流量提升影响最大，一旦停止广告投放网站的真实流量来自哪里等等，把这些经验归类记录在笔记中，久而久之就从初学者成长为高级分析师。但是话说回来，总会有你通过Excel猜不到，分析不出的问题，比如模拟人行为的爬虫，设置不同UA，不定时抓取等等。遇到暂时不能解决的问题，存在“难题库”，总有个契机会让你灵光乍现，解决它们的。   最后要说的就是，Excel作为最大众的数据分析工具，门槛低、功能强，性价比超高。只要你保持旺盛的求知欲，再加上一点点软件操作技巧，人人都能是数据分析师。   艾奇菌曰   这篇文章是一个案例，更是一种方法，几乎是手把手地告诉大家，如何通过数据定位出问题。   艾奇菌在后台经常收到小伙伴们的问题：   最近点击突然变多了，但是转化没有提升，是不是有恶意点击？   最近网站流量突然降低了，但我什么操作都没做，为什么会这样？……诸如此类。   然后得出结论大致是：“一定是恶意点击！”或者“淡季来了”或者“X度系统又抽风了……”   得出这些结论之前，大家有没有抽丝剥茧地，从客观数据里推敲出你们想要的答案呢？   艾奇菌建议大家按照作者的思路，拿自己账户的数据，进行一番分析与推敲，相信会有很大的收获。     作者：涂俊杰（微信：ffeels） 来源：投稿