MySQL数据分析案例——电商平台用户行为

1. 数据字典

某电商平台用户行为数据

数据集字段及含义：

user（用户id）

item（商品id）

category（商品类别id）

behavior（行为类型）：

pv：浏览

fav：收藏

cart：加购物车

buy：购买

time 发生时间 小时

date 发生时间日期：年 月 日

数据量：200万*6列

文件：user_behavior.sql

2. 导入数据

Navicat软件

3. 统计指标计算

• 计算复购率（复购率=购买2次及以上用户数/总购买用户数）

• 查询购买用户数

• 查询购买两次及以上用户数

(1) 总购买用户数

SELECT count(DISTINCT user)

FROM taobao

WHERE behavior = 'buy'

(2) 购买两次及以上用户数

#用子查询

SELECT count(*)

FROM(

SELECT count(user) tmp

FROM taobao WHERE behavior = 'buy'

GROUP BY user) t

WHERE tmp > '1'

• 计算平均购买次数（总购买次数/总购买用户数）

#总购买次数

SELECT count(*)

FROM taobao

WHERE behavior = 'buy'

4. 转化率计算

• AARRR模型：Acquisition（用户获取）、Activation（提高活跃度）、Retention（提高留存率）、Revenue（获取收入）、Refer（自传播）

• 计算转化率（Excel绘制漏斗图）：

PV(1) --> PV --> Cart/Fav --> Buy

浏览一次--> 浏览多次 --> 加购或收藏 --> 购买

#总用户数

SELECT count(DISTINCT user) 用户数

FROM taobao

#只有一次行为的用户数

SELECT *

FROM (

SELECT count(*) 行为数

FROM taobao

GROUP BY user) t

WHERE 行为数 = 1

#实施各行为的用户数

SELECT behavior, count(user)

FROM taobao

GROUP BY behavior