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のDatafeedをBigQueryのColumn-based Tim…

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

  2. Adobe Analytics

    Adobe Analytics: Webサイト内で発生したJavascriptエラーの内容をAdob…

    こんにちは、エクスチュアCTOの権です。今日は、Webサイト内で発…

  3. Adobe Analytics

    Adobe Analytics: Legacy s_code.jsからAppMeasurement.…

    こんにちは、エクスチュアCTOの権です。今まで個人ブログで技術メモ…

  4. Adobe Analytics

    Adobe Analytics: BigQueryにロードしたデータフィードをDataStudioで…

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

  5. Adobe Analytics

    Adobe Analytics: SegmentsAPIを使って大量のセグメント設定を作成・更新する…

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

  6. Adobe Analytics

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

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

最近の記事

  1. AdobeAnalyticsでReactNativeアプリを…
  2. Adobe Analytics: BigQuery+Look…
  3. Looker: エンジニアがBIで分析ダッシュボードを作る
  4. Adobe Analytics: DWHレポートをAWSのS…
  5. Adobe Analytics: レガシーs_code.js…
  1. Adobe Analytics

    Adobe Analytics:ワークスペース:セグメント比較機能の紹介
  2. Tableau

    Tableau:2つの値の比較に便利な「ダンベルチャート」を作る
  3. Firebase Analytics

    Firebase AnalyticsのデータをフラットなCSVに変換するETL処…
  4. ObservePoint

    ObservePoint:強力でアツい機能を持つサイト監査ツールの紹介
  5. Adobe Audience Manager

    Adobe Audience Manager: REST APIを使ってTrai…
PAGE TOP