ログ解析にWindow関数を利用してみよう(寄与度)
Hitoshi Harada
分析の対象として前回ではセッションをターゲットに行いました。ログデータにはパラメータの情報が残されており、この情報をうまく分析できれば何か知見が得られるかもしれません。
条件パラメータの寄与度を調べる
どのような条件で検索した人が詳細ページまで遷移しているかがわかれば、Webサイトの最適化の一助となるでしょう。つまり、「最初の検索で行き先を指定した人の方が詳細まで遷移しているのか？それとも日付を指定した人の方が詳細まで遷移しやすいのか？」を調べることで、よりユーザフレンドリーなWebへの改善が見込めそうです。
行を返す関数
集めたログから条件の寄与度を調べるためには条件をキーとした集計をできれば良さそうですが、ログの単位はセッションやアクセス単位になっていて、なかなか条件をキーにした形にするのは難しそうです。 ここで、条件パラメータを hstore にしておいたことが役立ちます。
=# SELECT (each(hparams)).* FROM log_table_h LIMIT 10; key | value ---------+---------- sort | 安い順 region | ハワイ country | ハワイ島 sort | 安い順 region | ハワイ country | ハワイ島 date | 20100701 sort | 安い順 region | ハワイ country | ハワイ島 (10 rows)
hstoreを引数に取るeach関数は、hstoreに含まれる（キー、値）のペアを行にして返します。1行のログあたりキーの数だけ行を増やすイメージです。PostgreSQLではこのような関数を「テーブル関数」や「SETOF関数」、または単に「行を返す関数」と呼んだりしています。
each関数が返したデータをkey列で集計すれば検索条件の指定された回数は取得できそうです。但し、今回見たいのは「詳細まで遷移したセッションが指定した条件」ですので、セッションが詳細ページまで遷移したかどうかを調べておく必要があります。
最初の検索行動を調べる
今回は便宜的に問題を簡略化し、「最初の検索行動で指定した検索条件と、それが指定された時のセッションの遷移率」について調べてみたいと思います。まずはセッションが遷移したかどうかですが、前回と同様、
=# SELECT
sessionid, -- セッションID
hparams, -- 条件パラメータ
action, -- アクションID
seq, -- ログ通し番号
'ShowDetail' = ANY(array_agg(action)
OVER (PARTITION BY sessionid)) AS is_success
FROM
log_table_h
として、is_successがtrueならば遷移したセッション、そうでなければ離脱したセッションとしておきます。 次に「最初の検索行動で指定した検索条件」だけを抜き出したいので、さらに下記のようなSQLで中間テーブルTを作成します。
=# CREATE TABLE T AS
SELECT
*
FROM(
SELECT
sessionid, -- セッションID
hparams, -- 条件パラメータ
is_success, -- セッションは遷移したか？
-- 検索処理での通し番号
row_number() OVER (PARTITION BY sessionid ORDER BY seq) AS rn
FROM(
SELECT
sessionid,
hparams,
action,
seq,
'ShowDetail' = ANY(array_agg(action)
OVER (PARTITION BY sessionid)) AS is_success
FROM
log_table_h
)s1
WHERE
action = 'DoSearch' -- 検索処理のみ
)s2
WHERE
rn = 1 -- 最初の検索処理のみ
サブクエリ s1 は最初の SQL と同じものです。サブクエリ s2 でウィンドウ関数の row_number() を使いました。同じレベルのクエリで WHERE action = 'DoSearch' としているので、この時点でデータは検索処理のみに絞られており、row_number()は「検索処理の中での通し番号」を返します。さらにその外のクエリでWHERE rn = 1を指定し、「最初の検索処理」のみを抜き出しています。WHERE 句は同じレベルのクエリのウィンドウ関数より前に実行されますので、ウィンドウ関数の結果でフィルタをかけるときはさらにネストさせる必要があります。ウィンドウ関数と WHERE 句の処理順序について、間違えないように気をつけて下さい。
条件キーで集計する
ここまで来たら、最初の each() 関数を使うだけです。先程作成した中間テーブルTについて、「最初の検索行動の条件パラメータ」の条件キーを列挙し、条件キー毎に全セッション数と詳細まで遷移したセッション数を調べ、その割合を調査することができます。
=# SELECT
key,
count(DISTINCT sessionid) AS session_count,
count(DISTINCT CASE WHEN is_success THEN sessionid END)
AS success_count,
count(CASE WHEN is_success THEN 1 END)::float / count(*)
AS success_ratio
FROM(
SELECT
(each(t.hparams)).*,
sessionid,
is_success
FROM
T
) T1
GROUP BY key
ORDER BY success_ratio DESC;
「セッション数」という意味で count() の引数に DISTINCTを 使っていますが、実際の「遷移した割合」は DISTINCT があってもなくても結果は同じです。
結果と考察
条件パラメータのキー毎の寄与度の調査結果は以下のようになりました。
key | session_count | success_count | success_ratio ---------+---------------+---------------+------------------- date | 3192 | 2103 | 0.658834586466165 region | 8979 | 5340 | 0.594721015703308 sort | 11095 | 6442 | 0.580621901757548 country | 3702 | 2090 | 0.564559697460832 city | 1170 | 623 | 0.532478632478632 (5 rows)
今回の分析では、日付 (date) を指定して検索した人の方がその他条件を指定した人よりも数%高い確率で詳細ページまで遷移してくれたことがわかりました。従って、日付を軸に検索させるようなインターフェイスに作り替えることで、より詳細ページまで遷移してくれる人が増えそうです。
上記の結果は検索条件のキー毎に集計を行いましたが、もちろんキーと値について集計を行うことも簡単ですね。キーと値毎に統計を取れば、例えば行き先毎のセッション遷移率などもすぐに数値化することができるでしょう。
まとめ
Webサイトにおける条件パラメータの寄与度分析を実施しました。Webサイトのような大量の人が集まる場所では、少数の意見を聞くだけではなかなか全体像を掴むことができません。今回試したように全ログから統計的な結果を得ることで、論理的にサイトの最適化を行うことができるでしょう。
アクセスログのような形式をただ集計するだけでも一定の効果がありますが、今回ご紹介したようにPostgreSQLには連番を振って柔軟に行を選択するウィンドウ関数の機能や、1行に収められた複数のキーと値を行に展開する機能など、分析を行うには十分すぎる程のツールが用意されています。これらの機能を使いこなすことで、隠れた情報を浮かび上がらせましょう。