gihyo.jpサイトのロゴ

これなら使える！ビッグデータ分析基盤のエコシステム

第7回アクセスログとマスタデータを使った応用KPI分析 ～準備編

2015-11-16

応用KPI分析: パス分析とは

前回までは、Webページ全体やページ単体のように点で捉える分析を基本KPI分析として定義していました。そのため、基本KPI分析では、そのページにアクセスしたユーザがどのような経路をたどってそこに辿りついたのかを焦点にせず、全てのアクセスを同等に扱っていました。

図1　Basic KPI
図1　Basic KPI

しかし、各ユーザはそれぞれ異なった目的を持って、異なったページを辿って同じ場所に辿り着いています。そこでユーザのパス情報を加味することでよりユーザに焦点を当てた分析ができると考えられます。このようなパスについての分析を、応用KPI分析として今回と次回で紹介していきます。ここでのパスとは、ユーザが自社のWebサイトに流入してから辿り着かせたいコンテンツに至るまで一連の経路を指します。

図2　Advanced KPI
図2　Advanced KPI

応用KPI分析をするためには、アクセスログのみではなく、複数の情報を組み合わせることで分析することができます。そのため、幾つかのテーブルを結合し、中間テーブルを作ることも必要になってきます。こうしたクエリの書き方の複雑さだけではなく、クエリ同士に順序関係や依存関係が発生してきてしまう分析だからこそ応用KPI分析と言えるでしょう。

そして、応用KPI分析の中のひとつであるパス分析では、ユーザのサイト上の遷移というパスを見ることによって、下記のような新たな概念がいくつも生まれてきます。PVやUUといった基本KPI分析のみを行ってきたユーザにとっては、まさに分析の新境地となることしょう。以下にいくつか例を挙げてみます。

パス平均長
コンバージョンまでにいくつのページを遷移したのかを算出します。これにより、コンバージョンの効率に関して理解することができます。
コンバージョン時間
Webサイトにきてからコンバージョンに至るまでの時間を算出します。これも同様にコンバージョンの効率に関して理解することができます。
パス類型
長さや組み合わせが膨大なパスに対して、特定のルールに従ってパスを分類し、その分類の中でどのパス類型がコンバージョンに寄与しているかを知ることができます。
スコアリング
パスの概念をもって改めてページを評価するには、パスの中でどの位置に出現するかによって重みを変えることによってパスの中でも重要度を発見できます。

パス分析をするためのテーブル情報

パス分析をするにあたり、2つのテーブルを分析エンジン上に用意します。

  1. アクセスログ
  2. コンバージョンマスタ

アクセスログは基本KPI分析で使ったテーブルです。コンバージョンマスタは、アクセスログ内のパス項目の中でコンバージョンに当たるレコードを抽出したもので、後付けでいくらでも再定義することが可能です。ここでのコンバージョンとは、自社のWebサイトに来たユーザにたどり着かせたいページを指します。今回は、トレジャーデータのWebページを回遊しているユーザを対象としたログデータとするため、ユーザがサインアップをしたことをコンバージョンと呼びます。

下記のテーブルの例は、「⁠サインアップ」「⁠リソースダウンロード」「⁠ラーニングページ」「⁠製品紹介ページ」を擬似的にコンバージョンポイントとして定義したコンバージョンマスタになっています。また、コンバージョンのページの他に、コンバージョンごとにレベルの設定を行い、重要度の管理もしておきましょう。

cv_levelcv_pathdescription
3/treasuredata.com/en/press-releasesプレスリリース参照
3/treasuredata.com/jp/press-releasesプレスリリース参照
3/treasuredata.com/en/success-storiesサクセスストーリ閲覧
3/treasuredata.com/jp/success-storiesサクセスストーリ閲覧
3/treasuredata.com/en/case-studiesケーススタディ閲覧
3/treasuredata.com/jp/case-studiesケーススタディ閲覧
4/treasuredata.com/en/whitepapersホワイトペーパーDL
4/treasuredata.com/jp/whitepapersホワイトペーパーDL
4/treasuredata.com/jp/webinarswebinar参加
5/treasuredata.com/en/signupサインアップ
5/treasuredata.com/jp/signupサインアップ
5/treasuredata.com/en/tryalトライアル登録
5/treasuredata.com/jp/tryalトライアル登録
CREATE TABLE IF NOT EXISTS `testdb`.`master` (
  `cv_level` INT,
  `cv_path` VARCHAR(255) NOT NULL ,
  `description` TEXT);

## descrptionは省いています
INSERT INTO `testdb`.`master` (cv_level, cv_path, description)
VALUES
  (3,'/treasuredata.com/en/press-releases',null),
  (3,'/treasuredata.com/jp/press-releases',null),
  (3,'/treasuredata.com/en/success-stories',null),
  (3,'/treasuredata.com/jp/success-stories',null),
  (3,'/treasuredata.com/en/case-studies',null),
  (3,'/treasuredata.com/jp/case-studies',null),
  (4,'/treasuredata.com/en/whitepapers ',null),
  (4,'/treasuredata.com/jp/whitepapers ',null),
  (4,'/treasuredata.com/jp/webinars',null),
  (5,'/treasuredata.com/en/signup',null),
  (5,'/treasuredata.com/jp/signup',null),
  (5,'/treasuredata.com/en/tryal',null),
  (5,'/treasuredata.com/jp/tryal',null);

これらのテーブルを見てわかる通り、応用KPI分析の一つであるパス分析を行うために必要な要素はたった2つの緩い条件のみとなります。

  • ユーザIDやCookieなどの個人を分別できるローデータをもっていること
  • ローデータの中でコンバージョンとなるIDやURLを時系列情報とともにもっていること

つまりここでのコンバージョンマスタは、コンバージョンの定義以外でもユーザ情報を持つ会員DBがあればそれでもいいですし、いろいろな社内の資産が使えます。それらをデータインポートで紹介したEmbulkを利用して分析エンジン上にインポートしましょう。

さて、今回は分析エンジン上でコンバージョンマスタを直接作成します。

コンバージョンパステーブルを作る

さきほどの2つのテーブルから、以下の手順でコンバージョンパステーブルを作成します。

  1. cookieでグルーピングして、時系列にソート
  2. コンバージョンページで区切ってコンバージョン1回目、2回目、…と設定

上記のコンバージョンパステーブルは、URLをノードにしたテーブルとなっていますが、ローデータに存在する項目なら何でもノードにすることが可能です。

図3　conversion path
図3　conversion path

全要素を結合したテーブルを作った方が便利ですが、今回は必要なカラムのみ抽出しています。また、分析エンジンとして利用しているMySQLではWindow関数のrow_number関数が使えないため、相関サブクエリで順番付けを行っています。

それではそのためにいくつかの中間テーブルを作っていきます。

コンバージョンの履歴テーブルの作成

まずは、コンバージョンをしたレコードの一覧を作成します。

CREATE TABLE conversion
SELECT MAX(path) AS path, MAX(cv_level) AS cv_level, cookie, time
FROM
(
SELECT path, cv_level, cv_path, cookie, log.time
FROM
(
  SELECT path, cookie, time, 1 AS one
  FROM accesslog
  WHERE cookie != '-'
) log
JOIN (
  SELECT cv_path, cv_level, 1 AS one
  FROM master
) master_t
ON ( log.one=master_t.one )
) log_master
WHERE path = cv_path
GROUP BY cookie, time

次に、コンバージョンをした順番をつけます。

CREATE TABLE conversion_history
SELECT
  path,
  cv_level,
  cookie,
  (SELECT COUNT(*) FROM conversion conv WHERE conv.cookie = conversion.cookie AND conv.time <= conversion.time) AS cv_id,
  time
FROM conversion
ORDER BY cookie, time

コンバージョンテーブルの作成

次に、アクセスログとコンバージョンの履歴を結合します。ここでは 2015-01-01 の1日を分析対象としています。

CREATE TABLE conv_temp
SELECT
    cookie, cv_id, cv_flag, cv_level, path, time, cv_time, cv_path
FROM
(
        SELECT
          cookie, path, cv_level, time,
          MIN(cv_id) AS cv_id,
          MIN(cv_time) AS cv_time,
          IF((MIN(UNIX_TIMESTAMP(cv_time))-UNIX_TIMESTAMP(time))=0,1,0) AS cv_flag,
          IF((MIN(UNIX_TIMESTAMP(cv_time))-UNIX_TIMESTAMP(time))=0,path,'') AS cv_path
        FROM
        (
                SELECT raw_data.cookie, raw_data.path, cv_id, cv_level, raw_data.time, cv_history.time AS cv_time
                FROM (
                  SELECT
                        cookie, path, time
                  FROM accesslog
                  WHERE cast(time as date)= '2015-01-01'
                  GROUP BY 1,2,3
                ) raw_data JOIN (
                  SELECT cookie, cv_id, path, cv_level, time
                  FROM conversion_history
                    WHERE  cast(time as date)= '2015-01-01'
                    GROUP BY 1,2,3,4,5
                ) cv_history ON raw_data.cookie=cv_history.cookie
                WHERE raw_data.time <= cv_history.time
        ) t1
        GROUP BY cookie, path, cv_level, time
        ORDER BY cookie, time
) t2
ORDER BY cookie, cv_id, time

各コンバージョンと各ユーザを1つのパスとして、各レコードに順番をつけていきます。これによりコンバージョンパステーブルを作成することができます。

CREATE TABLE conversion_path
SELECT cookie, cv_id, cv_flag, cv_level, path, time, cv_time,
( SELECT COUNT(*) FROM conv_temp conv
        WHERE conv.cookie = conv_temp.cookie
                AND conv.cv_time = conv_temp.cv_time
                AND conv.time <= conv_temp.time
) AS node_id
FROM conv_temp
ORDER BY cookie, cv_id, node_id
cookie44dc29b9-6b43-493b-a96d-fa7d04ebf29244dc29b9-6b43-493b-a96d-fa7d04ebf29244dc29b9-6b43-493b-a96d-fa7d04ebf292
cv_id111
cv_flag100
cv_level555
path/treasuredata.com/jp/signup/treasuredata.com/jp/home/treasuredata.com/jp/products
time2015-01-01 19:51:462015-01-01 19:51:372015-01-01 19:51:35
cv_time2015-01-01 19:51:462015-01-01 19:51:462015-01-01 19:51:46
node_id282628252824

非コンバージョンテーブルの作成

先ほどコンバージョンパスからは失われてしまった重要な情報があります。それは、コンバージョンに至らなかった非コンバージョンパスです。コンバージョンしなかったユーザの非コンバージョンパスは非常に巨大であったため、これまでのデータ分析では扱われずに捨てられてしまっていました。しかし、各々のパスのコンバージョン率を計算するためには、この非コンバージョンパスが不可欠となります。

まずは、非コンバージョンユーザのパスの一覧を作成します。

CREATE TABLE non_conv_temp
SELECT cookie, path, time
FROM
(
  SELECT
    raw_data.cookie, raw_data.path, raw_data.time AS time
  FROM
  (
    SELECT cookie, path, time
    FROM accesslog
    WHERE cast(time as date) = '2015-01-01'
  ) raw_data
  LEFT OUTER JOIN
  (
    SELECT cookie, cv_id, cv_level, time
    FROM conversion_history
    WHERE cast(time as date) = '2015-01-01' AND path = '/treasuredata.com/jp/sighup'
    GROUP BY 1,2,3,4
  ) cv_history
  ON
  ( raw_data.cookie=cv_history.cookie )
  WHERE cv_history.cookie IS NULL
  ORDER BY cookie, time
) t
WHERE cookie IS NOT NULL
ORDER BY cookie, time

パスの一覧から時系列に順番付けをしていくことで完成です。

CREATE TABLE non_conversion_path
SELECT cookie, path, time,
( SELECT COUNT(*) FROM non_conv_temp conv
  WHERE conv.cookie = non_conv_temp.cookie
    AND conv.time <= non_conv_temp.time
) AS node_id
FROM non_conv_temp
ORDER BY cookie, time, node_id
cookie0273fb37-6ce0-42c0-923a-5194089144860273fb37-6ce0-42c0-923a-5194089144860273fb37-6ce0-42c0-923a-519408914486
path/treasuredata.com/jp/home/treasuredata.com/jp/home/treasuredata.com/jp/home
time2015-01-01 19:51:022015-01-01 19:51:082015-01-01 19:51:14
node_id123

次回は、今回作ったコンバージョンテーブルを使って実際に応用KPI分析を行う方法を紹介します。

おすすめ記事

記事・ニュース一覧