This article introduces the basic concepts and fundamental usage of retention analysis functions.

Business scenarios #

The retention analysis function can be used to select a time range and observe the retention of users matching a certain dimension in each time unit within this time range after a period of time.

Brief introduction to retention calculation logic:

• First, use the genArrayIf function to pre-aggregate the behavioral data of initial visit (first_events) and return event (return_events) into specified time slots, resulting in a Array(UInt8) format bitset bitset representing visit status. This step divides the time range into discrete time slots and uses a bitset to indicate whether events occurred in each slot.
• Then, use retention2 to aggregate the user's initial visit (first_events) and return event (return_events) bitsets to obtain the retention data for each time slot.

Basic concepts #

• Time slot (Step): also known as step size. For example, to observe retention data for each day within 7 days, the number of time slots (number_step) is 7, and the duration of each time slot (step_time) is 86400 (unit: seconds; 86400 seconds is one day).

• Event bitset: intermediate data for calculating retention, returned as [0,1,0,1.....], representing whether an event occurred in each time slot.

• Initial visit event (first_events): indicates whether the user performed an initial visit event in each time slot. For example, user registration.

• Return event (return_events): indicates whether the user performed a return event in each time slot. For example, user login.

  • In the following example, first_events (initial visit event) is [13,2]. The first decimal number 13 corresponds to the 8-bit binary number 0000 1101, and the second decimal number 2 corresponds to the 8-bit binary number 0000 0010. This array corresponds to a 16-bit binary representation: 0000 0010 0000 1101. There are 16 bits in total, which can represent whether events occurred over 16 days. The lowest bit in the binary representation corresponds to day 1, and the highest bit corresponds to day 16. Therefore, the target event occurred on days 1, 3, 4, and 9. Similarly, the meaning of the return_events (return event) array is that the target event occurred on days 1, 3, 9, and 10.

    first_events array: [13,2] 
    Corresponding bit representation: 0000 0010     0000 1101 
    Corresponding decimal numbers:     2              13 
    Meaning: the target event occurred on days 1, 3, 4, and 10 
    
    return_events array: [5,3] 
    Corresponding bit representation: 0000 0011     0000 0101 
    Corresponding decimal numbers:     3              5 
    Meaning: The target event occurred on days 1, 3, 9, and 10.
    

• Retention array: Retention refers to observing from the perspective of a user who has performed an initial visit event, and whether a return event occurs in certain time slots in the future.

  --Example: 
  SELECT retention2(10)([13, 2], [5, 3]) 
  
  ┌─retention2(10)([13, 2], [5, 3])───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 
  │ [1,0,1,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,1,1,0,0,0,1,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1] │ 
  └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
  

  In the example above, the number of time slots is 10 days, the slot duration is 1 day, and the retention array is based on the premise that first_event and return_event are [13,2] and [5,3], respectively. The returned retention array is an array with number_steps elements (in the above example, an array of length 10)10. Therefore, this array should be split into 10 rows to view the results. Each row of the retention array represents the observation period starting from the ith day in first_events, and the result of performing a bitwise 'AND' operation with the events in return_events.

  1,0,1,0,0,0,0,0,1,1, -- Using day 1 as the starting observation point, the user returned on days 1, 3, 9, and 10, so columns 1, 3, 9, and 10 are 1. 
  0,0,0,0,0,0,0,0,0,0, -- Using day 2 as the starting observation point, no events occurred on the second day, so the second row is all 0. 
  0,0,1,0,0,0,0,0,1,1, -- Using day 3 as the starting observation point, the user returned on days 3, 9, and 10, so columns 3, 9, and 10 are 1. 
  0,0,0,1,0,0,0,0,1,1, -- Using day 4 as the starting observation point, the user returned on days 9 and 10, so columns 9 and 10 are 1. For day 4 itself, although no return event occurred, by default 1 is also output in the corresponding column. 
  0,0,0,0,0,0,0,0,0,0, 
  0,0,0,0,0,0,0,0,0,0, 
  0,0,0,0,0,0,0,0,0,0, 
  0,0,0,0,0,0,0,0,0,0, 
  0,0,0,0,0,0,0,0,0,0, 
  0,0,0,0,0,0,0,0,0,1
  

  By aggregating these retention arrays by city, media source, and others, you can obtain retention data, for example:

  select city, retention2(3)(..., ...) from ... group by city 
  --The retention array for a certain city is: 
  22,11,1 
  0,5,3 
  0,0,1
  

genArrayIf() #

use genArrayIf function to pre-aggregate behavioral data for initial visit (first_events) and return (return_events) into specified time slots, obtaining a Array(UInt8) format visits overview bitset bitset. This step divides the time range into discrete time slots and uses a bitset to indicate whether an event occurred in each slot.

genArrayIf(number_steps, begin_timestamp, step_time)(timestamp, if_expr)

Input parameters:

• number_steps: The number of time slots.
• begin_timestamp: Analysis start timestamp. For example, if the data filtering scope starts from 2022-06-01, the start timestamp is toUInt64(toUnixTimestamp('2022-06-01')) = 1654012800.
• step_time: Duration of one time slot.
• timestamp: The column containing the event timestamp. It must first be converted to a timestamp, then to Uint64 format.
• if_expr: Definition of the retention condition. If the condition is satisfied, the behaviour is considered to have occurred.

Output parameters:

• Output is a Array(UInt8) format visits overview bitset bitset.

retention2() #

Use retention2 to aggregate the bitsets of user first visits (first_events) and return visits (return_events) to obtain the retention data for each time slot.

retention2(number_steps)(first_events, return_events)

Input parameters:

• number_steps: The number of time slots. The value of number_steps should be consistent with the value of number_steps in genArrayIf.

  warning

  • The value range of number_steps is limited by the size of the first_events and return_events bitsets. The rule is (events_array.size()-1)*8 < number_steps <= events_array.size()*8. For example, in the following example, the number of UInt8 elements in first_events is 2, so the window size should be set to a value within (8, 16].
  • The sizes of the first_events and return_events arrays must be consistent. If they are not, the final calculation result will be incorrect, resulting in many zeros.
• first_events: The bitset of first visit events, which needs to be returned by genArrayIf.
• return_events: The bitset of return visit events, which needs to be returned by genArrayIf.

Output parameters:

• Outputs a retention array in Array(UInt64) format.

Example model #

The example uses a user behavior fact table (Log) and a user dimension table (User) to form a commonly used star schema model. The following sections will demonstrate the specific usage of the retention analysis function based on these tables.

CREATE TABLE -- Event log table 
  default.LOG ( 
    `Visitor` UInt32, 
    `EventName` String, 
    `EventTime` DateTime, 
    `Province` String, 
    `City` String 
  ) ENGINE = CnchMergeTree 
  ORDER BY Visitor; 

  CREATE TABLE -- User table 
  default.User ( 
    `Visitor` UInt32, 
    `Fvisit_Time` DateTime, 
    `Fvisit_Province` String, 
    `Fvisit_City` String 
  ) ENGINE = CnchMergeTree 
  ORDER BY Visitor;

Event analysis #

In the following example, the returned result indicates whether the event EventName = 'E0002' occurred in each unit time (1 day) over 7 days.

SELECT 
    Visitor, 
    genArrayIf(7, 1654012800, 86400)(toUInt64(toUnixTimestamp(LOG.EventTime)), EventName = 'E0002') AS return_events 
FROM LOG 
WHERE (EventTime >= '2022-06-01') AND (EventTime < '2022-06-08') AND (EventName = 'E0002') 
GROUP BY Visitor

Retention analysis #

Observe the retention from first visit ('E0001') to return visit ('E0002') within a 7-day window, grouped by city.

SELECT 
    City, 
    retention2(7)(first_events, return_events) 
FROM 
( 
    SELECT 
        City, 
        Visitor, 
        genArrayIf(7, 1654012800, 86400)(toUInt64(toUnixTimestamp(LOG.EventTime)), EventName = 'E0001') AS first_events 
    FROM LOG 
    WHERE (EventTime >= '2022-06-01') AND (EventTime < '2022-06-08') AND (EventName = 'E0001') 
    GROUP BY 
        Visitor, 
        City 
) AS t1 
ANY LEFT JOIN 
( 
    SELECT 
        Visitor, 
        genArrayIf(7, 1654012800, 86400)(toUInt64(toUnixTimestamp(LOG.EventTime)), EventName = 'E0002') AS return_events 
    FROM LOG 
    WHERE (EventTime >= '2022-06-01') AND (EventTime < '2022-06-08') AND (EventName = 'E0002') 
    GROUP BY Visitor 
) AS t2 ON t1.Visitor = t2.Visitor 
GROUP BY City 
ORDER BY City ASC 
SETTINGS distributed_product_mode = 'local', distributed_group_by_no_merge = 0

Return:

The result is a 7*7 array. As shown in the figure above, city1 is parsed as:

109777,61903,62185,62116,62064,62066,61952, 
0,109978,61922,62500,61817,62671,62417, 
0,0,110033,62193,62177,62230,62287, 
0,0,0,109715,61833,62142,62119, 
0,0,0,0,110598,62493,62515, 
0,0,0,0,0,109508,61909, 
0,0,0,0,0,0,110746

The explanation of this result is as follows:

• Row dimension (i): Indicates the i-th day of the user's first visit (i=1 is the first day, and so on).
• Column dimension (j): Indicates the user's return visit on the j-th day after their first visit.
• When i = j, the numerical value represents the total number of users who visited for the first time on the i-th day.

In the above result,

• The value 109777 in the first row and first column (1,1) represents the total number of users who visited for the first time on the first day.
• The value 61903 in the first row and second column (1,2) represents the number of users who visited for the first time on the first day and returned on the second day.
• The value 62500 in the second row and fourth column (2,4) represents the number of users who visited for the first time on the second day and returned on the fourth day.

You can calculate the 1-day conversion rate from Day1 to Day6 as needed. You can also change the time slot to week, month, and so on according to actual requirements.