项目导读：本项目基于亿级电商真实用户行为数据，还原真实业务场景下的底层逻辑。从放弃 MySQL 转向 ClickHouse 搭建明细数据层 (DWD) 开始，通过漏斗分析、时间序列、RFM模型、同期群分析及用户路径追踪等 9 大步骤，最终输出可落地的精细化运营与增长策略。

🛒 数据来源：阿里天池实验室 - 淘宝电商用户行为数据集 (User Behavior Data)
⚠️ 合规声明：本项目使用之数据仅用于个人数据分析技能展示与技术交流，已进行脱敏处理，底层数据版权归属阿里巴巴天池实验室所有。

核心可视化成果 (Tableau Dashboard)

下面是我基于清洗后的数据，使用 Tableau 制作的全局交互式数据大屏，涵盖了流量热力图、漏斗转化、RFM 树状图及商品气泡图等核心业务指标。

淘宝用户行为数据分析大屏

(注：由于静态图片展示受限，如果您对动态交互看板感兴趣，请点击此处查看 Tableau Public 在线版本)

数据处理与深度业务分析

数据清洗与底层数仓建设 (ODS 到 DWD)

由于数据规模高达上亿条，传统 MySQL (OLTP) 导入查询过慢，本项目采用 ClickHouse 列式数据库进行在线分析处理。

首先先检查时间列表是否有异常值：

1
2
3
4
5
6
SELECT 
    MIN(timestamp) AS min_timestamp_raw,
    toDateTime(MIN(timestamp)) AS min_real_time,
    MAX(timestamp) AS max_timestamp_raw,
    toDateTime(MAX(timestamp)) AS max_real_time
FROM wangchao_data.user_behavior;

ONE
通过查询，我们知道了表中确实存在异常值，但是我们依然不知道准确值的范围是什么，所以按天查户口（看数据主要集中在哪）：

1
2
3
4
5
6
7
8
9
10
11
12
13
with(
select count(*)
from wangchao_data.user_behavior) as total_sum,
(select sum(action_count)
from(
select toDate(toDateTime(timestamp)) as real_date,
count(*) as action_count
from wangchao_data.user_behavior
group by real_date
order by action_count desc 
limit 10)) as top10_sum
select total_sum as total,top10_sum as top10,
round((top10_sum/total_sum) * 100,2) as represent_percent;

通过探索性分析，发现 99.98% 的数据集中在 2017-11-24 到 2017-12-03 之间。我们在 DWD（明细数据层）建立新表，利用 MergeTree 引擎进行底层性能优化，清洗掉脏数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建清洗后的明细数据表 (DWD层)
CREATE TABLE wangchao_data.user_behavior_clean (
    user_id Int32,
    item_id Int32,
    category_id Int32,
    behavior_type String,
    timestamp Int32,
    date_id Date
) ENGINE = MergeTree()
PARTITION BY date_id 
ORDER BY (user_id, item_id);

-- 注入精准时间段内的优质数据
INSERT INTO wangchao_data.user_behavior_clean
SELECT 
    user_id, item_id, category_id, behavior_type, timestamp,
    toDate(toDateTime(timestamp)) AS date_id
FROM wangchao_data.user_behavior
WHERE timestamp > 1511452800 AND timestamp < 1512316800;

注意：

1
2
3
4
5
6
7
8
select count(user_id),count(item_id),count(category_id),count(behavior_type),count(timestamp)
from wangchao_data.user_behavior_clean;

SELECT 
    count(*) AS total_rows,
    uniqExact(user_id, item_id, behavior_type, timestamp) AS unique_combinations,
    total_rows - unique_combinations AS duplicates
FROM wangchao_data.user_behavior_clean;

由此可知，每条数据没有空值，却有49条重复值，但对于这个亿级别的数据来说，根本不值一提，选择视而不见+不处理，但要记录更好。

漏斗分析：寻找真实的转化率与“内鬼”

业务思考：单纯计算大盘转化率是危险的。大盘显示近百万真实用户产生了上亿浏览记录（人均高达100次操作） 。极少数“异常账号”单日产生了数万次浏览却零购买 。这些机器流量（竞对爬虫、刷单机器人）不仅污染了核心转化率指标，还白白消耗了服务器天价带宽，甚至造成黄牛抢购伤害真实用户 。

查看behavior_type的所有样式：

1
2
3
4
5
6
7
8
9
WITH (SELECT count(*) FROM wangchao_data.user_behavior_clean) AS total_actions

SELECT 
    behavior_type,
    count(*),
    round((count(*) / total_actions) * 100, 2) AS percent
FROM wangchao_data.user_behavior_clean
GROUP BY behavior_type
ORDER BY count(*) DESC;

TWO

计算真实用户的转化漏斗（看人，不看次数）：

1
2
3
4
5
6
7
select 
uniqExactIf(user_id,behavior_type='pv') as pv_people,
uniqExactIf(user_id,behavior_type='cart') as cart_people,
uniqExactIf(user_id,behavior_type='fav') as fav_people,
uniqExactIf(user_id,behavior_type='buy') as buy_people,
round((uniqExactIf(user_id,behavior_type='buy')/uniqExactIf(user_id,behavior_type='pv'))*100,2) as Conversion_Rate
from wangchao_data.user_behavior_clean;

THREE
由此图可以知道一共有近百万的真实用户，但却有上亿的浏览记录，说明1 亿条数据 ÷ 100 万人 = 平均每人 100 次操作。
这 100 次操作是分布在 10 天里的，也就是说：平均每个人每天只点了 10 下淘宝。
根据二八定律可以知道：
• 80% 的普通用户： 可能 10 天加起来只点了 20 下。
• 极少数的“变态”账号： 可能一天就能产生 几万次 甚至 几十万次 浏览！
这些账号根本就不是人，而是竞争对手派来的爬虫、自动抓取价格的脚本、或者是刷单公司的机器人！
我们需要查询那些机器人：

1
2
3
4
5
select user_id,count(*),sum(behavior_type='buy') as sum_buy
from wangchao_data.user_behavior_clean
group by user_id 
order by count(*) desc 
limit 5;

FOUR
输出了排名前五的用户浏览数和购买数，发现最高的也就才848，说明阿里天池实验室的工程师已经用他们内部的风控算法，把那些几万次点击的真实爬虫给提前清洗掉了

抓节奏（时间序列与活动运营）

业务思考：底层时间戳为英国格林尼治时间（UTC），必须进行时区转化（Asia/Shanghai）以还原国内用户的真实作息 。

分析用户在一天 24 小时内的行为活跃度分布：

1
2
3
4
5
6
7
8
select toHour(toDateTime(timestamp,'Asia/Shanghai')) as hour,
sum(behavior_type = 'pv') as sum_pv,
sum(behavior_type = 'cart') as sum_cart,
sum(behavior_type = 'fav') as sum_fav,
sum(behavior_type = 'buy') as sum_buy 
from wangchao_data.user_behavior_clean
group by hour
order by hour asc;

FIVE

1
2
注意:由于时区陷阱（Timezone Trap）
底层的 timestamp（比如 1511452800）是 Unix 时间戳，它的绝对零点在英国格林尼治天文台（UTC时区，也就是 0 时区）。而咱们阿里巴巴的服务器和用户都在中国（北京时间，东八区 UTC+8）。

SIX

1
2
3
4
5
6
7
8
（UTC+8 北京时间）的峰值：21:00（晚上 9 点）。
◦ 这完全符合中国网民的作息：吃完晚饭、忙完事情后的晚间黄金时段。
◦ 正确解读：流量高峰是晚间的休闲娱乐/购物时间。
运营策略建议（从数据中能推出来的）
基于“峰值在 21:00 且峰值高达 7M”这一点：
• 推送/营销时机：应该选在 20:30 左右 进行 APP 推送或大促活动开启，积蓄动能迎接 21:00 的高峰。
• 服务器扩容：每天晚上 20:00 - 22:00 需要保证资源充足。
• 最小干预期：凌晨 4:00-5:00 左右（推测的最低谷）是进行数据库维护、ETL 清洗、上线新功能的最佳时间。

抓大哥：RFM 价值模型（用户分层）

业务思考：精细化运营的核心是“看人下菜碟”。基于最近一次消费 (R) 和消费频率 (F)，建立精准的评分体系，将用户划分为四大价值阵营 。
检查用户在R、F上的区间范围，可以对用户进行RFM模型（因为此表没有消费金额，所以M不作参考）：

1
2
3
4
5
6
7
select user_id,date_diff('day',max(date_id),toDate('2017-12-04')) as R,
count(*) as F 
from wangchao_data.user_behavior_clean
where behavior_type = 'buy'
group by user_id 
order by R asc ,F desc
limit 20;

根据范围我们将用户分为四层：1.核心高价值SVIP、2.潜力活跃中产、3.濒危沉睡用户、4.边缘流失客

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
WITH new_biao1 AS (
    SELECT 
        user_id,
        date_diff('day', max(date_id), toDate('2017-12-04')) AS R,
        count(*) AS F
    FROM wangchao_data.user_behavior_clean
    WHERE behavior_type = 'buy'
    GROUP BY user_id
),
new_biao2 AS (
    SELECT 
        user_id, R, F,
        CASE 
            WHEN R <= 2 THEN 5
            WHEN R <= 4 THEN 4
            WHEN R <= 6 THEN 3
            WHEN R <= 8 THEN 2
            ELSE 1
        END AS R_Score,
        CASE 
            WHEN F >= 5 THEN 5
            WHEN F >= 3 THEN 4
            WHEN F >= 2 THEN 3
            ELSE 1
        END AS F_Score
    FROM new_biao1
)
SELECT 
    CASE 
        WHEN (R_Score + F_Score) >= 8 THEN '1.核心高价值SVIP(8-10分)'
        WHEN (R_Score + F_Score) >= 6 THEN '2.潜力活跃中产(6-7分)'
        WHEN (R_Score + F_Score) >= 4 THEN '3.濒危沉睡用户(4-5分)'
        ELSE '4.边缘流失客(2-3分)'
    END AS `用户分层`,
    count(user_id) AS `该层级的人数`
FROM new_biao2
GROUP BY `用户分层`
ORDER BY `用户分层` ASC;

SEVEN

现在我们知道每个层级有多少人了，但是如果想对每个用户精准进行不同的策略，需要把每个用户的RFM得分和用户分层给对应上，但是如此大数据再去创建新列名太麻烦，而且性能太高，可以创建新表，专门对应user_id 和 RFM_Score的新表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
create table wangchao_data.dim_user_rfm(
user_id Int32,
r_score Int8,
f_score Int8,
total_score Int8,
user_level String)engine = MergeTree()
order by user_id;

INSERT INTO wangchao_data.dim_user_rfm
WITH 
UserRF AS (
    SELECT 
        user_id,
        dateDiff('day', max(date_id), toDate('2017-12-04')) AS R,
        count(*) AS F
    FROM wangchao_data.user_behavior_clean
    WHERE behavior_type = 'buy'
    GROUP BY user_id
),
UserScored AS (
    SELECT 
        user_id,
        R, F,
        CASE 
            WHEN R <= 2 THEN 5 WHEN R <= 4 THEN 4 WHEN R <= 6 THEN 3 WHEN R <= 8 THEN 2 ELSE 1 
        END AS R_Score,
        CASE 
            WHEN F >= 5 THEN 5 WHEN F >= 3 THEN 4 WHEN F = 2 THEN 3 ELSE 1 
        END AS F_Score
    FROM UserRF
)
SELECT 
    user_id,
    R_Score,
    F_Score,
    (R_Score + F_Score) AS total_score,
    CASE 
        WHEN (R_Score + F_Score) >= 8 THEN '1.核心高价值SVIP(8-10分)'
        WHEN (R_Score + F_Score) >= 6 THEN '2.潜力活跃中产(6-7分)'
        WHEN (R_Score + F_Score) >= 4 THEN '3.濒危沉睡用户(4-5分)'
        ELSE '4.边缘流失客(2-3分)'
    END AS user_level
FROM UserScored;

EIGHT

1
2
3
4
5
6
7
8
9
10
注意:由于计算的是where behavior_type=’buy’购买的用户，所以接受评分的只有不到70w用户

• 【8 - 10 分】核心高价值 SVIP (土豪/代购)
• 业务动作： 全程绿灯！安排专属客服，推送大额满减券（满5000减500），推高端商品。绝不轻易打扰，一打扰必是重磅福利。
• 【6 - 7 分】潜力活跃中产 (主力军)
• 业务动作： 疯狂交叉销售！他买了手机，赶紧给他推手机壳和耳机。发“满199减20”的日常券，刺激他多下两单冲击 SVIP。
• 【4 - 5 分】濒危沉睡用户 (快跑了)
• 业务动作： 紧急挽回！他可能一周前买过一次就再没来过。赶紧发短信：“您的 5 元无门槛现金券即将过期！” 必须用最直接的利益把他拉回来。
• 【2 - 3 分】边缘流失客 (一波流)
• 业务动作： 放弃治疗或自动化处理。大概率是双十一薅了羊毛就卸载软件的人，不要在他们身上浪费昂贵的客服和短信成本。

抓爆款：商品与类目排行榜（选品策略）

业务思考：销量和流量不仅反映用户喜好，更是供应链压价和流量重分配的筹码 。
商品爆款前20：

1
2
3
4
5
6
7
8
select item_id as `商品id`,category_id as `类目id`,sum(behavior_type ='pv') as `浏览次数_PV`,
sum(behavior_type ='buy') as `购买次数_Buy`,
round((sum(behavior_type ='buy')/sum(behavior_type ='pv'))*100,2) as `单品转化率`
from wangchao_data.user_behavior_clean
group by item_id,category_id 
having `购买次数_Buy` >0 
order by `购买次数_Buy` desc
limit 20;

NINE
类目爆款前20：

1
2
3
4
5
6
7
select category_id as `类目id`,uniqExact(item_id) as `该类目下商品总数`,
sum(behavior_type ='pv') as `总浏览次数`,
sum(behavior_type ='buy') as `总购买次数`
from wangchao_data.user_behavior_clean
group by category_id 
order by `总购买次数` desc 
limit 20;

TEN

1
2
3
4
5
6
7
8
1. 流量重分配（找算法团队）
• 对于第一名（转化率 79% 的潜力爆款）： 它现在的瓶颈是“没人看”（只有 1700 多 PV）。必须立刻要求算法推荐团队：强行给这个商品增加曝光权重！ 把它挂到首页去！只要给它 1 万的流量，它能给你换回 8000 个订单！
• 对于第二名（转化率 5% 的引流款）： 它的流量极大，说明头图很吸引人，但很多人点进去不买。你要去揪住运营和设计团队的衣领：立刻优化它的商品详情页，或者给它配一个“下单立减 10 元”的促销！ 只要转化率能提 2 个百分点，销售额就能原地起飞。
2. 供应链压价与库存预警（找采购团队）
你看图二的类目榜单，第一名的类目 1,464,116卖了 3.4 万件。
拿着这个数据去找采购总监：“老大，这个赛道的货正在被疯抢。赶紧去跟上游工厂谈独家协议，把进货价往下压 10%！另外，立刻查一下仓库，按现在的日均消耗速度，这几款 Top 10 的单品还能撑几天？绝对不能出现爆款断货的情况！”
3. 关联推荐与打包销售
既然那个卖得最好的类目流量那么大，咱们能不能趁机带动一下其他不好卖的东西？比如买了这个类目里最火的 A 商品的人，大概率还会买什么 B 商品？咱们可以直接把 A 和 B 组合成一个“双十一必买套装”来提升客单价！

与爆款3122135绑定最深的“最佳拍档”top10：

1
2
3
4
5
6
7
8
9
10
11
select item_id as `关联购买商品id`,count(distinct user_id) as `共同购买人数`
from wangchao_data.user_behavior_clean
where behavior_type = 'buy' 
and item_id != 3122135
and user_id in (
select distinct user_id 
from wangchao_data.user_behavior_clean
where item_id = 3122135 and behavior_type = 'buy')
group by item_id 
order by `共同购买人数` desc 
limit 10;

ELEVEN
这就是跟 3,122,135 绑定得最深的“最佳拍档”。如果 A 是一部手机，你查出来的第一名 B 大概率就是一个手机壳或者充电头。

用户路径漏斗（打破迷信的桑基图）

业务思考：用户从来不会乖乖顺着漏斗往下走。通过 lead() 窗口函数追踪用户前后两步的动作，挖掘真实的转化场景 。
截取核心跳转动作计算：

1
2
3
4
5
6
7
8
9
10
11
12
13
with new_biao1 as (
select distinct user_id 
from wangchao_data.dim_user_rfm),
new_biao2 as (
select user_id,behavior_type as current_step,
lead(behavior_type , 1)over(partition by user_id order by timestamp) as next_step
from wangchao_data.user_behavior_clean
where user_id in (select user_id from new_biao1))
select current_step as `起点动作`,next_step as `终点动作`,count(*) as `跳转次数`
from new_biao2
where next_step is not null  and current_step != next_step 
group by current_step,next_step 
order by `跳转次数` desc;

TWELVE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
洞察一：疯狂的“折返跑”（打破漏斗迷信）
你重点看前四行数据：
• cart -> pv: 335万次
• pv -> cart: 334万次
• fav -> pv: 159万次
• pv -> fav: 157万次
商业结论： 用户根本不是乖乖顺着漏斗往下走的！他们处于一种极度纠结的“折返跑”状态（加了购物车，又退回去看商品；收藏了，又退回去接着逛）。这说明他们在疯狂比价或者凑单。
业务动作（去找产品经理）：
1. 购物车决断力不足： 必须在购物车页面增加“紧迫感”，比如提示“该商品库存紧张，仅剩2件”或者“降价通知”。
2. 流量闭环： 既然他们喜欢从购物车退回首页浏览，那我们在购物车页面底部一定要加上**“猜你喜欢 / 凑单满减推荐”**，肥水不流外人田，让他们在咱们的推荐池里继续循环，而不是跳出 APP。
洞察二：震撼的“冲动消费”（最重要的发现）
对比这两组购买数据：
• pv -> buy: 150万次（直接从商品页购买）
• cart -> buy: 8.7万次（从购物车结算）
• fav -> buy: 3.1万次（从收藏夹结算）
商业结论： 差距太夸张了！直接看完就买的次数（150万），是从购物车买的 17 倍！这说明淘宝双十一期间，绝大多数订单根本不是用户深思熟虑放在购物车里等结算的，而是极度上头的“冲动消费”和“直接购买”！
业务动作（去找运营和设计）：
“立即购买（Buy Now）”这个按钮的转化效能远大于“加入购物车”。在商品详情页的设计上，一定要把“立即购买”的按钮做得巨大、显眼，并且尽一切可能缩短直接支付的链路（比如引入面容支付、免密支付），千万不要强迫用户先加购物车。
洞察三：被忽视的“购后黄金区”
看第六行数据：
• buy -> pv: 145万次
商业结论： 用户买完东西之后，并没有直接关掉 APP（也就是表中终点为 null 的情况只有 5.4万次），而是有高达 145 万次操作是**“买完之后，立刻又回去接着逛（pv）”！
业务动作（去找营销团队）：
支付成功页面是一块极其宝贵的“风水宝地”！千万别只显示一个冷冰冰的“支付成功”。必须在这里发一张
“限时 10 分钟有效的购后专享 9 折券”**，或者推荐关联商品（比如刚买了手机，立刻推荐手机壳）。趁着用户还在疯狂购物的状态里，狠狠榨干他们的钱包！

同期群分析 (Cohort Analysis)

业务思考：留存率不仅仅反映产品粘性，更隐藏着平台的生命周期规律。
计算每日新增用户的 N 日留存情况 (SQL逻辑见源码)
使用 dateDiff 和 JOIN 追踪同期群留存情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
with new_biao1 as (
select user_id,min(date_id) as first_day
from wangchao_data.user_behavior_clean
group by user_id ),
new_biao2 as (
select n.first_day,dateDiff('day',n.first_day,b.date_id) as day_diff,
b.user_id as user_id
from new_biao1 as n 
join wangchao_data.user_behavior_clean as b on n.user_id = b.user_id)
select first_day as `同期群日期`,day_diff as `第n天留存`,uniqExact(user_id) as `留存人数`
from new_biao2 
where day_diff >=0 and day_diff< 11 
group by `同期群日期`,`day_diff`
order by `同期群日期` asc , `day_diff` asc ;

THIRTEEN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
洞察一：11月25日的“流量大爆炸”
你对比一下 同期群日期 这一列的 第 0 天（也就是新增首日）人数：
• 11月23日：889 人
• 11月24日：18万 人
• 11月25日：54.6万 人！
• 11月26日：13.8万 人
业务解释： 11月25日绝对是平台搞了极其重磅的拉新活动（比如双十一返场，或者黑五预热），这一天的获客量是平时的好几倍！
洞察二：违背常理的“留存微笑曲线”
按理说，留存率应该是一天比一天低的（也就是越来越少）。
11月25日 这一批人的留存轨迹：
• 第 0 天：54.6万
• 第 3 天：跌到了 40.2万（正常衰减）
• 第 7 天：竟然逆势暴涨回了 52.4万！
• 第 8 天：依然高达 51.3万！
业务解释： 为什么流失掉的人在第 7 天又集体回来了？查一下日历就全明白了：2017年11月25日是星期六。
7 天之后的第 7 天和第 8 天，正好是
下一个周末（12月2日和12月3日）！
这说明淘宝这种综合电商平台，有着极其强烈的**“周末潮汐效应”**。平时大家都在上班（第 1 到 第 5 天活跃度下降），一到周末，大批用户就集体回流开始疯狂按揭购物车。

寻找北极星指标的“魔法数字 (Aha Moment)”

业务思考：用户的“浏览次数”与“最终转化率”之间存在着非线性的魔法关系。
分析不同浏览深度的转化率分布:

1
2
3
4
5
6
7
8
9
10
11
12
13
with new_biao as (
select user_id , sum(behavior_type = 'pv') as total_pvs,
max(if(behavior_type = 'buy',1,0)) as is_buyer 
from wangchao_data.user_behavior_clean
group by user_id )
select total_pvs as `累计浏览次数`,
count(user_id) as `该阶段总人数`,
sum(is_buyer) as `买过的人数`,
round(sum(is_buyer)/count(user_id)*100,2) as `购买转化率`
from new_biao 
where total_pvs between 1 and 100 
group by `累计浏览次数`
order by `累计浏览次数` asc ;

FOURTEEN

1
2
3
4
5
6
7
8
9
10
11
12
洞察一：PV 1~3 的“狙击手效应”（反常识高转化）
• 数据表现： 浏览 1 次的人，转化率居然高达 75.56%！浏览 2 次、3 次的转化率也极高。
• 业务真相： 这绝对不是“瞎逛”的用户，而是**“目的极其明确的狙击手”**。他们可能是被站外的广告（比如抖音、小红书）直接种草，点进淘宝链接，买完就走；或者是直接搜索了某个确切的商品名（比如“iPhone 14 Pro 256G 暗夜紫”），看准了直接下单。
• 运营策略： 对于这类极速成交的流量，千万不要去打扰他们！ 不要弹窗、不要硬塞推荐商品。要做的就是“极致缩短交易链路”，推广免密支付、一键付款，让他们最快速度把钱付了。
洞察二：PV 8~10 的“犹豫谷底”（北极星干预点）
• 数据表现： 当浏览次数来到 8、9、10 次时，转化率跌到了全盘最低谷（49.9% 左右）。
• 业务真相： 这批用户是真正的“随便逛逛”或者“货比三家”。他们看了快 10 个商品还没下手，说明他们陷入了选择困难症，或者觉得价格不合适。 如果这个时候不管他们，他们大概率就流失了。
• 运营策略（魔法时刻）： 设定系统规则，当监测到某个新用户今天已经浏览了 8 个商品却没有任何加购动作时，立刻触发“强力干预”！ 弹出一个限时 15 分钟的“专属 9 折红包”。在他们马上就要放弃的时候，用利益推他们一把，帮他们做决定。
洞察三：PV 10~80 的“忠诚爬坡与边际递减”
• 数据表现： 从 PV=11 开始，转化率从 50% 稳步爬升，直到 PV=80 左右达到 70% 以上，随后在 71%~73% 之间进入平缓的平台期。
• 业务真相： 只要用户愿意在这个平台上投入大量时间去“淘”，看的东西越多，他们沉没成本就越高，最终总会挑到一件满意的买走。但超过 80 次之后，再怎么看，转化率也不会有质的飞跃了（边际效益递减）。
• 运营策略： 对于处于爬坡期的用户，核心是**“优化推荐算法（猜你喜欢）”**。不要让他们看了 50 个无用的东西才买 1 个，要通过精准推荐，让他们看 20 个东西就能挑中想买的，提高整个平台的流量变现效率。

帕累托验证 (二八定律应用)

业务思考：验证平台销量前 20% 的头部商品，是否真的支撑了全盘的核心营收，以辅助后续的仓储物流倾斜战略 。
使用窗口函数 (Window Function) 深度计算头部 20% 商品的销量占比；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH new_biao AS (
    SELECT 
        item_id, 
        buy_count,
        -- 给所有商品按销量打上排名
        row_number() OVER (ORDER BY buy_count DESC) AS rn,
        -- 计算被卖出的商品总种类数
        count(*) OVER () AS total_items,
        -- 计算大盘总销量
        sum(buy_count) OVER () AS total_buys
    FROM wangchao_data.ads_item_buy_count
)
SELECT 
    MAX(total_items) AS `总售出商品种类数`,
    MAX(total_buys) AS `大盘总销量`,
    SUM(buy_count) AS `前20%商品的总销量`,
    ROUND(SUM(buy_count) / MAX(total_buys) * 100, 2) AS `头部20%商品销量占比(%)`
FROM new_biao
WHERE rn <= total_items * 0.2;

FIFTEEN

1
2
3
4
根据底层数据测算，平台共售出 638,962 种商品，大盘总销量为 2,015,839 件。其中，排名前 20% 的头部商品总销量为 1,334,478 件，占全盘总销量的 66.2%。
这一数据打破了传统的“二八定律”，展现了电商平台极其典型的双轨生态：
头部聚集依然强劲（66.2%的现金牛）：前 20% 的核心 SKU 依然是平台的营收基石。供应链端必须采取“前置仓”策略，大促期间提前将这些爆款下沉到各省市中心仓，死保库存不穿透。流量端的主会场核心展位资源应向这部分商品倾斜，确保转化效率最大化。
极其庞大的“长尾效应”（贡献了剩余33.8%的销量）：在无限货架的电商模式下，尾部 80% 的商品依然贡献了超三分之一的单量。分发策略上，由于长尾商品种类浩如烟海，满足了个性化、小众需求，绝不能一刀切降权，必须重度依赖“千人千面”的个性化推荐算法。同时，长尾商品通常对价格不敏感、毛利更高，是平台拔高整体利润率的隐形金矿。

项目全景总结：数据驱动的“三维”价值闭环

回顾整个亿级电商用户行为分析项目，真正的难点并非跑通复杂的 SQL 代码，而是如何穿透海量的数据噪音，挖掘出能够直接指导业务增长的商业规律。

本项目从始至终贯穿了以下三个核心维度的分析哲学：

维度一：硬核的大数据处理与数仓思维

  • 破除工具局限：面对上亿级别的数据体量，果断摒弃传统的 MySQL (OLTP) 方案，引入 ClickHouse 列式存储引擎进行 OLAP 在线分析，实现了亿级数据的秒级查询响应。
  • 规范化数据清洗：遵循大厂数仓规范，完成从原始数据层 (ODS) 到明细数据层 (DWD) 的提纯，并利用底层 MergeTree 引擎进行分区与排序优化。
  • 复杂逻辑实现：熟练运用双层 CTE 表达式、Window Functions（窗口函数）、lead() 路径偏移等高级 SQL 语法，一步到位解决漏斗归因与帕累托占比计算等复杂业务需求。

维度二：敏锐的商业嗅觉与深度洞察

  • 拒绝虚假繁荣：在转化率计算中，敏锐地剥离了产生数十万次浏览的“异常爬虫流量”，还原了真实的商业转化大盘。
  • 重构经典模型：不迷信传统理论，通过数据验证了电商平台下“二八定律”的变体（头部 20% 贡献 66.2% 销量，长尾贡献 33.8%），并找出了“周末潮汐效应”和“冲动消费”的真实轨迹。
  • 捕捉 Aha Moment：精准定位到 PV 8-10 次的“犹豫谷底”和 PV 1-3 次的“狙击手效应”，为精细化运营找到了准确的切入点。

维度三：可落地的精细化增长策略

数据分析的终点永远是业务动作。本项目针对各大部门输出了明确的策略靶点：

  • 赋能运营端：基于 RFM 模型制定 4 大阶梯营销策略；锁定晚上 21:00 流量巅峰进行大促 Push；针对购后黄金区发放专享券。
  • 赋能产品端：在购物车链路增加“紧迫感”提示；在浏览犹豫期（看 8-10 个商品未下单）触发系统弹窗限时派券；针对异常高频账号接入“活人滑块验证”以节约服务器带宽。
  • 赋能供应链：基于帕累托验证，对头部 20% 的现金牛商品采取“前置仓”备货策略；利用关联挖掘进行爆款捆绑销售，拉升全盘客单价。

优秀的数据分析师不应只是业务部门的“取数机”，而应是拥有全局视角的“商业参谋”。让每一次查询都能指向一个业务痛点，让每一个数据都能转化为真实的利润，这正是我在数据分析道路上始终践行的准则。