ログ解析にWindow関数を利用してみよう (遷移/離脱)

夏目伸彦

前回までで、ログデータを扱いやすい形のテーブルにしました。今回は具体的に分析を進めていきます。分析すべき内容はニーズによって様々ですが、今回は詳細ページへの遷移率、及びその他の要因との関係を見ていくことにします。

遷移と離脱

遷移率は検索画面において大切な指標になります。検索において、ユーザーが結果を見て次の画面に遷移するか離脱するかは、検索結果の品質や操作性が優れているか否かにかかっていると言えるでしょう。従って遷移率は是非知っておきたい情報です。さらに遷移したユーザとそうでないユーザを特定したとき、「それぞれがどのぐらいサイトに滞在していたか」や「サーバからのレスポンス時間がどうだったか」を調べると、検索サイトの改善ポイントが見つかるのではないかと仮定します。

まとめると、遷移したユーザと離脱したユーザがどれだけいるのか、またユーザーの遷移と相関のある要因はないか、ということを今回分析していきます。

遷移率

今回のデータにおいてはアクションが "ShowDetail" という値であった場合に詳細ページを開いたことを表します。従って、1行でもアクションが "ShowDetail" という値を持っているセッションは遷移、持っていないセッションは離脱と判定できそうです。以下の様なSQLで表現できます。

=# SELECT
     sessionid,
     'ShowDetail' = ANY(array_agg(action)) AS is_success -- 遷移したか否か
   FROM
     log_table_h
   GROUP BY sessionid;

array_agg() は特定の列を配列へと集約する集約関数です。逆に配列を行に展開する関数にはunnest() と言うものがあります。今回はカラム action を1つの配列に集約し、その配列内に "ShowDetail" があるか否かを判定し、その結果を真偽値として取得しています。

滞在時間

次にアクセス時間のデータを使って、滞在時間が求めてみます。セッション毎の滞在時間を求めるには、セッション毎に一番最初のログのアクセス時刻と最後のアクセス時刻を比べることで求めることができそうです。

=# SELECT
     sessionid,
     max(atime) - min(atime) AS sojourn_time -- 滞在時間
   FROM
     log_table_h
   GROUP BY sessionid;

セッション毎の最初のアクセス、および最後のアクセスを取得するには sessionid で GROUP BY をして、集約関数である min(), max() を用いると取得できます。それらの差が滞在時間となります。

平均レスポンス時間

さらにセッション毎のサーバからの平均レスポンス時間を求めてみましょう。検索アクションと一覧表示アクションでは処理が異なりますし、条件パラメータによっては重い処理も走るでしょう。しかしセッション毎に集約したレスポンス時間を比較することで、大まかに遷移したユーザと遷移していないユーザの違いを調べてみたいと思います。ここではシンプルにセッション毎の平均レスポンス時間を取得します。

=# SELECT
     sessionid,
     avg(elapse) AS avg_elapse -- 平均レスポンス時間
   FROM
     log_table_h
   GROUP BY sessionid;

組み合わせよう

ここまでで、

遷移率
滞在時間
平均レスポンス時間

の3つの値を求めてきました。

これら3つの値を sessionid 毎に比較することによってそれぞれの関係が見えてくるかもしれません。それぞれの値を sessionid で JOIN する方法もありますが、今回はウィンドウ関数を用いて表現してみました。

=# CREATE TABLE session_list AS
   SELECT
     sessionid,
     min(atime) AS min_atime, -- 初回アクセス時刻
     max(duration) AS sojourn_time, -- 滞在時間
     avg(elapse) AS avg_elapse, -- 平均レスポンス時間
     'ShowDetail' = ANY(ary_action) AS is_success -- 遷移フラグ
   FROM(
     SELECT
       sessionid, atime, elapse, action,
        -- アクセス開始からの経過時間
       atime - first_value(atime) OVER (w1 ORDER BY seq) AS duration,
        -- 同一セッション内のactionを集めた配列
       array_agg(action) OVER w1 AS ary_action 
     FROM
       log_table_h
     WINDOW w1 AS (PARTITION BY sessionid)
   )s
   WHERE 'DoSearch' = ANY(ary_action) -- ★
   GROUP BY sessionid, ary_action;

★のWHERE句では、検索エンジンのロボット等のノイズの除去をしています。今回の分析対象のシステムでは、"DoSearch"を一度も行っていないセッションは検索エンジンのロボットであると見なすことが出来るため、そのようなセッションを除去しています。

この結果をsession_listというテーブルで保存します。session_listは以下の様な結果になります。 (注)幅の関係で表示を2段に分けて記述しています。

            sessionid             |      min_atime      | sojourn_time |
----------------------------------+---------------------+--------------+
 000edad18e98200f585306e7a300fd8f | 2010-05-16 22:58:40 | 00:05:21     |
 0014017dc3d53874369fb28e131c24fc | 2010-05-18 23:43:54 | 00:01:21     |
 001a605ef40f449f2a27a27fd25ab97e | 2010-05-17 14:32:35 | 00:02:33     |
 0023e9cb9713e60a78e0c8b9569d3aef | 2010-05-13 11:47:17 | 00:26:22     |
 0027b1ac70fcef9625e019ded833d51e | 2010-05-17 22:09:08 | 00:00:02     |
 0028fef109a0c50bcb7561737544531a | 2010-05-13 08:38:44 | 00:01:18     |
 002adc07c52474abe98e06f6c876f181 | 2010-05-13 21:02:01 | 00:17:07     |
 002b8a5e87e7b3016f364e9f0beeabea | 2010-05-17 23:50:19 | 00:03:39     |
 002c12c917cf2691f4301f2944a6f5a9 | 2010-05-15 16:14:01 | 00:02:08     |
 002f9038a7880f7a508941ec5157d6ab | 2010-05-13 06:14:04 | 00:04:09     |
・
・
・
|      avg_elapse      | is_success 
+----------------------+------------
| 246.9545454545454545 | f
| 252.2400000000000000 | f
| 121.0000000000000000 | t
| 260.2000000000000000 | t
|  74.6666666666666667 | f
|  75.2222222222222222 | f
| 122.1403508771929825 | t
| 261.2142857142857143 | f
| 139.8000000000000000 | f
|  63.8000000000000000 | t
・
・
・

これでセッション毎の行動傾向が出ました。1セッションずつ見ていくのも興味深いですが、遷移率とその他の要因の相関等を見いだすのはまだ少し困難です。もう少し集約した結果を求めてみたいところです。作成した session_list テーブルを、日毎、及び遷移と離脱のそれぞれの区分で、滞在時間と平均レスポンス時間を集計してみましょう。

=# SELECT
    date_trunc('day', min_atime)::date AS atime_head, -- 日付
     CASE WHEN is_success
       THEN '遷移' ELSE '離脱' END AS result, -- 遷移したか否か
     count(*)::float /
       (lag(count(*)) OVER (
         PARTITION BY date_trunc('day', min_atime)::date
         ORDER BY is_success
         ) + count(*)) AS ratio, -- 遷移セッション率
     count(*) AS session_count, -- 時間中セッション数
     avg(sojourn_time) AS avg_sojourn, -- 平均滞在時間
     avg(avg_elapse) AS avg_elapse -- 平均レスポンスタイム
   FROM
     session_list
   GROUP BY atime_head, is_success
   ORDER BY atime_head, is_success;

date_trunc() は timestamp 型等の時間を表すデータから日付や時間といった任意の項目を取得できます。非常に便利ですので覚えておきましょう。また、遷移セッション率を求めるにあたり、ウィンドウ関数の1つである lag() を用いています。lag() は対象となるウィンドウフレーム内の前の行の値を取得することが可能です。該当する値が取得できない場合にはNULLを返します。今回は1行前の離脱のセッション数を取得し、遷移率を求めています。

上記のSQLの結果は以下の様になります。

 atime_head | result |       ratio       | session_count |
------------+--------+-------------------+---------------+
 2010-05-13 | 離脱   |                   |           835 |
 2010-05-13 | 遷移   | 0.569143446852425 |          1103 |
 2010-05-14 | 離脱   |                   |           744 |
 2010-05-14 | 遷移   | 0.566433566433566 |           972 |
 2010-05-15 | 離脱   |                   |           662 |
 2010-05-15 | 遷移   | 0.598544572468163 |           987 |
 2010-05-16 | 離脱   |                   |           799 |
 2010-05-16 | 遷移   | 0.580136626379401 |          1104 |
 2010-05-17 | 離脱   |                   |           789 |
 2010-05-17 | 遷移   | 0.590342679127726 |          1137 |
 2010-05-18 | 離脱   |                   |           824 |
 2010-05-18 | 遷移   | 0.580234335201223 |          1139 |

|   avg_sojourn   |      avg_elapse      
+-----------------+----------------------
| 00:05:50.269461 | 336.8352476866804873
| 00:30:58.737081 | 214.0190453271945792
| 00:20:29.025538 | 315.3769504668602304
| 00:27:45.279835 | 204.8017418108089652
| 00:08:07.196374 | 299.1762202049145926
| 00:27:28.381966 | 209.5022609642337360
| 00:04:49.403004 | 319.5872817535458209
| 00:31:04.747283 | 215.0614037266732208
| 00:05:15.064639 | 311.6847237523766289
| 00:30:19.948989 | 215.3729584115934428
| 00:02:57.652913 | 301.6144638714504254
| 00:16:28.504829 | 235.4392991406422371
(12 rows)

上記の結果を見ると

遷移の方が滞在時間が10～30分長い
遷移の方がレスポンス時間が約100ミリ秒短い

ことが窺えます。滞在時間が長い方が遷移の確率が上がっているということは、すぐに閉じたくなってしまうページではユーザの興味を惹かないということがわかります。また、レスポンス時間については速ければ速い程遷移率も上がるようです。一般に「レスポンスが速くなるとサイトのトラフィックや収益も上がる」と言われていますが、それを実証するデータが取得できたと思います。アプリケーションやDBをチューニングして快適な検索を提供することでサイトの収益に貢献できるということがはっきりしたので、次はゴリゴリチューニングしたいですね！

まとめ

ログデータから詳細ページへ遷移したユーザと、検索で離脱したユーザを比較し、遷移率と相関のある要因について調査しました。SQLの集約機能を使うと一度に見きれない情報をわかりやすく纏めることができ、全体像を掴むのに役立ちます。また、Webアプリのログのような時間軸をもったり順番に意味があるようなデータの場合、ウィンドウ関数をうまく使うことで前後関係を調べたり、何行か先のログの情報を使ってデータを処理することで新たな情報が見えてきます。集約機能やレポーティング機能は PostgreSQL が得意とする部分です。うまく使いこなして重要な事実を発掘しましょう。

(2010年8月16日公開)

ログ解析にWindow関数を利用してみよう (遷移/離脱)

ログ解析にWindow関数を利用してみよう (遷移/離脱)

夏目 伸彦

遷移と離脱

遷移率

滞在時間

平均レスポンス時間

組み合わせよう

まとめ

夏目伸彦