计算代码重复率_MySQL_复购回购率

指标解释

如何计算复购率/回购率

计算方法一：复购的人 复购率 = 单位时间内购买次数大于1的人/所有购买的人 例如： 一段时间内，10个人中有3个人购买2次，这3个人中有一个人又购买了一次，累计复购人数为3人，则这段时间内的复购率为30%。

计算方法二：复购次数 复购率 = 单位时间内复购次数/所有购买的人 例如： 一段时间内，10个人中有3个人购买2次，这3个人中有一个人又购买了一次，累计复购次数为4次，则这段时间内的复购率为40%。

复购和回购的区别

复购是一个单位时间内的多次购买，回购是在下一个单位时间内仍然购买。 例如： 6月总共100人购买产品，其中有10人在6月购买了2次，5人在6月购买了3次。 按照方法一计算，6月的复购率为15% 按照方法二计算，6月的复购率为20% 若6月购买过产品的100个人中有10个在7月又购买了，则7月的回购率为10%。

何时关注复购？

90天内重复购买率达到1%~15%；说明你处于用户获取模式；把更多的精力和资源投入到新用户获取和转化； 90天内重复购买率达到15~30%；说明你处于混合模式；平衡用在新用户转化和老用户留存、复购上的精力和资源； 90天内重复购买率达到30%以上；说明你处于忠诚度模式；把更多的精力和资源投入到用户复购上；

上述观点出自《精益数据分析》，跟本人无关。

SQL题

题目出自《七周成为数据分析师》。

建表导入数据

DROP TABLE IF EXISTS orderinfo;

CREATE TABLE orderinfo ( id int, userid int, ispaid varchar(3), price decimal(10, 2), paidtime datetime NULL );

DROP TABLE IF EXISTS userinfo;

CREATE TABLE userinfo ( userid int, sex varchar(3) NULL, birth date NULL );
# 设置允许从本地导入文件
SET GLOBAL local_infile=1;

load data local infile 'F:/order_info_utf.csv' into table test.orderinfo fields terminated by ',' optionally enclosed by "'" escaped by '' lines terminated by 'rn';

load data local infile 'F:/user_info_utf.csv' into table test.userinfo fields terminated by ',' optionally enclosed by "'" escaped by '' lines terminated by 'rn'  (userid,sex,birth);

我这边选择使用load data语句将csv导入mysql，你也可以选择使用可视化界面导入，例如SQLyog,Navicat等。语句导入的执行效率较高。下图为使用Navicat导入的效果。

下图为使用load data命令导入的效果。

1. 统计不同月份已支付的订单数，下单人数

SELECT CONCAT(YEAR(paidtime), '/', MONTH(paidtime)) AS 下单年月
 , COUNT(*) AS 支付订单数, COUNT(DISTINCT userid) AS 下单人数
FROM orderinfo
WHERE ispaid = '已支付'
GROUP BY 下单年月;

2. 统计不同月份的回购率和复购率（复购率按照下单人数来算）

SELECT 下单年月, COUNT(c) AS 下单人数
 , COUNT(if(c > 1, 1, NULL)) AS 当月复购人数
 , concat(round(COUNT(if(c > 1, 1, NULL)) / COUNT(c) * 100, 2), '%') AS '当月复购率'
FROM (
 SELECT CONCAT(YEAR(paidtime), '/', MONTH(paidtime)) AS 下单年月
  , COUNT(userid) AS c
 FROM orderinfo
 WHERE ispaid = '已支付'
 GROUP BY 下单年月, userid
) a
GROUP BY 下单年月;

SELECT a.date, COUNT(a.userid) AS 当月购买人数, COUNT(b.userid) AS 次月回购人数
 , concat(round(COUNT(b.userid) / COUNT(a.userid) * 100, 2), '%') AS 次月回购率
FROM (
 SELECT DATE_FORMAT(paidtime, '%Y-%m-%01') AS date, userid
 FROM orderinfo
 WHERE ispaid = '已支付'
 GROUP BY date, userid
 ORDER BY userid
) a
 LEFT JOIN (
  SELECT DATE_FORMAT(paidtime, '%Y-%m-%01') AS date, userid
  FROM orderinfo
  WHERE ispaid = '已支付'
  GROUP BY date, userid
  ORDER BY userid
 ) b
 ON a.userid = b.userid
  AND a.date = date_sub(b.date, INTERVAL 1 MONTH)
GROUP BY date

3. 统计多次消费的用户，第一次和最后一次消费时间的间隔

SELECT userid
 , DATEDIFF(MAX(paidtime), MIN(paidtime)) AS 间隔天数
FROM orderinfo
WHERE ispaid = '已支付'
GROUP BY userid
HAVING COUNT(userid) > 1;

4. 统计男女消费频次是否有差异

SELECT sex, AVG(c) AS 消费频次
FROM (
 SELECT COUNT(*) AS c, sex
 FROM orderinfo o
  INNER JOIN userinfo u ON o.userid = u.userid
 WHERE sex IS NOT NULL
  AND ispaid = '已支付'
 GROUP BY o.userid
) a
GROUP BY sex

5. 统计男女消费金额是否有差异

SELECT sex, AVG(s) AS 消费金额
FROM (
 SELECT SUM(price) AS s, sex
 FROM orderinfo o
  INNER JOIN userinfo u ON o.userid = u.userid
 WHERE sex IS NOT NULL
  AND ispaid = '已支付'
 GROUP BY o.userid
) a
GROUP BY sex

6. 统计不同年龄段的用户消费金额是否有差异

SELECT CASE 
  WHEN age BETWEEN 0 AND 9 THEN '0-9岁'
  WHEN age BETWEEN 10 AND 19 THEN '10-19岁'
  WHEN age BETWEEN 20 AND 29 THEN '20-29岁'
  WHEN age BETWEEN 30 AND 39 THEN '30-39岁'
  WHEN age BETWEEN 40 AND 49 THEN '40-49岁'
  WHEN age BETWEEN 50 AND 59 THEN '50-59岁'
  WHEN age BETWEEN 60 AND 69 THEN '60-69岁'
  WHEN age BETWEEN 70 AND 79 THEN '70-79岁'
  WHEN age BETWEEN 80 AND 89 THEN '80-89岁'
  ELSE NULL
 END AS 年龄段, round(AVG(price), 2) AS 平均消费
FROM orderinfo o
 INNER JOIN (
  SELECT userid, year(now()) - year(birth) AS age
  FROM userinfo
  WHERE year(birth) > 1900
 ) a
 ON (o.userid = a.userid
  AND age IS NOT NULL
  AND ispaid = '已支付')
GROUP BY 年龄段
HAVING 年龄段 IS NOT NULL
ORDER BY 年龄段

7. 统计消费的二八法则，消费的top20%用户，贡献了多少额度

SELECT @sum_price := SUM(price)
 , @count_user := COUNT(DISTINCT userid)
FROM orderinfo
WHERE ispaid = '已支付';

SELECT SUM(s) AS '前20%累计消费'
 , concat(round(SUM(s) * 100 / @sum_price, 2), '%') AS '占比'
FROM (
 SELECT userid, SUM(price) AS s, row_number() OVER (ORDER BY SUM(price) DESC) AS r
 FROM orderinfo
 WHERE ispaid = '已支付'
 GROUP BY userid
) a
WHERE a.r <= @count_user * 0.2

数据数据集及代码下载链接：

百度云​pan.baidu.com

提取码：xarx