用户行为分析系列实践 13 – 双维有序

目标任务

用户事件表T结构和部分数据示例如下:

Time

UserID

EventType

OS

Browser

ProductID

f1

f2

f3

f4

f5

2022/6/1 10:20

1072755

Search

Android

IE

100001

true

false

false

true

false

2022/6/1 12:12

1078030

Browse

IOS

Safari

100002

false

false

true

true

true

2022/6/1 12:36

1005093

Submit

Android

Chrome

100003

true

true

true

false

false

2022/6/1 13:21

1048655

Login

Windows

Chrome


false

false

true

true

true

2022/6/1 14:46

1037824

Logout

Android

Edge


false

false

false

true

true

2022/6/1 15:19

1049626

AddtoCart

Windows

Edge

100004

true

true

false

true

false

2022/6/1 16:00

1009296

Submit

IOS

Firefox

100005

false

true

false

false

true

2022/6/1 16:39

1070713

Browse

IOS

Sogou

100006

true

true

true

false

false

2022/6/1 17:40

1090884

Search

Windows

IE

100007

true

false

true

true

false

T表字段说明:

字段名

数据类型

字段含义

Time

日期时间

事件发生的时间戳,精确到毫秒

UserID

字符串

用户ID

EventType

字符串

事件类型,取值为Login,Browse,Search,AddtoCart,Submit,Logout

OS

字符串

操作系统,取值为Android,IOS,Windows,Unknown

Browser

字符串

浏览器,取值为IE,Safari,Edge,Firefox,Chrome,Sogou,Unknown

ProductID

字符串

产品ID,取值为产品维表中的ProductID字段

字符串

更多其它取值为枚举值的字段

f1

布尔值

是否异地发生,取值为真和假

f2

布尔值

是否惯用设备,取值为真和假

f3

布尔值

是否惯用浏览器,取值为真和假

f4

布尔值

是否是手机,取值为真和假

f5

布尔值

是否首次操作,取值为真和假

布尔值

更多其它取值为真和假的字段

维表Product:

ProductID

ProductName

Unit

Price

ProductType

100001

Apple

Pound

5.5

Fruits

100002

Tissue

Packs

16

Home&Personalcare

100003

Beef

Pound

35

Meat

100004

Wine

Bottles

120

Beverage

100005

Pork

Pound

25

Meat

100006

Bread

Packs

10

Bakery

100007

Juice

Bottles

6

Beverage

维表Product字段说明:

字段名

数据类型

字段含义

ProductID

字符串

产品ID

ProductName

字符串

产品名称

Unit

字符串

销售单位

Price

数值

单价

ProductType

整数

产品类别

计算任务:

统计指定时间段内,产品类别为Home&Personalcare,本地使用安卓/苹果手机,使用Safari/ Edge/ Chrome,且非首次操作的用户,在一个时间窗口期内依次发生过搜索、加购物车、提交订单这三个动作中前N个的用户数量,从而方便后续统计用户转化率和用户流失率,即漏斗转化分析。

需要注意的事项:总数据量很大,跨度时间很长,但每次选择的时间窗口比较短。其它事项和前述漏斗分析一致:

1、 上述三个事件必须按时间顺序依次发生,顺序不对的不算

2、 上述三个事件必须是同一个用户在一个时间窗口期内发生,超出时间窗口期则不算

3、 以第一个事件发生时间点开始计时,如果后续事件在时间窗口期内按顺序发生,则相应事件记为1次,否则为0次,一旦出现某个事件为0次,后续事件就不用继续扫描。

实践技能

关于双维有序的相关知识可参考:

SPL 虚表的双维有序结构

双维有序结构提速大数据量用户行为分析

使用双维有序结构,使数据整体上对Time有序(从而实现快速过滤),结果集还可以做到对UserID有序(从而方便地实施后续计算),看起来相当于实现了两个维度有序

示例代码

1、 定义复组表,将数据按month@y()拆分表(假设数据为2010-2021年的)


A

1

=to(2021,2022).conj((a=~*100,12.(~+a)))

2

=file("T.ctx":A1).create@y(#UserID,#Time,Month,EventType,OS,Browser,ProductID,b1;month@y(Time))

3

=A2.close()

A1 根据时间范围,定义年月序列,用作分表的分表号,即每月一个分表

A2 定义复组表,使用A1作为分表号,并定义month@y(Time)作为分表号的计算表达式

2、 在复组表的基础上定义虚表


A

/前面定义复组表的代码

4

=T("Product.btx").keys@i(ProductID)

5

=[{file:"T.ctx",

zone:A1,

user:"UserID",

date:"Time",

column:[

{name:"Month",exp:"month@y(Time)"},

{name:"EventType",pseudo:"EventTypeName",enum:["Login","Browse","Search","AddtoCart","Submit","Logout"]},

{name:"OS",pseudo:"OSName",enum:["Android","IOS","Windows","Unknown"]},

{name:"Browser",pseudo:"BrowserName",enum:["IE","Safari","Edge","Firefox","Chrome","Sogou","Unknown"]},

{name:"b1",bits:["f1","f2","f3","f4","f5"]},

{name:"ProductID",dim:A4}]

}]

6

=pseudo(A5)

A4 读入产品维表,并建立主键索引

A5 在复组表T.ctx的基础上定义虚表,zone为分表号列表,date为分列字段,这里是Timeuser为账户字段,这里是UserID; 分列字段和帐户字段的意义可参考前述参考文章。

其它定义参照上一篇文章

A6 生成虚表

3、 把数据读出来,然后按UserID,Time排序,通过虚表追加到复组表中


A

/前面定义虚表的代码

7

=connect("demo").cursor@x("select * from T").sortx(UserID,Time)

8

=A6.append@i(A7)

A7 连接数据库,读取T表数据并产生游标, 对着游标按UserID,Time排序

A8 将游标数据读出通过虚表追加到复组表中

实际应用中,数据是不断追加的,我们这里假设Time字段是数据的发生时间,因此追加的数据始终是Time更大的数据,新数据要么写入最后一个分表,要么写入新的分表,不会插入中间的分表里,SPL会自动保证最后一个分表的数据始终按UserID有序。因此对于增量数据,依旧按上述代码追加即可。

4、 利用虚表进行数据统计


A

B

C

D

/前面定义虚表的代码

7

>start=date("2022-03-15","yyyy-MM-dd"),end=date("2022-04-05","yyyy-MM-dd"),tw=7

8

[Search,AddtoCart,Submit]

=A8.(0)

9

=A6.cursor(UserID, EventType,Time;Time>=start && Time<=end && A8.contain(EventType) && ProductID.ProductType=="Home&Personalcare"&& ["Safari","Edge","Chrome"].pos(BrowserName) && ["Android","IOS"].pos(OSName) && ! f1 && f4 && !f5)

10

for A9;UserID

=first=A10.select@1(EventType==A8(1))

11


if(B10==null)

next


12


=t=null

=A8.(null)


13


for A8

if #B13==1

>C12(1)=t=t1=first.Time

14



else

>C12(#B13)=t=if(t,A10.select@1(EventType==B13 && Time>t && Time<elapse(t1,tw)).Time,null)

15


=C12.(if(~,1,0))

16


>D8=D8++B15

17

return D8


A7 定义时间段参数、时间窗口tw,实际使用中通过参数传入

A8 目标事件名称,其顺序是重点

D8 产生一个和A8等长的数组,用于存储每个事件的发生次数,此为最终返回结果

A9 用虚表产生游标,游标中对时间段、目标事件、产品类别、浏览器、设备等进行过滤。虚表会自动执行快速过滤,选出目标时间段的数据

A10 对游标循环,每次取出一个用户的所有数据,虚表会保证数据对UserId有序,可以使用类似前面有序游标一样的技术。

以下内容就和有序游标上的计算是一样了。

B10 读第一个事件第一次发生的记录,赋给first变量

B11 如果第一个事件没有发生,则跳转下一个用户,当前用户为无效用户,不需要继续统计

B12 定义变量t,用于存储后续循环中的当前事件发生的时间

C12 定义和A8等长的数组,用于存储后续循环中每一个事件对应的发生时间

B13 循环A8

C13-D13 如果B13的循环序号是1,表明当前为第一个事件,此时记录tfirst的发生时间,同时把此时间赋给t1

C14-D14 如果不是第一个事件,则判断上一个t是否为空,如果为空则把当前t赋值null;如果不为空,则从满足>t且小于时间窗口t1+tw的数据中寻找最早发生的当前事件记录,把其发生时间赋给t

B15 循环C12,把时间为null的次数记为0,不为null的次数记为1

B16 B15累加到D8

运行结果:

Member

393400

257539

83375