ウィンドウファネル関数の基本構文と例 - Simple Log Service

ウィンドウファネル関数は、スライディングタイムウィンドウ内の連続イベントを追跡することで、ユーザー行動、アプリのトラフィック、プロダクト目標のコンバージョンを分析します。

ウィンドウファネル関数は、次の 2 つの構文をサポートします。

重要

分析文で文字列を使用する場合は、文字列を一重引用符 ('') で囲む必要があります。囲まれていない文字列または二重引用符 ("") で囲まれている文字列は、フィールド名または列名を示します。たとえば、 'status' は「status」という文字列を示し、status または "status" は「status」というフィールドを示します。

関数

構文

説明

SQL のサポート

SPL のサポート

ウィンドウファネル関数

window_funnel(sliding_window, timestamp, event_id, ARRAY[event_list01, event_list02...])

スライディングタイムウィンドウ内のイベントチェーンを検索し、チェーン内で連続するイベントの最大数を計算します。

データ内にイベント列 (event_id) が既に定義されている場合は、この構文を使用します。

√

window_funnel(sliding_window, timestamp, ARRAY[event_id=event_list01, event_id=event_list02...])

スライディングタイムウィンドウ内のイベントチェーンを検索し、チェーン内で連続するイベントの最大数を計算します。

より柔軟に、列挙されていないカスタムイベント値を定義する場合は、この構文を使用します。

√

仕組み

ウィンドウファネル関数は、スライディングタイムウィンドウ内のイベントチェーンを検索し、チェーンの先頭から一致する最長のシーケンスを特定し、その長さを返します。

この関数は、次のアルゴリズムを使用します：

定義したチェーンの最初のイベントから開始し、イベントカウンターを 1 に設定して、スライディングウィンドウを開きます。
スライディングウィンドウ内で、後続のイベントが正しい順序で発生した場合、カウンターをインクリメントします。
シーケンスが中断された場合、現在の検索を終了し、イベントカウンターのインクリメントを停止して、新しい検索を開始します。
すべての検索が完了した後、複数の一致が見つかった場合は、最も高いカウンター値を返します。この値は、一致した最長のイベントチェーンの長さを表します。

例えば、スライディングタイムウィンドウを 100 秒、イベントチェーンを Event 1 -> Event 2 -> Event 3 -> Event 4 -> Event 5 とします。実際のシーケンスが Event 1 -> Event 2 -> Event 4 -> Event 5 -> Event 1 -> Event 3 の場合、この関数は 2 を返します。これは、Event 1 から開始する最長の一致シーケンスが Event 1 -> Event 2 であるためです。

重要

検索は、定義したチェーンの最初のイベントから開始する必要があります。例えば、実際のイベントシーケンスが Event 2 -> Event 3 -> Event 4 の場合、この関数は 0 を返します。
検索には厳密な順序が必要であり、イベントをスキップできません。例えば、シーケンスが Event 1 -> Event 2 -> Event 4 の場合、Event 3 がスキップされているため、一致は Event 2 で停止します。したがって、Event 4 はカウントされません。

窗口漏斗函数

構文

ウィンドウファネル関数は、次の 2 つの構文をサポートします。

データ内にイベント列 (event_id) が既に定義されている場合は、次の構文を使用します：
```
window_funnel(sliding_window, timestamp, event_id, ARRAY[event_list01, event_list02...])
```
列挙されていないカスタムイベント値を定義するには、次の構文を使用します：
```
window_funnel(sliding_window, timestamp, ARRAY[event_id=event_list01, event_id=event_list02...])
```

パラメータ

パラメータ	説明
sliding_window	スライディングタイムウィンドウ (秒)。値は bigint 型である必要があります。
timestamp	タイムスタンプ (秒)。値は bigint 型である必要があります。組み込みの __time__ ログフィールドを使用することを推奨します。
event_id	A、B、C などのイベントを表すログフィールド。値は varchar 型である必要があります。
event_list	一致させるカスタムイベントチェーン。最大 32 件のイベントを含めることができます。値は array 型である必要があります。例： ARRAY['A', 'B', 'C'] ARRAY[event_id='A', event_id='B', event_id='C']

例

あるEコマースストアがプロモーションを実施し、ウィンドウファネル関数を使用してコンバージョンパフォーマンスを分析します。コンバージョンパスは、商品の閲覧、カートへの追加、商品の購入の 3 つのステップで構成されます。Simple Log Service がこれらのイベントのログを収集します。

次の例は、ログエントリ内のフィールドを示します：behavior_type:pv、category_id:149192、item_id:2073959、timestamp:1511860012、user_id:137617。

ログフィールド	説明
behavior_type	ユーザー行動の種類。有効な値： pv：商品の閲覧。 cart：カートへの追加。 buy：商品の購入。
category_id	商品のカテゴリ ID。
item_id	商品の ID。
timestamp	ユーザー行動が発生した時刻。
user_id	ユーザーの ID。

例 1

24時間のウィンドウでユーザーの購入行動を分析します。

クエリ文

* |
SELECT
  user_id,
  window_funnel(
    86400,
    timestamp,
    ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy']
  ) AS levels
GROUP BY
  user_id
ORDER BY
  user_id
LIMIT
  1000

結果
- ユーザー 24 の levels 値は 3 です。これは、ユーザーが「商品の閲覧 → カートへの追加 → 商品の購入」のフルパスを完了したことを示します。
- ユーザー 14 の levels 値は 2 です。これは、ユーザーが商品を閲覧してカートに追加したものの、購入は完了しなかったことを示します。
このクエリは 1,000 件のレコードを返します。テーブルは user_id と levels の 2 列で構成されます。

例 2

ファネルの各ステップを完了したユーザー数を分析します。

クエリ文

* |
SELECT
  levels,
  count,
  sum(count) over(
    ORDER BY
      levels DESC
  ) AS total
FROM  (
    SELECT
      levels,
      count(1) AS count
    FROM      (
        SELECT
          user_id,
          window_funnel(
            86400,
            timestamp,
            ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy']
          ) AS levels
        FROM          log
        GROUP BY
          user_id
      )
    GROUP BY
      levels
    ORDER BY
      levels
  )

結果
- 513,194 人のユーザーが商品を閲覧し、そのうち 138,491 人がこのステップで離脱しました。
- 374,703 人のユーザーが商品をカートに追加し、そのうち 198,642 人がこのステップで離脱しました。
- 176,061 人のユーザーが商品を購入しました。

例 3

プロモーションのコンバージョン率を計算します。

絶対コンバージョン率：特定のステップを完了したユーザー数が、全ユーザー数に占める割合。
相対コンバージョン率：特定のステップを完了したユーザー数が、直前のステップを完了したユーザー数に対する割合。

クエリ文

* |
SELECT
  *,
  100.0 * total /(sum(count) over()) AS "absolute conversion rate",
  if(
    lag(total, 1, 0) over() = 0,
    100,
    (100.0 * total / lag(total, 1, 0) over())
  ) AS "relative conversion rate"
FROM  (
    SELECT
      levels,
      count,
      sum(count) over(
        ORDER BY
          levels DESC
      ) AS total
    FROM      (
        SELECT
          levels,
          count(1) AS count
        FROM          (
            SELECT
              user_id,
              window_funnel(
                86400,
                timestamp,
                ARRAY [behavior_type='pv', behavior_type='cart',behavior_type='buy']
              ) AS levels
            FROM              log
            GROUP BY
              user_id
          )
        GROUP BY
          levels
      )
    ORDER BY
      levels
  )

結果
- このクエリは、levels、count、total、absolute conversion rate、relative conversion rate の 5 列からなるテーブルを返します。例えば、レベル 2 では絶対コンバージョン率が約 72.72% です。最終ステップのレベル 3 では、absolute conversion rate が約 34.17% で、relative conversion rate が約 46.99% です。
- ファネルグラフ