トレジャーデータ(Treasure Data)ブログ

トレジャーデータ(Treasure Data)ブログです。

本サイトは移転しました。新サイトにリダイレクトします。

アクセスログ分析最前線:Treasure Data JavaScript SDK で始めるパス分析 その2

本記事は移転しました。新サイトにリダイレクトします。

はじめに

トレジャーデータが提供する Treasure Data JavaScript SDK は,他のアクセスログ収集ツールと同様に,HTML 内にタグを埋め込む事でアクセスに関する情報を収集することができます。

docs.treasuredata.com

前回はまず先にパス分析のアウトプットが何なのかを明示するため,いくつかのダイアグラムを紹介しました。

blog-jp.treasuredata.com

今回からはそのダイアグラムを得るための手順を紹介していきます。

分析フロー

f:id:doryokujin:20160616142359j:plain

実際にアクセスログからパス分析を経てダイアグラムを得るためには,上図のフローをとります。今回はトレジャーデータ内での処理(2.〜6.)をクエリを交えて説明します。次回では NYSOL, Graphviz パッケージを紹介し,可視化までに必要なコマンド群を解説します。

パス分析を行うための必要条件

本記事においては基本的にはTreasure Data Serviceのユーザーであること,つまり

  1. Treasure Data Javascript SDK でログが収集されている
  2. Treasure Data Serviceを利用できる(Presto/Hive クエリが実行できる,中間テーブルが作成できるなど)

を前提としています。もう少し前提を緩めたとすると,本質的な必要条件は以下になります:

  1. アクセスログが収集できている
  2. データベースに自由にアクセスできる(= データの読み書き,中間テーブルの生成など)
  3. SQL が実行できる(= TDクエリテンプレートが適用できる)
  4. 強力な分散処理エンジンを持っている(= Presto / Hive などの SQL エンジンが利用できる)

1. のアクセスログの収集に関しては他のツールでも可能でありますが,2.〜4. を満たすためには,そのアクセスログを csv ファイルにダンプして,Treasure Data Serviceに(カラム名をTreasure Data SDK に準拠するように変更して)インポートし直すのが現実的です。

また,外部アクセスログデータのダンプ→インポートはそれなりのハードルがありますので,新しく Treasure Data Javascript SDK をサイトに埋め込んで並行運用しながら試してもらう事をお薦めします。

※Treasure Data Serviceの利用検討や,パス分析を試してみたい方は お問い合わせ からご連絡下さい!

 

では、やってみましょう。

1. Raw Data(アクセスログ)テーブル

f:id:doryokujin:20160614130418p:plain

Javascript SDK によって収集されるテーブルは,あらかじめ項目名が定まっています。td_title(ページタイトル),td_url(URL), td_client_id(uuid)など,メジャーな項目は全ておさえてあります。このテーブルを Raw Data テーブルと呼ぶことにします。そしてパス分析は,この Raw Data テーブルさえあればできてしまいます。

2. Conversion Master テーブル

規定テーブル名:master_conversion

コンバージョンマスタ テーブルは,Raw Data テーブルの中の td_title(ページタイトル)の中で,サイト内のコンバージョンと見なせるページの値をリストアップした,非常にシンプルなものです。以下に例を挙げます:

f:id:doryokujin:20160616132407p:plain

トレジャーデータのサイトのコンバージョンを「資料ダウンロード」および「お問い合わせ」とします。このページに到達したユーザーをコンバージョンしたユーザーとします。ここに記述する td_title 値は,後で Raw Data テーブルと文字列マッチングをしますので,Raw Data 内に存在する値を入れて下さい。また,time カラムは使いませんので自動で付与される時間で問題ありません。

3. Conversion History テーブル 

規定テーブル名:itmd_conversion_history  ※ itmd_ は 中間(intermediate)テーブルの意味

ここからは,1. Raw Data テーブルと,2. Conversion Master テーブルを使って中間テーブルを作っていきます。Conversion History テーブルは,コンバージョンしたユーザーの td_client_id と,コンバージョンした時間: time を保存しただけのものです。また,一人のユーザーが複数回コンバージョンする可能性もあるので,cv_id にて何回目のコンバージョンかを記録しておきます。

クエリテンプレート

f:id:doryokujin:20160616134120j:plain

4. Conversion Path テーブル

規定テーブル名:itmd_conversion_path

このコンバージョンパステーブルこそが,あらゆるパス分析のインプットソースとなる貴重ななテーブルとなります。1. Raw Data テーブルと 3. Conversion History テーブルを用いて作成します。巨大な JOIN を伴う処理となりますので,Presto でメモリ不足によりうまくいかない場合は Hive の方で実行して下さい。

クエリテンプレート

f:id:doryokujin:20160616140314j:plain

 ↑ 今回作成したコンバージョンパステーブルです。ここで重要なポイントは,コンバージョンパステーブルは Raw Data テーブルを「集計」したテーブルではなく,

  • ユーザーごとに「グルーピング」,
  • 時系列に「ソート」,
  • コンバージョンしたユーザーのみの「抽出」,
  • コンバージョンポイントに達した時点で「切り出し」

を行った「部分」テーブルであることです。そしてこのコンバージョンパステーブルは,今回紹介するダイアグラムの作成以外にも様々な使い道があります。そこも追ってご説明していく予定です。

f:id:doryokujin:20160616140520p:plain

↑ 今回は td_title をノードのラベルと限定していましたが,上図のように page_id (URL) やカテゴリといった任意のカラムをノードラベルとして据えることができます。ただし,URL をノードラベルに採用する場合は,パラメータの消去などしっかりと正規化しておかないと大変なことになります。

5. Non Conversion Path テーブル

規定テーブル名:itmd_non_conversion_path

コンバージョンしなかったユーザーに関しても,同様の概念でテーブルを作成しておきます。

クエリテンプレート

f:id:doryokujin:20160616141606p:plain

コンバージョンしなかったユーザーは cv_id は意味を持ちませんので省略しています。ユーザーごとに見て node_id が 最大のレコードを見れば,そのユーザーが最後に見た離脱ページとなっています。

基本的にコンバージョンしないユーザーの方が10倍以上多いものですから,このテーブルもまた思いな集計処理が行われることに注意してください。そして,

Raw Data Table = Converion Path Table + Non Conversion Path Table

となっていることも明らかですね。

6. ダイアグラム作成用クエリ

Conversion Path テーブルに対して,ダイアグラム作成用のクエリを適用します。

クエリテンプレート

f:id:doryokujin:20160616143728j:plain

↑この出力をcsvファイルとしてエクスポートしておきます。この度紹介するダイアグラムでは 6 列目以降は用いません。

  • コンバージョンダイアグラム(遷移回数)→ 3 列目の値
  • コンバージョン率ダイアグラム → 5列目の値

を活用していきます。

 

その3に続く・・・・・