漏斗逻辑说明

漏斗分析逻辑说明

[TOC]

步骤说明

1、第一步骤的事件通过筛选条件（如果有）获得触发该事件人数，和设备ID及统计时间范围内最新的日期（1、默认条件为查询事件的时间范围。2、事件，设备ID和统计日期组合唯一）
2、如果漏斗统计共五步，第二步骤的触发事件数，需要用第一步骤中的设备ID来匹配第二步骤的设备ID并且用第一步骤中的最新日期匹配窗口期，如果设备ID匹配并且日期没有超过窗口期设定那么该事件数量即为第二步骤的事件数，其他步骤以此类推。
比如：有二个事件，那么第一个事件有1，2，3三个设备且每个设备最新的日期都是2020年2月2日，第二个事件有2，3，4，5四个设备，其中3设备的最新时间是2020年2月10号，其他2，4，5设备的最新时间为2020年4月，窗口期为7天，那么在统计第二个事件的时候，只有2设备满足设备ID匹配和窗口期范围匹配的条件，所以第二个事件是1个转化，转化率就是 1/3=33%.
3、漏斗分析最多支持10个事件，比如步骤为：A，B，C，D，E，F，G，H，I，j，那么不重复单向组合最多为45个。
4、（注意）前期需实现单链漏斗 a->b->c->d->e->f->g->h->i->j，b->c->d->e->f->g->h->i->j，以此类推，无需实现单步骤跨其他步骤统计（单步和跨步统计）

跨步统计图例：

单步统计图例：

基础结构

字段名

备注

事件英文名

数据库中的字段名称

事件中文名

通过英文名关联总事件中的中文名称

事件数

该事件在指定筛选条件下的去重设备ID数量

分组

可参考业务文档中的【分组项】说明

事件顺序

漏斗步骤顺序ID，默认第一步骤中的事件为：1001开始，第二步骤的事件为：2001开始

统计日期

统计日期范围（统计累计数据的时候为日期区间，统计趋势的时候，为每日数据）

总计

每个分组下的所有事件数的总和

转化率

各分组对应的事件数在上一级事件数中的百分比

交集图例

说明：关联总事件中文名称

名词解释

名称

说明

全局筛选

针对所有步骤都生效的筛选条件

事件筛选

针对独立事件生效的筛选条件

窗口期

第一个事件中的独立用户（设备ID去重）在统计日期内的最后触发时间，

到第二个事件中用户触发事件的最后触发事件的时间范围（归因窗口）

漏斗统计说明：

累计图表用例

累计逻辑用例

趋势图表用例

趋势逻辑用例

查询语句

第一步骤

说明：查询出第一个事件对应的设备信息和事件数量

第二步骤

说明：根据第一步骤查询出来的设备ID匹配第二个事件中的设备ID并统计窗口期内的事件触发人数

图表语句（趋势）

SELECT grouptype,min(sum)/max(sum) as 'per',min(sum) as 'groupmin',max(sum) as 'groupmax',sum(sum) as 'groupsum',atime from 
(
SELECT
	devinfo as 'grouptype',
			count( DISTINCT devid ) AS 'sum',
			from_unixtime( atime, '%Y-%m-%d' ) AS atime 
		FROM
			`all-impala-1700` 
		WHERE
			devplatform = 'android' #事件所属平台
			AND action IN ( 'fbLoginSucccessful') #第一个步骤对应的事件
			AND from_unixtime( atime, '%Y-%m-%d' ) BETWEEN '2020-05-09' AND '2020-05-11' #统计时间范围
			and devinfo in ('OPPO A1601','vivo vivo 1606','samsung SM-J111F')#统计最多四个分组
		GROUP BY
			from_unixtime( atime, '%Y-%m-%d' ),
		grouptype 
		
UNION ALL

	SELECT a.grouptype,count( DISTINCT b.devid ) AS 'sum',a.atime from `all-impala-1700`  b INNER JOIN 
(
SELECT
	action,
	devid,
	devinfo as 'grouptype',
			count( DISTINCT devid ) AS 'sum',
			max(FROM_UNIXTIME( atime )) AS 'maxdate',
			from_unixtime( atime, '%Y-%m-%d' ) AS atime 
		FROM
			`all-impala-1700` 
		WHERE
			devplatform = 'android' 
			AND action IN ( 'fbLoginSucccessful') #第一个步骤对应的事件
			and devinfo in ('OPPO A1601','vivo vivo 1606','samsung SM-J111F')#统计最多四个分组
			AND from_unixtime( atime, '%Y-%m-%d' ) BETWEEN '2020-05-09' AND '2020-05-11' #统计时间范围
		GROUP BY
			action,
			from_unixtime( atime, '%Y-%m-%d' ),
		devid,
		grouptype
		) as a
		on  a.devid = b.devid 
where b.action= 'entergame'  #最后一个事件的事件名
and b.devplatform = 'android'#事件所属平台
and  DATE_SUB(a.maxdate, INTERVAL -7 DAY) >= FROM_UNIXTIME(b.atime)#窗口期设置
and FROM_UNIXTIME(b.atime) >= '2020-05-09'#设置最后一个事件的起始日期
GROUP BY b.action,a.grouptype,a.atime
) as wzg
GROUP BY atime,grouptype

图表语句（累计）

SELECT
	cc.action,cc.grouptype,cc.sum/bb.sum as 'per'
FROM
	(

SELECT 

			action,grouptype,sum
FROM
(

	SELECT
		1001 AS 'id',
		action,
		devinfo AS 'grouptype',#其他分组类型
		count( DISTINCT devid ) AS 'sum' #from_unixtime( atime, '%Y-%m-%d' ) AS atime #按天统计的时候使用，累计趋势不需要该值
	FROM
		`all-impala-1700` 
	WHERE
		devplatform = 'android' #事件所属平台
		
		AND action IN ( 'fbLoginSucccessful' ) #第一个步骤对应的事件
		
		AND devinfo IN ( 'OPPO A1601', 'vivo vivo 1606', 'samsung SM-J111F' ) #统计最多四个分组
		
		AND from_unixtime( atime, '%Y-%m-%d' ) BETWEEN '2020-05-09' 
		AND '2020-05-11' #统计时间范围
		
	GROUP BY
		action,
		grouptype 
		
		UNION ALL###用第一个步骤的事件，去查询第二步骤中的触发事件数和分组类型
		
	SELECT
		1002 AS 'id',
		b.action,
		a.grouptype,
		count( DISTINCT b.devid ) AS 'sum'

	FROM
		`all-impala-1700` b
		INNER JOIN (
		SELECT
			action,
			devid,
			devinfo AS 'grouptype',#其他分组类型
			count( DISTINCT devid ) AS 'sum',
			max(
			FROM_UNIXTIME( atime )) AS 'maxdate' 
		FROM
			`all-impala-1700` 
		WHERE
			devplatform = 'android' #统计所属平台
			
			AND action IN ( 'fbLoginSucccessful' ) #第一个步骤对应的事件
			
			AND devinfo IN ( 'OPPO A1601', 'vivo vivo 1606', 'samsung SM-J111F' ) #统计最多四个分组
			
			AND from_unixtime( atime, '%Y-%m-%d' ) BETWEEN '2020-05-09' 
			AND '2020-05-11' #统计时间范围
			
		GROUP BY
			action,
			devid,
			grouptype 
		) AS a ON a.devid = b.devid 
	WHERE
		b.action = 'entergame' #第二个事件的名称
		
		AND b.devplatform = 'android' #事件所属平台
		
		AND DATE_SUB( a.maxdate, INTERVAL - 7 DAY ) >= FROM_UNIXTIME( b.atime ) #窗口期
		
		AND FROM_UNIXTIME( b.atime ) >= '2020-05-09' #设置一个事件的起始日期（如果从第二步开始计算的话，在讨论））
		
	GROUP BY
		b.action,a.grouptype
		) as wzg

	) as cc
	
	
	inner JOIN
	(

SELECT 

			action,grouptype,sum
FROM
(

	SELECT
		1001 AS 'id',
		action,
		devinfo AS 'grouptype',#其他分组类型
		count( DISTINCT devid ) AS 'sum' #from_unixtime( atime, '%Y-%m-%d' ) AS atime #按天统计的时候使用，累计趋势不需要该值
	FROM
		`all-impala-1700` 
	WHERE
		devplatform = 'android' #事件所属平台
		
		AND action IN ( 'fbLoginSucccessful' ) #第一个步骤对应的事件
		
		AND devinfo IN ( 'OPPO A1601', 'vivo vivo 1606', 'samsung SM-J111F' ) #统计最多四个分组
		
		AND from_unixtime( atime, '%Y-%m-%d' ) BETWEEN '2020-05-09' 
		AND '2020-05-11' #统计时间范围
		
	GROUP BY
		action,
		grouptype 
		
		UNION ALL###用第一个步骤的事件，去查询第二步骤中的触发事件数和分组类型
		
	SELECT
		1002 AS 'id',
		b.action,
		a.grouptype,
		count( DISTINCT b.devid ) AS 'sum'

	FROM
		`all-impala-1700` b
		left JOIN (
		SELECT
			action,
			devid,
			devinfo AS 'grouptype',#其他分组类型
			count( DISTINCT devid ) AS 'sum',
			max(
			FROM_UNIXTIME( atime )) AS 'maxdate' 
		FROM
			`all-impala-1700` 
		WHERE
			devplatform = 'android' #统计所属平台
			
			AND action IN ( 'fbLoginSucccessful' ) #第一个步骤对应的事件
			
			AND devinfo IN ( 'OPPO A1601', 'vivo vivo 1606', 'samsung SM-J111F' ) #统计最多四个分组
			
			AND from_unixtime( atime, '%Y-%m-%d' ) BETWEEN '2020-05-09' 
			AND '2020-05-11' #统计时间范围
			
		GROUP BY
			action,
			devid,
			grouptype 
		) AS a ON a.devid = b.devid 
	WHERE
		b.action = 'entergame' #第二个事件的名称
		
		AND b.devplatform = 'android' #事件所属平台
		
		AND DATE_SUB( a.maxdate, INTERVAL - 7 DAY ) >= FROM_UNIXTIME( b.atime ) #窗口期
		
		AND FROM_UNIXTIME( b.atime ) >= '2020-05-09' #设置一个事件的起始日期（如果从第二步开始计算的话，在讨论））
		
	GROUP BY
		b.action,a.grouptype
		) as wzg
		where id = 1001
	)  as bb
	ON  cc.grouptype = bb.grouptype
	order by action desc

最后更新于5年前

这有帮助吗？