retention

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

Syntax

ARRAY retention(ARRAY input)

Parameters

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

Return value

0と1の配列を返します。0と1の数は入力条件の数と同じです。

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

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

Examples

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 を使用してユーザーリテンション率を計算します。

   Example 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)

   結果では:

   • ユーザー1と5は2つの条件を満たし、[1,1] が返されます。

   • ユーザー2は2番目の条件を満たさず、[1,0] が返されます。

   • ユーザー3は2番目の条件を満たしますが、最初の条件を満たしません。[0,0] が返されます。

   • ユーザー4はどの条件も満たさず、[0,0] が返されます。

   Example 2: 2022-01-01 に商品ページを閲覧し (action='pv')、2022-01-02 に注文を行った (action='buy') ユーザーの割合を計算します。

   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 のユーザーリテンション率です。

keyword

retention, retention rate, RETENTION