Dataを使ってアクセスログの可視化を行なった際に、分析クエリの設計のポイントを説明します。ページ遷移するアプリケーションで、特定のページのアクセス状況を可視化します。各ページが表示されるタイミングで、アクセスログとして、リクエスト日時、ユーザID、セッションID、ページIDが出力されているとします。本記事で紹介するクエリはPrestoで実行しています。分析対象のモデルアクセスログ(access_log)UU数、PV数、セッション数を計測する計測対象のページ(ページIDの「PAGE-0110」ページを対象とする。以下同じ)のUU数、PV数、セッション数はクエリ(LogAnalysis01.sql)で計測できます。page_idクエリの実行結果のイメージは以下となります。遷移元、遷移先のページを計測する次に計測対象のページを閲覧するきっかけとなったページ(遷移元)、計測対象のページの閲覧後に訪問したページ(遷移先)の情報を可視化します。Windows関数を使用して、アクセスログを加工します(Step1.sql)。LAG関数、LEAD関数を使うと指定したパーティション内の前後のレコードを参照できます。セッションID毎にリクエスト日時でソートしたパーティションを指定し、LAG関数で遷移元のリクエスト日時、ページIDを、LEAD関数で遷移先のリクエスト日時、ページIDを取得しています。クエリ実行後のテーブルのイメージは以下となります。LAG関数、LEAD関数で前後のレコードが存在しない場合に値は`NULLになります。対してクエリ(LogAnalysis02.sql)を実行すると、遷移元のページ毎のUU数、PV数、セッション数を計測できます。descクエリの実行結果のイメージは以下となります。変更すると遷移先のページを計測できます。外部サイトから直接、計測対象のページを訪問した訪問数は、遷移元の情報が存在しない条件と同義になります。クエリ(LogAnalysis03.sql)を実行すると、外部サイトから訪問した場合の計測対象のページUU数、PV数、セッション数を計測できます。page_idクエリの実行結果のイメージは以下となります。変更することで、離脱数、直帰数を計測できます。