常见电商指标数据分析(MySQL)
〇、数据准备
本次电商数据分析主要涉及userinfo、orderinfo两个数据表。
– orderinfo 订单详情表
– orderid 订单id
– userid 用户id
– isPaid 是否支付
– price 付款价格
– paidTime 付款时间
– userinfo 用户信息表
– userid 用户id
– sex 用户性别
– birth 用户出生日期
一、销量分析·统计不同月份的下单人数
SELECT
YEAR (paidtime),
MONTH (paidtime),
count(DISTINCT userid) AS cons
FROM
orderinfo
WHERE
ispaid = '已支付'
GROUP BY
YEAR (paidtime),
MONTH (paidtime)
-- 下单人数而非下单量因此需要用DISTINCT去重
二、统计用户三月份的回购率和复购率
复购率 = 当月多次购买用户数/当月所有购买用户数
– a.先筛选出3月消费情况
SELECT * FROM orderinfo
WHERE ispaid = '已支付' AND month(paidtime) = '03'
– b.统计每个用户在3月消费次数
SELECT userid,count(orderid) AS cons
FROM orderinfo WHERE ispaid = '已支付'
AND month(paidtime) = '03'
GROUP BY userid
– c.判断消费次数大于1的userid
SELECT
count(1) AS userid_cons,
SUM(IF(cons > 1, 1, 0)) AS fugou_cons,
SUM(IF(cons > 1, 1, 0)) / count(1) AS fugou_rate
FROM
(
SELECT
userid,
count(1) AS cons
FROM
orderinfo
WHERE
ispaid = '已支付'
AND MONTH (paidtime) = '03'
GROUP BY
userid
) a;
--巧用SUM()和IF()的组合,然后将b步骤作为子查询
– 回购率 = n+1月中n月购买过的用户数 / n+1月购买用户数
– a. 统计每年每月单个用户消费情况
SELECT userid,date_format(paidtime,'%Y-%m-01'),count