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. Ad Hoc Analysis

    Adobe Analytics: Ad Hoc Analysisでセグメントの条件を一気に追加する方…

    Adobe Analyticsでセグメントを作るとき、「A または B…

  2. Google BigQuery

    Google Apps Scriptを使ってスプレッドシートからBigQueryのテーブルを更新する…

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

  3. Adobe Analytics

    Adobe Analytics: ランドスケイプの「企業ログ解析データベースAPI」と連携する

    こんにちは、エクスチュアCTOの権です。今回はランドスケイプの…

  4. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードす…

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

  5. Adobe Analytics

    Adobe Analytics: AppMeasurement.jsの実装開発作業に使うツール Ja…

    こんにちは、エクスチュアCTOの権です。今回はAdobe Anal…

  6. Adobe Analytics

    Adobe Analytics: DWHレポートの日付列をBigQueryのDate型として扱う

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

最近の記事

  1. Adobe AnaltyicsとGoogle Analyti…
  2. Tableau:抽出を含んだワークブックを10.4以下にダウ…
  3. Adobe Analytics: DWHレポートの日付列をB…
  4. AdobeAnalyticsでReactNativeアプリを…
  5. Adobe Analytics: BigQuery+Look…
  1. Adobe Analytics

    Adobe Analytics: VisitorAPIでSafariでもクロスド…
  2. Adobe Experience Cloud

    Adobe Marketing Cloud:サインインで問題が起きたときの対処法…
  3. Adobe Analytics

    Adobe Analytics:セグメントの落とし穴:滞在時間がおかしくなる
  4. Google Analytics

    Google Analytics StandardのデータをBigQueryで分…
  5. Adobe Analytics

    Adobe Analytics: SegmentsAPIを使って大量のセグメント…
PAGE TOP