こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。
今回はAdobe AnalyticsのDatafeedログからBigQueryを使ってフォールアウトレポートを作成する方法についてです。
Adobe AnalyticsのデータをBigQueryにロードする方法は過去にこのブログでも何度か説明しましたので参考にしてください。
Adobe Analytics: データフィードをGoogle Compute EngineのLinuxインスタンスにSFTP転送する
Adobe AnalyticsからDWHレポートをGoogle Cloud Storageにアップロードする方法
Adobe AnalyticsのDatafeedをBigQueryのColumn-based Time-partitioned Tableにロードする
Compute EngineでSFTPサーバーを立てて、そこに放り込んだファイルをBigQueryにロードする手順が一番カンタンで手っ取り早いです。
で、以下はデータセット名「datafeed」、テーブル名「hit_data」にhit_data.tsvのデータをロードしている前提で進めます。
1. トップページ (index)
2. サイト内検索 (search)
3. サービス詳細 (services)
4. 問い合わせフォーム (inquiry)
5. 問い合わせ完了 (complete)
という5つのステップでのフォールアウトを出します。
そして、SQLそのものはここに貼ると長いので、Githubに載せておきます。
https://github.com/youngdongk/exture-aa-datafeed-query/blob/master/aa-fallout.sql
BigQueryで実行すると、結果はこうなります。
step … ステップ番号
pagename … チェックポイント対象のページ名
count … インスタンス
fallout … 起点ページから遷移率
step_fallout … 直前のチェックポイントからの遷移率
という結果のレポートになります。
以下、何をやってるか簡単に説明します。
まずは冒頭のCTE(with句)「base」において、フォールアウトのチェックポイント(ステップ)となるページをを定義します。
2番目のCTE 「aa_fallout」では、hit_dataから必要なデータを抽出します。
・セッションIDは、post_visid_high + post_visid_low + 訪問回数(visit_num)の３つを結合したものを使います。
・その同一セッション内に通過したページ名(post_pagename)と、最初と最後のタイムスタンプ(hit_time_gmt)を抽出してグループ化します。
・クリックのヒットは除外するため、post_page_event = 0 を指定してます。
3番目のCTE 「aa_fallout_v2」と4番目の「aa_fallout_v3」では、通過した各ステップのページのタイムスタンプがセッションの範囲内なのかというのと、ステップの順番通りに進んでいるかのチェックをしてます。
そして最後にレポートとして整形してすることで、フォールアウトレポートが出来上がります。
このデータを、Google Data StudioなどのBIツールに投入してグラフ化するともっと分かりやすくなります。
今回はAdobe AnalyticsのDatafeedログからBigQueryを使ってフォールアウトレポートを作成する方法について説明しました。
弊社では、Adobe認定エキスパート資格とGoogleCloud認定データエンジニア資格を持ったエンジニアが、Adobe Analyticsのデータフィードと、その他のデータソースを統合した分析基盤をGoogle Cloud Platform上に構築するサービスを提供しております。
お問い合わせはこちらからどうぞ。
