同期群分析
目录
一、同期群分析概念和理论
1 同期群分析方法介绍
同期群分析（CohortAnalysis）实际上是一种用户分群的细分类型，是一种“纵横”结合的分析方法：
- 横向上——分析同期群随着周期推移而发生的变化；
- 纵向上——分析在生命周期相同阶段的群组之间的差异。
“同期群”：同一时期的群体。可以是“同一天注册的用户”、同一天第一次发生付费行为的用户等等。“周期的指标变化”：用户在一定周期内的留存率、付费率等等。同期群分析包含三个核心的元素：
- 客户首次行为时间：这是划分同期群体的基点；
- 时间周期维度：比如N日留存率、N日转化率中的N日，一般即为+N日、+N月等
- 变化的指标：比如注册转化率、付款转化率、留存率等等。
2 同期群分析的意义
为啥要做同期群分析，不分群不行吗？同期群分析，给到更加细致的衡量指标，帮助我们实时监控真实的用户行为、衡量用户价值，并为营销方案的优化和改进提供支撑，避免出现“被平均”的虚荣数据。
二、SQL实现同期群分析
Excel数据源部分数据截图：
1 python读取Excel数据导入MySQL
import pymysql
import xlrd
from datetime import datetime
# 1、读取本地Excel数据集
book = xlrd.open_workbook('同期群.xlsx')
sheet = book.sheet_by_name('1-数据源')
print("数据行数：",sheet.nrows,'---',"数据列数：",sheet.ncols)
# 2、连接数据库，创建游标、创建插入语句
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='同期群')
cursor = db.cursor()
sql = f"INSERT INTO tongqiqun (nick_name,pay_time,order_status,pay_amount,purchase_quantity,province) VALUES (%s,%s,%s,%s,%s,%s)"
# 3、插入函数
def insert_info():
# 循环每行数据，跳过标题行，从第二行开始
for r in range(1, 5):
nick_name = sheet.cell(r,0).value # 用户昵称
# 支付时间处理
# pay_time = sheet.cell(r,1).value ->直接读取会报错，Excel的日期数据读出来是double
# print(sheet.cell(1,1).value) ->43709.00699074074
try:
pay_time = xlrd.xldate_as_tuple(sheet.cell(r,1).value,0) # 转换成元组
pay_time = datetime(*pay_time) # *args 任意位置参数传参
except:
pay_time = None
order_status = sheet.cell(r,2).value # 订单状态
pay_amount = sheet.cell(r,3).value # 支付金额
# print(sheet.cell(1,4).value) ->1.0
purchase_quantity = int(sheet.cell(r,4).value) # 购买数量
province = sheet.cell(r,5).value # 省份
# 组装元组格式数据，执行SQL插入脚本
data = (nick_name,pay_time,order_status,pay_amount,purchase_quantity,province)
# print(data)
cursor.execute(sql,data)
insert_info()
用了python的xlrd操作Excel文件，pymysql库连接MySQL数据库。用xlrd读取Excel数据时，会出现一些格式上的问题。比如，在Excel中的日期数据是以数值型存储的，所以需要做一下处理才能导入MySQL数据库。不太建议用python来读取Excel数据进行入库操作，可以用Navicat可视化工具导入更方便。导入datatime类型数据时，先以varchar类型导入，导入完成后，执行SQL语句：
ALTER TABLE tongqiqun CHANGE pay_time create_date DATETIME;
即可。
2 数据清洗
订单状态为“交易失败”的行，付款时间是缺失的。
-- 筛选订单状态为：‘交易成功’的行，接下来分析只用到这两个字段：nick_name、pay_time
CREATE TABLE order_sheet1 AS
SELECT
nick_name,
pay_time
FROM tongqiqun
WHERE order_status = '交易成功';
3 计算留存量
（1）对用户进行分组，用min()函数计算日期最小值
-- 1、每个用户首单日期
SELECT
nick_name,
min(pay_time) as fir_time
FROM order_sheet1
GROUP BY nick_name;
（2）中间表：计算客户每次下单时间与首次下单时间的差值
以用户名为连接条件，让每次下单的时间都与首次下单时间建立连接关系。假若数据量比较大，拼接需要遍历整个表很多遍，对于这种中间的过程查询，并不是最后的结果呈现。在保证查询准确度的情况下，可以用分页查询limit语句来限制查询的结果行数，从而提升运行效率。
-- 2、计算每单时间差、重采样首次订单时间
SELECT
a.nick_name,
b.fir_time,
TIMESTAMPDIFF(month,b.fir_time,a.pay_time) AS m_diff,
CONCAT(year(b.fir_time),'年',month(b.fir_time),'月') AS y_m
FROM order_sheet1 a
LEFT JOIN (
SELECT
nick_name,
min(pay_time) as fir_time
FROM order_sheet1
GROUP BY nick_name
-- 测试计算是否成功，提升运行效率
-- LIMIT 20
) b ON a.nick_name=b.nick_name
WHERE b.fir_time IS NOT NULL;
（3）计算留存量
对首付月份、月份差分组，去重统计nick_name数量即可。
CREATE TABLE order_sheet2 AS
SELECT
t.y_m AS 首付月份,
t.m_diff AS 月份差,
COUNT(DISTINCT nick_name) AS 留存量
FROM
(SELECT
a.nick_name,
b.fir_time,
TIMESTAMPDIFF(month,b.fir_time,a.pay_time) AS m_diff,
CONCAT(year(b.fir_time),'年',month(b.fir_time),'月') AS y_m
FROM order_sheet1 a
LEFT JOIN (
SELECT
nick_name,
min(pay_time) as fir_time
FROM order_sheet1
GROUP BY nick_name
) b ON a.nick_name=b.nick_name
WHERE b.fir_time IS NOT NULL) t
GROUP BY t.y_m,t.m_diff;
4 计算留存率
-- ① 提取首付月份、留存量
SELECT
首付月份,
留存量
FROM order_sheet2
WHERE 月份差=0;
-- ② 左连接，计算同期首付月份，各差值月份的留存率
SELECT
a.`首付月份`,
b.`留存量`,
a.`月份差`,
round( (a.`留存量`/b.`留存量`)*100,2) AS 留存率
FROM order_sheet2 a
LEFT JOIN (
SELECT
首付月份,
留存量
FROM order_sheet2
WHERE 月份差=0
) b ON a.`首付月份`=b.`首付月份`
-- ③ case when表格转置，即月份差作为列名
SELECT
c.首付月份,
c.留存量,
CASE c.月份差 WHEN 1 THEN c.留存率 ELSE 0 END AS '+1月',
CASE c.月份差 WHEN 2 THEN c.留存率 ELSE 0 END AS '+2月',
CASE c.月份差 WHEN 3 THEN c.留存率 ELSE 0 END AS '+3月',
CASE c.月份差 WHEN 4 THEN c.留存率 ELSE 0 END AS '+4月',
CASE c.月份差 WHEN 5 THEN c.留存率 ELSE 0 END AS '+5月'
FROM
(SELECT
a.`首付月份`,
b.`留存量`,
a.`月份差`,
round( (a.`留存量`/b.`留存量`)*100,2) AS 留存率
FROM order_sheet2 a
LEFT JOIN (
SELECT
首付月份,
留存量
FROM order_sheet2
WHERE 月份差=0
) b ON a.`首付月份`=b.`首付月份`) c
-- ④分组，最终计算留存率
SELECT
d.首付月份,
AVG(d.留存量) AS '本月新增',
CONCAT(SUM(d.`+1月`),'%') AS `+1月`,
CONCAT(SUM(d.`+2月`),'%') AS `+2月`,
CONCAT(SUM(d.`+3月`),'%') AS `+3月`,
CONCAT(SUM(d.`+4月`),'%') AS `+4月`,
CONCAT(SUM(d.`+5月`),'%') AS `+5月`
FROM(
SELECT
c.首付月份,
c.留存量,
CASE c.月份差 WHEN 1 THEN c.留存率 ELSE 0 END AS '+1月',
CASE c.月份差 WHEN 2 THEN c.留存率 ELSE 0 END AS '+2月',
CASE c.月份差 WHEN 3 THEN c.留存率 ELSE 0 END AS '+3月',
CASE c.月份差 WHEN 4 THEN c.留存率 ELSE 0 END AS '+4月',
CASE c.月份差 WHEN 5 THEN c.留存率 ELSE 0 END AS '+5月'
FROM(
SELECT
a.`首付月份`,
b.`留存量`,
a.`月份差`,
round( (a.`留存量`/b.`留存量`)*100,2) AS 留存率
FROM order_sheet2 a
LEFT JOIN (
SELECT
首付月份,
留存量
FROM order_sheet2
WHERE 月份差=0
) b ON a.`首付月份`=b.`首付月份`) c
) d
GROUP BY d.首付月份;
思路总结：通过左外连接计算时间差值（与首次支付的时间差值、与首次登录的时间差值）中间表的产生，计算留存量，再计算留存率。
5 同期群简单分析
有了同期群，就可以从横向和纵向比较。从横向上，可以看到同一个用户群在之后N月的留存率变化；而在纵向上，可以看到不同群组在N月后的留存率，可以比较各个群组用户的粘性。经过分析，发现9月份新增用户很少，但留存率比其他月份高4-6%左右；而10月份应该是做了促销活动，用户新增2.5倍，但次月留存率低了7%，往后月份的留存率低了3%，说明用户质量不行，促销只是为了短时间冲量，并没有考虑后期的留存。后续三个月，新增用户基本稳定在5000左右，10月份促销活动虽然打开增量，但是应该注意同期留存率却是在持续下降的。2月份的促销方案应该根据前面的经验做一些优化调整。
三、python实现同期群分析
import pandas as pd
import pymysql
import numpy as np
# 从数据库读表
sql = 'select nick_name,pay_time,order_status,pay_amount,purchase_quantity,province from tongqiqun'
con = pymysql.connect(host='localhost',user='root',passwd='123456',database='同期群',port=3306,charset='utf8')
df = pd.read_sql(sql,con)
df.head()
# 清洗数据
order = df[df['order_status']=='交易成功'].copy()
order.loc[:, 'pay_time'] = order['pay_time'].astype(str).str[:7]
order.head()
# 计算留存量
months = ['2019-09','2019-10','2019-11','2019-12','2020-01','2020-02']
months_1 = months[1:]
customer_num = pd.DataFrame(data=None,columns=['新增用户数','2019-10','2019-11','2019-12','2020-01','2020-02'],index=months)
m = 0
for i in months:
m = m + 1
if m == 1:
cur_month = order.loc[order['pay_time']==i]
cur_month_num = cur_month['nick_name'].nunique()
customer_num.loc[i,'新增用户数'] = cur_month_num
else:
j = months[months.index(i) - 1]
prv_month = order.loc[order['pay_time']<=j]
now_month = order.loc[order['pay_time']==i]
cur_month = now_month.loc[now_month['nick_name'].isin(prv_month['nick_name'])==False]
cur_month_num = cur_month['nick_name'].nunique()
customer_num.loc[i,'新增用户数'] = cur_month_num
if m == 6:
continue
for k in months_1:
next_month = order.loc[order['pay_time'] == k]
next_remained = next_month.loc[next_month['nick_name'].isin(cur_month['nick_name']) == True]
next_remained_num = next_remained['nick_name'].nunique()
customer_num.loc[i, k] = next_remained_num
months_1.pop(0)
customer_num
# 计算留存率
k=0
for i in range(6):
for j in range(1,6):
if (j+k)<=5:
customer_num.iloc[i,j]=str(round((customer_num.iloc[i,(j+k)])/(customer_num.iloc[i,0]) * 100,2)) + '%'
else:
customer_num.iloc[i,j]=np.nan
k+=1
customer_num.columns = ['本月新增','+1月','+2月','+3月','+4月','+5月']
customer_num
