メインコンテンツに移動

2021.06.21

【SQLでローデータからレポートする流れを理解しよう】GA4/BigQuery編

前回の「5分で分かる!GA4と旧バージョンとの違いとは?GA4/GTM導入 応用編」では、Googleアナリティクス 4 (以下GA4) 、と旧GAのユニバーサルアナリティクス (以下UA) の主な違いとGoogleタグマネージャー(以下GTM)でGA4のイベントを計測する方法をご紹介しました。

今回はBigQueryを使ってGA4からStructured Query Language(以下SQL)でロー(生)データを出力する方法をご紹介します。


1.BigQueryを理解しよう

s-t-r-f-r-d-i-s-g-b-e_01BigQueryとは?

Google Cloud Platformで提供されているビッグデータを解析するサービスです。一般的には長い時間かかるクエリを、TB(テラバイト)、 PB(ペタバイト)のデータでも数秒~数十秒で処理することが可能となります。
クラウドなので、拡張性があり、コストパフォーマンスに優れています。

ストレージ料金(データ保存):毎月10GBまで無料
分析料金(クエリ処理):毎月1TBまで無料
※最初の90日間は、$300分の無料クレジット有
※各料金は以下のURLから最新の情報をご参照ください。

(参照)https://cloud.google.com/bigquery/pricing/?hl=ja

まずはBigQueryのページから、「無料トライアル」を試してみましょう。
https://cloud.google.com/bigquery?hl=JA

s-t-r-f-r-d-i-s-g-b-e_02

個人情報とクレジットカード情報を登録する必要はありますが、これだけで、簡単にBigQueryのアカウントが使用可能になります。

s-t-r-f-r-d-i-s-g-b-e_03

s-t-r-f-r-d-i-s-g-b-e_04

※無料トライアルから有料版に自動的にアップグレードされることはありませんのでご安心ください。

次にBigQueryにプロジェクトを作成してみましょう。
s-t-r-f-r-d-i-s-g-b-e_05

ここでは、プロジェクト名を「Test Project 123456789」と入力しています。
入力が終わったら、「作成」ボタンをクリックしてプロジェクトを作成します。

s-t-r-f-r-d-i-s-g-b-e_06

プロジェクトを作成すると、プロジェクトIDとプロジェクト番号が自動的に付与されます。

2.GA4とBigQueryを連携する

作成したBigQueryのプロジェクトとGA4を紐づけて、GA4からBigQueryにデータを送信する設定を行います。

s-t-r-f-r-d-i-s-g-b-e_07

GAの管理画面から該当するGA4のプロパティの編集欄から「BigQueryのリンク設定」をクリックします。

s-t-r-f-r-d-i-s-g-b-e_08

「リンク」ボタンをクリックします。

s-t-r-f-r-d-i-s-g-b-e_09

「BigQueryプロジェクトを選択」リンクをクリックします。

s-t-r-f-r-d-i-s-g-b-e_10

プロジェクトを一覧から選択します。
ここでは、先ほど作成した「Test Poroject 123456789」を選択します。

s-t-r-f-r-d-i-s-g-b-e_11

データロケーションを選択します。
ここでは、なるべくご自分の職場に近い場所(東京)を選択しておきます。

s-t-r-f-r-d-i-s-g-b-e_12

データストリームを「1/1個のデータストリーム」を選択し、頻度は「毎日」にチェックを入れます。

s-t-r-f-r-d-i-s-g-b-e_13

入力内容を確認して「送信」ボタンをクリックします。

s-t-r-f-r-d-i-s-g-b-e_14

これで、GA4とBigQueryのプロジェクトを紐づけることが完了しました。

s-t-r-f-r-d-i-s-g-b-e_15

※GA4のデータは設定した翌日からBigQueryにインポートされます。

3.GA4のローデータをBigQueryで出力する

あらためてBigQueryのコンソール(管理画面)を開いて、GA4のデータがインポートされているか確認しましょう。

s-t-r-f-r-d-i-s-g-b-e_16

BigQueryはプロジェクトとデータセットで構成されます。
1つのプロジェクトに1つのデータセットがあり、そこに“日毎”にテーブルが作成されます。

s-t-r-f-r-d-i-s-g-b-e_17

※「エディタタブ」を有効にしておくと編集画面が大きくなって使いやすくなります。

このブログは技術に詳しくない方でも読んで頂けるように意識して作成してあります。まずは著者が一番簡単だと考えているSQL構文を使ってデータを取得してみましょう。

BigQueryでは、データを集計して取得するためには、エディタにSQL文でクエリを書く必要があります。
主に以下の流れを実際にイメージして頂けるように、2、3の例をもとにGA4のデータを表示してみましょう。

① クエリを書く(構文にエラーがないかチェック)
② クエリを「実行」する(クエリを実行したときの容量も事前にチェック)
③ 結果が表示される(意図したデータが出てきているかチェック)
④ データの保存方法を選択

(1)恐らく一番簡単なSQL

SELECT
*
FROM
`dd-kensho-corporate.analytics_217119740.events_*`
LIMIT
3 ;

SQLの詳細解説は書籍などにお任せしますが、大まかに“プロジェクト「dd-kensho-corporate」のデータセット「analytics_217119740」からすべてのフィールド(イベント)の3行分だけ取得する”といった内容になります。

s-t-r-f-r-d-i-s-g-b-e_18

「実行」ボタンをクリックする前に、クエリを実行したときの容量もチェックしておいてください。

s-t-r-f-r-d-i-s-g-b-e_19

実行したクエリの結果が表示されます。
GA4のイベントが3行表示されていることがお分かり頂けますでしょうか。
イベント名(exent_name)にイベントパラメータ(キーと値)が5つぶら下がっていることが確認できると思います。

このデータ構造はネストといって、GA4のデータの取り扱いをちょっと難しいものにしています。

Googleのデベロッパーズのブログ記事では、一般的な(ネストされていない)データ構造を

s-t-r-f-r-d-i-s-g-b-e_20

と表現し、ネストされているデータ構造を以下の様に表現しております。
なんとなくイメージをつかめますでしょうか?

s-t-r-f-r-d-i-s-g-b-e_21

(参考)https://developers-jp.googleblog.com/2017/04/bigquery-tip-unnest-function.html

では、次に、このNEST構造ではなくなるように集計するUNNEST関数を使ったSQL構文でデータを表示してみましょう。

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
COUNT(1) AS pageviews
FROM
`dd-kensho-corporate.analytics_217119740.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20210521' and '20210522'
AND event_name = 'page_view'
GROUP BY page_location
ORDER BY pageviews DESC;

大まかに“プロジェクト「dd-kensho-corporate」のデータセット「analytics_217119740」からフィールド(イベント)に「page_location」を、パラメータをUNNESTした状態で、「page_location」の行数をカウントして「pageviews」として、2021年5月21日~20201年5月22日までのデータを「page_location」でグルーピングして、「pageviews」を降順で並び替えて表示”といった内容になります。

s-t-r-f-r-d-i-s-g-b-e_22

先程よりもGAのレポートっぽいデータになってきましたね。

最後にもう少し複雑なSQLを実行してみましょう。

WITH
pages AS (
SELECT PARSE_DATE('%Y%m%d',event_date) AS date,
user_pseudo_id AS user_id,
event_name,
(
SELECT value.int_value FROM UNNEST(event_params)
WHERE event_name = 'page_view' AND key = 'ga_session_id') AS session_id,
(
SELECT value.string_value FROM UNNEST(event_params)
WHERE event_name = 'page_view' AND key = 'page_location') AS page_location
FROM `dd-kensho-corporate.analytics_217119740.events_*`
WHERE _table_suffix = '20210521'
AND event_name = 'page_view')
SELECT date,page_location,
COUNT(page_location) AS pageviews,
COUNT(DISTINCT user_id) AS users
FROM pages
GROUP BY 1,2
ORDER BY date DESC,pageviews DESC

大まかに“プロジェクト「dd-kensho-corporate」のデータセット「analytics_217119740」からフィールド(イベント)を「event_date」を「年月日」の形式で、「page_location」を、パラメータをUNNESTした状態で、「page_location」の行数をカウントして「pageviews」として、「user_id」をカウントして「users」として、2021年5月21日のデータを「date」と「page_location」をグルーピングして、「pageviews」を降順で並び替えて表示”といった内容になります。

s-t-r-f-r-d-i-s-g-b-e_23

構文は先程よりも複雑になりましたが、より普段見慣れているGAのレポートに近づいたのではないでしょうか。

このようにSQLを駆使することで、GA4でも旧来のUAのようなデータが取得できるようになります。
といっても、前回の応用編でご紹介したような、カスタム定義でパラメータをカスタムディメンションに格納してレポートしたり、今回のBigQueryからSQLでデータを集計したりするなど、なかなかGA4のレポート集計はハードルが上がってしまっていると思います。

WEBやアプリの行動解析をわかりやすいプラットフォームで、そこまでデータリテラシーの高くない方々にも扱いやすかったGAが、“みんなのGA”ではなくなってしまっているとも言えます。(少なくとも2021年5月の段階では。)

勿論、昨今のCookie規制やユーザーのデジタルデバイスの多様化、機械学習の需要、BIツールでの複数のデータのリアルタムでの可視化などの要望に応えるツールになってきていることは間違いありません。

これからプラットフォームがアップデートを繰り返して、データリテラシーが高くても低くてもみんなの要求に応えてくれるツールになってくれることを期待しつつ、引き続きGA4の動向を追っていきたいと思います。

今回は、BigQueryを使ってGA4からStructured Query Language(以下、SQL)でロー(生)データを出力する方法をご紹介させて頂きました。

電通アイソバー(現 電通デジタル) データデザイン部では、データ活用に関するデジタルソリューション導入、データ活用コンサル等幅広く対応しております。
Googleアナリティクスや、AdobeAnalytics等、アナリティクス導入や現状の計測データ活用でお悩みやお困りのことがあれば、ぜひお問い合わせください。

※この記事は2021年5月現在で弊社にてGA4を検証しながら作成したものです。
 最新の情報はGoogleアナリティクスのヘルプをご参照頂き、変更点を随時ご確認ください。

吉國 勇也 Yuya Yoshikuni

プラットフォーム&データ本部 / プラットフォームコンサルティング部 プラットフォーム&データ本部 シニアデータマーケティングコンサルタント
システムエンジニアからキャリアをスタートし、アドテクノロジーの発達から2010年にアイソバーにジョインする。様々なツールを駆使して大手広告主のKPI達成に向けた広告効果の最適化・最大化を実現していく。現在は、マーケティングテクノロジーの啓蒙とクライアントのビジネスに貢献するべく課題設定、施策の改善提案、インハウス化支援などのコンサルティング業務を中心に行っている。
保有認定スキル:WACA上級WEB解析士 / Google Analytics Individual Qualification / Salesforce 認定 Marketing Cloud メールスペシャリスト

記事一覧

林 智広 Tomohiro Hayashi

プラットフォーム&データ本部 / プラットフォームコンサルティング部 プラットフォーム&データ本部 データマーケティングコンサルタント
2016年電通アイソバーに新卒入社。主にWebサイトアクセス解析、ダッシュボード構築、SNSコンテンツ分析、LINE配信コンテンツ分析、ソーシャルリスニングなどWeb領域の分析業務と、ペイドメディアの戦略策定、運用ディレクション業務に従事。現在もWebサイトアクセス解析・データ分析業務を中心としたコンサルティング業務を行っている。
保有認定スキル:Google Analytics Individual Qualification / Salesforce 認定 Marketing Cloud メールスペシャリスト

記事一覧

CX UPDATES TOP