Retention functions

Updated at:

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 retention function is supported in Hologres V0.9 and later.

  • The range_retention_count and range_retention_sum functions 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:

  • 1: The condition is met.

  • 0: The condition is not met.

Examples

  • Example 1: Analyze user retention for PushEvent events 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_id

    Result:

    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 a bigint array. The output is not human-readable and is intended as input for the range_retention_sum function.

  • range_retention_sum: Aggregates the data from range_retention_count to calculate the daily retention rate for all users. It returns a text array.

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 expand to 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 bigint array.

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