很可惜 T 。T 您现在还不是作者身份，不能自主发稿哦～
如有投稿需求，请把文章发送到邮箱tougao@appcpx.com，一经录用会有专人和您联系
咨询如何成为春羽作者请联系：鸟哥笔记小羽毛（ngbjxym）
作者｜吃饭第一名的 Claire
用户行为分析是互联网公司的核心分析指标。这个过程需要数据分析师通过海量交易数据，分析（新/老）用户在整个产品使用环节（随时间）的行为路径，从而发现异常值，探索出现异常的原因，进而减少用户流失、提升用户的产品参与度、最后帮助产品实现持续性营收。
注：这里的互联网产品可以是手机app、网站平台等各种SaaS（Software as a Service）产品，以下皆用“app”来代表以简化语言。
本文我们以电商平台的一组数据来深度剖析一下如何进行用户分析，有哪些指标要关注，分别说明了什么问题，这些数据结果又如何指导我们决策。本期文章分为 2 个模块：
注：为了更有效的凸显数据带给我们的洞察，不同模块采取了不同时段不同来源的数据集，所以数据前后不一致是正常的。
本文的目的是为了帮助大家搭建数据分析的思维，学习用 SQL 帮助实现分析，并通过可视化进行呈现和汇报。
注：全文符合 PostgreSQL 语法格式
非常简单，5 个字段，每一行表示某 user 在某 date 某 hour 对某 item 做了某 behavior，其中 behavior 有 pv （浏览/点击）、fav（收藏）、cart（加入购物车）和 buy（下单支付）4 种。
通常会涉及时间段，总天数，总记录数，总访问量，总用户数，购买用户数，平均日浏览量，平均日用户量，平均跳出率。
以下是 SQL 代码：
说明数据是从 2020 年 11 月 25 日至 2020 年 12 月 2 日。
我们要面对的是 8 天将近 90M 的数据。
总访问量，总用户数及比值
PV 是 page view，app 被浏览/刷新/点击都算一次。
UV 是 unique visitor，每个访问 app 的终端算一个独立访客。
PV/UV=81 表示在这 8 天的数据中，平均每个用户访问页面 81 次。
购买用户数（643,832人）
平均日浏览量（10M）要先按 date group by 算出 pv，再求平均值。
平均日用户量（760K 人）同理
Bounce rate 跳出率(只有一次点击行为的用户/总用户数)
假设只有一个页面可以浏览，用户点进页面后要么收藏加购付款，要么跳出。
得到 8% 的跳失率，说明 8% 的用户点进来，没有往下转化就流失掉了，公司应对此查看商品详情页是不是还需改进，网页加载有没有延迟等问题。
可以是行为数漏斗，也可以是独立访客数漏斗，我们这里用后者进行分析。
先按用户，统计每个人的行为：
再计算每个行为的用户数，最后计算转化率，这里我们的转化率是以 view/click -> favorite/add to cart -> purchase 路径为基准进行的计算，并且将收藏和加入购物车的行为进行了合并（考虑到这两个阶段不分先后顺序，而且都是确定购买意向的行为）。
这里表示有点击行为的用户中，86% 的人都点了加购或者收藏，有上述行为的人又购买的人占所有点击人数的 58%。这样的转化率可是非常不错！
每日新增访问用户数：
每日新增购买用户数：
每日新增用户整体来看都是逐渐减少，12 月 1 日新增购买用户有一个回弹，在 11 月27 日以后，每日新增购买用户数超过每日新增浏览用户数，说明一部分之前浏览的用户在几天后开始下单，可以联系业务部门查看公司有做什么活动推进了用户的支付。
是什么：用户留存率是一批用户在一定时间间隔里，某些行为重复发生的比例。
用户可以分为新活跃用户、老活跃用户；
时间通常有次日、7 日、30 日留存，不同 app 由于产品属性和用户使用频率的不同而采用的时间间隔也不同，内容社交类 app 更看重次日留存，电商类可以是按周或者按月，也取决于电商品类，出行旅游类 app 则由于用户使用频率低而看重月留存率；
注：行业基准（Benchmark）电商产品的平均用户留存率是 30%，超过 35% 是良好的产品表现。
行为针对不同 app 可以是完成一次购买，观看一次视频，查看一次地图等。
为什么：用户留存分析是衡量用户粘性（持续使用 APP）的重要指标，不能只看 DAU/MAU（只能表面的展示产品使用情况），拉新用户会掩盖流失用户的问题。一个典型的比喻是把 app 看成水桶，拉新是蓄水，用户流失是流水，留存率低就意味着水不断流走，这样的水桶很难保证恒定的水量，这样的 app 也不会有长久稳定的发展。更重要的是拉新的成本是留住老用户的 5-7 倍。把握好用户留存能让你事半功倍。
怎么做：提高用户留存的方法有很多维度，比如通过邮件、app 消息推送、投放广告等提醒用户采取行动；缩短用户行为路径，减少用户使用 app 成本；给用户提供多样的奖励；培养用户习惯，强化用户对产品的依赖等。
我们在这里着重介绍如何进行用户留存的分析：同期群分析（Cohort analysis）是用户留存分析的重要分析方法。
同期群（cohort）是一组在特定时间做同样事的人，比如都在 1 月 1 日注册账户的用户是一个同期群，都在 1 月 2 日注册账户的用户是另一个同期群。
同期群分析是一个测量用户对 app 随时间参与度的工具，他最常见的分析方式是画类似如下图的同期群用户留存表。
图源https://clevertap.com/blog/cohort-analysis/，版权归作者所有
我先解释上图表格每个维度代表的含义，再展示如何用我们已有的数据生成这个表格。
表格的每一行代表一个群组，群组 Jan 25 表示 1098 个用户都在 1 月 25 日开始使用这个 app，从左往右来看，1 天后的留存率是 33.9%，即在 1098 个用户中，1098 X 33.9% = 372 个用户继续使用了这个 app；7 天后还有 1098 X 14.5% = 159 个用户用了这个 app，或者说在这 1098 个用户中，平均每 7 个人中有一个在 7 天后还是活跃用户。
注：从左到右来的用户留存率一般是逐渐降低的，但并不绝对，同一行右边的留存率可能比左边大，这里的留存率不要求一个人每天连续使用。
纵向来看可以比较不同期用户同一生命周期的留存，用户生命周期的早期的留存率能反应用户对产品的第一印象和接受度。
我们的数据来源于电商行业，这里我们用一周比较不同群组之间的留存率。
要生成这个表格，用 Excel 的 pivot table 很容易做到，但是，我要从数据库用 SQL 取什么样的数据呢？自己思考一下再往下看。
答案：付费用户首次购买产品的日期（周），当周购买的新用户人数，付费用户其他时间（周）购买和首周的间隔，与首周间隔后的购买人数。
下面上代码：
通过 Excel pivot table 我们得到以下表格：
我们发现 12-8 那周和 12-29 那周的一周后留存明显降低，而这两周的新增用户激增，很可能与双 12 和元旦期间做的促销和上新有关。节日过后，一方面用户购买欲望降低，另一方面促销结束，价格回升，导致了留存率降低。
我们还发现 12-1 那周和 12-22 所在周首次下单的用户，一周后的用户留存显著高于其他同期群，和业务部门沟通后，发现是市场部在节日一周前专门做了节日促销、满减、送优惠券的宣传，吸引了用户注册，这部分用户也是为了享受下周的优惠而停留，优惠过后，留存率又回到了平均水平。
通过 cohort table，我们还能应该分析出 11-24 所在周首次下单的用户更有粘性，跟业务方沟通发现那批用户正是产品进行 A/B 测试，进行页面改变的时间，说明页面的改变带来了一定的效益，数据分析师可以和做 A/B 测试组讨论，进行进一步的验证。
通过这组数据我们可以发现大概 77% 的新用户在购买产品一周后就不再购买，第二个显著的下降是在 7 周后。下图说明该 app 还需提升新用户留存率，可以从简化用户购买路径，优化支付按钮，提高产品质量等角度考虑。
注：实际工作中看留存率变化会选定一个基准日，比如今天是 2 月 4 日，看 10 天内的留存率变化就是以 1 月 25 日为起始，而不是选用各 cohort 的平均值。
当然 cohort analysis 也不一定要以时间为周期进行划分，具有某种共同行为特征的用户也可以划分为一个 cohort，这里举一个 Disney+ 的例子：
图源https://secondmeasure.com/datapoints/Disney-takes-customer-retention-crown-from-netflix/ ，版权归Second Measure所属
Disney+ 是美国的一个流媒体平台，这里他把订阅者分成了 4 类：为了看 Mulan 订阅的人、为了看 Hamilton 订阅的人、和其他服务一起订阅的人和普通每月订阅者。这个图告诉我们曾在木兰发行期订阅的人中，82% 的用户在下个月还订阅了 Disney+，曾在 Hamilton 发行期订阅的人中，只有 75% 还订阅了下个月的会员，而平均 1 月后的留存率是 86%。
提升复购率是增加收入的重要方法，下面我们进行 2 个维度的分析。
3.2.3.1 整体复购率 (购买 2 次及以上的用户比率）
得出 63% 的用户都进行了复购，还不错。
3.2.3.2 用户复购次数分布
复购 5 次以上的用户仅占总用户的 10%。
本期我们用 SQL 进行了 SaaS 产品用户数据的初探索、用户漏斗模型、同期群分析、和复购分析的实现。希望下次老板再找你做用户行为分析的时候，你不会没有头绪或者手忙脚乱了！
后续计划：别看我们讨论的数据这么简单，其实能做的分析可太多了，这期由于篇幅原因我们先说到这里，下期接着说用户在时间维度的分析，RFM 模型分析和商品角度的研究。如果你有学到一点什么，别忘了点赞 收藏 加关注哦！
-END-
本文为作者独立观点，不代表鸟哥笔记立场，未经允许不得转载。
《鸟哥笔记版权及免责申明》 如对文章、图片、字体等版权有疑问，请点击 反馈举报
Powered by QINGMOB PTE. LTD. © 2010-2022 上海青墨信息科技有限公司 沪ICP备2021034055号-6
我们致力于提供一个高质量内容的交流平台。为落实国家互联网信息办公室“依法管网、依法办网、依法上网”的要求，为完善跟帖评论自律管理，为了保护用户创造的内容、维护开放、真实、专业的平台氛围，我们团队将依据本公约中的条款对注册用户和发布在本平台的内容进行管理。平台鼓励用户创作、发布优质内容，同时也将采取必要措施管理违法、侵权或有其他不良影响的网络信息。
一、根据《网络信息内容生态治理规定》《中华人民共和国未成年人保护法》等法律法规，对以下违法、不良信息或存在危害的行为进行处理。
1. 违反法律法规的信息，主要表现为：
1)反对宪法所确定的基本原则；
2)危害国家安全，泄露国家秘密，颠覆国家政权，破坏国家统一，损害国家荣誉和利益；
3)侮辱、滥用英烈形象，歪曲、丑化、亵渎、否定英雄烈士事迹和精神，以侮辱、诽谤或者其他方式侵害英雄烈士的姓名、肖像、名誉、荣誉；
4)宣扬恐怖主义、极端主义或者煽动实施恐怖活动、极端主义活动；
5)煽动民族仇恨、民族歧视，破坏民族团结；
6)破坏国家宗教政策，宣扬邪教和封建迷信；
7)散布谣言，扰乱社会秩序，破坏社会稳定；
8)宣扬淫秽、色情、赌博、暴力、凶杀、恐怖或者教唆犯罪；
9)煽动非法集会、结社、游行、示威、聚众扰乱社会秩序；
10)侮辱或者诽谤他人，侵害他人名誉、隐私和其他合法权益；
11)通过网络以文字、图片、音视频等形式，对未成年人实施侮辱、诽谤、威胁或者恶意损害未成年人形象进行网络欺凌的；
12)危害未成年人身心健康的；
13)含有法律、行政法规禁止的其他内容；
2. 不友善：不尊重用户及其所贡献内容的信息或行为。主要表现为：
1)轻蔑：贬低、轻视他人及其劳动成果；
2)诽谤：捏造、散布虚假事实，损害他人名誉；
3)嘲讽：以比喻、夸张、侮辱性的手法对他人或其行为进行揭露或描述，以此来激怒他人；
4)挑衅：以不友好的方式激怒他人，意图使对方对自己的言论作出回应，蓄意制造事端；
5)羞辱：贬低他人的能力、行为、生理或身份特征，让对方难堪；
6)谩骂：以不文明的语言对他人进行负面评价；
7)歧视：煽动人群歧视、地域歧视等，针对他人的民族、种族、宗教、性取向、性别、年龄、地域、生理特征等身份或者归类的攻击；
8)威胁：许诺以不良的后果来迫使他人服从自己的意志；
3. 发布垃圾广告信息：以推广曝光为目的，发布影响用户体验、扰乱本网站秩序的内容，或进行相关行为。主要表现为：
1)多次发布包含售卖产品、提供服务、宣传推广内容的垃圾广告。包括但不限于以下几种形式：
2)单个帐号多次发布包含垃圾广告的内容；
3)多个广告帐号互相配合发布、传播包含垃圾广告的内容；
4)多次发布包含欺骗性外链的内容，如未注明的淘宝客链接、跳转网站等，诱骗用户点击链接
5)发布大量包含推广链接、产品、品牌等内容获取搜索引擎中的不正当曝光；
6)购买或出售帐号之间虚假地互动，发布干扰网站秩序的推广内容及相关交易。
7)发布包含欺骗性的恶意营销内容，如通过伪造经历、冒充他人等方式进行恶意营销；
8)使用特殊符号、图片等方式规避垃圾广告内容审核的广告内容。
4. 色情低俗信息，主要表现为：
1)包含自己或他人性经验的细节描述或露骨的感受描述；
2)涉及色情段子、两性笑话的低俗内容；
3)配图、头图中包含庸俗或挑逗性图片的内容；
4)带有性暗示、性挑逗等易使人产生性联想；
5)展现血腥、惊悚、残忍等致人身心不适；
6)炒作绯闻、丑闻、劣迹等；
7)宣扬低俗、庸俗、媚俗内容。
5. 不实信息，主要表现为：
1)可能存在事实性错误或者造谣等内容；
2)存在事实夸大、伪造虚假经历等误导他人的内容；
3)伪造身份、冒充他人，通过头像、用户名等个人信息暗示自己具有特定身份，或与特定机构或个人存在关联。
6. 传播封建迷信，主要表现为：
1)找人算命、测字、占卜、解梦、化解厄运、使用迷信方式治病；
2)求推荐算命看相大师；
3)针对具体风水等问题进行求助或咨询；
4)问自己或他人的八字、六爻、星盘、手相、面相、五行缺失，包括通过占卜方法问婚姻、前程、运势，东西宠物丢了能不能找回、取名改名等；
7. 文章标题党，主要表现为：
1)以各种夸张、猎奇、不合常理的表现手法等行为来诱导用户；
2)内容与标题之间存在严重不实或者原意扭曲；
3)使用夸张标题，内容与标题严重不符的。
8.「饭圈」乱象行为，主要表现为：
1)诱导未成年人应援集资、高额消费、投票打榜
2)粉丝互撕谩骂、拉踩引战、造谣攻击、人肉搜索、侵犯隐私
3)鼓动「饭圈」粉丝攀比炫富、奢靡享乐等行为
4)以号召粉丝、雇用网络水军、「养号」形式刷量控评等行为
5)通过「蹭热点」、制造话题等形式干扰舆论，影响传播秩序
9. 其他危害行为或内容，主要表现为：
1)可能引发未成年人模仿不安全行为和违反社会公德行为、诱导未成年人不良嗜好影响未成年人身心健康的；
2)不当评述自然灾害、重大事故等灾难的；
3)美化、粉饰侵略战争行为的；
4)法律、行政法规禁止，或可能对网络生态造成不良影响的其他内容。
二、违规处罚
本网站通过主动发现和接受用户举报两种方式收集违规行为信息。所有有意的降低内容质量、伤害平台氛围及欺凌未成年人或危害未成年人身心健康的行为都是不能容忍的。
当一个用户发布违规内容时，本网站将依据相关用户违规情节严重程度，对帐号进行禁言 1 天、7 天、15 天直至永久禁言或封停账号的处罚。当涉及欺凌未成年人、危害未成年人身心健康、通过作弊手段注册、使用帐号，或者滥用多个帐号发布违规内容时，本网站将加重处罚。
三、申诉
随着平台管理经验的不断丰富，本网站出于维护本网站氛围和秩序的目的，将不断完善本公约。
如果本网站用户对本网站基于本公约规定做出的处理有异议，可以通过「建议反馈」功能向本网站进行反馈。
(规则的最终解释权归属本网站所有)