Z05 - 037、关键路径转化率分析（漏斗模型）一、关键路径转化率分析（漏斗模型）- - - - - - - - - - - - - - - - - - - - - - - - - - - - ＾至此，关键路径转化率分析（漏斗模型）完成。 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

48 阅读 0 评论 32 点赞

我是靠谱客的博主无情玉米，最近开发中收集的这篇文章主要介绍Z05 - 037、关键路径转化率分析（漏斗模型）一、关键路径转化率分析（漏斗模型）- - - - - - - - - - - - - - - - - - - - - - - - - - - - ＾至此，关键路径转化率分析（漏斗模型）完成。 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

初学耗时：0.5h

注：CSDN手机端暂不支持章节内链跳转，但外链可用，更好体验还请上电脑端。

一、关键路径转化率分析（漏斗模型）
1.1 需求分析。
1.2 模型设计。
1.3 开发实现。

记忆词：

关键路径转化率分析、漏斗模型

Z05 - 999、网站流量日志分析

ギ舒适区ゾ || ♂ 累觉无爱 ♀

一、关键路径转化率分析（漏斗模型）

1.1 ~ 需求分析。

转化：在一条指定的业务流程中，各个步骤的完成人数及相对上一个步骤的百分比。

1.2 ~ 模型设计。

定义好业务流程中的页面标识，下例中的步骤为：

Step1、 /item
Step2、 /category
Step3、 /index
Step4、 /order

1.3 ~ 开发实现

1.3.1 . 查询每一个步骤的总访问人数。

# 查询每一步人数存入 dw_oute_numbs
create table dw_oute_numbs as 
select 'step1' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103and request like '/item%'
union
select 'step2' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103and request like '/category%'
union
select 'step3' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103and request like '/order%'
union
select 'step4' as step,count(distinct remote_addr) as numbs from ods_click_pageviews where datestr='20181103and request like '/index%';

注：UNION ALL 将多个 SELECT 语句的结果集合并为一个独立的结果集。

1.3.2 . 查询每一步骤相对于路径起点人数的比例。

思路：级联查询，利用自 join

# dw_oute_numbs 跟自己 join
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join 
dw_oute_numbs rr;

# 每一步的人数/第一步的人数==每一步相对起点人数比例
select tmp.rnstep,tmp.rnnumbs/tmp.rrnumbs as ratio
from
(
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join 
dw_oute_numbs rr) tmp
where tmp.rrstep='step1';

1.3.3 . 查询每一步骤相对于上一步骤的漏出率。

# 自 join 表过滤出每一步跟上一步的记录
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join 
dw_oute_numbs rr
where cast(substr(rn.step,5,1) as int)=cast(substr(rr.step,5,1) as int)-1;

select tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as leakage_rate
from
(
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join 
dw_oute_numbs rr) tmp
where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1;

1.3.4 . 汇总以上两种指标。

select abs.step,abs.numbs,abs.rate as abs_ratio,rel.rate as leakage_rate
from 
(
select tmp.rnstep as step,tmp.rnnumbs as numbs,tmp.rnnumbs/tmp.rrnumbs as rate
from
(
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join 
dw_oute_numbs rr) tmp
where tmp.rrstep='step1'
) abs
left outer join
(
select tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as rate
from
(
select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from dw_oute_numbs rn
inner join 
dw_oute_numbs rr) tmp
where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1
) rel
on abs.step=rel.step;