背景
笔者因实际业务需求,每个月都要统计客户近3个月以来的下单金额、下单SKU,结合指标计算不同会员等级的客户数量,并按月度形成趋势图。
在Qlik的图表中,可通过表达式统计“指定日期”的客户下单金额、下单SKU,并计算会员等级和对应的数量,但无法做到同时统计不同月度的情况。
因此需要通过Qlik的脚本实现,生成一张客户在不同月份下单情况的数据表。
思路&目的
问题是需要客户的订单记录,计算客户近3个月的订单金额;解答者的思路是通过IntervalMatch函数,构建日期区间来实现滚动式统计。
IntervalMatch函数的官方功能定义如下:
IntervalMatch ‒ Qlik Sense Enterprise on Kubernetes
(由于接下来就将展示脚本步骤,此函数就不再介绍,感兴趣的同学可自行阅读官方定义。)
笔者参考社区上分享的脚本,进一步制作了统计近3个月客户下单SKU和下单金额的脚本,以下是步骤和说明。和之前的分享一样,笔者也希望能抛砖引玉,获得更多更好的想法。
脚本步骤
1.构建样例数据
样表包括4个字段:客户,下单SKU,下单金额,下单日期。
data:
LOAD * INLINE [
customercode, SKU, Amount, OrderDate
A, apple, 50, 2022/1/1
A, banana, 30, 2022/2/5
A, apple, 40, 2022/3/21
A, pear, 60, 2022/4/1
A, peach, 55, 2022/5/17
A, cherry, 10, 2022/6/15
A, orange, 70, 2022/7/2
B, apple, 50, 2022/6/1
B, banana, 30, 2022/7/2
B, apple, 40, 2022/8/5
B, pear, 60, 2022/9/1
B, peach, 55, 2022/10/17
B, cherry, 10, 2022/11/15
B, melon, 88, 2022/12/2
];
2.将数据按年月进行聚合
实际上可直接在第一步就将样本数据设置为客户每月的下单数据,但考虑到实际情况大多为按天统计,故而在脚本中进行聚合操作。
首先提取下单年月,并通过客户号+下单年月生成了一个关联键,用于之后的关联;另外,通过monthend函数求得了每个订单日期对应的“当月最后一天”,便于之后计算3个月前的日期。此步骤也可合并至第一步,简化脚本。
data_add_month:
Load
distinct customercode,
SKU,
Amount,
year(OrderDate)*100+month(OrderDate) as orderym,
text(customercode)&text(year(OrderDate)*100+month(OrderDate)) as linkkey,
monthend(date(OrderDate)) as last_date
Resident data;
Drop Table data;
databymonth:
Load
Distinct customercode,
orderym,
last_date,
linkkey,
SKU,
sum(Amount) as orderamount
Resident [data_add_month]
Group By customercode, orderym, last_date, SKU, linkkey
Order By customercode, orderym, last_date, SKU, linkkey asc;
Drop Table data_add_month;
图2
3.提取订单日期的年月,生成日历表
由于第2步的聚合,此日历表只包含了年月,这样可以控制数据量,提升计算效率。
*如订单日期对应的月份不完整,会导致计算结果不完整,因此笔者认为用其他方式生成日历表较好;后续如找到简洁的主日历表脚本,会更新文章。
yearmonth:
Load
distinct orderym,
last_date
Resident databymonth;
日历表如下图:
图3
4.客户+日期交叉
将下单客户和日历表关联,形成"客户+日期"的交叉表(暂称交叉表1),并通过客户和日期这两个字段形成连接主键。
Join yearmonth:
Load
distinct customercode
Resident databymonth;
orderbydate:
Load
*,
text(customercode)&text(orderym) as linkkey
Resident yearmonth;
Drop Table yearmonth;
生成图表如下:
图4
5.关联客户订单信息
把客户的订单信息关联至交叉表1。客户无订单的日期,订单信息会显示为空值。
Left Join orderbydate:
Load
linkkey,
SKU,
orderamount as SKUamount
Resident databymonth;
Drop Table databymonth;
生成图表如下:
图5
6.获取近3个月的起-止日期
通过monthstart函数,生成近3个月的开始和结束日期,并通过区间函数形成日期的交叉表(暂称交叉表2)。
recent3m:
Load Distinct
Monthstart(AddMonths(last_date,-2)) as StartDate,
Date(last_date) as EndDate,
last_date AS LinkDate
Resident orderbydate;
Inner Join IntervalMatch (last_date)
Load
StartDate,
EndDate
Resident recent3m;
生成图表如下:
图6.1
注意这里是33行,由于订单年月是2022年全年,共12个月;而统计的区间是每个月向前推3个月(含当月), 1-10月对应3对“起-止日期”,而11月只对应2对“起-止日期”(2022/9/1-2022/11/30,2022/10/1-2022/12/31,再往下由于不存在2023年的数据所以没有了);12月只对应1对“起-止日期”(2022/10/1-2022/12/31)。总行数=10*3+2+1=33行。
图6.2
7.合并交叉表
把交叉表1和交叉表2合并,关联交叉表1的客户订单信息至交叉表2。
Join (recent3m)
Load
last_date,
customercode,
SKU,
SKUamount
Resident orderbydate;
生成图表如下:
图7
表的行数为66,实际上是日期交叉表的行数(=33)*客户数(=2)*客户每月下单SKU数(=1)。如果客户在一个月内下单了多个SKU,则行数会相应增长。
8.得出近3个月的下单数据
对交叉表2进行聚合,得出客户在每月对应的近3月下单SKU和下单金额。
recent3maggr:
Load
LinkDate,
customercode,
count(distinct SKU) as Recent3mSKU,
sum(SKUamount) as Recent3mAmount
Resident recent3m
Group by LinkDate, customercode
Order by LinkDate, customercode;
结果如下图:
图8
此聚合表的行数为24,等于所有年月(12)*客户数(2)。可以看到,客户还没有开始下单的月份,近3个月的下单SKU和金额均为0。
9.最终整合
将上一步生成的聚合表关联至第5步生成的客户订单表,得到客户近3月的下单SKU和下单金额。
客户订单表包含了下单SKU列,如不需要也可以按实际情况再进行整合。
Left Join (orderbydate)
Load
LinkDate as last_date,
customercode,
Recent3mSKU,
Recent3mAmount
resident recent3maggr;
drop tables recent3m, recent3maggr;
最终得出的数据表如下图:
图9
至此,客户每月的下单金额、下单SKU数和近3月的下单金额、下单SKU数就全部整合完毕了。由于要统计SKU数,因此需要把“客户下单的所有SKU”也作为一个字段,相应的增加数据表的行数。
以上脚本也可按日期进行聚合,如:统计近60天的下单情况,但这样会导致数据量剧增,运算压力增大,建议考虑实际情况后再自行调整脚本。