アクセスログデータの前処理、ユーザIDとセッションの生成、URLの集約

2017年2月7日
この記事は連載「アクセスログの生ログ分析」の全 4 ページ中 4 ページ目です。

アクセスログデータの前処理

これまでの手順で取り込んだログデータはそのままでは分析に使いにくい。
今後の分析がやりやすいように、ある程度の前処理が必要になる。

ここでは前処理のポイントになるものを列挙する。

  1. 分析対象とするリクエスト行の抽出/削除
    取り込んだままのログデータに不要な情報が含まれることがある場合、必要に応じてそれらを削除する。アクセスログファイルからのデータ抽出時にフィルタリングしていない場合、ここで削除する。

    • ボットの抽出/削除
    • 画像の削除
  2. ユニークユーザとセッションの作成
    • 疑似ユニークユーザ
    • セッション
  3. 集計単位URLの指定
    • URLの分割
    • ダミーパラメータの除外
    • URLごとのPV数を集計する際、同じページとして扱うURLを集約
  4. 分析に必要な変数を追加
    • デバイス
      スマートフォン、PC、タブレット
    • 流入元識別
      広告、自然検索、直接流入、…

      • リファラを使う
      • 流入元識別用のダミーパラメータを使う(広告のキャンペーンコードなど)
    • その他分類(ページのトピックなど)
    • コンバージョン
      コンバージョンページへのアクセスをCV完了に
    • ランディングページ
      セッションの開始ページがランディングページ
    • ページ閲覧時間

このように変数(特徴量)を追加することで、実用的な分析ができるようになる。
ユーザIDが会員情報などと紐付けられていれば、属性ごとの行動も把握できる。

疑似ユニークユーザとセッションの作成

アクセスログでは1行が1ページビューである。一方でアクセス解析ではセッションやユニークユーザ単位での分析をすることも多く(今月トータルで○○セッション、うちPCは○○セッション。UU数は○○、…)、実現のためにはログにセッションIDやユーザIDをつける必要がある。

疑似ユニークユーザ

webサーバの設定でcookieを使ったトラッキングを行い、ユーザIDをログに出力している場合はユニークユーザの特定が可能。
デフォルトではそうなっていないため、webサーバのログにはユーザIDが残っていないことが多い。
その場合は便宜的に

が同一のものをユニークユーザとしてみなす。
同時に同一IPアドレスかつ同一ブラウザの複数ユーザからのアクセスがあれば仕方ない。
企業からのアクセスやモバイルの場合は普通にその可能性があるが、目をつぶるしかない。

単純にトラフィック元のIPアドレスにUserAgentの文字列をくっつけただけである。
UserAgentの文字列が長い場合はハッシュ化すればよい。

セッション

セッションの定義はあいまいだが、概念としては「同じユーザの1回の訪問(=一連のページビュー)」のこと。
あとはサーバや計測ツールの定義によって「1回の訪問」をどこからどこまでとするかが異なる。

セッションIDは

  • Webサーバ自体がセッションIDをログ書き出す→CMSなどのプログラム側で設定することで可能
  • サーバにそのようのような実装がない場合は機械的に「同じユーザ(ID)の最終PVから○分以内のページビューは同一セッションとしてカウントする」と扱うしかない。

標準的な定義では最後のページビューから30分以内のページビューを前のセッションに含め、
30分過ぎたページビューは新しいセッションとしてカウントすることが多い。

いずれにせよ前項のユニークユーザの識別ができていることが前提となる。
cookie単位でのIDがない場合はIPアドレスとブラウザ(UserAgent)の組み合わせでやるしかない。

ログから「最後のページビューから30分以内のページビュー」を識別するためには、
そのユーザの最後のページビューからの経過時間を新しい変数として追加する必要がある。

なおRDBでセッション分析や経過時間を扱う場合、ウィンドウ関数を使うことになる。オープンソースではMySQLやSQLiteはダメで、PostgreSQL(やその派生版RDB)でないと使えない。商用DBやデータ分析用のRDBならOK。

ウィンドウ関数の説明は長くなるので省略するが、詳細は以下などを参照。

http://lets.postgresql.jp/documents/technical/window_functions

前回のページビューの時刻をウィンドウ関数LAG()で取得し、
今回のページビューの時刻との差分をとれば前回のページビューからの経過時間が分かる。

経過時間が30分以内のページビューは継続セッションフラグを立てる。

各セッションの最初のページビューを抽出してセッションIDを付ける。
row_number() over()で連番を生成してセッションIDとする。

集計単位URLの指定

同一コンテンツ同一URLの原則が守られているならいいが、実際にはそうでないことも多い。そこで集計したいURLの単位で

  • URLごとのPV数を集計する際、同じページとして扱うURLを集約
    • 末尾の「/」の有無を統一
  • ダミーパラメータの除外

をする必要がある。

URLの集約

パラメータがなく、拡張子がなく、末尾に「/」の付いていないURLをすべて「/」付きに変換

URLのコンポーネント分割

ダミーパラメータの除外など今後の処理を行うためにURLの文字列を

  • (プロトコル+)ホスト名
  • パス
  • クエリ文字列
  • ハッシュ

に分割するのがいい。
サーバログにはパスとクエリ文字列しか残らない(ホスト名とハッシュは入らない)が、JavaScriptを使った計測ツールのログではホスト名やハッシュが付くことがある。

サーバログのrequest_pathからパスとクエリ文字列を抽出する場合

URLの入ったログからパスとクエリ文字列を抽出する場合

URLの列名がrequest_urlの場合

以上のようにURLをコンポーネントに分割しておくと、後述のクエリ文字列に対する処理などがやりやすくなる。

ダミーパラメータの除外

ダミーパラメータ名がutm_***, _gaの場合

URLを分割してクエリ文字列のみ抽出しておくとまとめて置換できる。

分析に必要な変数を追加

デバイス、ブラウザを変数として追加

デバイスとOSごとのセッションを集計してCSVファイルに出力する。

流入元識別

リファラを使う

流入識別のダミーパラメータが付いている場合

広告のリンク先URLでパラメータを付けている場合など

たとえば以下のようなDDLで定義されるキャンペーンマスタがあり、

キャンペーンを表すパラメータ名が「cid」の場合

UPDATE JOINを使ってキャンペーン名を結合する。

Series Navigation<< アクセスログデータをデータベース(PostgreSQL)に取り込む