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との併用をして見て下さい。

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

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

ピックアップ記事

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

関連記事

  1. GA 360 Suite

    BigQuery: Google Analytics 360のネストされたデータをフラット変換するS…

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

  2. Google Analytics

    Google Analytics フィルタ①

    こんにちは!今回はGAの機能の一つである「フィルタ」について解説したい…

  3. Adobe Analytics

    AA + GA : SafariのITP2.1に備えてphpでクッキーを永続化する

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

  4. Google Analytics

    【GA4】推奨イベント・カスタムイベントの設定方法

    こんにちは、エクスチュアの岩川です。以前GA4のイベ…

  5. GA 360 Suite

    GoogleDataStudio:複数のデータソースにフィルターを適用する

    こんにちは。エクスチュアの渡部です。今回はDataStudio(デ…

  6. Adobe Dynamic Tag Manager

    Tag Manager: Adobe DTM で Google Analytics (gtag.js…

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

最近の記事

  1. LangChainのソースコードから実装を見てみる(Chat…
  2. Tableau×Teams連携
  3. AIを使ったマーケティングゲームを作ってみた
  4. Snowflakeや最新データ基盤が広義のマーケティングにも…
  5. 回帰分析はかく語りき Part3 ロジスティック回帰
  1. Google Analytics

    【GA4】DebugViewの使い方
  2. ブログ

    「Power BI」を使ってみよう!
  3. Tableau

    Tableauのモデリング関数を理解する
  4. ブログ

    夏休みのまとめ
  5. Google Analytics

    Google Analytics4 を知る。
PAGE TOP