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

今回集計したいコンバージョン情報

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

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

コンバージョンの定義

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

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

今回利用するテーブル

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

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	アクセス時間

コンバージョン集計

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

クエリ

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

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

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

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

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");

実行

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

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

まとめ

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

参考

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

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

今回集計したいコンバージョン情報

コンバージョンの定義

今回利用するテーブル

click_log

access_log

purchase_log

コンバージョン集計

クエリ

具体例

データの準備

ストーリー

ユーザ1

ユーザ2

ユーザ3

click_log

access_log

purchase_log

実行

まとめ

参考