大数据漏斗模型分析满级理解顶级运用动态SQL-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_68076599/article/details/144398177

以下是优化后的内容：

漏斗模型分析及动态SQL实现

一、目录

漏斗模型概念
漏斗分析流程
- 流程分解确定业务路径
- 数据统计
- 计算转化率
动态SQL优化

二、漏斗模型概念

漏斗模型是一种数据分析模型，用于描述用户在一系列操作流程中的转化率变化情况。它就像一个漏斗，用户在各个操作环节中逐渐流失。通过分析各环节的转化率，可以找出问题所在，进而优化业务流程，提升整体转化率。这种模型在电商、营销等众多领域都有广泛应用。例如，在电商领域，通过漏斗模型可以分析用户从浏览商品到最终购买的各个环节转化率，从而了解用户购买行为，优化商品展示和购买流程；在营销领域，可以分析用户从看到广告到参与活动的转化率，评估营销效果，调整营销策略。

三、漏斗分析流程

（一）流程分解确定业务路径

在这里插入图片描述
此图展示了漏斗模型的业务路径分解。漏斗分析就像一个分层的漏斗，每一层都有每层要达到的条件，想要满足下一层需要满足上一层的条件。以电商的漏斗模型为例，其业务路径可定义为：浏览 --> 加购物车 --> 下单 --> 支付。

下面是与该业务路径相关的数据表结构创建语句：

-- 用于存储用户在漏斗中各步骤信息的表
create table dws.user_buy_funnel(
  deviceid          string,    -- 设备编号(用户id)
  funnel_name       string,    -- 漏斗名称 [自定义]
  max_step          int,       -- 最大完成该漏斗的步骤
  funnel_starttime  string,    -- 漏斗统计数据的窗口开始时间
  funnel_endtime    string     -- 漏斗统计数据的窗口结束时间
)partitioned by (dt string);

-- 用于临时存储聚合数据的表，方便后续计算转化率
create table dws.user_funnel_aggr(
  funnel_name       string,    -- 漏斗名称
  step              int,       -- 漏斗的步骤数
  user_count        int,       -- 完成到该步骤的用户数
  funnel_starttime  string,    -- 漏斗统计数据的窗口开始时间
  funnel_endtimes string     -- 漏斗统计数据的窗口结束时间
)partitioned by (dt string);

在dws.user_buy_funnel表中，deviceid用于标识用户，funnel_name是自定义的漏斗名称，max_step记录用户在该漏斗中完成的最大步骤，funnel_starttime和funnel_endtime是统计数据的时间窗口。dws.user_funnel_aggr表则用于在计算转化率过程中存储中间数据，step表示漏斗的步骤，user_count是完成到该步骤的用户数量。

（二）数据统计

对每个步骤的数据进行统计时，要确保每个步骤完成的数量是在上个步骤完成的基础上。这里使用regexp_extract函数对每个用户进行到哪一步进行定位。

regexp_extract函数的基本语法是：regexp_extract(string, pattern, index)，其中string是要进行匹配的字符串，pattern是正则表达式模式，index是要返回的匹配组索引。在漏斗模型数据统计中，它用于从用户操作记录字符串中提取符合漏斗步骤模式的信息。

以下是一个数据统计的SQL示例：

select deviceid,
       "秒杀活动购物漏斗模型",
       case
           when regexp_extract(concat_ws(",",sort_array(collect_list(concat(ts,eventid)))),'.*(display),.*(addCart),.*(order),.*(pay)',4) == 'pay' then 4
           when regexp_extract(concat_ws(",",sort_array(collect_list(concat(ts,eventid)))),'.*(display),.*(addCart),.*(order)',3) == 'order' then 3
           when regexp_extract(concat_ws(",",sort_array(collect_list(concat(ts,eventid)))),'.*(display),.*(addCart)',2) =='addCart' then 2
           when regexp_extract(concat_ws(",",sort_array(collect_list(concat(ts,eventid)))),'.*(display)',1) == 'display' then 1 else 0
               end max_step,
       min(ts),
       max(ts)
from tmp.tmp_event_log_detail where eventid in ('display','addCart','order','pay')
group by deviceid;

在上述SQL中，concat_ws(",",sort_array(collect_list(concat(ts,eventid))))是将用户操作记录的时间戳ts和事件IDeventid进行拼接和排序处理后的字符串。case when语句中的每个条件判断是根据regexp_extract函数提取的结果来确定用户在漏斗中的最大步骤。

（三）计算转化率

首先创建用于存储转化率相关数据的表结构：

create table ads.funnel_rate(
  funnel_name      string,    -- 漏斗名称
  step             int,        -- 步骤
  converted_user   int,        -- 转化人数
  conversion_rate  double,     -- 转化率
  completion_rate  double,     -- 总转化率
  funnel_starttime  string,    -- 漏斗统计数据的窗
  funnel_endtime    string     -- 漏斗统计数据的窗
)partitioned by (dt string);

在这个表中，funnel_name和step分别表示漏斗名称和步骤，converted_user是完成到该步骤的转化人数，conversion_rate是该步骤的转化率，completion_rate是总转化率，funnel_starttime和funnel_endtime是统计数据的时间窗口。

计算转化率的SQL语句如下：

insert into table ads.funnel_rate partition (dt='2022-11-25')
select funnel_name,
       step,
       user_count,
       round(user_count/lag(user_count,1,user_count) over (partition by funnel_name order by step),2) conversion_rate,
       round(user_count/first_value(user_count) over (partition by funnel_name order by step),2) completion_rate,
       funnel_starttime,
       funnel_endtime
from dws.user_funnel_aggr where dt='2022-11-25';

在上述SQL中，round函数用于将计算结果进行四舍五入保留两位小数。lag函数用于获取分区内上一行的值，这里用于计算相邻步骤之间的转化率。first_value函数用于获取分区内第一行的值，用于计算总转化率。

四、动态SQL优化

在开发过程中，发现不同业务路径的漏斗需要编写类似的SQL语句，为了省去重复代码编写，采用Python和Spark结合实现动态SQL的编写。

（一）代码整体功能概述

下面的Python代码实现了根据数据库中存储的漏斗业务路径信息自动拼接SQL语句，并执行相应的查询和插入操作。

（二）代码详细解释

import sys
from Funnell import Funnel
from utils.SparkUtils import *

"""
------------------------------------------
  Description : TODO：
  SourceFile : ToDwsUserBuyFunnel
  Author  : BJ
  Date  : 2024/12/10
-------------------------------------------
"""

if __name__ == '__main__':
    # 获取SparkSession
    spark: SparkSession = SparkUtils.getSparkSession("漏斗分析", "xxx")
    property = {"user": "xxx", "password": "xxxx", "driver": "com.mysql.cj.jdbc.Driver"}
    # 读取数据库中漏斗业务路径信息表
    jdbcDF = spark.read.jdbc("jdbc:mysql://xxx:3306/spark_project", "t_funnel", properties=property)
    jdbcDF.show()
    jdbcDF.createOrReplaceTempView("t_funnel")

    if len(sys.argv) >= 2:
        time1 = sys.argv[1]
        funnel_name = sys.argv[2]
    else:
        print("请输入参数, 格式为yyyy-MM-dd")
        exit(-1)
    print(time1)

    # SQL拼接
    funnelDf = spark.sql(f"""
          select * from t_funnel where funnel_name='{funnel_name}'
        """)

    list01 = funnelDf.rdd.map(
        lambda row: Funnel(row.id, row.funnel_name, row.step, row.eventid, row.eventname)).collect()

    sql = "case "
    length = len(list01)

    list01.reverse()  # 反转列表，方便后续从后往前拼接SQL
    print(list01)
    size = len(list01)
    strevent = ""
    for funnel in list01:

        # 拼接里面的正则表达式  '.*(display):.*(addCart):.*(order):.*(pay)'
        str01 = ""
        for i in range(size):
            fun1 = list01[3 - i]
            str01 += ".*(" + fun1.eventId + "):"

        print(str01[0:len(str01) - 1])
        size -= 1
        sql += "when regexp_extract(concat_ws(':',sort_array(collect_list(concat(ts,'_',eventid)))),'" + str01[0:len(
            str01) - 1] + "'," + str(funnel.step) + ") =='" + funnel.eventId + "' then " + str(funnel.step) + " "

        strevent += "'" + funnel.eventId + "',"

    sql += " else 0 end"
    print(sql)

    fullsql = f"""
           insert into table dws.user_buy_funnel partition (dt='{time1}')
            select deviceid,
                   '{funnel_name}',
                   {sql}  max_step,
                   min(ts) funnel_starttime,
                   max(ts) funnel_endtime
                   from tmp.tmp_event_log_detail
                   where eventid in({strevent[0:len(strevent) - 1]}) and dt='{time1}'
                   group by deviceid 
                   """
    print(fullsql)

    spark.sql(fullsql).show()

    print(spark)

在上述代码中，首先获取SparkSession并读取数据库中的t_funnel表，该表存储了漏斗业务路径信息。然后根据传入的时间参数time1和漏斗名称funnel_name进行SQL拼接。在拼接过程中，先将list01列表反转，以便从后往前拼接case when语句中的条件判断。对于每个漏斗步骤，通过循环拼接正则表达式模式，并将其用于regexp_extract函数的参数中，以确定用户在漏斗中的步骤位置。最后将拼接好的SQL语句执行，将结果插入到dws.user_buy_funnel表中。
以上可以看得出来其实sql变化的部分就是在于case情况里面的改变，我把每个业务路径只要写入到mysql，代码就可以读数据库进行sql的自动拼接，执行需要的sql语句，省去了大量的重复编写，后续代码不需要动态改变。