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

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

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) 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.


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.

/ The above pseudo table definition code
5=connect("demo").cursor@x("select * from T where Time>=? order by UserID,Time",date(now()-1))

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.

/ The above pseudo table definition code
5>start =elapse@m(now(),-3),tw=7
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)
8for A7;
11for A6if #B11==1>C10(1)=t=t1=first.Time
12else>C10(#B11)=t=if(t, && Time>t && Time<elapse(t1,tw)).Time,null)
15return 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:


Leave a Reply