用SQL进行路径分析

填充空白 2023-04-09

798

背景：

路径分析，就是把用户在APP或者网站等的访问行为流转，多用来辅助分析用户行为偏好、优化改进产品设计。大部分第三方数据平台都会直接提供路径分析的功能，比如国内的神策,海外的Google Analytics。但是如果因为条件所限无法使用这些平台，或者觉得这些平台提供的路径分析局限性比较大，我们也可以基于用户的原始行为数据，用SQL一步步徒手做出来路径分析，并作桑基图。

首先仍然假设我们有一张这样的用户行为数据表events：event,user_id,time,

即包含用户id,事件名和时间。

本文会包括的内容：

1首先选择路径中要包含的事件，并形成session。

2根据分析目的进行路径分析。

1）不选择明确的开始事件和结束事件，只看不同会话的流转走向。

2）以某事件A为开始事件进行路径分析

3）以某事件A为结束事件进行路径分析

3.路径分析畅想

一、选择路径分析要包含的事件，并生成session

选择事件表中包含所选择的事件。根据session条件限制(一般为时间间隔)，即用户行为序列中相邻2个事件超过窗口期。则为新的会话。

session具体思路，参考历史文章，

用SQL进行session分析

SQL进行session分析2

用SQL进行session分析3

为什么会有这么多session查询的文章，后边的每篇是对前序文章的修正和补充。

本文在之前session的逻辑基础上，获取了每个session的session_id,在session中的位置，当前事件名，时间，当前事件的后一个事件和前一个事件名，以方便后续计算。如对session逻辑较为清楚，可仅查看输出的session表的数据表结构，然后跳过此环节。

以下SQL代码参考：

select session_id,sess_position as rank,event as current_node,time1,
lead(event) over (partition by session_id order by time asc) as target_node,
lag(event) over (partition by session_id order by time asc) as qianyige_node
from
(select sess_1st.user_id,
concat(cast(sess_1st.user_id as string),'-',cast(sess_1st.session_id as string)) as session_id,
sess_1st.currentsession_starttime,
sess_1st.currentsession_endtime,
sess_1st.currentsession_endtime-sess_1st.currentsession_starttime as sess_length,
a.time,a.time1,a.event,
row_number() over (partition by sess_1st.user_id,sess_1st.session_id order by a.time asc) as sess_position,
count() over (partition by sess_1st.user_id,sess_1st.session_id) as sess_depth,a.len
from
(select user_id,event,time,max_time,
currentsession_starttime,
if(lead(beforesession_endtime,1) over (PARTITION BY user_id ORDER BY time asc) is null,
max_time,lead(beforesession_endtime,1) over (PARTITION BY user_id ORDER BY time asc)) as currentsession_endtime,
row_number() over (partition by user_id order by time asc) as session_id
from
(SELECT
user_id,event,time,max_time,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>1200,1 , 0) as se,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>1200,lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc),null) as beforesession_endtime,
if (a.end_time - a.begin_time is null or a.end_time - a.begin_time>1200,EXTRACT(EPOCH FROM time),null) as currentsession_starttime
FROM
(SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS end_time,
  lag(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS begin_time,
  max(EXTRACT(EPOCH FROM time)) over (PARTITION BY user_id) as max_time
  FROM events
  WHERE  to_date(time)='2023-04-05' and
  event in ('$AppStart','inRoom','CommodityDetail','AddToShoppingCart','roomaddToCart'))a
  order by user_id,time asc
)sess where se=1
)sess_1st
left join
(SELECT
user_id,event,time,
a.end_time - a.begin_time,EXTRACT(EPOCH FROM time) as time1,
case when a.end_time - a.begin_time<=1200 then a.end_time-a.begin_time else null end as len
FROM
(SELECT user_id,event,time,EXTRACT(EPOCH FROM time) AS begin_time,
  lead(EXTRACT(EPOCH FROM time),1,null) over (PARTITION BY user_id ORDER BY time asc) AS end_time
  FROM events
  WHERE  to_date(time)='2023-04-05' and
event in ('$AppStart','inRoom','CommodityDetail','AddToShoppingCart','roomaddToCart'))a
order by user_id,time asc
)a
on sess_1st.user_id=a.user_id and a.time1>=sess_1st.currentsession_starttime and a.time1<=sess_1st.currentsession_endtime
order by a.user_id,a.time asc)a

输出结果命名为session表，如下：

二、根据分析目的进行路径分析

1.不选择明确的开始事件和结束事件，只看不同会话的流转走向。

缺点是可能每个会话的开始行为不一样，可能有些会话中一开始才进入了商详页开始购买路径，有些用户第10步才进入商详页才开始购买路径。整体用户行为路径图较散。意义不大。

此处做简单拆解，思路：

统计一中每种路径的具体“流量”是多少。

有多少是第一步从A到了B节点。

有多少是第一步从B到了C节点。

....

有多少是第2步从B到了C节点。

有多少是第2步从C到了D节点。

具体代码如下：

select rank,current_node,target_node,count(distinct session_id) as value
from
session a
group by 1 ,2,3
order by 1 asc ,4 desc

输出结果如下：

然后根据上述输出结果，绘制桑基图，桑基图的绘制在此处不探讨。参考2.2中。

以下wumubiao_stas_0409_Sankey_Diagram1为所有会话的桑基图。

以下wumubiao_stas2_0409_Sankey_Diagram1为去掉target_node=null的桑基图。

2.以某事件A为开始事件分析。这种有明确的分析目的，是想要查看开始事件A之后用户是如何流转的。

2.1输出A事件开始后，后续每层每个节点的会话数。及其流转的目标事件的会话数。

思路：

1）查找含事件A的session，并将该session中的第一个A事件作为session的第一个事件。当一个session中含多个A事件时，将该session中的第一个A事件作为session的第一个事件，其他A事件仍为该session的序列中的行为而非第一个事件。

这里以$AppStart为开始事件。找出这样的session。

select session_id,min(time1) as min_time
from session a
where current_node='$AppStart'
group by session_id

2）排序行为序列。将2.1的1）中找到的session，匹配查找对应的事件，并对session中的事件进行排序。

select a.session_id,row_number() over (partition by b.session_id order by b.time1 asc) as rank,b.current_node,b.target_node,b.time
from
(select session_id,min(time1) as min_time
from session a
where current_node='$AppStart'
group by session_id)a
left join session b
on a.session_id=b.session_id and b.time1>=a.min_time

3）计算每种路径流转的具体流量。每层流转时，开始事件，结束事件，及其流量数（会话数）。整体代码如下：

select rank,current_node,target_node,count(distinct session_id) as value
from
(select a.session_id,row_number() over (partition by b.session_id order by b.time1 asc) as rank,b.current_node,b.target_node,b.time
from
(select session_id,min(time1) as min_time
from session a
where current_node='$AppStart'
group by session_id)a
left join session b
on a.session_id=b.session_id and b.time1>=a.min_time)a
group by 1 ,2,3
order by 1 asc ,4 desc

输出结果如下示例：

2.2用python运行后输出桑基图.并跟系统软件结果对比。

绘制桑基图可以用tableau，python。对应教程很多，自行网上学习。这里就不在具体指导了。

那么如何用python绘制桑基图了。简单来说，是把数据整理成对应的格式即可。即包含数据节点及节点之间的关系（links）。

节点就是流入流出的节点名。节点之间的关系（links），包含source，target，value.分别表示关系的源头，目标，及其流转的数值大小；

以下是本次的python代码供参考

import pandas as pd
from pyecharts.charts import Sankey
from pyecharts import options as opts
import os


###先清除下数据
def clear():os.system('cls')
clear()


# 读取CSV文件
drawable_df = pd.read_csv('sta_0409.csv')


# 低于多少流量的路径不绘制
value_threshold = 0
# 绘制到路径的第几层
node_level = 10


# 保存输出的文件名称
title = 'sta_0409_Sankey_Diagram1'
links = []
nodes_set = set()


for index, series in drawable_df.iterrows():
    # 将节点的名称加工成 <rank>-<node_name> 形式
    source = f'{int(series["rank"])}-{series["current_node"]}'
    target = f'{int(series["rank"])+1}-{series["target_node"]}'
    value = series['value']    
    if value < value_threshold:
        continue
    else:
        # 构造符合pyecharts要求的links数据
        link = {
            'source': source,
            'target': target,
            'value': value
        }
        nodes_set.add(source)
        nodes_set.add(target)
        links.append(link)
# 构造符合pyecharts要求的nodes数据
nodes = [{'name': x} for x in sorted(nodes_set, key=lambda x: int(x.split('-')[0]))]
# 使用pyecharts生成桑基图
diagram = (
    Sankey()
    .add('', # 图例名称
         nodes,  # 节点数据
         links, # 边和流量数据
         # 设置透明度、弯曲度、颜色
         linestyle_opt=opts.LineStyleOpts(opacity = 0.2, curve = 0.5, color = "source"),
         # 标签显示位置
         label_opts=opts.LabelOpts(position="right"),
         # 节点之前的距离
         node_gap = 30,
        )
    .set_global_opts(title_opts=opts.TitleOpts(title = title))
)
os.makedirs('export/', exist_ok=True)
diagram.render(f'export/{title}.html')

输出结果为在export文件夹下的title=sta_0409_Sankey_Diagram1的html文件。

这里展示用第三方平台做出的桑基图

python运行后输出桑基图结果sta_0409_Sankey_Diagram1如下：

，

如你所见，因为上述2.2步骤中输出的csv文件中每层有target_node=null，即流失部分(该层路径流转时，结束事件未在所选事件中)，都显示在最后了。但似乎这样也是合理的。用户的最后一个行为都是流失，只是有的是第2步，有的是第3步....等。

为了与上述系统软件的保持一致，手动拖动每一步的流失事件得到如下图sta_0409_Sankey_Diagram1，(把每一步的流失自动绘制在对应的层，好像得重新写，我也不会了。为了对应上所以就手动拖了下)：

如果只是想看行为流，不显示流失事件时，可将target_node=null的数据2.2查询的结果中直接删除，绘制桑基图如下(sta_0409_2_Sankey_Diagram1)：

3.以某事件A为结束事件，分析用户之前的行为是如何流转到该结束事件的.

3.1输出A事件结束前，每层每个节点的会话数。及其前一个事件的会话数。

思路

1）查找含事件A的session，并将该session中的最后一个A事件作为session的最后一个事件。

2）当一个session中含多个A事件时，将该session中的最后一个A事件作为session的最后一个事件，其他A事件仍为该session的序列行为而非最后一个事件。重新对session中的事件进行排序。

3）计算每层流转进来时，当前事件（开始事件），前序事件（结束事件）及其会话数。

因为是以结束事件往前分析的。所以这里对session中的事件倒序排列。查询代码如下

select rank,current_node,qianyige_node as target_node,count(distinct session_id) as value
from
(select a.session_id,row_number() over (partition by b.session_id order by b.time1 desc) as rank,b.current_node,b.qianyige_node,b.time
from
(select session_id,max(time1) as max_time
from session a
where current_node='$AppStart'
group by session_id)a
left join session b
on a.session_id=b.session_id and b.time1<=a.max_time)a
group by 1,2,3
order by 1 asc ,4 desc

结果如下：

3.2用python运行后输出桑基图.并跟3方系统结果对比。

因为python做图尚未学会从右往左画图。所以还是从左往右的流转。python代码为仅对2.2中的代码改了读取的csv文件和输出的文件名。其他一样。

# 读取CSV文件
drawable_df = pd.read_csv('sta_ni2_0409.csv')


# 保存输出的文件名称
title = 'sta_ni2_0409_Sankey_Diagram1'

输出桑基图如下：

结合3方系统输出结果对比：

3.路径分析畅想

在实际业务分析时，比如一个购买转化，用户可能通过icon进入了菜单首页，进入了商详页，然后又查看了另一个商详页。中间连续查看多个商详页，最后去下单。这个过程中很多用户可能是一直在商详页之间流转。而且很多时候，路径流转因为某个环节可能是多个支流的混入，通过以上分析就很难分析出明显的主路径，这时候就需要做些处理。

1)当中间连续出现几个同样的事件时，事件流转会存在多层行为流转时，这种要求在做session处理时，对相同行为做折叠(连续几次做字段标记)或直接对连续出现相同的事件做去重处理。

2)查看表现突出的Top10路线,这种分析对业务更有意义。

具体实现之后有机会再探讨吧。

祝大家好运！

文章转载自填充空白，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

用SQL进行路径分析

评论