Adobe Analytics

Adobe Analytics: DatafeedのログからフォールアウトレポートをBigQueryで作成する

こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。

今回は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)の3つを結合したものを使います。
・その同一セッション内に通過したページ名(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上に構築するサービスを提供しております。
お問い合わせはこちらからどうぞ。

関連記事

  1. Adobe Analytics

    Adobe Analytics: データフィードをBigQueryで集計する

    こんにちは、エクスチュアCTOの権です。前回のブログで、Ado…

  2. Adobe Analytics

    Adobe Analyticsに入り切らないデータをBigQueryに投入する

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。Ado…

  3. Google Analytics

    Google Analytics StandardのデータをBigQueryで分析するための力技

    こんにちは、エクスチュアの権です。先月「Google Clou…

  4. Adobe Analytics

    Adobe Analytics:IF関数を使用した計算指標

    Adobe Analyticsで1年以上前についた計算指標に関数が使え…

  5. Adobe Analytics

    AdobeAnalytics:WordPressやHTML5内の動画を計測する

    こんにちは、エクスチュアの鈴木です。Adobe Analyti…

  6. Adobe Analytics

    Adobe Analytics + Google BigQueryでよく使うSQL例 6選

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。今回は…

最近の記事

  1. Adobe Analytics + Google BigQu…
  2. Adobe AnaltyicsとGoogle Analyti…
  3. TableauでTreasure Data上のデータへ接続す…
  4. Treasure DataからBigQueryにデータを移動…
  5. Adobe AnaltyicsとGoogle Analyti…
  1. Adobe Analytics

    Adobe Analytics: DWHレポートをAWSのS3バケットに配信する…
  2. Google BigQuery

    GCP: 今月のGCP課金額をslackに自動的に書き込む
  3. Adobe Analytics

    Adobe Analytics: Webサイト内で発生したJavascriptエ…
  4. プログラミング

    Node.jsでCSVファイル内のダブルクオートで囲まれたカラム内のカンマを除去…
  5. Tableau

    Tableau:累計ユニークカウント数を時系列グラフで表現する
PAGE TOP