Retention functions
Retention analysis is an analytical model used to measure user engagement and activity. It tracks how many users who perform an initial event return for retention events, which helps measure a product's value to its users. This model is widely used for user analysis in industries such as the internet, e-commerce, and gaming. Hologres, a one-stop real-time data warehouse from Alibaba Cloud, supports multidimensional analysis of real-time data. For user behavior analysis, Hologres provides retention functions to help businesses perform such analyses efficiently.
Limitations
-
The
retentionfunction is supported in Hologres V0.9 and later. -
The
range_retention_countandrange_retention_sumfunctions are supported in Hologres V0.10 and later. -
To use any retention function, a user with Superuser access must enable the extension by running the following statement.
CREATE extension flow_analysis;-
The extension is enabled at the database level, so you only need to run this command once per database.
-
By default, the extension is loaded into the public schema and cannot be loaded into other schemas.
-
If you are connected to a different schema, you must prefix the function name with
public., for example,public.retention().
-
Prerequisites
All examples in this topic are based on Business and data understanding.
-
Dataset description
Developers on GitHub contribute to open source projects, generating a massive volume of events. GitHub records details for each event, such as the event type, developer, and repository, and exposes public events like starring a repository or committing code. For a list of event types, see Webhook events and payloads.
-
Import the dataset
You can import the github_event dataset into your Hologres instance with a few clicks. For instructions, see Import public datasets with a few clicks.
The table schema for the GitHub public event data is as follows:
BEGIN;
CREATE TABLE hologres_dataset_github_event.hologres_github_event (
id bigint,
actor_id bigint,
actor_login text,
repo_id bigint,
repo_name text,
org_id bigint,
org_login text,
type text,
created_at timestamp with time zone NOT NULL,
action text,
iss_or_pr_id bigint,
number bigint,
comment_id bigint,
commit_id text,
member_id bigint,
rev_or_push_or_rel_id bigint,
ref text,
ref_type text,
state text,
author_association text,
language text,
merged boolean,
merged_at timestamp with time zone,
additions bigint,
deletions bigint,
changed_files bigint,
push_size bigint,
push_distinct_size bigint,
hr text,
month text,
year text,
ds text
);
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'orientation', 'column');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'bitmap_columns', 'actor_login,repo_name,org_login,type,action,commit_id,ref,ref_type,state,author_association,language,hr,month,year,ds');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'clustering_key', 'created_at:asc');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'dictionary_encoding_columns', 'actor_login:auto,repo_name:auto,org_login:auto,type:auto,action:auto,commit_id:auto,ref:auto,ref_type:auto,state:auto,author_association:auto,language:auto,hr:auto,month:auto,year:auto,ds:auto');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'distribution_key', 'id');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'segment_key', 'created_at');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'time_to_live_in_seconds', '3153600000');
COMMENT ON TABLE hologres_dataset_github_event.hologres_github_event IS NULL;
ALTER TABLE hologres_dataset_github_event.hologres_github_event OWNER TO test1_developer;
END;
Retention function (retention)
Retention analysis measures overall user engagement and activity. It is often used to measure user retention rates, which helps you make better product decisions.
Syntax
The retention function takes from 1 to 32 conditional expressions as arguments.
retention(cond1, cond2, ..., cond32);
Parameters:
|
Parameter |
Description |
|
cond |
A conditional expression that returns one of the following values:
|
Examples
-
Example 1: Analyze user retention for
PushEventevents starting from a specific day. The following SQL statement shows how to perform this analysis:SELECT sum(r[1]) as first_day, sum(r[2]) as second_day, sum(r[3]) as third_day, sum(r[4]) as fourth_day, sum(r[5]) as fifth_day, sum(r[6]) as sixth_day, sum(r[7]) as seventh_day FROM ( SELECT retention (ds = '2023-07-23' AND type = 'PushEvent',ds = '2023-07-24' AND type = 'PushEvent', ds = '2023-07-25'AND type = 'PushEvent', ds = '2023-07-26' AND type = 'PushEvent', ds = '2023-07-27'AND type = 'PushEvent' , ds = '2023-07-28'AND type = 'PushEvent', ds = '2023-07-29'AND type = 'PushEvent') AS r FROM hologres_dataset_github_event.hologres_github_event GROUP BY actor_id) tt;Result:
first_day |second_day | third_day | fourth_day | fifth_day |sixth_day |seventh_day ----------------+-----------+-------------+----------------+--------------------+---------------+----------- 250678 |105351 |93219 |81071 |81474 |76264 |23319 -
Example 2: Analyze the retention of each user over a seven-day period.
-- Retention for each user SELECT actor_id, retention( DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08') , DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '1 day'), DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '2 day'), DATE(created_at) = DATE(TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '6 day') ) AS r FROM hologres_dataset_github_event.hologres_github_event WHERE (created_at >= TIMESTAMP '2023-07-23 10:00:00+08') AND (created_at <= TIMESTAMP '2023-07-23 10:00:00+08' + INTERVAL '6 day') GROUP BY actor_idResult:
actor_id | r ----------------+------- 122588631 |{1,1,1,0} 909706 |{1,0,1,0} 1893117 |{0,0,0,0} 56136246 |{1,0,1,0} 33266253 |{0,0,0,0} 106332614 |{0,0,0,0}
Extended retention functions
Retention analysis is a common scenario in user growth analysis. You often need to create data visualizations to analyze user retention. For this scenario, Hologres provides two functions: range_retention_count and range_retention_sum.
-
range_retention_count: Records the retention status for each user and returns abigintarray. The output is not human-readable and is intended as input for therange_retention_sumfunction. -
range_retention_sum: Aggregates the data fromrange_retention_countto calculate the daily retention rate for all users. It returns atextarray.
Syntax
-
range_retention_count
range_retention_count(is_first, is_active, dt, retention_interval, retention_granularity,output_format)Parameters:
Parameter
Type
Description
is_first
boolean
A boolean value that specifies whether the event is an initial event.
-
true: The event is an initial event. -
false: The event is not an initial event.
is_active
boolean
A boolean value that specifies whether the event is a retention event.
-
true: The event is a retention event. -
false: The event is not a retention event.
dt
date
The date of the event. Example: 2020-12-12.
retention_interval
int[]
An array of integers specifying the retention intervals. You can specify a maximum of 15 intervals. Example:
ARRAY[1,3,5,7,15,30].retention_granularity
text
The retention granularity. Supported values:
-
day -
week -
month
output_format
text
The output format. Supported values:
-
normal(default) -
expand
Note-
This parameter is supported in Hologres V1.1.38 and later.
-
Use
expandto retrieve daily retention details.
-
-
range_retention_sum
range_retention_sum(range_retention_count)range_retention_count: Records the retention status of each user. The returned result is a
bigintarray.
Examples
The following examples use the GitHub public event dataset to analyze user retention.
-
Example 1: Calculate the retention status for each user.
SELECT actor_id, r FROM ( SELECT actor_id, range_retention_count(type = 'CreateEvent', type = 'PushEvent', ds::date, array[1, 2],'day','normal') AS r FROM hologres_dataset_github_event.hologres_github_event GROUP BY actor_id ) AS t ORDER BY actor_id;A partial query result is as follows:
actor_id | r -----------+------- 771 |{84022445211651} 772 |{84022445211651,84026740178947,84031035146243,84048215015424} 777 |{} 797 |{84013855277059,84018150244353,84022445211648,84035330113536} -
Example 2: Calculate the 7-day retention rate.
WITH tbl_detail AS ( SELECT range_retention_count(is_first, is_active, dt, ARRAY[1, 3, 7],'day' ) AS detail FROM ( SELECT actor_id, created_at::DATE AS dt , CASE WHEN created_at >= timestamp '2023-07-23 00:00:00' AND created_at < timestamp '2023-07-23 00:00:00' + INTERVAL '7' DAY AND type = 'CreateEvent' THEN true ELSE false END AS is_first , CASE WHEN created_at >= timestamp '2023-07-23 00:00:00' + INTERVAL '1' DAY AND created_at < timestamp '2023-07-23 00:00:00' + INTERVAL '7' DAY + INTERVAL '7' DAY THEN true ELSE false END AS is_active FROM hologres_dataset_github_event.hologres_github_event ) tbl GROUP BY actor_id ), tbl_sum AS ( SELECT regexp_split_to_array(unnest(range_retention_sum(detail)), ',') AS s FROM tbl_detail ) SELECT s[1] AS "Start Date" ,s[3]::numeric / s[2]::numeric AS "Day 1" ,s[4]::numeric / s[2]::numeric AS "Day 3" ,s[5]::numeric / s[2]::numeric AS "Day 7" FROM tbl_sum ORDER BY s[1];The query returns the following result:
Start Date | Day 1 |Day 3 | Day 7 ------------+---------------+---------------+--------- 20230723 |0.3656546240 |0.2603730723 |0.0000000000 20230724 |0.4076098204 |0.3123156723 |0.2030384953 20230725 |0.3930270566 |0.3154280149 |0.0000000000 20230726 |0.4275885111 |0.1000445494 |0.0000000000 20230727 |0.4043748374 |0.0000000000 |0.0000000000 20230728 |0.1463595703 |0.2279064106 |0.0000000000 20230729 |0.0000000000 |0.0000000000 |0.0000000000