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