Path analysis functions

Background

Path analysis records the distribution of user paths and the sequence of actions in each session when users interact with your product or feature. It generates an intuitive Sankey diagram of user behavior. The Sankey diagram displays each key node in the user flow and the traffic between these nodes, which enables granular business analysis. By using path analysis, you can clearly understand user engagement with each key feature of your product. This helps your operations and product teams optimize business strategies and iterate on the product, driving business growth. Hologres supports a path detail function for detailed path computation and a path parsing function for result analysis. The output can be used directly to create a Sankey diagram.

The top of the path analysis Sankey diagram interface provides a time zone selector, a time range selector, settings for the maximum number of nodes per layer, and an Export button. The Sankey diagram starts from an initial event (such as $AppStart) and progressively displays subsequent user actions (like $AppEnd, AppPageview, Search, CheckIn, and AllPayments). Each node is labeled with the event name and its trigger count, and the width of the flow between nodes reflects the traffic volume.

Usage notes

Path analysis functions are available in Hologres V2.2 and later. If your instance is V2.1 or earlier, contact Hologres technical support to upgrade your instance.
To use path analysis functions, you must create an extension. By default, the extension is loaded into the public schema and cannot be loaded into other schemas.
```
 --Create the extension.
CREATE extension flow_analysis;
```
Note
An extension is database-level and only needs to be created once per database.

Path detail functions

Path detail function (path_analysis_detail)

The path_analysis_detail function analyzes specified event data and presents it as a detailed path structure. The output is a serialized array that contains comprehensive information, including the complete path sequence, the parent-child relationships between nodes in the path, and the duration of each step.

Syntax

path_analysis_detail(
  event, 
	event_time, 
	start_event, 
	session_interval_sec, 
	path_depth, 
	path_offset, 
	is_reverse,
split_session_by_event)

Parameters

Parameter	Type	Description
event	text (data)	The event to be analyzed.
event_time	timestamp, timestamptz, bigint (data)	The timestamp of each event.
start_event	text (literal)	The specified start or end event.
session_interval_sec	bigint (literal)	The session interval in seconds (s). If the time between adjacent events in a session exceeds the specified session interval, the system splits it into two sessions. For example, if you set the interval to 30s and the time between two events is greater than 30s, the events are considered parts of two different sessions.
path_depth	bigint (literal)	The number of events to retrieve in the sequence, starting from the specified start or end event.
path_offset	bigint (literal)	The event offset from the start or end event. A value of 0 means no offset. For example, for an event series a, b, and c, if a is the start event and the offset is 1, matching starts from event b.
is_reverse	bool (literal)	The output order of the event series. Valid values: false: Specifies a start event. The event series is returned in chronological order. true: Specifies an end event. The event series is returned in reverse chronological order.
split_session_by_event	bool (literal)	The method for session splitting. Valid values: true: A new session starts whenever the specified start or end event is encountered in the event series. false (Default): Sessions are split only based on the value of the `session_interval_sec` parameter. Note If `session_interval_sec` is set to -1, sessions are split by event instead of time. For example, if a path such as `abcabc...abc` is repeated 10 times within the same session, the function's matching mechanism records only a single conversion. If you want to treat each repetition of the path as a new session to calculate each conversion independently, you can set this parameter to `true`. This allows each new operation to be recorded and counted separately.

Return value

path_analysis_detail: Returns a value of the text data type. Example: {"",\x01a\x01b<,\x01b\x01c<,""}.

Note
To read the result, you must use a corresponding path parsing function.

`pad_funnel` function

The pad_funnel function retrieves subpath information for a specific combination of events.

Syntax

pad_funnel(path_analysis_detail(), target_path)

Parameters

Parameter	Type	Description
path_analysis_detail()	text (data)	The result returned by the `path_analysis_detail` function.
target_path	text (literal)	The specified event series.

Return value

pad_funnel: Returns a text value. Example: text []{"",\x01a\x01b<,\x01b\x01c<,""}.

Note
To read the result, you must use a corresponding path parsing function.

Path parsing functions

pad_full_path function

The pad_full_path function parses the result of path_analysis_detail into parent paths, subpaths, and the duration of each step.

Syntax
```
pad_full_path(path_analysis_detail())
```
Parameters

path_analysis_detail(): The result returned by the path_analysis_detail function.

Return value

The pad_full_path function returns the following content.

pad_sub_path_left(unnested_pad_result)
pad_sub_path_right(unnested_pad_result)
pad_sub_index_left(unnested_pad_result)
pad_sub_index_right(unnested_pad_result)
pad_sub_cost(unnested_pad_result)
pad_sub_session(unnested_pad_result)

The following table describes the return parameters.

Function	Type	Description
pad_sub_path_left	text	The start event of the subpath.
pad_sub_path_right	text	The end event of the subpath.
pad_sub_index_left	bigint	The index of the subpath's start event within the complete path of the session.
pad_sub_index_right	bigint	The index of the subpath's end event within the complete path of the session.
pad_sub_cost	bigint	The transition duration for the subpath, in seconds (s).
pad_sub_session	bigint	The index of the valid session to which the subpath belongs.

pad_session_path_array function

The pad_session_path_array function extracts the event series for a specified session ID as an array.

Syntax

pad_session_path_array(path_analysis_detail(), session_idx)

Parameters
- path_analysis_detail(): The result returned by the path_analysis_detail function.
- session_idx: The specified session index.
Return value

Returns data of the array type.

Examples

Prepare data

--Create the extension if it does not already exist.
CREATE extension flow_analysis;
--Prepare the data.
CREATE TABLE path_demo( 
    uid text,
    event text,
    event_time timestamptz
);
INSERT INTO path_demo VALUES
('1','Register','2023-11-24 16:01:23+08'),
('1','Log on','2023-11-24 16:02:10+08'),
('1','Browse','2023-11-24 16:02:15+08'),
('1','View live streams','2023-11-24 16:03:10+08'),
('1','Browse','2023-11-24 16:03:15+08'),
('1','Add to favorites','2023-11-24 16:04:20+08'),
('1','Browse','2023-11-24 16:07:21+08'),
('1','Purchase','2023-11-24 16:08:23+08'),
('1','Exit','2023-11-24 16:09:05+08'),
('2','Log on','2023-11-24 16:10:23+08'),
('2','Purchase','2023-11-24 16:12:23+08'),
('3','Log on','2023-11-24 16:02:23+08'),
('3','Browse','2023-11-24 16:02:23+08'),
('3','Add to favorites','2023-11-24 16:03:53+08'),
('3','View live streams','2023-11-24 16:04:53+08'),
('4','Log on','2023-11-24 16:02:23+08'),
('4','Browse','2023-11-24 16:03:53+08'),
('4','Purchase','2023-11-24 16:04:23+08'),
('4','View live streams','2023-11-24 16:05:53+08'),
('4','Cancel the order','2023-11-24 16:06:53+08');