一、同期群分析概念和理论

1 同期群分析方法介绍

  同期群分析（CohortAnalysis）实际上是一种用户分群的细分类型，是一种“纵横”结合的分析方法：

• 横向上——分析同期群随着周期推移而发生的变化；
• 纵向上——分析在生命周期相同阶段的群组之间的差异。

  “同期群”：同一时期的群体。可以是“同一天注册的用户”、同一天第一次发生付费行为的用户等等。“周期的指标变化”：用户在一定周期内的留存率、付费率等等。同期群分析包含三个核心的元素：

1. 客户首次行为时间：这是划分同期群体的基点；
2. 时间周期维度：比如N日留存率、N日转化率中的N日，一般即为+N日、+N月等
3. 变化的指标：比如注册转化率、付款转化率、留存率等等。

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 = '交易成功';

2 计算留存量

（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;

3 计算留存率

-- ① 提取首付月份、留存量
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.首付月份;

• 还没有人评论，欢迎说说您的想法！