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

ホーム
Firebase Analytics, Google Analytics, Google BigQuery
GoogleAnalytics Apps+Web プロパティをBigQueryと連携したついでにフラット化する

2019.08.7

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で確認する

リンクしてから翌日にならないとテーブルは出てこないので、ｗｋｔｋしながら一晩寝かせます。
さて、翌日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との併用をして見て下さい。

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

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