User Behavior Analysis in Practice 13: Bi-dimension Ordering

User Behavior Analysis in Practice 13: Bi-dimension Ordering

Target task

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

2022/6/1 10:201072755SearchAndroidIE100001truefalsefalsetruefalse
2022/6/1 12:121078030BrowseIOSSafari100002falsefalsetruetruetrue
2022/6/1 12:361005093SubmitAndroidChrome100003truetruetruefalsefalse
2022/6/1 13:211048655LoginWindowsChromefalsefalsetruetruetrue
2022/6/1 14:461037824LogoutAndroidEdgefalsefalsefalsetruetrue
2022/6/1 15:191049626AddtoCartWindowsEdge100004truetruefalsetruefalse
2022/6/1 16:001009296SubmitIOSFirefox100005falsetruefalsefalsetrue
2022/6/1 16:391070713BrowseIOSSogou100006truetruetruefalsefalse
2022/6/1 17:401090884SearchWindowsIE100007truefalsetruetruefalse

Fields in table T:

Field nameData typeDescription
TimeDatetimeTime stamp of an event, accurate to milliseconds
UserIDStringUser ID
EventTypeStringEvent type, whose value is Login, Browse, Search, AddtoCart, Submit or Logout
OSStringOperating system, whose value is Android, IOS, Windows or Unknown
BrowserStringBrowser, whose value is IE, Safari, Edge, Firefox, Chrome, Sogou or Unknown
ProductIDStringProduct ID, whose value is the ProductID field of dimension table Product
StringOther fields that have enumerated values
f1BooleanWhether it is an offsite event or not; value is true or false
f2BooleanWhether it is a usual device or not; value is true or false
f3BooleanWhether it is a usual browser or not; value is true or false
f4BooleanWhether it is a cell phone or not; value is true or false
f5BooleanWhether it is the first operation; value is true or false
BooleanOther fields that have Boolean values

Dimension table Product:


Fields in dimension table Product:

Field nameData typeDescription
ProductIDStringProduct ID
ProductNameStringProduct name
UnitStringSales unit
PriceNumericUnit price
ProductTypeIntegerProduct 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) under the product type Home & Personal care within a time window during the specified time period so that we can calculate the customer conversion rate and churn rate. Such a computing scenario is known as conversion funnel analysis.

Make a note that the total volume of data is large and the time span is long, but that each time window is relatively short. The other aspects are same as the circumstances in the conversion funnel analysis in series 11:

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 within 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

Learn more about bi-dimension ordering in articles SPL Pseudo Table Bi-dimension Ordering Structure and Speed up Big Data User Behavior Analysis using Bi-dimension Ordering Structure.

The use of bi-dimension ordering structure enables ordering data as a whole by Time (which helps achieve fast filtering) and the result set by UserID (for convenience of subsequent computations). It appears that data is ordered by two dimensions at the same time through this structure.

Sample code

1. Define a multizone composite table and split table by month@y()(suppose data is within 2010-2021).


A1 Define a sequence of yearmonths whose members will be zone table numbers, that is, one zone table for each month.

A2 Define a multizone composite table, use A1 as its zone table numbers and define zone table number’s computing expression as month@y(Time).

2. Define a pseudo table based on the multizone composite table

/ The above multizone composite table definition code

A4 Import dimension table Product and create primary key and index for it.

A5 Define a pseudo table based on multizone composite table T.ctx. zone is the list of zone table numbers; date is the zone field, which is Time here; and user is the account field, which is UserID here. You can learn more about the roles of zone field and account filed in above hyperlinked essays.

Read the last essay in this series to learn about explanations of other definitions.

A6 Generate pseudo table.

3. Retrieve data from the table T, sort data by UserID and Time and append it to the multizone composite table through the pseudo table

/ The above pseudo table definition code
7=connect("demo").cursor@x("select * from T").sortx(UserID,Time)

A7 Connect to the database, retrieve data from table T to generate a cursor, and sort the cursor data by UserID and Time.

A8 Fetch data from the cursor, and append it to the multizone composite table through the pseudo table.

In actual practice, new data is added continuously. Suppose Time field contains the time when an event occurs, the newly-added data always has a larger Time value. The new data is either appended to the last zone table or written to a new zone table, but they won’t be inserted to any other zone table. SPL can automatically ensure that data in the last zone table is always ordered by UserID, so we can still append the newly increased data using the above code.

4. Summarize data using the pseudo table

/ The above pseudo table definition code
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)
10for A9;
13for A8if #B13==1>C12(1)=t=t1=first.Time
14else>C12(#B13)=t=if(t, && Time>t && Time<elapse(t1,tw)).Time,null)
17return D8

A7 Define a time interval through two parameters and the time window tw. Thei values will be passed as parameters in during the computation.

A8 Names of target events. Note that their order is important.

D8 Generate an array having same length as A8’s to store the number of occurrences of each event. This is the final return result.

A9 Generate cursor for the pseudo table and perform filtering on the cursor data according to the specified time interval, target events, product type, browsers and devices. A fast filtering will be automatically executed on the pseudo table to select desired data from the specified time interval.

A10 Loop data in the cursor and retrieve all data of one user each time. The pseudo table ensures that data is ordered by UserID. Here we can use same approach as that we used to handle an ordered cursor in the above.

The rest of the computation is the same as that on an ordered cursor.

B10 Get the record where the first event occurs for the first time, and assign it to variable first.

B11 If the first event does not occur, move on to the next user as the current user has become ineligible and there is no need to perform the computation on it.

B12 Define a variable t to store the time when the current event occurs during the later loop.

C12 Define an array of same length as A8 to store the time when each event occurs during the later loop.

B13 Loop each member of A8.

C13-D13 If the loop number in B13 is 1, the current event is the first one. The system records the time when the first event, which is now the value of t, occurs and assigns the time to t1.

C14-D14 If the current event isn’t the first one, check whether the previous value of t is null. If it is null, assign null to the current t; if it isn’t null, find the record of event that occurs the earliest from those where the time is greater than t and less than the time window t1+tw and assign the time to t.

B15 Loop C12, and record frequency for time that is null as 0 and that for time that is non-null as 1.

B16 Add the number of frequencies in B15 to D8.

Execution result:


Leave a Reply