用Excel、SQL、Python做数据分析有何不同？

作者简介
HeoiJin：立志透过数据看清世界的产品策划，专注爬虫、数据分析、产品策划领域。

万物皆营销 | 资本永不眠 | 数据恒真理
CSDN：https://me.csdn.net/weixin_40679090

一、前言

后互联网时代，获客拉新的成本越来越高，如何增加客户的留存，提高客户的复购次数、购买金额等变得十分重要，同期群分析便是当中非常重要的分析方法。

关于同期群分析概念和思路的文章很多，但分享如何实现的文章非常罕见。因此，本文将简单介绍同期群分析的概念，并用数据分析师的三板斧ESP（Excel、MySQL、Python）分别实现同期群分析。

二、项目准备

• Excel：

• • office或wps均可，office 2013后的版本更好

• MySQL：

• • 版本：8.0（本次不涉及窗口函数，其他版本亦可）

  • Navicat

• Python：

• • 版本：3.7

  • IDE：pycharm

  • 库：pandas、xlrt

PS.

• 因篇幅原因，可能会有未能详细讲解的过程

• 完整源码及数据集请移步至文末获取

三、同期群分析概念讲解

数据分析最终目标都是为了解决业务问题，任何分析方法都只是工具。因此在详细讲解如何实现之前，需要先明晰方法的含义是什么，能带来什么收益，才能在合适的问题上选对分析方法。

3.1 同期群分析含义

同期群(Cohort)即相同时间内具有相似或特定属性 、行为的群体。核心要素为时间+特定属性，比如把00后出生的人划分为一个群组。

同期群分析指将用户进行同期群划分后，对比不同同期群用户的相同指标。我们耳熟能详的留存率就是同期群分析的其中一种，案例如下图：

同期群分析包含了3个重要元素：

1. 客户首次行为时间，这是我们划分同期群的依据

2. 时间维度，即上图中+N月或者N日留存率中的N日

3. 指标，注册转化率、付款转化率、留存率等等

3.2 意义

同期群分析给到更加细致的衡量指标，帮助我们实时监控真实的用户行为、衡量用户价值，并为营销方案的优化和改进提供支撑：

• 横向比较：观察同一同期群在不同生命周期下的行为变化，推测相似群体的行为随时间的变化

• 纵向比较：观察不同的同期群在同一个生命周期下的行为变化，验证业务行为是否取得预期效果

四、材料梳理

4.1 数据情况梳理

拿到数据的第一步，自然是了解数据的情况。针对本次同期群分析，我们可能需要用到的字段有：

• 客户昵称

• 付款时间：时间戳形式

• 订单状态：交易失败/交易成功

• 支付金额

• 购买数量

通过进一步计算，发现付款时间中缺失值所在行的订单状态均为“交易失败”，那么下文分析都需要将订单状态为“交易失败”的行全部剔除。

4.2 分析方法确定

针对此份数据，有3个分析方向可以选择：

1. 留存率或付款率

2. 人均付款金额

3. 人均购买次数

我们选择其中最经典，也是数分面试中最常考的留存率作为例子，需要用到的字段有：

• 客户昵称

• 付款时间

• 订单状态

相信各位对留存率都十分熟悉，不过多介绍。在本次的分析中，留存率的具体计算方式为：+N月留存率=（+N月付款用户数/首月付款用户数）*100%

注意：公式中的+N月存在歧义，会有两种计算方法：

1. 以自然月作为月份偏移的依据：即所有首次行为在9月的用户，只要10月有付款行为，都计算进+1月留存

2. 以每30天作为月份偏移的依据：即9月30日首次付款的用户，在10月30日-11月29日之间有付款行为，才计算进+1月留存

具体的差距会在Excel（用算法1）和MySQL（用算法2）两种工具实现的结果中分别展示。没有相关技术背景的看官老爷可直接对比最终的留存率结果。

五、Excel实现

Excel的实现方式是三个当中门槛最低的，只需要掌握数据透视表和一些基础函数，但过程相对繁杂。实现思路如下：

实现思路一共分为4大部分：数据清洗 -> 计算首单时间 -> 计算首单时间与付款时间差 -> 利用透视表计算同期群留存量和留存率。其中由于部分版本的office和wps的数据透视表不支持非重复计数，因此需要先计算各月中各用户出现的次数。

数据清洗部分只需要筛选+删除便可完成，相信如此简单的操作难不倒各位看官老爷们，那么我们便从第二部分开始详细讲解。

5.1 计算每个客户首单时间

首先通过数据透视表求每一个用户首次付款时间。数据透视表，说白了就是通过特定的条件进行分组，并对数据进行求和、求均值、求方差等聚合操作。在制作数据透视表时要注意以下几点：

1. 数据区域的第一行为标题栏（字段名称）

2. 标题栏不能出现空单元格，亦不要出现重复的标题名

3. 数据中避免有合并单元格

4. 不能出现非法日期

5.1.1 创建透视表

全选数据 -> 插入 -> 数据透视表 -> 确定

5.1.2 选择分组字段和值字段

将“客户昵称”拖进“行”，将付款时间拖进“值”，并将值字段设置中的汇总方式设置为最小值