TreasureDataのクエリでアクセスログを可視化する

17904 ワード
Window関数 SQL TreasureData SQL テキストリンク

はじめに

Treasure Dataを使ってアクセスログの可視化を行なった際に、分析クエリの設計のポイントを説明します。
ページ遷移するアプリケーションで、特定のページのアクセス状況を可視化します。各ページが表示されるタイミングで、アクセスログとして、リクエスト日時、ユーザID、セッションID、ページIDが出力されているとします。
また、本記事で紹介するクエリはPrestoで実行しています。

分析対象のモデル

アクセスログ（access_log）

UU数、PV数、セッション数を計測する

計測対象のページ(ページIDの「PAGE-0110」ページを対象とする。以下同じ)のUU数、PV数、セッション数はクエリ（LogAnalysis01.sql）で計測できます。

LogAnalysis01.sql
select
  page_id,
  count(distinct user_id) as uu,
  count(distinct session_id) as session_count,
  count(1) as pv
from
  access_log
where
  page_id = 'PAGE-0110'
group by
  page_id

クエリの実行結果のイメージは以下となります。

遷移元、遷移先のページを計測する

次に計測対象のページを閲覧するきっかけとなったページ（遷移元）、計測対象のページの閲覧後に訪問したページ（遷移先）の情報を可視化します。
Windows関数を使用して、アクセスログを加工します（Step1.sql）。LAG関数、LEAD関数を使うと指定したパーティション内の前後のレコードを参照できます。
セッションID毎にリクエスト日時でソートしたパーティションを指定し、LAG関数で遷移元のリクエスト日時、ページIDを、LEAD関数で遷移先のリクエスト日時、ページIDを取得しています。
また、対象ページの滞在時間を 遷移先のリクエスト日時 - 計測対象のページのリクエスト日時 で計算しています。

access_log_step1.sql
  select 
    request_datetime,
    user_id,
    session_id,
    page_id,
    LAG(request_datetime) OVER (PARTITION BY session_id order by request_datetime) as prev_request_datetime,
    LAG(page_id) OVER (PARTITION BY session_id order by request_datetime) as prev_page_id,
    LEAD(request_datetime) OVER (PARTITION BY session_id order by request_datetime) as next_request_datetime,
    LEAD(page_id) OVER (PARTITION BY session_id order by request_datetime) as next_page_id,
    LEAD(request_datetime) OVER (PARTITION BY session_id order by request_datetime) - request_datetime as stay_time
  from 
    access_log

クエリ実行後のテーブルのイメージは以下となります。LAG関数、LEAD関数で前後のレコードが存在しない場合に値は`NULLになります。

access_log_step1 に対してクエリ（LogAnalysis02.sql）を実行すると、遷移元のページ毎のUU数、PV数、セッション数を計測できます。

LogAnalysis02.sql
select
  prev_page_id,
  count(distinct user_id) as uu,
  count(distinct session_id) as session_count,
  count(1) as pv
from
  access_log_step1
where
  page_id = 'PAGE-0110'
  and prev_page_id is not null
group by
  prev_page_id
order by
  4 desc

クエリの実行結果のイメージは以下となります。 prev_page_id を next_page_id に変更すると遷移先のページを計測できます。

外部サイトからの訪問数、離脱数、直帰数を計測する

access_log_step1 を利用して、さらに外部サイトからの訪問数、離脱数、直帰数を計測できます。

外部サイトから直接、計測対象のページを訪問した訪問数は、遷移元の情報が存在しない条件と同義になります。
クエリ（LogAnalysis03.sql）を実行すると、外部サイトから訪問した場合の計測対象のページUU数、PV数、セッション数を計測できます。

LogAnalysis03.sql
select
  page_id,
  count(distinct user_id) as uu,
  count(distinct session_id) as session_count,
  count(1) as pv
from
  access_log_step1
where
  page_id = 'PAGE-0110'
  and prev_page_id is null
group by
  page_id

クエリの実行結果のイメージは以下となります。

クエリ（LogAnalysis03.sql）のWHERE句の条件 prev_page_id is null を変更することで、離脱数、直帰数を計測できます。
離脱数は、計測対象ページを閲覧したあとそのまま外部サイトに移動した場合で、計測対象ページの遷移先が存在しない条件 next_page_id is null となります。
直帰数は、外部サイトから計測対象ページを訪問したあとそのまま外部サイトに移動した場合で、計測対象ページの遷移元、遷移先が存在しない条件 prev_page_id is null and next_page_id is null となります。

計測対象のページから遷移後のページ遷移回数を計測する

計測対象のページから次のページに遷移した後、再び計測対象のページに戻ってくるまで、または遷移先で離脱するまでに回遊したページ遷移回数を計測します。
access_log_step1 を順番に加工して、計測に必要な情報を求めます。

まず、クエリ（access_log_step2.sql）では、計測対象のページから再び計測対象のページに戻ってくるまでの時間（ lead_request_datetime ）を付加しています。
便宜上、遷移先で離脱した場合に lead_request_datetime = 9999999999 の値が入るようにしています。
WHERE句の条件に計測対象ページのページIDを指定し絞り込んだ後で、LEAD関数で次のレコードを取得しているところがポイントになります。また、同一セッションIDの中のレコードを一意に識別できるようにROW_NUMBER関数で番号を付加しています。

access_log_step2.sql
  select
    request_datetime,
    user_id,
    session_id,
    page_id,
    COALESCE(LEAD(request_datetime) OVER (PARTITION BY session_id order by request_datetime), 9999999999) as lead_request_datetime,
    ROW_NUMBER() over (PARTITION BY session_id order by request_datetime) as row_number
  from
    access_log
  where
    page_id = 'PAGE-0110'

クエリの実行結果のイメージは以下となります。

次に、 access_log_step1 と access_log_step2 をJOINして、計測対象のページから再び計測対象のページに戻ってくるまでのレコードを1つのグループにまとめます。 access_log_step1 のテーブルに access_log_step2.row_number を付加しています。

access_log_step3.sql
  select
    access_log_step1.request_datetime,
    access_log_step1.user_id,
    access_log_step1.session_id,
    access_log_step1.page_id,
    access_log_step2.row_number
  from
    access_log_step1
  join
    access_log_step2
  on
    access_log_step1.session_id = access_log_step2.session_id
    and access_log_step1.request_datetime >= access_log_step2.request_datetime
    and access_log_step1.next_request_datetime < access_log_step2.lead_request_datetime

クエリの実行結果のイメージは以下となります。

access_log_step3 をセッションID、row_numberでグループ化すると、回遊したページ数を計測できます。（LogAnalysis04.sql）

LogAnalysis04.sql
  select
    session_id,
    row_number,
    count(1) as transitions
  from
    access_log_step3
  group by
    session_id,
    row_number

クエリの実行結果のイメージは以下となります。

LogAnalysis04.sql の実行結果をさらに、集約することでユーザ全体のページ遷移回数の傾向がわかります。（LogAnalysis05.sql）

LogAnalysis05.sql
select
  MIN(transitions) as transitions_min,
  APPROX_PERCENTILE(transitions, 0.25) AS transitions_p25,
  APPROX_PERCENTILE(transitions, 0.5) AS transitions_median,
  APPROX_PERCENTILE(transitions, 0.75) AS transitions_p75,
  MAX(transitions) as transitions_max,
  ROUND(AVG(transitions), 2) as transitions_avg
from
  LogAnalysis04

クエリの実行結果のイメージは以下となります。

おわりに

本記事ではアクセスログの可視化の方法について紹介しました。
SQLのクエリで分析できるものの、識別キーを持たないログを扱う場合にテーブルの結合条件で苦労する部分が多く、Windows関数を使った中間テーブルを設計していくことで解決できるところが多くあります。
また本記事では割愛していますが、実際にシステムで出力するログを扱う際にはノイズとなるデータを除くことや、同一の日時で出力されたログの扱いを決めるところも大切になります。特に、Window関数を使う場合には、指定したパーティションのなかでレコードの並び順が一意になるようにログ出力を設計する必要があります。