User Behavior Analysis in Practice 13: Real-time T+0 Analysis

 

Target task

We have a user events table T. Below is its structure and part of its data:

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

Fields in table T:

Field name

Data type

Description

Time

Datetime

Time stamp of an event, accurate to milliseconds

UserID

String

User ID

EventType

String

Event type, whose value is Login, Browse, Search, AddtoCart, Submit or Logout

OS

String

Operating system, whose value is Android, IOS, Windows or Unknown

Browser

String

Browser, whose value is IE, Safari, Edge, Firefox, Chrome, Sogou or Unknown

ProductID

String

Product ID, whose value is the ProductID field of dimension table Product

String

Other fields that have enumerated values

f1

Boolean

Whether it is an offsite event or not; value is true or false

f2

Boolean

Whether it is a usual device or not; value is true or false

f3

Boolean

Whether it is a usual browser or not; value is true or false

f4

Boolean

Whether it is a cell phone or not; value is true or false

f5

Boolean

Whether it is the first operation; value is true or false

Boolean

Other fields that have Boolean values

Dimension table 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

Fields in dimension table Product:

Field name

Data type

Description

ProductID

String

Product ID

ProductName

String

Product name

Unit

String

Sales unit

Price

Numeric

Unit price

ProductType

Integer

Product type

Computing task:

Count users who are not newcomers on a local Android or IOS system using Safari, Edge or Chrome and who perform the first N of a series of events (search, add to cart and submit order) in order under the product type Home & Personal care in the past three months so that we can calculate the customer conversion rate and churn rate. Such a computing scenario is known as conversion funnel analysis.

Note that the time window is the recent three months and the latest data should be included. The other aspects are same as the circumstances in the conversion funnel analysis:

1. The three events should occur in the time order. Those that do not appear in order are not eligible.

2. The three events should happen under one user in a specified time window. Those that occur out of the time range are not included.

3. Begin timing at the occurrence of the first event. If the subsequent events occur in order within the time window, each event is recorded as 1; otherwise, it is recorded as 0. If the frequency of an event is 0, there is no need to scan the subsequent events.

Techniques involved

The historical data, according to explanations in the previous articles, can be exported to T.ctx. And the latest generated real-time data can be appended to the in-memory patch zone through append@y() function to engage in the real-time analysis and computation. The new data won’t be written to the composite table file and thus won’t disturb the regular data store and appen to the composite table file.

All pseudo tables, composite tables and multizone composite tables support append@y() function.

Sample code

1. As we do in the previous articles, we store historical data in composite table file T.ctx. The data is stored in different zone tables by yearmonth to form a multizone composite table.

2. Define a pseudo table based on the multizone composite table using the same code provided in the previous article.


A

1

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

2

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

3

=[{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:A2}]

}]

4

=pseudo(A5)

3. Retrieve the newly-increased, real-time data, sort it by UserID and Time and append it to the multizone composite table’s patch zone using append@y() function.

Suppose in each day the newly-generated data in the previous day is appended to the multizone composite table file after 24:00. In this case. the real-time, new data is the data generated after 00:00 in each morning.


A

/ The above pseudo table definition code

5

=connect("demo").cursor@x("select * from T where Time>=? order by UserID,Time",date(now()-1))

6

=A4.append@y(A7)

A5 Connect to the database, retrieve the newly-generated data from table T to generate a cursor while sorting the cursor data by UserID and Time.

A6 Append data in the cursor to the multizone composite table’s in-memory patch zone through the pseudo table.

Beside using the pseudo table, we can also directly open the multizone composite table and append the new data to it using append@y(). To do this, we need to first convert each enumerated field to ordinal numbers and each binary field to bit-based dimension.

4. Summarize data using the pseudo table using the same code provided in the previous article. SPL will automatically merge the real-time data stored in the memory and the historical data held in the composite table file and export the merged data in an appropriate way.


A

B

C

D

/ The above pseudo table definition code

5

>start =elapse@m(now(),-3),tw=7

6

[Search,AddtoCart,Submit]

=A6.(0)

7

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

8

for A7;UserID

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

9


if(B8==null)

next


10


=t=null

=A6.(null)


11


for A6

if #B11==1

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

12



else

>C10(#B11)=t=if(t,A8.select@1(EventType==B11 && Time>t && Time<elapse(t1,tw)).Time,null)

13


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

14


>D6=D6++B13

15

return D6


5. When the multizone composite table does not have a bi-dimension ordering structure, we handle data in it in the same way – append data to the composite table’s in-memory patch zone using append@y function – without changing the code.

Execution result:

Member

393400

257539

83375