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. Google Analytics

    Google Analytics 4: イベントパラメータをセッションスコープで集計する方法

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

  2. Adobe Analytics

    続・SafariのITP2.x対策として別サーバー使ってクッキーを永続化する

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

  3. Google BigQuery

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

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

  4. Adobe Analytics

    Adobe Analytics: VisitorAPIでSafariでもクロスドメイン計測をする

    この情報は2016年9月時点のものです。こんにちは、エクスチュ…

  5. Adobe Analytics

    【実験してみた】CNAMEを使わずにAdobeAnalyticsを1stパーティクッキーで計測する

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

  6. Adobe Analytics

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

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

最近の記事

  1. サーバーサイドGTM: ウェブコンテナを使って1stパーティ…
  2. BigQuery: テーブルに格納されたURL文字列をKey…
  3. AdobeAnalytics: Adobe I/OのAPIを…
  4. Google Analytics 4: イベントパラメータを…
  5. Google Analytics 4 + BigQueryで…
  1. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの…
  2. Adobe Experience Cloud

    Adobe Summit 2020レポート: Experience Cloud …
  3. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い① セグメ…
  4. Google BigQuery

    GoogleNext 2019レポート:3日目
  5. Cloud Dataproc

    BigQueryテーブルをAVRO形式でエクスポートしてHiveで扱う
PAGE TOP