ユーザ行動ログを利用してBigQueryでコンバージョン集計を行う

ユーザのアクセスログ並びにクリックログと、商品購入ログのデータを利用してBigQueryでコンバージョン集計を行う方法を紹介します。

# 今回集計したいコンバージョン情報
ECサイトを例に、以下の上なコンバージョン情報を集計することにします。

1. ユーザが特定のURLをクリックする(メール等からのアクセス)
2. ユーザがサイト内を回遊する(同一セッションでアクセスし続ける)
3. ユーザが商品を購入したらコンバージョン

# コンバージョンの定義
今回はsession_idがない場合を想定します。そこで、上記を具体的にしたコンバージョンの定義を以下とします。

1. 以下の条件で新規セッション開始
  1. 前回のアクセスから、10分以上経過してからのアクセス
  2. 特定のURLをクリックする
2. 前回のアクセスから、10分以内にアクセスしてきた同一ユニークユーザを同一セッションとする
3. 特定のURLをクリックし、同一セッションで商品を購入した場合はコンバージョン
4. ユーザが商品を購入してから同一セッションで商品を購入した場合はコンバージョンの対象外とする

# 今回利用するテーブル
今回利用するテーブルを定義します。簡易化のため、必要なカラムのみに絞り込んで居ます。

## click_log
特定のURLをクリックしたときのログ。

|  カラム   |    型     |       説明       |
| --------- | --------- | ---------------- |
| uid       | int       | ユニークユーザID |
| url       | string    | クリックしたURL  |
| click_at | timestamp | アクセス時間     |

## access_log
ユーザがサイトにアクセスしたときのログ。

|  カラム   |    型     |       説明       |
| --------- | --------- | ---------------- |
| uid       | int       | ユニークユーザID |
| url       | string    | アクセスしたURL  |
| access_at | timestamp | アクセス時間     |

## purchase_log
ユーザが商品を購入したときのログ。

|   カラム    |    型     |        説明        |
| ----------- | --------- | ------------------ |
| uid         | int       | ユニークユーザID   |
| item_id     | string    | 購入したアイテムID |
| purchase_at | timestamp | アクセス時間       |

# コンバージョン集計
実際にコンバージョン集計をするクエリを紹介します。

## クエリ
以下がコンバージョン集計を行うクエリです。コメントで説明も記載しています。

```sql
WITH
  -- ①まずはじめに各種イベントを非正規化しUNIONで縦につなげます
  click_events AS (
  SELECT
    uid,
    'click' AS event,
    click_at AS event_timestamp,
    REGEXP_EXTRACT(url, r'campaign_id=(\d+)') AS campaign_id,
    NULL AS item_id,
  FROM
    `zozo-ma-dev.test_tajima.click_log` ),

  access_events AS (
  SELECT
    uid,
    'access' AS event,
    access_at AS event_timestamp,
    CAST(NULL AS string) AS campaign_id,
    NULL AS item_id,
  FROM
    `zozo-ma-dev.test_tajima.access_log` ),

  purchase_events AS (
  SELECT
    uid,
    'purchase' AS event,
    purchase_at AS event_timestamp,
    CAST(NULL AS string) AS campaign_id,
    item_id AS item_id,
  FROM
    `zozo-ma-dev.test_tajima.purchase_log` ),

  events AS (
    SELECT * FROM click_events
    UNION ALL
    SELECT * FROM access_events
    UNION ALL
    SELECT * FROM purchase_events ),

  -- ②「event情報」や「event時間と一個前のevent時間」をもとに、新規セッションにフラグ立て(session_flag=1)をします
  event_and_session_flag AS (
  SELECT
    uid,
    event,
    campaign_id,
    item_id,
    event_timestamp,
    -- イベントをuidごとにevent_timestamp昇順でならべて、LAG関数を利用し一個前のevent_timestampを取得
    LAG(event_timestamp) OVER (PARTITION BY uid ORDER BY event_timestamp) AS previous_event_timestamp,
    -- 新規セッションにフラグ立て(session_flag=1)
    CAST(
      -- 10分以上間隔が空いたアクセスは新規セッション
      DATETIME_DIFF(event_timestamp, IFNULL(LAG(event_timestamp) OVER (PARTITION BY uid ORDER BY event_timestamp), event_timestamp), MINUTE) > 10 OR
      -- クリックがあったら新規セッション
      event = 'click' OR
      -- ユーザが商品を購入してから同一セッションで商品を購入した場合はコンバージョンの対象外とするため新規セッション
      LAG(event) OVER (PARTITION BY uid ORDER BY event_timestamp) = 'purchase' AS INT ) AS session_flag
  FROM events ),

  -- ③session_flagを利用しセッションごとにユニークなIDを付与
  session AS (
  SELECT
    *,
    uid || '_' || SUM(session_flag) OVER (PARTITION BY uid ORDER BY event_timestamp, session_flag DESC ROWS UNBOUNDED PRECEDING ) AS user_session
  FROM
    event_and_session_flag
  ORDER BY
    event_and_session_flag.uid,
    event_and_session_flag.event_timestamp,
    event_and_session_flag.previous_event_timestamp ),

  -- ④clickとpurchase時のsessionのみ情報を準備しておく
  click_session AS (
  SELECT
    *
  FROM
    session
  WHERE
    event = 'click' ),

  purchase_session AS (
  SELECT
    *
  FROM
    session
  WHERE
    event = 'purchase' )

-- ⑤click_sessionとpurchase_sessionが同じ場合コンバージョン
SELECT
  click_session.uid,
  click_session.campaign_id,
  purchase_session.item_id,
  click_session.event_timestamp AS click_at,
  purchase_session.event_timestamp AS conversion_at
FROM
  click_session
INNER JOIN
  purchase_session
ON
  click_session.user_session = purchase_session.user_session;
```

# 具体例
上記のコンバージョン集計を実際に行ってみます。

## データの準備
まずは、テーブルとデータの準備をします。

### ストーリー
ユーザごとの行動を以下のようにします。

#### ユーザ1

- 特定のURLをクリック
- サイトを巡回
- 商品購入(コンバージョン)
- サイトを巡回
- 商品購入

#### ユーザ2

- 特定のURLをクリック
- サイトを巡回
- 特定のURLをクリック
- サイトを巡回
- 商品購入(コンバージョン)

#### ユーザ3

- 特定のURLをクリック
- サイトを巡回
- 10分後移行に再度サイトにアクセス
- 商品を購入

### click_log
```sql
CREATE TABLE
  test_tajima.click_log
(
  uid INT64,
  url STRING,
  click_at TIMESTAMP
);

INSERT INTO test_tajima.click_log VALUES
(1, "http://example.com?campaign_id=1", "2022-12-01 08:00:00"),
(2, "http://example.com?campaign_id=1", "2022-12-01 08:00:00"),
(2, "http://example.com?campaign_id=2", "2022-12-01 08:03:00"),
(3, "http://example.com?campaign_id=2", "2022-12-01 08:00:00");
```

### access_log
```sql
CREATE TABLE
  test_tajima.access_log
(
  uid INT64,
  url STRING,
  access_at TIMESTAMP
);

INSERT INTO test_tajima.access_log VALUES
(1, "http://example.com", "2022-12-01 08:01:00"),
(1, "http://example.com", "2022-12-01 08:02:00"),
(1, "http://example.com", "2022-12-01 08:03:00"),
(1, "http://example.com", "2022-12-01 08:05:00"),
(2, "http://example.com", "2022-12-01 08:01:00"),
(2, "http://example.com", "2022-12-01 08:02:00"),
(2, "http://example.com", "2022-12-01 08:04:00"),
(2, "http://example.com", "2022-12-01 08:05:00"),
(3, "http://example.com", "2022-12-01 08:01:00"),
(3, "http://example.com", "2022-12-01 08:12:00");
```

### purchase_log
```sql
CREATE TABLE
  test_tajima.purchase_log
(
  uid INT64,
  item_id INT64,
  purchase_at TIMESTAMP
);

INSERT INTO test_tajima.purchase_log VALUES
(1, 1, "2022-12-01 08:04:00"),
(1, 2, "2022-12-01 08:06:00"),
(2, 1, "2022-12-01 08:06:00"),
(3, 2, "2022-12-01 08:13:00");
```

## 実行
以下が実行結果です。実際にほしいコンバージョンデータが取れていることがわかります。

![Query_results_–_BigQuery_–_zozo-ma-dev_–_Google_Cloud_console.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/74514/be428033-53cb-7bb9-631e-e5bbe2a77fbf.png)

また、途中経過のである `session` は以下のようになっています。実際に定義し多様にユニークにセッションが振られているのがわかります。

![Query_results_–_BigQuery_–_zozo-ma-dev_–_Google_Cloud_console 2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/74514/ab916497-ea13-871c-49db-48c6a1ca044f.png)

## まとめ
今回session_idがない場合でのコンバージョンの集計処理を紹介させていただきました。

## 参考
今回のクエリは以下の記事を参考にさせていただきました。

https://datumstudio.jp/blog/%E3%82%BB%E3%83%83%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%92%E5%88%A9%E7%94%A8%E3%81%97%E3%81%9F%E3%82%A2%E3%82%AF%E3%82%BB%E3%82%B9%E3%83%AD%E3%82%B0%E3%81%AE%E5%88%86%E6%9E%90/