AnalyticDB:ファネル関数とリテンション関数

テストデータセット

このトピックでは、Tianchi Lab の淘宝 (Taobao) 行動データを使用して、ファネル関数とリテンション関数をテストします。詳細については、「User Behavior Data from Taobao for Recommendation」をご参照ください。

次の表に、テストデータセットで提供される 4 種類のユーザー行動を示します。

テストの前に、テストデータセットを Object Storage Service (OSS) にアップロードし、OSS から AnalyticDB for MySQL にデータをインポートします。

1. テストデータセットを OSS にアップロードします。詳細については、「オブジェクトのアップロード」をご参照ください。

2. OSS 外部テーブルを使用して、データを AnalyticDB for MySQL にインポートします。次の例は、必要な操作を示しています:

   1. OSS 外部テーブルを作成します。

      CREATE TABLE `user_behavior_oss` (
        `user_id` string,
        `item_id` string,
        `cate_id` string,
        `event` string,
         `ts` bigint) ENGINE = 'oss'
      TABLE_PROPERTIES = '{
      "endpoint":"oss-cn-zhangjiakou.aliyuncs.com",
      "accessid":"******",
      "accesskey":"*******",
      "url":"oss://<bucket-name>/user_behavior/",
      "delimiter":","
      }'

      詳細については、「OSS 外部テーブルの構文」をご参照ください。

   2. AnalyticDB for MySQL テストテーブルを作成します。

      CREATE TABLE user_behavior(
        uid string,
        event string,
        ts string
      )

   3. OSS 外部テーブルからテストテーブルにデータをインポートします。

      SUBMIT JOB INSERT OVERWRITE user_behavior SELECT user_id , event, ts FROM user_behavior_oss;

window_funnel

window_funnel 関数は、スライディングタイムウィンドウでイベントリストを検索し、連続するイベントの最大数をカウントするために使用されます。window_funnel 関数は、指定したイベントリストの最初のイベントからカウントを開始し、イベントを順番にチェックして、連続するイベントの最大数を返します。

• c1、c2、c3 を含むイベントリストを指定し、ユーザーデータに c1、c2、c3、c4 が含まれている場合、関数は 3 を返します。

• c1、c2、c3 を含むイベントリストを指定し、ユーザーデータに c4、c3、c2、c1 が含まれている場合、関数は 1 を返します。

• c1、c2、c3 を含むイベントリストを指定し、ユーザーデータに c4 と c3 が含まれている場合、関数は 0 を返します。

構文

window_funnel(window, mode, timestamp, cond1, cond2, ..., condN)

パラメーター

select uid, window_funnel(cast(1800 as integer),"default", TIMESTAMPDIFF('second','2017-11-25 00:00:00.000',ts),event = 'pv',event = 'fav',event = 'cart',event = 'buy') as funnel from user_behavior where ts > '2017-11-25 00:00:00.000' and ts < '2017-11-26 00:00:000.000'

例

たとえば、2017-11-25 00:00:00 から 2017-11-26 00:00:00 までのコンバージョンステータスを、次のコンバージョンパスに基づいて分析するとします: 商品の閲覧 > 商品をお気に入りに追加 > 商品をショッピングカートに追加 > 商品の購入。関連する SQL 文には、次の条件が含まれます:

• スライディングタイムウィンドウのサイズは 30 分で、1,800 秒に相当します。

• クエリ時間は 2017-11-25 00:00:00 から 2017-11-26 00:00:00 までで、タイムスタンプ形式では 1511539200 から 1511625600 に相当します。

• イベントステップは、商品の閲覧 > 商品をお気に入りに追加 > 商品をショッピングカートに追加 > 商品の購入の順に実行されます。

次の SQL 文を実行します:

SELECT funnel, count(1) FROM (SELECT uid, window_funnel(cast(1800 as integer),"default", ts, event='pv',  event='fav', event='cart', event='buy') AS funnel FROM user_behavior WHERE ts > 1511539200 AND ts < 1511625600 group by uid) GROUP BY funnel;

結果の例:

+--------+----------+
| funnel | count(1) |
+--------+----------+
|      0 |    19687 |
|      2 |    78458 |
|      3 |    11640 |
|      1 |   596104 |
|      4 |      746 |
+--------+----------+
5 rows in set (0.64 sec)

retention

retention 関数は、パラメーターとして条件のセットを使用して、イベントが条件を満たすかどうかを分析できます。

構文

retention(cond1, cond2, ..., cond32)

パラメーター

例

たとえば、2017 年 11 月 25 日時点のユーザーリテンションステータスを分析するとします。関連する SQL 文には、次の条件が含まれます:

• 分析の開始日は 2017-11-25 です。

• 初日のアクティブユーザー数は sum(r[1]) で表されます。2 日目から 7 日目までのリテンションユーザー数は sum(r[2]), sum(r[3])...sum(r[7]) で表されます。

次の SQL 文を実行します:

SELECT sum(r[1]),sum(r[2]),sum(r[3]),sum(r[4]),sum(r[5]),sum(r[6]),sum(r[7]) FROM (SELECT retention(ds='2017-11-25' AND event='pv', ds='2017-11-25', ds='2017-11-26',ds='2017-11-27',ds='2017-11-28',ds='2017-11-29',ds='2017-11-30') AS r FROM user_behavior_date GROUP BY uid);

結果の例:

+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| sum(r[1]) | sum(r[2]) | sum(r[3]) | sum(r[4]) | sum(r[5]) | sum(r[6]) | sum(r[7]) |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|    686953 |    686953 |    544367 |    529979 |    523516 |    524530 |    528105 |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
1 row in set (2.96 sec)

retention_range_count と retention_range_sum

リテンション分析関数 (retention_range_count および retention_range_sum) は、可視化されたグラフを生成し、ユーザーグロース分析シナリオでユーザーリテンションを分析するために使用できます。retention_range_count 関数は、各ユーザーのリテンションステータスを記録するために使用できます。この関数の戻り値は 2 次元の配列であり、retention_range_sum 関数の入力パラメーターとして機能します。retention_range_sum 関数は、すべてのユーザーの日次リテンション率を要約するために使用できます。

構文

• retention_range_count

  retention_range_count(is_first, is_active, dt, intervals, outputFormat)

• retention_range_sum

  retention_range_sum(retention_range_count_result)

パラメーター

例

たとえば、2022 年 5 月 1 日から 2022 年 5 月 4 日までのユーザーデータに基づいて、2022 年 5 月 1 日と 2022 年 5 月 2 日のリテンションステータスをクエリするとします。アクティベーションイベントは login で、リテンションイベントは pay です。

1. テストテーブルを作成し、データをテーブルに挿入します。

   1. テーブルを作成します。

      CREATE TABLE event(uid string, event string, ds date);

   2. テーブルにデータを挿入します。

      INSERT INTO event VALUES("user1", "pay", "2022-05-01"),("user1", "login", "2022-05-01"),("user1", "pay", "2022-05-02"),("user1", "login", "2022-05-02"),("user2", "login", "2022-05-01"),("user3", "login", "2022-05-02"),("user3", "pay", "2022-05-03"),("user3", "pay", "2022-05-04");

      テストデータの例:

      +-------+-------+------------+
      | uid   | event | ds         |
      +-------+-------+------------+
      | user1 | login | 2022-05-01 |
      | user1 | pay   | 2022-05-01 |
      | user1 | login | 2022-05-02 |
      | user1 | pay   | 2022-05-02 |
      | user2 | login | 2022-05-01 |
      | user3 | login | 2022-05-02 |
      | user3 | pay   | 2022-05-03 |
      | user3 | pay   | 2022-05-04 |
      +-------+-------+------------+

2. 各ユーザーのリテンションステータスをクエリします。

   SELECT
     uid,
     r
   FROM
     (
       SELECT
         uid,
         retention_range_count(event = 'login', event = 'pay', ds, array(1, 2)) AS r
       FROM
         event
       GROUP BY
         uid
     ) AS t
   ORDER BY
     uid;

   結果の例:

   +-------+-----------------------------+
   | uid   | r                           |
   +-------+-----------------------------+
   | user1 | [[738642,0,0],[738641,1,0]] |
   | user2 | [[738641,0,0]]              |
   | user3 | [[738642,1,1]]              |
   +-------+-----------------------------+

3. すべてのユーザーの日次リテンション率をクエリします。

   SELECT
     from_days(u [1]) AS ds,
     u [3] / u [2] AS retention_d1,
     u [4] / u [2] AS retention_d2
   FROM
     (
       SELECT
         retention_range_sum(r) AS r
       FROM
         (
           SELECT
             uid,
             retention_range_count(event = 'login', event = 'pay', ds, array(1, 2)) AS r
           FROM
             event
           GROUP BY
             uid
         ) AS t
       ORDER BY
         uid
     ) AS r,
     unnest(r.r) AS t(u);

   結果の例:

   +------------+--------------+--------------+
   | ds         | retention_d1 | retention_d2 |
   +------------+--------------+--------------+
   | 2022-05-02 |          0.5 |          0.5 |
   | 2022-05-01 |          0.5 |          0.0 |
   +------------+--------------+--------------+