网站流量日志分析--统计分析--漏斗模型转化分析实现|学习笔记

开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）： 网站流量日志分析--统计分析--漏斗模型转化分析实现】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/696/detail/12244

网站流量日志分析--统计分析--漏斗模型转化分析实现

1、漏斗模型转化率计算。

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

Step1、/item

Step2、/category

Step3、/index

Step4、/order

第一步 item，第二步包含 category，以此类推。

2、打开参考资料，点击路径转换漏斗模型分析。

原来采集的数据中已经不满足于需，所以里面人为的伪造了一些数据，把数据导入到数据表中，在参考资料中，点击数据采集，漏斗模型原始数据。

它不是真实数据而是伪造的假的数据，把数据导入到表中，打开表，表中有 session，有步骤号，时间，访问的页面。

数据看起来就非常假，因为它是用 for 循环写的，把数据导到表中，打开 sql 文件进行执行，需要把文件上传到 linux 服务器上，拖拽它进行上传。

3、使用 load data 命令，把数据从本地加载到表中，在本地文件里，不需要进行覆盖，分区是20181103。

load data local inpath '/root/hivedata/click-part-r-00000' intotable ods_click_pageviews partition (datestr= '20181103') ;

在 hive 终端中把伪造的数据导入进来，统计当中每一步的人数根据讲义需求，以访问 item 作为第一步，category 作为第二步，进行伪造，重点是 UNION All 语法，在 hive 中它支持创建表通过 select

查询，create table 创建一个表。

每一个步骤的总访问人数

UNION All 将多个 SELECT 语句的结果集合并为一个独立的结果集

create table dw_ oute_ numbs as没有指定字段和类型，属性，数据，有两个字段，一个字段是 step，一个叫 numbs，会有四条记录，分别来自于四条查询语句，它可以将结果集进行合并。

select 'step1' as step, count (distinct remote_ addr)

as numbs from ods_ click_ pageviews where datestr='20181103' and request like '/item

如果只跟一个比较好理解，意味着表的字段跟长语句相关，长语句返回两个字段，一个是 step，一个是 numbs，数据就是返回的字段。

union all

select 'step2' as step, count (distinct remote addr) as numbs from ods_ click pageviews where datestr='20181103' and request like '/category

union all

select 'step3' as step , count (distinct remote_ addr)

as numbs from ods_ click_ _pageviews where datestr='20181103' and request like '/order

union all

select 'step4' as step, count (distinct remote_ addr) as numbs from ods_ click_ pageviews where datestr= ' 20181103'

and request like '/index

执行，得到表的结果就得到了当中每一步的人数，输入select*

from dw_ oute_ numbs;

可以得到转化人数的分析，

 

第一步有1029人，第二步有1029人，第三步有1028人，第四步有1018人，要想得到每一步相对于上一步的转化率，相对于第一步的转化率，都跟之前的数据相关，所以针对这类问题的突破点就是自己跟自己 join，总共有四条记录，都需要在一起，都需要加过滤条件，不加过滤条件，四条和四条进行 join，最终结果是16条。

4、查询每一步骤相对于路径起点人数的比例

--级联查询，自己跟自己 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；

起别名，单独看有两个字段，join 完有四个字段。

执行，没有过滤条件，所以有16个结果。

 5、--每一步的人数/第一步的人数==每一步相对起点人数比例

相对于第一步需要一一二一三一四一的数据，每一步都跟第一步在一行，在这一行直接相除即可。前四条数据的右半部分rrstep都是第一步，如果给四条数据，用rnnumbs除以rrnumbs就计算出每一步相对第一步的转化率，比如用1029除以1029，第一步对第一步，第二步1029除以第一步1029是第二步的，算出每一步相对于第一步的转化率，因此在 join 的基础上加where tmp . rrstep= ' step1 ' ;

rnnumbs除以 rrnumbs 得到最终结果。

seloct tmp. rnstep, tmp . rnnumbs/ tmp . rrnumbs as

ratio得到结果

From

(

Select rn. Step as rnstep, rn . numbs as rnnumbs , rr . step

InnorJoin

dw_ oute_ numbs rr)tmp起别名

where tmp . rrstep= ' step1 ' ;

执行，得到结果，每一步相对于第一步的绝对转化。

如果一家公司在企业中转化率达到这种程度，基本上没有人流失，计算每一步相对第一步的转化，流失率等于1-转化率。

6、计算每一步跟上一步的。

二和一在一行，三跟二在一行，四跟三在一行，相差的在一行才能直接相除得到相关的结果，根据数据发现，前面的字段减去一等于后面的字段，把数据拿出来就可以得到的结果，二一可以得到第二步相对第一步，二一就是一二反过来相除，三四四三，结果不止一个，因此定论为例，找出二一的，三二的，四三的，不能直接用字段减一，因为字段不是数字而是字符串，截取字段中的最后一个数字，三减一的等 于 rrstep 的最后一个数字才符合。

7、查询每一步骤相对于上一步骤的漏出率

--首先通过自 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_ nunbs rr

where cast (substr (rn.step,5,1) as int)=cast (substr(rr.step,5,1) as int) -1;第五位截取一个，把它变成 int 类型，需要前面的字段等于后面字符串减一，rn setp 等于 rr setp 减1，找一二，二三，三四，相等完之后再结果相除数据算出每一步相对上一步的转化率

截取有substr函数，从哪里开始截取，第五位截取一个，问题在于截取一个之后返回的数字并不是字符类型的，本身是字符串针对字符串进行截取返回的还是字符串，所以需要把数字做类型的转换，在 hive 中通过内置的函数可以进行类型的转换，所以里面比较难的原因在于里面有很多 hive 的内置函数，as 前面是原来类型，as 后面是其他类型。

注意: cast 为 Hive 内置函数类型转换

select cast(1 as float) ; --1.0变成服务点类型

select cast('2016-05-22' as dnte) ; --2016-05-22把字符串变成时间类型

8、join 完之后找规律非常方便，但是核心的突破点在于如何进行 join，得到每一步和上一步的人数，再相除得到最终结果。

每一步相对上一步的的转化率就是相对转化，流失用一减去它即可。把两个结果保存在表中，这就是转化分析，找规律很集中很简单，重点突破点还是自己跟自己 join，只要发现数据的需求中，指标中涉及到当前行和之前行，当前行和上一行，当前行和第一行的规律，立马采用 join，这就是漏斗模型的转化分析。