mysql复购率_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 '\r\n';
load data local infile 'F:/user_info_utf.csv' into table test.userinfo fields terminated by ',' optionally enclosed by "'" escaped by '' lines terminated by '\r\n' (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
總結
以上是生活随笔為你收集整理的mysql复购率_MySQL_复购回购率的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 超强大的货币汇率实时查询工具
- 下一篇： OpenMV新手上路1 -- OpenM