关于数据库:淘宝用户购物行为分析

在本案例中，咱们将应用 Databend Cloud 对来自天池实验室的淘宝用户购物行为数据集进行剖析，一起发现乏味的购物行为。

该数据集为 CSV 格局，蕴含了 2017 年 11 月 25 日至 2017 年 12 月 3 日之间，有行为的约一百万随机用户的所有行为（包含点击、购买、加购、喜爱）。数据集的每一行示意一条用户行为，由以下 5 列组成，并以逗号分隔：

筹备工作

下载数据集

1. 下载淘宝用户购物行为数据集到本地，而后应用以下命令解压：

unzip UserBehavior.csv.zip

2. 将解压后的数据集文件 (UserBehavior.csv) 压缩为 gzip 格局：

gzip UserBehavior.csv

创立内部 Stage

1. 登入 Databend Cloud，并新建一个工作区。
2. 在工作区中，执行以下 SQL 语句在阿里云上创立一个名为 ”mycsv” 的内部 Stage:

CREATE STAGE mycsv URL = 's3://<YOUR_BUCKET_NAME>'
CONNECTION = (
  ACCESS_KEY_ID = '<YOUR_ACCESS_KEY_ID>',
  SECRET_ACCESS_KEY = '<YOUR_SECRET_ACCESS_KEY>',
  ENDPOINT_URL = '<YOUR_ENDPOINT_URL>',
  ENABLE_VIRTUAL_HOST_STYLE = TRUE
)
FILE_FORMAT = (
  TYPE = CSV
  COMPRESSION = AUTO
);

3. 执行以下 SQL 语句验证 Databend Cloud 是否可拜访到该内部 Stage：

LIST @mycsv;

上传数据集到内部 Stage

应用 BendSQL 将压缩后的数据集文件 (UserBehavior.csv.gz) 上传到内部 Stage。获取计算集群的连贯信息，请参考连贯到计算集群。

(base) eric@Erics-iMac ~ % bendsql --host tenantID--YOUR_WAREHOUSE.gw.aliyun-cn-beijing.default.databend.cn \
  --user=cloudapp \
  --password=<YOUR_PASSWORD> \
  --database="default" \
  --port=443 --tls
Welcome to BendSQL 0.9.3-db6b232(2023-10-26T12:36:55.578667000Z).
Connecting to tenantID--YOUR_WAREHOUSE.gw.aliyun-cn-beijing.default.databend.cn:443 as user cloudapp.
Connected to DatabendQuery v1.2.183-nightly-1ed9a826ed(rust-1.72.0-nightly-2023-10-28T22:10:15.618365223Z)

cloudapp@tenantID--YOUR_WAREHOUSE.gw.aliyun-cn-beijing.default.databend.cn:443/default> PUT fs:///Users/eric/Documents/UserBehavior.csv.gz @mycsv

PUT fs:///Users/eric/Documents/UserBehavior.csv.gz @mycsv

┌─────────────────────────────────────────────────────────────────┐
│                    file                   │  status │    size   │
│                   String                  │  String │   UInt64  │
├───────────────────────────────────────────┼─────────┼───────────┤
│ /Users/eric/Documents/UserBehavior.csv.gz │ SUCCESS │ 949805035 │
└─────────────────────────────────────────────────────────────────┘
1 file uploaded in 401.807 sec. Processed 1 file, 905.80 MiB (0.00 file/s, 2.25 MiB/s)

数据导入和荡涤

创建表格

在工作区中，执行以下 SQL 语句为数据集创建表格：

CREATE TABLE `user_behavior` (
  `user_id` INT NOT NULL,
  `item_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `behavior_type` VARCHAR,
  `ts` TIMESTAMP,
  `day` DATE );

荡涤、导入数据

1. 执行以下 SQL 语句导入数据到表格中，并同时实现荡涤：

   • 去除有效的工夫区外的数据
   • 数据去重
   • 生成额定列数据

INSERT INTO user_behavior
SELECT $1,$2,$3,$4,to_timestamp($5::bigint) AS ts, to_date(ts) day
FROM @mycsv/UserBehavior.csv.gz WHERE day BETWEEN '2017-11-25' AND '2017-12-03'
GROUP BY $1,$2,$3,$4,ts;

2. 执行以下 SQL 语句验证数据导入是否胜利。该语句将返回表格的 10 行数据。

SELECT * FROM user_behavior LIMIT 10;

数据分析

在实现了后期的筹备和数据导入之后，咱们正式开始进行数据分析。

用户流量及购物状况剖析

总访问量和用户数

SELECT SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) as pv,
COUNT(DISTINCT user_id) as uv
FROM user_behavior;

日均访问量和用户量

SELECT day,
       SUM(CASE WHEN behavior_type = 'pv' THEN 1 ELSE 0 END) AS pv,
       COUNT(DISTINCT user_id) AS uv
FROM user_behavior
GROUP BY day
ORDER BY day;

也能够通过 应用仪表盘 性能，生成折线图：

统计每个用户的购物状况，生成新表：user_behavior_count

create table user_behavior_count as select user_id,
       sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,   -- 点击数
       sum(case when behavior_type = 'fav' then 1 else 0 end) as fav,  -- 珍藏数
       sum(case when behavior_type = 'cart' then 1 else 0 end) as cart,  -- 加购物车数
       sum(case when behavior_type = 'buy' then 1 else 0 end) as buy  -- 购买数
       from user_behavior
group by user_id;

复购率：两次或两次以上购买的用户占购买用户的比例

select sum(case when buy > 1 then 1 else 0 end) / sum(case when buy > 0 then 1 else 0 end)
from user_behavior_count;

用户行为转换率

点击 /(加购物车 + 珍藏)/ 购买，各环节转化率

select a.pv,
       a.fav,
       a.cart,
       a.fav + a.cart as `fav+cart`,
       a.buy,
       round((a.fav + a.cart) / a.pv, 4) as pv2favcart,
       round(a.buy / (a.fav + a.cart), 4) as favcart2buy,
       round(a.buy / a.pv, 4) as pv2buy
from(select sum(pv) as pv,   -- 点击数
sum(fav) as fav,  -- 珍藏数
sum(cart) as cart,  -- 加购物车数
sum(buy) as buy  -- 购买数
from user_behavior_count
) as a;

计算一个小时实现浏览 -> 增加到购物 -> 并领取的用户

SELECT
   count_if(level>=1) as pv, count_if(level>=2) as cart, count_if(level>=3) as buy
FROM
(
    SELECT
        user_id,
        window_funnel(3600000000)(ts, behavior_type = 'pv',behavior_type = 'cart',behavior_type = 'buy') AS level
    FROM user_behavior
    GROUP BY user_id
);

用户行为习惯

每天用户购物行为

select to_hour(ts) as hour,
       sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,   -- 点击数
       sum(case when behavior_type = 'fav' then 1 else 0 end) as fav,  -- 珍藏数
       sum(case when behavior_type = 'cart' then 1 else 0 end) as cart,  -- 加购物车数
       sum(case when behavior_type = 'buy' then 1 else 0 end) as buy  -- 购买数
from user_behavior
group by hour
order by hour;

也能够通过 应用仪表盘 性能，生成折线图：

每周用户购物行为

select to_day_of_week(day) as weekday,day,
       sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,   -- 点击数
       sum(case when behavior_type = 'fav' then 1 else 0 end) as fav,  -- 珍藏数
       sum(case when behavior_type = 'cart' then 1 else 0 end) as cart,  -- 加购物车数
       sum(case when behavior_type = 'buy' then 1 else 0 end) as buy  -- 购买数
from user_behavior
where day between '2017-11-27' and '2017-12-03'
group by weekday,day
order by weekday;

也能够通过 应用仪表盘 性能，生成柱状图：

基于 RFM 模型找出有价值用户

RFM 模型是掂量客户价值和客户创利能力的重要工具和伎俩，其中由 3 个因素形成了数据分析最好的指标：

• R-Recency（最近一次购买工夫）
• F-Frequency（生产频率）
• M-Money（生产金额）

R-Recency（最近购买工夫）：R 值越高，用户越沉闷

select user_id,
       to_date('2017-12-04') - max(day) as R,
       dense_rank() over(order by (to_date('2017-12-04') - max(day))) as R_rank
from user_behavior
where behavior_type = 'buy'
group by user_id
limit 10;

F-Frequency（生产频率）：F 值越高，用户越虔诚

select user_id,
       count(1) as F,
       dense_rank() over(order by count(1) desc) as F_rank
from user_behavior
where behavior_type = 'buy'
group by user_id
limit 10;

用户分组

对有购买行为的用户依照排名进行分组，共划分为 5 组：

• 前 1/5 的用户打 5 分
• 前 1/5 – 2/5 的用户打 4 分
• 前 2/5 – 3/5 的用户打 3 分
• 前 3/5 – 4/5 的用户打 2 分
• 其余用户打 1 分

依照这个规定别离对用户工夫距离排名打分和购买频率排名打分，最初把两个分数合并在一起作为该名用户的最终评分。

with cte as(
select user_id,
       to_date('2017-12-04') - max(day) as R,
       dense_rank() over(order by (to_date('2017-12-04') - max(day))) as R_rank,
       count(1) as F,
       dense_rank() over(order by count(1) desc) as F_rank
from user_behavior
where behavior_type = 'buy'
group by user_id)
select user_id, R, R_rank, R_score, F, F_rank, F_score,  R_score + F_score AS score
from(
select *,
       case ntile(5) over(order by R_rank) when 1 then 5
                                           when 2 then 4
                                           when 3 then 3
                                           when 4 then 2
                                           when 5 then 1
       end as R_score,
       case ntile(5) over(order by F_rank) when 1 then 5
                                           when 2 then 4
                                           when 3 then 3
                                           when 4 then 2
                                           when 5 then 1
       end as F_score
from cte
) as a
order by score desc
limit 20;

商品维度剖析

销量最高的商品

select item_id ,
       sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,   -- 点击数
       sum(case when behavior_type = 'fav' then 1 else 0 end) as fav,  -- 珍藏数
       sum(case when behavior_type = 'cart' then 1 else 0 end) as cart,  -- 加购物车数
       sum(case when behavior_type = 'buy' then 1 else 0 end) as buy  -- 购买数
from user_behavior
group by item_id
order by buy desc
limit 10;

销量最高的商品类别

select category_id ,
       sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,   -- 点击数
       sum(case when behavior_type = 'fav' then 1 else 0 end) as fav,  -- 珍藏数
       sum(case when behavior_type = 'cart' then 1 else 0 end) as cart,  -- 加购物车数
       sum(case when behavior_type = 'buy' then 1 else 0 end) as buy  -- 购买数
from user_behavior
group by category_id
order by buy desc
limit 10;

用户留存剖析

开始前，创建表格 ”day_users” 并插入数据：

create table day_users(
day date,
users bitmap);

insert into day_users select day, build_bitmap(list(user_id::UInt64)) from user_behavior group by day;

统计每天 UV

select day,bitmap_count(users) from day_users order by day;

绝对留存

这里计算绝对于 11 月 23 日，12 月 2 号还在应用淘宝用户：

select bitmap_count(bitmap_and(a.users, b.users))
from (select users from day_users where day='2017-11-25') a ,
(select users from day_users where day='2017-12-02') b;

绝对新增

select bitmap_count(bitmap_not(b.users, a.users)) from (select users from day_users where day='2017-11-25') a ,
(select users from day_users where day='2017-12-02') b;