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

Business scenarios #

Path analysis functions are suitable for selecting a time window to analyze user behavior paths within this time window. The frontend display effect is roughly as follows:

Brief introduction to path analysis computational logic:

• First, use pathSplit to generate an array containing all path combinations for each user.
• Then, use array join to expand the two-dimensional array containing all paths for a user in a single row into multiple rows, each containing a single path.
• Then, use pathCount to calculate the path conversion for all users.

Basic concepts #

• Event (event): The event that occurs. In this context, it also refers to the page visit nodes shown in the path analysis chart.
• Prop (prop): Equivalent to tagging the corresponding event.
• Level (level): Can also be understood as the path depth in the chart. Paths are truncated if they exceed x nodes.
• Node (node): In the context of path analysis, each node is a page.
• Edge (edge): The access path from page 1 to page 2.

pathSplit() #

Use pathSplit to generate an array containing all path combinations for each user.

pathSplit(session_time, level)(timestamp, event, prop)

Input parameters:

• session_time: The time window for path analysis. The unit is consistent with timestamp. In the example below, it is 600 seconds, which means only paths formed within 10 minutes after the first event occurred are calculated.
• level: Path depth. Paths are truncated if they exceed x nodes.
• timestamp: The timestamp column. It needs to be converted to a timestamp first, then to Uint64 format.
• event: The event column. Only numerical value type is supported and must be explicitly specified via multiIf. As in the following example: multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0003', 3, 0) AS e.
• prop: The prop column. If not present, pass '' as a placeholder.

Output parameters (intermediate result):
Returns a two-dimensional array: [(e1, p1), (e2, p2), (e1, p2)]

• (ex, px): Corresponds to the event stream, that is, the events that occurred on this path.
• e: Event, corresponds to the event number defined by multiif in the event column.
• p: The prop corresponding to the event, corresponds to prop.

pathCount() #

Use arrayjoin to expand the two-dimensional array containing all paths for a user in a single row into multiple rows, each containing a single path, and then use pathCount to calculate the path conversion for all users. Examples are as follows:

pathCount(y, x)(path, cnt1, cnt2)

SELECT pathCount(3, 3)(path, 1, cnt) 
FROM 
( 
 SELECT 
 path AS path, count() AS cnt 
 FROM 
 ( 
 SELECT arrayJoin(pathSplit(600, 3)(toUInt64(toUnixTimestamp(LOG.EventTime)), multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0) AS e, '')) AS path 
 FROM LOG 
 WHERE (EventTime >= '2022-06-01 00:00:00') AND (EventTime < '2022-06-02 00:00:00') AND (EventName IN ('E0001', 'E0002', 'E0004')) AND (Visitor = 964049) 
 GROUP BY Visitor 
 ) 
 GROUP BY path 
)

pathCount returns the data required for page display. x represents the horizontal Path depth (as shown in the figure below, 4 columns). The frontend requires data for each light blue square representing a node and the edge between light blue squares; both are output by pathCount.

• y: vertical single-layer display depth, as shown in the yellow circled area in the figure above, that is, y = 5;
• x: horizontal Path depth, paths are truncated if they exceed x nodes, as shown in the figure above, x = 4;
• path: The path column returned by pathSplit (needs to be expanded via arrayJoin);
• cnt1: Path weight 1, usually just pass 1 directly, used to calculate the Path deduplicated count;
• cnt2: Path weight 2, usually pass the cnt calculated by pathSplit subquery's count(), used to calculate the Path total count.

Return format:

• The return format is a 3-tuple ([node_index], [nodes], [edges]), which forms a page Path conversion flow diagram from this result set.
• node_index: Format: (event, prop), displays event nodes.
  • event is the index number of the events. If pathSplit previously filled event with multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0), then the returned 1 corresponds to 'E0001', 2 corresponds to 'E0002', and 3 corresponds to 'E0004'.
  • prop is the prop passed to pathSplit.
• nodes: two-dimensional array. Indicates the deduplicated count and total count for each node at each level (level, index starting from 0), format: (level, node_index, cnt1, cnt2).
  • level: The level where the current node is located;
  • node_index: The index of the events, refer to the mapping in the node_index array;
  • cnt1: Deduplicated count of node visits;
  • cnt2: Total count of node visits.
• edge: two-dimensional array. For each edge at each level, shows deduplicated count and total count, format: (level, from_node_index, to_node_index, cnt1, cnt2).
  • level: Level, that is, the input x;
  • from_node_index: Source node, refer to the mapping in the node_index array;
  • to_node_index: Target node, refer to the mapping in the node_index array; to_node_index uses 65535 to mark the case where from_node_index is already the endpoint, that is, when the user is lost after reaching this event;
  • cnt1: Deduplicated count of node visits;
  • cnt2: Total count of node visits.

Example model #

The example table below consists of the user behavior fact table Log and the user dimension table User, forming a commonly used star schema. The following will demonstrate the specific usage of the retention analysis function based on this table.

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;

Page path analysis #

This example specifies a user with Visitor = 964049 for illustration.
This example shows Path data for Events 'E0001', 'E0002', and 'E0004' that occurred between 2022/6/1 and 2022/6/2. It displays Page information within 10 minutes after each event, with a Page Path depth of 3 layers.
First, use pathSplit to calculate the intermediate result:

SELECT Visitor,pathSplit(600, 3) (toUInt64(toUnixTimestamp(LOG.EventTime)), multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0) AS e, '') AS path 
FROM LOG 
WHERE (EventTime >= '2022-06-01 00:00:00') 
  AND (EventTime < '2022-06-02 00:00:00') 
  AND (EventName IN ('E0001', 'E0002', 'E0004')) 
  and Visitor = 964049 
GROUP BY Visitor 

--Return: 
┌─Visitor─┬─path────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 
│  964049 │ [[(1,''),(2,''),(3,'')],[(1,'')],[(1,''),(2,''),(3,'')],[(1,''),(2,''),(2,'')],[(1,'')],[(1,'')],[(1,'')],[(1,''),(3,''),(3,'')],[(1,''),(2,''),(3,'')],[(1,''),(1,''),(3,'')],[(1,'')],[(1,''),(2,''),(3,'')],[(1,'')],[(1,''),(2,''),(2,'')]] │ 
└─────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Then, use arrayJoin to expand the two-dimensional array returned by the single-row path into multiple single-row paths, and calculate the number of occurrences for each Path.

SELECT path    AS path, 
             count() AS cnt 
      FROM (SELECT arrayJoin(pathSplit(600, 3)(toUInt64(toUnixTimestamp(LOG.EventTime)), multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0) AS e, '')) AS path 
            FROM LOG 
            WHERE (EventTime >= '2022-06-01 00:00:00') 
              AND (EventTime < '2022-06-02 00:00:00') 
              AND (EventName IN ('E0001', 'E0002', 'E0004')) 
              and Visitor = 964049 
            GROUP BY Visitor) 
      GROUP BY path 
--Return 
┌─path───────────────────┬─cnt─┐ 
│ [(1,'')]               │   6 │ 
│ [(1,''),(1,''),(3,'')] │   1 │ 
│ [(1,''),(2,''),(3,'')] │   4 │ 
│ [(1,''),(2,''),(2,'')] │   2 │ 
│ [(1,''),(3,''),(3,'')] │   1 │ 
└────────────────────────┴─────┘

Next, apply pathCount to calculate the final 3*3 result (Path depth is 3, and each layer displays up to 3 nodes):

SELECT pathCount(3, 3) (path, 1, cnt) 
FROM (SELECT path    AS path, 
             count() AS cnt 
      FROM (SELECT arrayJoin(pathSplit(600, 3)(toUInt64(toUnixTimestamp(LOG.EventTime)), multiIf(EventName = 'E0001', 1, EventName = 'E0002', 2, EventName = 'E0004', 3, 0) AS e, '')) AS path 
            FROM LOG 
            WHERE (EventTime >= '2022-06-01 00:00:00') 
              AND (EventTime < '2022-06-02 00:00:00') 
              AND (EventName IN ('E0001', 'E0002', 'E0004')) 
              and Visitor = 964049 
            GROUP BY Visitor) 
      GROUP BY path ) 
--Return 
┌─pathCount(5, 3)(path, 1, cnt)─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 
│ ([(1,''),(3,''),(2,'')], 
[[0,0,5,14],[1,0,1,1],[1,1,1,1],[1,2,2,6],[2,1,3,6],[2,2,1,2]], 
[[0,0,0,1,1],[0,0,1,1,1],[0,0,65535,1,6],[0,0,2,2,6],[1,2,2,1,2],[1,1,1,1,1],[1,0,1,1,1],[1,2,1,1,4]]) │ 
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

The result is:

• node_index: [(1,''),(3,''),(2,'')], indicating that the event number is mapped as 0->1(E0001), 1->3(E0004), 2->2(E0002)

• nodes：[[0,0,5,14],[1,0,1,1],[1,1,1,1],[1,2,2,6],[2,1,3,6],[2,2,1,2]]

  • Layer 1, [0,0,5,14]
    • Only one node "Event 1", that is, E0001;
    • This node's deduplicated visit count is 5, and the total visit count is 14.
  • Layer 2, [1,0,1,1],[1,1,1,1],[1,2,2,6]
    • Three events occurred: E0001, E0004, E0002
    • The deduplicated visit counts for the 3 nodes are 1, 1, and 2; the total visit counts are 1, 1, and 6
  • ....

• edge：[0,0,0,1,1],[0,0,1,1,1],[0,0,65535,1,6],[0,0,2,2,6],[1,2,2,1,2],[1,1,1,1,1],[1,0,1,1,1],[1,2,1,1,4]

  • In the first layer, the following edges exist:
    • 0->0: that is, 'E0001'->'E0001', 1 visit;
    • 0->1: 1 visit;
    • 0->65535 (which is already the endpoint), 1 deduplicated visit;
    • 0->2, 2 deduplicated visits.
  • In the second layer, the following edges exist:
    • 2->2, 1 deduplicated visit;
    • 1->1, 1 deduplicated visit;
    • 0->1, 1 deduplicated visit;
    • 2->1, 1 deduplicated visit.

  The result information can be supplemented as page path analysis, for example