SQL进阶实战技巧：如何分析浏览到下单各步骤转化率及流失用户数？

目录

0 问题描述

1 数据准备 

2 问题分析

3 问题拓展

3.1 跳出率计算 

3.2  计算从浏览商品到支付订单的不同路径的用户数，并按照用户数降序排列。

往期精彩

0 问题描述

统计从浏览商品到最终下单的各个步骤的用户数和流失用户数,并计算转化率

用户表结构和数据
假设我们有一个名为user_behavior_log的用户行为日志表,包含以下字段: 

字段名数据类型描述
user_idINT用户ID
behaviorSTRING用户行为，例如’view’，‘add_to_cart’，‘submit_order’，‘pay_order’
timestampBIGINT行为发生的时间戳
item_idINT商品ID

 用户行为数据示例：

user_idbehaviortimestampitem_id
1view1678886400101
1add_to_cart1678886460101
2view1678886520201
1submit_order1678886580101
2view1678886640301
2pay_order1678886700301
3view1678886760401
3add_to_cart1678886820401
3view1678886880501
4view1678886900601
4add_to_cart1678886960601
4submit_order1678887020601
4pay_order1678887080601

行为步骤定义：将用户从浏览商品到最终下单的步骤定义为浏览商品（view）、加入购物车（add_to_cart）、提交订单（submit_order）、支付订单（pay_order。 

1 数据准备 

（1）建表语句

CREATE TABLE user_behavior_log (
    user_id INT,
    behavior STRING,
    `timestamp` BIGINT,
    item_id INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

（2）插入数据

INSERT INTO user_behavior_log VALUES
(1, 'view', 1678886400, 101),
(1, 'add_to_cart', 1678886460, 101),
(2, 'view', 1678886520, 201),
(1,'submit_order', 1678886580, 101),
(2, 'view', 1678886640, 301),
(2, 'pay_order', 1678886700, 301),
(3, 'view', 1678886760, 401),
(3, 'add_to_cart', 1678886820, 401),
(3, 'view', 1678886880, 501),
(4, 'view', 1678886900, 601),
(4, 'add_to_cart', 1678886960, 601),
(4,'submit_order', 1678887020, 601),
(4, 'pay_order', 1678887080, 601);

2 问题分析

步骤1：用户行为序列构建：按照用户 ID 和时间戳对行为进行排序，构建每个用户的行为序列。

行为序列构建核心操作：

collect_list(behavior) OVER (PARTITION BY user_id ORDER BY timestamp)

 具体SQL如下：

WITH user_behavior_sequence AS (
    SELECT
        user_id,
        collect_list(behavior) OVER (PARTITION BY user_id ORDER BY `timestamp`) AS behavior_sequence
    FROM
        user_behavior_log
)
SELECT
    user_id,
    behavior_sequence
FROM
    user_behavior_sequence;

步骤2：按照用户行为顺序，构建转换漏斗矩阵

核心思路：将用户在各个阶段的行为状态用0-1进行标记，构建漏斗转换矩阵

 目的：将用户在各个关键行为步骤的状态以简洁的 0 - 1 标记形式呈现出来，方便后续对用户行为漏斗转化率等指标的计算和分析。

关键操作：array_contains(）函数进行判断

CASE WHEN array_contains(behavior_sequence, 'view') THEN 1 ELSE 0 END AS step_view,

具体SQL如下：

WITH user_behavior_sequence AS (
    SELECT
        user_id,
        collect_list(behavior) OVER (PARTITION BY user_id ORDER BY `timestamp`) AS behavior_sequence
    FROM
        user_behavior_log
),
user_funnel_matrix AS (
    SELECT
        user_id,
        -- 判断用户是否浏览过商品
        CASE WHEN array_contains(behavior_sequence, 'view') THEN 1 ELSE 0 END AS step_view,
        -- 判断用户是否加入过购物车（无论之前是否浏览过商品）
        CASE WHEN array_contains(behavior_sequence, 'add_to_cart') THEN 1 ELSE 0 END AS step_add_to_cart,
        -- 判断用户是否提交过订单（无论之前是否浏览过商品或加入过购物车）
        CASE WHEN array_contains(behavior_sequence,'submit_order') THEN 1 ELSE 0 END AS step_submit_order,
        -- 判断用户是否支付过订单（无论之前是否浏览过商品、加入过购物车或提交过订单）
        CASE WHEN array_contains(behavior_sequence, 'pay_order') THEN 1 ELSE 0 END AS step_pay_order
    FROM
        user_behavior_sequence
)
SELECT
    user_id,
    step_view,
    step_add_to_cart,
    step_submit_order,
    step_pay_order
FROM
    user_funnel_matrix;

 步骤3：转化率和流失用户数计算

WITH user_behavior_sequence AS (
    SELECT
        user_id,
        collect_list(behavior) OVER (PARTITION BY user_id ORDER BY `timestamp`) AS behavior_sequence
    FROM
        user_behavior_log
),
user_funnel_matrix AS (
    SELECT
        user_id,
        -- 判断用户是否浏览过商品
        CASE WHEN array_contains(behavior_sequence, 'view') THEN 1 ELSE 0 END AS step_view,
        -- 判断用户是否加入过购物车（无论之前是否浏览过商品）
        CASE WHEN array_contains(behavior_sequence, 'add_to_cart') THEN 1 ELSE 0 END AS step_add_to_cart,
        -- 判断用户是否提交过订单（无论之前是否浏览过商品或加入过购物车）
        CASE WHEN array_contains(behavior_sequence,'submit_order') THEN 1 ELSE 0 END AS step_submit_order,
        -- 判断用户是否支付过订单（无论之前是否浏览过商品、加入过购物车或提交过订单）
        CASE WHEN array_contains(behavior_sequence, 'pay_order') THEN 1 ELSE 0 END AS step_pay_order
    FROM
        user_behavior_sequence
)
    , funnel_step_map AS (
    SELECT  user_id,
        max('step_view') AS step_name,
        max(step_view) AS step_value
    FROM
        user_funnel_matrix
    GROUP BY user_id
    UNION ALL
    SELECT  user_id,
        max('step_add_to_cart') AS step_name,
        max(step_add_to_cart) AS step_value
    FROM
        user_funnel_matrix
    WHERE step_view = 1  --只有浏览过商品的用户才会统计后续步骤
    GROUP BY user_id
    UNION ALL
    SELECT  user_id,
        max('step_submit_order') AS step_name,
        max(step_submit_order) AS step_value
    FROM
        user_funnel_matrix
    WHERE step_view = 1 --只有浏览过商品的用户才会统计后续步骤
    GROUP BY user_id
    UNION ALL
    SELECT user_id,
        max('step_pay_order') AS step_name,
        max(step_pay_order) AS step_value
    FROM
        user_funnel_matrix
    WHERE step_view = 1 --只有浏览过商品的用户才会统计后续步骤
        GROUP BY user_id
)

SELECT
    step_name,
    COUNT(DISTINCT user_id) AS total_user,
    sum(step_value) user_count,
    COUNT(DISTINCT user_id) - sum(step_value) AS lost_user_count,
    cast(sum(step_value) * 1.0 /  COUNT(DISTINCT user_id) as decimal(18,2)) AS conversion_rate
FROM
    funnel_step_map
group by step_name ;

 CET funnel_step_map 表示step_name与step_value进行key-value映射的过程组成map集合便于后续计算（标 1 成对）具体结果如下：

计算的最终结果如下： 

上述代码，行转列部分也可以用stack()语句进行优化，优化后更简洁优雅

WITH user_behavior_sequence AS (
    SELECT
        user_id,
        collect_list(behavior) OVER (PARTITION BY user_id ORDER BY `timestamp`) AS behavior_sequence
    FROM
        user_behavior_log
),
user_funnel_matrix AS (
    SELECT
        user_id,
        CASE WHEN array_contains(behavior_sequence, 'view') THEN 1 ELSE 0 END AS step_view,
        CASE WHEN array_contains(behavior_sequence, 'add_to_cart') THEN 1 ELSE 0 END AS step_add_to_cart,
        CASE WHEN array_contains(behavior_sequence,'submit_order') THEN 1 ELSE 0 END AS step_submit_order,
        CASE WHEN array_contains(behavior_sequence, 'pay_order') THEN 1 ELSE 0 END AS step_pay_order
    FROM
        user_behavior_sequence
),
flattened_steps AS (
    SELECT
        user_id,
        step_name,
        max(step_value) step_value
    FROM
        (select user_id
              , step_view
              , step_add_to_cart
              , step_submit_order
              , step_pay_order
         from user_funnel_matrix
          WHERE step_view = 1 --只有浏览过商品的用户才会统计后续步骤(非法数据过滤)
         ) t1
    LATERAL VIEW
        stack(
            4,
            'view', step_view,
            'add_to_cart', step_add_to_cart,
           'submit_order', step_submit_order,
            'pay_order', step_pay_order
        ) sub_table AS step_name, step_value
  group by user_id,step_name
)

SELECT
    step_name,
    SUM(step_value) AS user_count,
    COUNT(DISTINCT user_id) AS total_user,
    COUNT(DISTINCT user_id) - SUM(step_value) AS lost_user_count,
    cast(SUM(step_value) * 1.0 / COUNT(DISTINCT user_id) as decimal(18,2)) AS conversion_rate
FROM
    flattened_steps
GROUP BY
    step_name;

 中间结果如下

最终结果如下：

通过stack函数和LATERAL VIEW简化了数据的重组过程，相比多个UNION ALL的方式更加简洁紧凑。 

3 问题拓展

3.1 跳出率计算 

  •  跳出率：流失用户数 / 到达该步骤的用户数
  • 即：（总用户数 - 到达该步骤的用户数） / 到达该步骤的用户数

 SQL如下：

SELECT
    step_name,
    SUM(step_value) AS user_count,
    COUNT(DISTINCT user_id) AS total_user,
    COUNT(DISTINCT user_id) - SUM(step_value) AS lost_user_count,
    cast(SUM(step_value) * 1.0 / COUNT(DISTINCT user_id) as decimal(18,2)) AS conversion_rate,
    --计算跳出率
    cast((COUNT(DISTINCT user_id) - SUM(step_value)) * 1.0 / SUM(step_value) as decimal(18,2)) AS bounce_rate
FROM
    flattened_steps
GROUP BY
    step_name;

3.2  计算从浏览商品到支付订单的不同路径的用户数，并按照用户数降序排列。

  1. 路径构建：使用concat_ws函数将每个用户的行为路径拼接成一个字符串。
  2. 路径统计：统计每个路径的用户数，并按照用户数降序排列。

步骤1：构建用户浏览路径 

WITH user_behavior_sequence AS (
    SELECT
        user_id,
        concat_ws('->',collect_list(behavior) OVER (PARTITION BY user_id ORDER BY `timestamp`)) AS user_path
    FROM
        user_behavior_log
)

步骤2：统计每个路径下的用户数，并按用户数降序排列

WITH user_behavior_sequence AS (
    SELECT
        user_id,
        concat_ws('->',collect_list(behavior) OVER (PARTITION BY user_id ORDER BY `timestamp`)) AS user_path
    FROM
        user_behavior_log
)

SELECT
    user_path,
    count(DISTINCT user_id) AS user_count
FROM
    user_behavior_sequence
GROUP BY
    user_path
ORDER BY
    user_count DESC;

往期精彩

别再为用户流失头疼啦！掌握SQL秘籍，从零构建用户流失风险评估模型

SQL进阶技巧：如何分析连续签到领金币数问题？

SQL进阶技巧：如何计算商品需求与到货队列表进出计划？

解锁SQL无限可能：如何利用HiveSQL实现0-1背包问题？

数据科学与SQL：组距分组分析 | 区间分布问题

SQL进阶技巧：如何分析互逆记录？| 相互关注为例分析

 ~~SQL进阶实战技巧系列~~  

SQL很简单，可你却写不好？每天一点点，收获不止一点点_sql断点-CSDN博客文章浏览阅读1.3k次，点赞54次，收藏19次。在写本文之前，我需要跟大家探讨以下几个话题。SQL进阶技巧：车辆班次问题分析SQL 进阶技巧：断点重分组应用求连续段的最后一个数及每段的个数【拼多多面试题】SQL进阶技巧-：字符串时间序列分析法应用之用户连续签到天数及历史最大连续签到天数问题【腾讯面试题】SQL进阶技巧：断点重分组算法应用之用户订单日期间隔异常问题分析SQL进阶技巧：如何对连续多条记录进行合并？【GroingIO 面试题】SQL进阶技巧：断点重分组算法应用之相邻时间间隔跳变问题分析。_sql断点https://flyingsql.blog.csdn.net/article/details/143609283https://flyingsql.blog.csdn.net/article/details/143609283

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/962134.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Autosar-Os是怎么运行的？（内存保护）

Autosar-Os是怎么运行的？（内存保护）

写在前面： 入行一段时间了，基于个人理解整理一些东西，如有错误，欢迎各位大佬评论区指正！！！ 1.功能概述 以TC397芯片为例，英飞凌芯片集成了MPU模块， MPU模块采用了硬件机…
阅读更多...
什么是Maxscript？为什么要学习Maxscript？

什么是Maxscript？为什么要学习Maxscript？

MAXScript是Autodesk 3ds Max的内置脚本语言，它是一种与3dsMax对话并使3dsMax执行某些操作的编程语言。它是一种脚本语言，这意味着您不需要编译代码即可运行。通过使用一系列基于文本的命令而不是使用UI操作，您可以完成许多使用UI操作无法完成的任务。 Maxscript是一种专有…
阅读更多...
（一）QT的简介与环境配置WIN11

（一）QT的简介与环境配置WIN11

目录 一、QT的概述 二、QT的下载 三、简单编程 常用快捷键 一、QT的概述 简介 Qt（发音：[kjuːt]，类似“cute”）是一个跨平台的开发库，主要用于开发图形用户界面（GUI）应用程序，…
阅读更多...
vim交换文件的作用

vim交换文件的作用

1.数据恢复：因为vim异常的退出，使用交换文件可以恢复之前的修改内容。 2.防止多人同时编辑：vim检测到交换文件的存在,会给出提示，以避免一个文件同时被多人编辑。 （vim交换文件的工作原理：vim交换文件的工作…
阅读更多...
SpringCloudGateWay和Sentinel结合做黑白名单来源控制

SpringCloudGateWay和Sentinel结合做黑白名单来源控制

假设我们的分布式项目，admin是8087，gateway是8088，consumer是8086 我们一般的思路是我们的请求必须经过我们的网关8088然后网关转发到我们的分布式项目，那我要是没有处理我们绕过网关直接访问项目8087和8086不也是可以&#xff1…
阅读更多...
将多目标贝叶斯优化与强化学习相结合用于TinyML

将多目标贝叶斯优化与强化学习相结合用于TinyML

论文标题 Combining Multi-Objective Bayesian Optimization with Reinforcement Learning for TinyML 作者信息 Mark Deutel, Friedrich-Alexander-Universitt Erlangen-Nrnberg, Germany Georgios Kontes, Fraunhofer IIS, Fraunhofer Institute for Integrated Circuits …
阅读更多...
Big Bird：适用于更长序列的Transformer模型

Big Bird：适用于更长序列的Transformer模型

摘要 基于Transformer的模型，如BERT，已成为自然语言处理（NLP）中最成功的深度学习模型之一。然而，它们的一个核心限制是由于其全注意力机制，对序列长度的二次依赖（主要是在内存方面）…
阅读更多...
26_DropDown使用方法

26_DropDown使用方法

创建下拉框DropDown 其中样板Template 是展示的选项框 其中Caption 是选中某个选项之后 展示的内容（Caption Text 说明文字/Caption Image 说明图示） 修改其 说明文字Caption Text 创建一个说明图示Image 设置为居左 而Item是 展示的选项框所展示的文字与…
阅读更多...
【redis进阶】redis 总结

【redis进阶】redis 总结

目录 介绍一下什么是 Redis，有什么特点 Redis 支持哪些数据类型 Redis 数据类型底层的数据结构/编码方式是什么 ZSet 为什么使用跳表，而不是使用红黑树来实现 Redis 的常见应用场景有哪些 怎样测试 Redis 服务器的连通性 如何设置 key 的过期时间 Redis …
阅读更多...
AI大模型开发原理篇-1：语言模型雏形之N-Gram模型

AI大模型开发原理篇-1：语言模型雏形之N-Gram模型

N-Gram模型概念 N-Gram模型是一种基于统计的语言模型，用于预测文本中某个词语的出现概率。它通过分析一个词语序列中前面N-1个词的出现频率来预测下一个词的出现。具体来说，N-Gram模型通过将文本切分为长度为N的词序列来进行建模。 注意：这…
阅读更多...
Linux工具使用

Linux工具使用

1.gcc/g的使用 1.1程序翻译的过程 ①预处理：展开头文件，替换宏，调节编译，去注释。 ②编译：将代码变成汇编语言 ③汇编：将汇编代码变成二进制不可执行的目标文件。 ④链接：将多个我写的多个…
阅读更多...
后端token校验流程

后端token校验流程

获取用户信息 前端中只有 await userStore.getInfo() 表示从后端获取数据 在页面中找到info对应的url地址，在IDEA中查找 这里是getInfo函数的声明，我们要找到这个函数的使用，所以点getInfo() Override public JSONObject getInfo() {JSO…
阅读更多...
Python 梯度下降法（二）：RMSProp Optimize

Python 梯度下降法（二）：RMSProp Optimize

文章目录 Python 梯度下降法（二）：RMSProp Optimize一、数学原理1.1 介绍1.2 公式 二、代码实现2.1 函数代码2.2 总代码 三、代码优化3.1 存在问题3.2 收敛判断3.3 函数代码3.4 总代码 四、优缺点4.1 优点4.2 缺点 Python 梯度下降法&#xff…
阅读更多...
excel如何查找一个表的数据在另外一个表是否存在

excel如何查找一个表的数据在另外一个表是否存在

比如“Sheet1”有“张三”、“李四”“王五”三个人的数据，“Sheet2”只有“张三”、“李四”的数据。我们通过修改“Sheet1”的“民族”或者其他空的列，修改为“Sheet2”的某一列。这样修改后筛选这个修改的列为空的或者为出错的，就能找到两…
阅读更多...
2024年数据记录

2024年数据记录

笔者注册时间超过98.06%的用户 CSDN 原力是衡量一个用户在 CSDN 的贡献和影响力的系统，笔者原力值超过99.99%的用户 其他年度数据
阅读更多...
7层还是4层？网络模型又为什么要分层？

7层还是4层？网络模型又为什么要分层？

~犬📰余~ “我欲贱而贵，愚而智，贫而富，可乎？ 曰：其唯学乎” 一、为什么要分层 \quad 网络通信的复杂性促使我们需要一种分层的方法来理解和管理网络。就像建筑一样，我们不会把所有功能都混在一起…
阅读更多...
JxBrowser 8.2.2 版本发布啦！

JxBrowser 8.2.2 版本发布啦！

JxBrowser 8.2.2 版本发布啦！ • 已更新 #Chromium 至更新版本 • 实施了多项质量改进 🔗 点击此处了解更多详情。 🆓 获取 30 天免费试用。
阅读更多...
论文阅读(十五)：DNA甲基化水平分析的潜变量模型

论文阅读(十五)：DNA甲基化水平分析的潜变量模型

1.论文链接：Latent Variable Models for Analyzing DNA Methylation 摘要： 脱氧核糖核酸（DNA）甲基化与细胞分化密切相关。例如，已经观察到肿瘤细胞中的DNA甲基化编码关于肿瘤的表型信息。因此，通过研究DNA…
阅读更多...
【综合决策模型】考虑生命周期评估LCA 与多目标优化MOO的综合决策模型MOOLCA

【综合决策模型】考虑生命周期评估LCA 与多目标优化MOO的综合决策模型MOOLCA

目录 1. 概念和目的1.1 生命周期评估 (LCA, Life Cycle Assessment)1.2 多目标优化 (MOO, Multi-Objective Optimization)1.3 MOOLCA 的目标2. MOOLCA 的组成2.1 生命周期评估模块2.2 优化模块2.3 决策支持模块参考Life Cycle Assessment with Multi-Objective Optimization (M…
阅读更多...
系统思考—蝴蝶效应

系统思考—蝴蝶效应

“个体行为的微小差异，可能在系统中引发巨大且不可预测的结果。” — 诺贝尔经济学得主托马斯谢林 我们常说，小变动带来大影响，这种现象，在复杂系统理论中被称为“蝴蝶效应”：即使极小的变化，也能在动态系…
阅读更多...
最新文章

Copyright @ 2022~2023