跳至正文

【深度Tableau】Prep Builder2021.3“新行”与客户迁徙数据准备案例

前段时间,喜乐君分享了Prep多年来的关键功能,其中最新的当属2021.3版本的“新行”(New Rows)了。本文将阐述它的两个基本功能,并结合最近研究的“客户迁徙”问题,分享这个功能如何简化了之前复杂的数据准备过程。

1、“新行”的目的

Prep2021.3版本的“新行”旨在为连续的数据值补充缺失数据,比如[1,3,4]中缺少了数字2。目前这个功能仅限于日期。

有两个基本场景:为单一字段列补充缺失值,为两个日期填充中间值。如下图所示,

在为日期增加缺失值或中间值时,可以指定间隔的单位,比如1天、1月甚至7天;也可以根据数据库中的值手动做调整。

2、在Prep Builder中增加新行

总体而言,增加新行可以分为“选择新行方法”、“设置控制字段”、“设置填充规则”三步,根据不同的方法又略有差异。

喜乐君把两个情形分别称之为:为单一字段列补充缺失值、为两个字段填充中间值。通过下文介绍,要理解“缺失值”和“中间值”的差异。

2.1  “为单一字段列补充缺失值”

基于超市数据,可以为“订单日期”补充缺失值,从而生成新行数据明细。如下图,插入“新行”节点,选择“来自一个字段的值”(即为单一字段补充缺失值),然后选择“订单日期”即可。默认为系统中所有值查找缺失值,并填充到数据明细中。

特别注意,填充的字段值是完整的(订单日期),其他字段都是null或者零(null对应维度字段,0对应度量字段)。因此,每次“新行”之后,都需要做进一步的处理方有意义。

可以结合案例讲解这里的逻辑。

在“客户迁徙”分析中,喜乐君就尝试为没有消费的月份补充空行,这样就可以分析“多少客户上月流失、本月消费”。如图所示,有两个客户5月份都有购买,其中“客户001”6月购买、7月流失,而“客户002”则6月流失、7月回流,因此6月份的复购率是50%(1/2)。如果结合购买金额打标签,甚至可以计算“中等客户到大客户的迁徙率”“大客户的流失重新激活率”等深度分析。

为客户的流失月份填充数据明细,本能的,先做聚合到指定客户层次(每个客户的消费年月),而后添加“新行”,试图增加。

可惜,这个思路不通!
因此,使用第一个方法增加缺失值,务必要注意prep此处没有“分区”的概念,它不会针对每个客户增加从头到尾的缺失值,而只针对数据库所有日期补充缺失值!如果想要增加分区概念,这里就要使用第二种方法:指定首尾字段填充中间值。

2.2 “指定两个字段填充中间值”

Prep的第二个新行方法是指定两个日期字段填充中间值,而不考虑不同行之间的差异。也正是因为每一行是独立的,对应的维度就可以作为分区来使用。如图所示,可以借助于“新行”为两个日期填充中间值。

这里借助案例讲解。沿着上述的客户迁徙问题来介绍,如何为每个客户生成“首次消费年月”至当前的所有年月记录呢?我们先把数据聚合到客户的层次,并借助于DATETRUNC函数转化为“消费年月”,DATE转化日期(目前只有日期支持新行)同时,借助于FIXED LOD功能计算最后月份作为“当前日期”。

这样,我们就生成了接下来“新行”所需要的结构。
在后面增加“新行”,选择第二种新行方法,选择开始日期和结束日期,并设置“1月”为增量步长,就为每个客户增加了中间的消费年月。注意,为了避免生成的空行中客户id变成null,在设置填充规则时,选择“从上一行复制”。

这样就可以生成每个客户从首次消费年月到当前(max日期)的所有节点。这就是“新行”的关键使用场景。​

3、客户迁徙分析后续

沿着上述的分析,需要补充几点:谨慎选择增加新行的层次、格外关注度量。上面之所以没有为客户增加度量,就是因为空行中复制度量必然会引起数据的错误。因此,具体的业务实践中,应该在新行增加后补充度量,这就要借助join了。

把这里的“增加新行后的客户月历数据”和之前的“客户的消费年月”建立join,引入每个月的订单次数,就构建了客户迁徙所需要的基本数据源。本文重点不是讲解join,所以内容忽略,有兴趣的可以下载源文件查看。

另外,在没有“新行”之前,prep就不能完成类似的功能吗? 

可以,方法也不难,甚至还有很多额外的好处——可以直接获得历史累计销售额、历史累计订单数等数据,相当于随手完成了FIXED分析,只是这里的“大于等于匹配”,导致数据膨胀严重,性能相对低一些。喜乐君在博客文章“医药覆盖率数据准备”中详细有过介绍。如下图所示。

当然,上述过程也可以使用SQL完成,喜乐君的客户就给我提供过一个存储过程的方法,逻辑之复杂超过了大多数分析师的能力。
有实力的可以感受一下,反正我还不懂:

BEGIN
	DECLARE @card_num VARCHAR(12), @open_month VARCHAR(6), @month_id VARCHAR(6),@last_month VARCHAR(6)
	DECLARE @cj_num INT,@cj_money DECIMAL(15,4),@tot_num int,@tot_money DECIMAL(15,4) 
	DECLARE @last_num INT,@last_tot_num INT,@label VARCHAR(10),@last_label VARCHAR(10)


	SELECT DISTINCT LEFT(CONVERT(varchar(100),the_date, 112),6) month_id INTO #month
	FROM dbo.time_by_day
	WHERE the_date >= '2015-07-01'
	AND the_date < '2021-07-01'

	DECLARE c_1 INSENSITIVE SCROLL CURSOR      
	FOR  
	SELECT card_num,LEFT(CONVERT(varchar(100),m_copentime, 112),6) open_month
	FROM dbo.member WHERE m_followman IN (SELECT eid FROM dbo.remployee WHERE edept = 'YWB' AND egroup LIKE 'Y%')
	ORDER BY card_num FOR READ ONLY  
	OPEN c_1  
	FETCH FIRST FROM c_1 INTO @card_num, @open_month                                        
	WHILE @@fetch_status = 0    
	BEGIN      
		DECLARE c_2 INSENSITIVE SCROLL CURSOR      
		FOR  
		SELECT month_id
		FROM dbo.#month
		WHERE month_id >= @open_month
		ORDER BY month_id FOR READ ONLY  
		OPEN c_2  
		FETCH FIRST FROM c_2 INTO @month_id                                        
		WHILE @@fetch_status = 0    
		BEGIN      
			SET @last_label = NULL
			SET @last_num = NULL
			SET @last_tot_num = NULL
			SET @cj_num = NULL
			SET @cj_money = NULL
			SET @tot_money = NULL
			SET @tot_num = NULL

			SET @last_month = LEFT(CONVERT(varchar(100),DATEADD (MONTH ,-1,@month_id+'01') , 112),6)
			
			SELECT @cj_num = COUNT(DISTINCT sm.Said)
			FROM sale_master sm WITH(NOLOCK)
			WHERE  sm.card_num = @card_num
			AND sm.curStatus >= '3'
			AND sm.Satype NOT IN ( 'DJ','TH','DF','WD','PJ')
			AND ISNULL(sm.Sainstman2,'') NOT IN ('2','5','6') 	
			AND ISNULL(saisfm,'0') <> '1'
			AND LEFT(CONVERT(varchar(100),sm.SaCshDate, 112),6) = @month_id
			AND NOT EXISTS(SELECT * FROM sale_master WHERE saoid = sm.Said 
							AND  LEFT(CONVERT(varchar(100),SaMakDate, 112),6) <=  @month_id ) 

			SELECT @cj_money = SUM(sd.SaDisMoney)
			FROM sale_master sm WITH(NOLOCK),sale_detail sd WITH(NOLOCK)
			WHERE sm.Said = sd.Said
			AND sm.card_num = @card_num
			AND sm.curStatus >= '3'
			AND sm.Satype NOT IN ( 'DJ','DF','WD','PJ')
			AND LEFT(CONVERT(varchar(100),sm.SaCshDate, 112),6) = @month_id
			and sd.Sagmax+'-'+sd.Sagmid+'-'+sd.Sagmin <>'A01-JFP-X20'


			SELECT @tot_num = COUNT(DISTINCT sm.Said)
			FROM sale_master sm WITH(NOLOCK)
			WHERE  sm.card_num = @card_num
			AND sm.curStatus >= '3'
			AND sm.Satype NOT IN ( 'DJ','TH','DF','WD','PJ')
			AND ISNULL(sm.Sainstman2,'') NOT IN ('2','5','6') 	
			AND ISNULL(saisfm,'0') <> '1'
			AND LEFT(CONVERT(varchar(100),sm.SaCshDate, 112),6) <= @month_id
			AND NOT EXISTS(SELECT * FROM sale_master WHERE saoid = sm.Said 
							AND  LEFT(CONVERT(varchar(100),SaMakDate, 112),6) <=  @month_id ) 

			SELECT @tot_money = SUM(sd.SaDisMoney)
			FROM sale_master sm WITH(NOLOCK),sale_detail sd WITH(NOLOCK)
			WHERE sm.Said = sd.Said
			AND sm.card_num = @card_num
			AND sm.curStatus >= '3'
			AND sm.Satype NOT IN ( 'DJ','DF','WD','PJ')
			AND LEFT(CONVERT(varchar(100),sm.SaCshDate, 112),6) <= @month_id
			and sd.Sagmax+'-'+sd.Sagmid+'-'+sd.Sagmin <>'A01-JFP-X20'


			--IF @cj_num IS NULL OR @cj_num < 1 
			--	SET @label = 'L'
			--ELSE IF @cj_num >=5 
			--	SET @label = 'C5+'
			--ELSE
			--	SET @label = 'C'+CAST(@cj_num AS VARCHAR)


			SELECT @last_num = cj_num,@last_tot_num = tot_num--,@last_label = label
			FROM member_migration_analysis 
			WHERE card_num = @card_num AND month_id = @last_month


			DELETE FROM member_migration_analysis 
			WHERE card_num = @card_num AND month_id = @month_id	
		
			INSERT INTO member_migration_analysis
			(card_num,month_id,cj_num,tot_num,cj_money,tot_money,last_num,last_tot_num,label,last_label)
			SELECT @card_num,@month_id,@cj_num,@tot_num,@cj_money,@tot_money,@last_num,@last_tot_num,@label,@last_label

			FETCH NEXT FROM c_2  INTO @month_id    
		END  
		CLOSE c_2     
		DEALLOCATE c_2  
		
		FETCH NEXT FROM c_1  INTO @card_num, @open_month    
	END  
	CLOSE c_1     
	DEALLOCATE c_1  
	DROP TABLE #month

END

4、总结
随着Prep功能日渐完整,新增功能也日渐抽象。“新行”是2021年Prep少有的重磅功能,如果没有合适的案例,就很难以理解并应用。传统上,这里是IT的领导和业务人员的禁区,今天也没有实质上的改变——除了少数人偶尔能边缘徘徊一阵。


@喜乐君
Oct 16, 2021

附件:下载实例流程

了解 喜乐君 的更多信息

立即订阅以继续阅读并访问完整档案。

Continue reading