メインコンテンツまでスキップ

retention

リテンション

概要​

指定された期間内のユーザーのリテンション率を計算します。この関数は1から31の条件を受け入れ、各条件がtrueかどうかを評価します。条件がtrueの場合、1が返されます。それ以外の場合、0が返されます。最終的に0と1の配列が返されます。これを使ってユーザーのリテンション率を計算することができます。

構文​

ARRAY retention(ARRAY input)

パラメータ​

input: 条件の配列。最大で31の条件を渡すことができます。複数の条件はカンマで区切ります。

返り値​

0と1の配列が返されます。0と1の数は入力条件の数と同じです。

評価は最初の条件から始まります。

  • 条件がtrueの場合、1が返されます。それ以外の場合、0が返されます。
  • 最初の条件がtrueでない場合、現在の位置とその後の位置はすべて0に設定されます。

例​

  1. testという名前のテーブルを作成し、データを挿入します。

    CREATE TABLE test(
        id TINYINT,
        action STRING,
        time DATETIME
    )
    ENGINE=olap
    DUPLICATE KEY(id)
    DISTRIBUTED BY HASH(id);

    INSERT INTO test VALUES 
    (1,'pv','2022-01-01 08:00:05'),
    (2,'pv','2022-01-01 10:20:08'),
    (1,'buy','2022-01-02 15:30:10'),
    (2,'pv','2022-01-02 17:30:05'),
    (3,'buy','2022-01-01 05:30:09'),
    (3,'buy','22022-01-02 08:10:15'),
    (4,'pv','2022-01-02 21:09:15'),
    (5,'pv','2022-01-01 22:10:53'),
    (5,'pv','2022-01-02 19:10:52'),
    (5,'buy','2022-01-02 20:00:50');

  2. testからデータをクエリします。

    MySQL > select * from test order by id;
    +------+--------+---------------------+
    | id   | action | time                |
    +------+--------+---------------------+
    |    1 | pv     | 2022-01-01 08:00:05 |
    |    1 | buy    | 2022-01-02 15:30:10 |
    |    2 | pv     | 2022-01-01 10:20:08 |
    |    2 | pv     | 2022-01-02 17:30:05 |
    |    3 | buy    | 2022-01-01 05:30:09 |
    |    3 | buy    | 2022-01-02 08:10:15 |
    |    4 | pv     | 2022-01-02 21:09:15 |
    |    5 | pv     | 2022-01-01 22:10:53 |
    |    5 | pv     | 2022-01-02 19:10:52 |
    |    5 | buy    | 2022-01-02 20:00:50 |
    +------+--------+---------------------+
    10 rows in set (0.01 sec)

  3. retentionを使用してユーザーのリテンション率を計算します。例1: ユーザーの行動が以下の条件を満たすか評価します: 2022-01-01に商品ページを閲覧する(action='pv') かつ 2022-01-02に注文をする(action='buy')。

    MySQL > select id, retention([action='pv' and to_date(time)='2022-01-01',
                                  action='buy' and to_date(time)='2022-01-02']) as retention 
    from test 
    group by id
    order by id;

    +------+-----------+
    | id   | retention |
    +------+-----------+
    |    1 | [1,1]     |
    |    2 | [1,0]     |
    |    3 | [0,0]     |
    |    4 | [0,0]     |
    |    5 | [1,1]     |
    +------+-----------+
    5 rows in set (0.01 sec)

    結果:例2: 2022-01-01に商品ページを閲覧したユーザーの割合と、2022-01-02に注文をしたユーザーの割合を計算します。

    MySQL > select sum(r[1]),sum(r[2])/sum(r[1])
    from (select id, retention([action='pv' and to_date(time)='2022-01-01',
                                action='buy' and to_date(time)='2022-01-02']) as r 
    from test 
    group by id 
    order by id) t;

    +-----------+---------------------------+
    | sum(r[1]) | (sum(r[2])) / (sum(r[1])) |
    +-----------+---------------------------+
    |         3 |        0.6666666666666666 |
    +-----------+---------------------------+
    1 row in set (0.02 sec)

    返り値は2022-01-02のユーザーのリテンション率です。

    • ユーザー1と5は2つの条件を満たし、[1,1]が返されます。
    • ユーザー2は2つ目の条件を満たさないため、[1,0]が返されます。
    • ユーザー3は2つ目の条件を満たしますが、1つ目の条件を満たしません。[0,0]が返されます。
    • ユーザー4はどの条件も満たさず、[0,0]が返されます。

キーワード​

retention, retention rate, RETENTION