Firebase Analytics

GoogleAnalytics Apps+Web プロパティをBigQueryと連携したついでにフラット化する

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

今回は、先日ベータ版がリリースされた「Google Analytics Apps+Webプロパティ」のデータをBigQueryと連携する方法についてです。

そもそもApps+Webプロパティってなんぞや?ですが、これはFirebase Analyticsがベースになっている新しい計測プラットフォームです。
なので、中身はUniversal Analyticsではなくて、Firebase AnalyticsにWeb用の計測機能がついたものです。

A new way to unify app and website measurement in Google Analytics

で、Firebaseがベースになってると何がいいかというと、BigQueryへのデータエクスポート機能がついている点です。

Firebase 料金プラン

BigQueryを使うためには、従量制のBlazeプランを選ぶ必要があります。
しかし、Analytics機能は無料で使えるので、「GoogleAnalyticsのデータを無料でBigQueryにエクスポート出来る」と行っても過言ではないです。
※注意: BigQuery/Google Cloud Platformの利用料金は別途掛かります。

ただし、あくまでもFirebaseスキーマなので、既存のGoogleAnalytics360スキーマではないので注意が必要です。

で、GoogleAnalytics Apps+Webの導入方法についてはSimo Ahava氏のブログやら、公式ドキュメントに任しといて、さらにその先のBigQueryとの連携についてが本題。

FirebaseコンソールでBigQueryと連携する

この作業はGoogle Analyticsの画面ではなく、Firebaseコンソールで行います。
Firebaseプロジェクトの [プロジェクトの設定] > [統合] > [BigQuery] を開き、[リンク]を選ぶと連携されます。
※連携した翌日から、毎日「前日分」のデータがBigQueryにエクスポートされます。

FirebaseコンソールからBigQuery連携をON

エクスポートされたデータをBigQueryで確認する

リンクしてから翌日にならないとテーブルは出てこないので、wktkしながら一晩寝かせます。
さて、翌日BigQueryを見てみましょう。

テーブルが出来上がってます

「analytics_{property_id}」というデータセットと、その下に「events_YYYYMMDD」というテーブルが出来上がってます。

データをプレビューしてみます。

エクスポートされたデータをプレビュー

パッと見でevent_paramsカラムがネストされた縦方向のデータで構成されている事がわかります。

スキーマ仕様はこちらにに掲載されているとおりです。
Firebaseヘルプ > BigQuery Export のスキーマ

Firebase/Apps+Webプロパティは「イベント」毎に一行のデータで構成されてます。
その各イベントに含まれる複数のプロパティ(GoogleAnalyticsで言うディメンション)がネストされています。

BigQueryのデータをフラットにする

ネストされたJSON型のスキーマだとBI・ダッシュボードツールと連携する時に扱いづらいんですよね。

というわけで、私はこのネストされたデータを横方向に展開して、フラットなヒットログに直してからBIツールに接続させてます。

そして出来上がったクエリがこちらです。

SELECT event_date, 
event_timestamp,
event_name,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engaged_session_event') AS engaged_session_event,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS ga_session_number,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS page_referrer,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS term,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'entrances') AS entrances,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'percent_scrolled') AS percent_scrolled,
event_previous_timestamp,
event_value_in_usd,
event_bundle_sequence_id,
event_server_timestamp_offset,
user_id,
user_pseudo_id,
user_first_touch_timestamp,
user_ltv.revenue,
user_ltv.currency,
device.category,
device.mobile_brand_name,
device.mobile_model_name,
device.mobile_marketing_name,
device.mobile_os_hardware_model,
device.operating_system,
device.operating_system_version,
device.vendor_id,
device.advertising_id,
device.language,
device.is_limited_ad_tracking,
device.time_zone_offset_seconds,
device.browser,
device.browser_version,
device.web_info.browser AS device_web_info_browser,
device.web_info.browser_version AS device_web_info_browser_version,
device.web_info.hostname,
geo.continent,
geo.country,
geo.region,
geo.city,
geo.sub_continent,
geo.metro,
app_info.id,
app_info.version,
app_info.install_store,
app_info.firebase_app_id,
app_info.install_source,
traffic_source.name,
traffic_source.medium AS traffic_source_medium,
traffic_source.source AS traffic_source_source,
stream_id,
platform,
event_dimensions.hostname AS event_dimensions_hostname
FROM `my-project-id.analytics_123456789.events_20190806`

今回はApps+Webプロパティを一切カスタマイズせずにGTMで実装したので、user_properties(カスタムディメンション)を一切使ってません。

そのため、上記のクエリからはuser_propertiesが抜け落ちますが、もしuser_propertiesを使った場合は、event_paramsのUNNESTを参考にしてクエリを直してください。

上記のクエリを実行すると、このようになります。

フラット化されたApps+Webプロパティのテーブル

event_paramsに格納されていた縦方向のデータが横方向に展開されました。
これなら扱いやすいですね。

ただ、ページビュー以外にもfirst_visitやsession_startなどのイベントも同様に一行として記録されるので、ここからページビューだけを集計したい場合は、
event_name = ‘page_view’ でフィルタする必要があります。

まとめ

GoogleAnalytics Apps+Webプロパティは無料でBigQueryエクスポートが出来るとうBigQuery大好き人間にはとても魅力的なWeb解析ツールです。
ただしまだこれはベータ版です。
今回は一切デフォルトのWebレポートUIの説明をすっ飛ばしてますが、EコマースレポートがなかったりUniversal Analyticsのノリで使うとアレがないコレがないとなります。
スキーマもGoogleAnalytics360のスキーマとは異なり、Firebaseスキーマです。

しかし、BigQueryの無料連携機能はとても強力な機能です。
※BigQuery/GCPの利用料金は別途掛かります。

いきなり乗り換えるのでなく、余裕があれば従来のGoogleAnalyticsとの併用をして見て下さい。

弊社はデジタルマーケティングからビッグデータ分析まで幅広くこなすデータ分析のプロ集団です。
コンサルティング/導入支援についてのお問合わせはこちらからどうぞ。

ブログへの記事リクエストはこちらまでどうぞ。

BigQueryのユーザー定義関数(UDF)をTableauで使う前のページ

AA(Adobe Analytics)を導入しよう!次のページ

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Cloud Dataflow

    Firebase AnalyticsのデータをフラットなCSVに変換する – Googl…

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

  2. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

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

  3. Adobe Analytics

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

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

  4. Google Analytics

    初めてのGoogle Analytics分析

    こんにちは、インターン生の松本です。今回は初めてのGoogle An…

  5. Google Analytics

    Google Analytics: ユニバーサルアナリティクスの拡張Eコマース用dataLayerを…

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

  6. Google Analytics

    Google Analytics 4 + BigQueryでよく使う基本的なSQL例

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

カテゴリ
最近の記事
  1. 【dbts25】Snowflake×PostgreSQLのニ…
  2. TROCCO dbt連携編
  3. KARTEの「フレックスエディタ(β)」登場!ノーコードでこ…
  4. dbt Projects on Snowflake使ってみた…
  5. Cortex Analystを使ってみた
  1. Google BigQuery

    BigQueryのユーザー定義関数(UDF)をTableauで使う
  2. Snowflake

    【速報】Snowflake Summit Opening Keynote 要約
  3. Application Integration

    Google Cloud iPaaS 「Application Integrat…
  4. IT用語集

    オンラインストレージ(Online Storage)って何?
  5. Mouseflow

    Mouseflow:検索の仕様
PAGE TOP