AnalyticDB:ファネルおよび保持関数

仕組み

この関数は、各ユーザーのイベントを時系列順に処理します。

1. まず、指定したシーケンスの最初の条件に該当するイベントをスキャンし、該当するイベントが見つかるとスライドウィンドウが開始されます。

2. 次に、ウィンドウ内で順次、その後の各条件を検索します。各条件が一致すると、カウンターが 1 ずつ増加します。

3. シーケンスが中断されるか、すべての条件が一致する前にウィンドウが有効期限切れになると、カウンターの増加が停止します。関数は、到達した最大のカウンター値を返します。

例：

• イベントリスト [c1, c2, c3]、ユーザーのデータ [c1, c2, c3, c4] → 3 を返します（完全なシーケンスが一致）

• イベントリスト [c1, c2, c3]、ユーザーのデータ [c4, c3, c2, c1] → 1 を返します（先頭からのみ c1 が一致）

• イベントリスト [c1, c2, c3]、ユーザーのデータ [c4, c3] → 0 を返します（最初の条件が一度も一致しなかった）

構文

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

パラメーター

例

以下の例では、2017 年 11 月 25 日 00:00:00 から 2017 年 11 月 26 日 00:00:00 までの期間について、閲覧 → お気に入りへの追加 → ショッピングカートへの追加 → 購入というコンバージョンパスを分析します。スライドタイムウィンドウは 30 分（1,800 秒）であり、タイムスタンプは UNIX 形式（1511539200 ～ 1511625600）です。

クエリは 2 段階で実行されます。内部クエリではユーザー単位のファネル深度を計算し、外部クエリでは各深度に到達したユーザー数をカウントします。

SELECT
  funnel,
  count(1)
FROM (
  SELECT
    uid,
    window_funnel(
      cast(1800 as integer),  -- 30 分のスライドウィンドウ
      "default",
      ts,
      event = 'pv',           -- ステップ 1：閲覧
      event = 'fav',          -- ステップ 2：お気に入りへの追加
      event = 'cart',         -- ステップ 3：ショッピングカートへの追加
      event = 'buy'           -- ステップ 4：購入
    ) AS funnel
  FROM user_behavior
  WHERE ts > 1511539200
    AND ts < 1511625600
  GROUP BY uid
)
GROUP BY funnel;

サンプル結果：

+--------+----------+
| funnel | count(1) |
+--------+----------+
|      0 |    19687 |
|      1 |   596104 |
|      2 |    78458 |
|      3 |    11640 |
|      4 |      746 |
+--------+----------+
5 行 (0.64 秒)

値 4 は、ユーザーが 4 つのステップすべてを完了したことを意味します。値 1 は、最初のステップ（閲覧）のみが一致したことを意味します。

構文

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

パラメーター

例

以下の例では、2017 年 11 月 25 日から始まる 7 日間の保持率を測定します。sum(r[1]) は初日（基準日）にアクティブだったユーザー数をカウントし、sum(r[2]) から sum(r[7]) までは、それらのユーザーのうち翌日以降に再訪問したユーザー数をそれぞれカウントします。

ステージ 1 — ユーザー単位の保持配列を計算：

SELECT
  uid,
  retention(
    ds = '2017-11-25' AND event = 'pv',  -- 初日：アクティブ（ベースライン）
    ds = '2017-11-25',                   -- 初日の保持チェック
    ds = '2017-11-26',                   -- 2 日目
    ds = '2017-11-27',                   -- 3 日目
    ds = '2017-11-28',                   -- 4 日目
    ds = '2017-11-29',                   -- 5 日目
    ds = '2017-11-30'                    -- 6 日目
  ) AS r
FROM user_behavior_date
GROUP BY uid

ステージ 2 — 全ユーザーにわたる集計：

SELECT
  sum(r[1]),   -- 初日にアクティブだったユーザー数
  sum(r[2]),   -- 初日に保持されたユーザー数
  sum(r[3]),   -- 2 日目に保持されたユーザー数
  sum(r[4]),   -- 3 日目に保持されたユーザー数
  sum(r[5]),   -- 4 日目に保持されたユーザー数
  sum(r[6]),   -- 5 日目に保持されたユーザー数
  sum(r[7])    -- 6 日目に保持されたユーザー数
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 行 (2.96 秒)

構文

-- ユーザー単位の保持
retention_range_count(is_first, is_active, dt, intervals, outputFormat)

-- ユーザー全体への集約
retention_range_sum(retention_range_count_result)

パラメーター

retention_range_count：

outputFormat の値：

retention_range_sum：

例

以下の例では、2022 年 5 月 1 日～2 日にログインしたユーザーについて、2022 年 5 月 1 日～4 日のアクティビティに基づいて 1 日後および 2 日後の保持率を計算します。アクティベーションイベントは login、保持イベントは pay です。

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

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

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)        -- 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]]              |
+-------+-----------------------------+

各内側の配列には [開始日（日数）, 1 日後保持フラグ, 2 日後保持フラグ] が含まれます。たとえば、user3 は 5 月 2 日（日数 738642）にログインし、5 月 3 日（1 日後）および 5 月 4 日（2 日後）に支払いを行っています。

ステップ 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 |
+------------+--------------+--------------+

5 月 1 日には、2 人のユーザー（user1 および user2）がログインしました。そのうち、user1 のみが 5 月 2 日（アクティベーションから 1 日後）に支払いを行ったため、5 月 1 日の 1 日後保持率は 0.5 です。また、5 月 3 日（アクティベーションから 2 日後）に支払いを行ったユーザーはいなかったため、5 月 1 日の 2 日後保持率は 0.0 です。一方、5 月 2 日には 2 人のユーザー（user1 および user3）がログインしました。両者とも翌 2 日以内に支払いを行ったため、5 月 2 日にアクティベーションしたユーザーの 1 日後および 2 日後保持率はいずれも 0.5 です。