数仓 DW层 用户固定漏斗分析主题

数仓 DW层 用户固定漏斗分析主题

1. 背景

1. 在app业务和流程设计中，页面和页面之间，流程和流程之间的每个环节并不是100%流转下去的。例如大家使用购物App，从商品详情页到下单页面，到支付页面，中间可能随时因为各种原因跳出。这时候就涉及到漏斗分析，也就是分析这些环节之间的转换率。
2. 在当今快节奏开发，推崇敏捷开发时代，不管是什么app，都不会一开始就做成尽善尽美，反而只会有核心功能，然后接入比较完整的数据分析和观察工具后，每个迭代周期根据线上数据分析和用户反馈进行迭代优化。这样就可以使用2周一个迭代的快速开发优化出最终想要的app。
3. 只不过使用上述方式，有一个很重要的前提，就是需要有一个技术团队可以支撑这么快速的迭代，同时有足够的数据采集和分析工具可以提供相对完善完整的线上数据分析用来支撑决策。
4. 本文涉及的漏斗分析，用在关键环节和页面分析时是有很大作用的。
5. 大家都知道解决问题的前提是发现问题，而漏洞分析就是用来发现问题的。如果商品详情页到下单页面的转换率太低，漏斗分析就可以看出来。
   PS：
   业内经典案例有如将支付按钮变大，支付按钮红色加深等方式让下单率提升50%甚至更高的案例。就是因为发现流程的页面漏斗数据异常，进而做优化而得来的。

2. 案例

1. 模拟

– 漏斗模型的定义：LD_X
step1: pageView,pageid=‘786’
step2: search, keyword 包含 ‘智能手机’
step3: addCart, pname 包含 ‘手机’
step4: submit

2. 数据样例

– 目标表的数据样例：
2020-10-07,LD_X,step1,g02
2020-10-07,LD_X,step4,g01

3. 计算

WITH tmp AS
(
SELECT
guid,
concat_ws(',',sort_array(collect_list(concat_ws('_',cast(ts as string),eventid)))) as  event_lst
FROM dwd.app_action_detail
WHERE  dt='2020-10-07'
       AND (
            (eventid = 'pageView' and properties['pageid']='786')
         or (eventid = 'search' and instr(properties['keyword'],'智能手机')>0)
         or (eventid = 'addCart' and instr(properties['pname'],'手机')>0)
         or (eventid = 'submit')
      )
GROUP BY guid
)

INSERT INTO TABLE dws.app_ld_user_compstep PARTITION (dt='2020-10-07')
SELECT   
'2020-10-07' as cacl_dt,
'LD_X'       as ld_name,
guid,
case 
  when regexp_extract(event_lst,'.*?(pageView).*?(search).*?(addCart).*?(submit).*?',4)='submit' then 4 
  when regexp_extract(event_lst,'.*?(pageView).*?(search).*?(addCart).*?',3)='addCart' then 3 
  when regexp_extract(event_lst,'.*?(pageView).*?(search).*?',2)='search' then 2 
  when regexp_extract(event_lst,'.*?(pageView).*?',1)='pageView' then 1  
  else null 
end as compstep  
FROM tmp
;

4. 思路分析

• 如果行为数据中，需要拿到一系列页面浏览数据的漏斗，则数据展示出来必定是根据时间排序，用户依次进行了页面p1，p2,p3,p4的浏览
• 则在这个过程中，可以直接提取出关键信息，将用户的页面浏览id提取出来，使用concat_ws拼接为字符串，再转为数组，再拼接起来，这样一来就可以得到一个很长的字符串，然后使用正则表达式去匹配，就饿可以得到一系列页面浏览数据。

这个处理思路最关键的思路就是将列数据转行，用到了concat和collect as，最终将数据转为了一个长字符串

注意行转列和列转行是sql中常见做法，主要是为了将数据转换。