- ナレッジ・ノウハウ
- 北原 直明
【GA4で始めるSQL】第2回 SELECT文編
Googleが公開しているGA4のエクスポートデータをもとに簡単な分析を行いながらSQLを学ぶシリーズの第2回目で、具体的なSQLの構文に入ります。今回はSELECT文を実行することでデータを抽出してみましょう。
>>オーリーズが分かる資料セット（サービス資料・事例集）のダウンロードはこちらをクリック<<
目次
はじめに：GA4の「BigQuery Export」機能とは
BigQueryは、GCP（Google Cloud Platform）が提供しているクラウド上のデータウェアハウス、つまりデータの蓄積の場です。
以前からありましたが、GA4（Google アナリティクス4）では、BigQueryへ簡単にデータをエクスポートすることができるようになりました（BigQuery Export）。
これによりユーザー行動のデータを細かい単位で分析できるようになったので、SQLを書ける人にとっては「より根本的なデータ」にアクセスできる機会が与えられました。
まずは、このBigQuery Export機能によって出力されるデータについてGoogleが用意しているサンプルデータセットを用いて確認してみましょう。
必要となるのはGCPアカウントのみです。
サンプルデータへアクセス
BigQueryを開く
まずはGCPからBigQueryを開くために上部の検索窓から、BigQueryを開きます。
BigQueryを開くと以下のような3つの部分に分かれます。
ペイン①：BigQuery内のサービス。今回はSQLワークスペースしか使わないので下部の<|マークを押下して閉じてしまっても構いません。
ペイン②：データセットを選択できます。今回はサンプル用のデータを利用するためこちらも利用しません。上部の|<マークを押下して閉じてしまっても構いません。
ペイン③：クエリを記述・実行するペインになります。
クエリを実行する：クエリ No.1
Google Developersに記載されているクエリを実行してみましょう。
SELECT
COUNT(*) AS event_count,
COUNT(DISTINCT user_pseudo_id) AS user_count,
COUNT(DISTINCT event_date) AS day_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`③のクエリ記述部にクエリをコピーして、すぐ上の実行ボタンを押してみましょう。
このようにクエリを実行することでデータを抽出できるのがSQLです。
今回のサンプルクエリではデータの行数・ユーザー数・データに含まれる日数を集計して取得しています。
SELECT文を実行する
先程は集計してしまっていたので実際のデータがわかりづらくなってしまっていました。
今回は集計する前の「生」のデータを見てみましょう。
ここからクエリの解説もしながらデータを確認していきます。
SELECT文の実行（全カラム取得）：クエリ No.2
SELECT
*
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210118`
上記のクエリを実行してみましょう。
以下のような結果が返ってくるはずです。
今回実施したのはSELECT文というデータを抽出するための構文で書かれたクエリになります。
基本的な構文は以下です。
SELECT [取得列の指定] FROM [取得元を指定]
SELECT文の実行（カラム指定で取得）：クエリ No.3
今回は[取得列の指定]は「*」で行いましたが、これは全てのカラム(列)を取得する際に使う記号です。
カラム名で指定することもでき、その場合は以下のような結果になります。
💡 データベースでは列のことをカラム、行のことをレコードといいます。
SELECT
event_date
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210118`
【余談】FROM句の「*」とは？
クエリ No.1のクエリではFROM句の後のデータの取得元が
bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*
となっていたのに対して
クエリ No.2・クエリ No.3では
bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210118
としています。
これらの差異についてはGA4のデータが「シャーディング」という特殊なデータ構造を取っているために起因したものとなります。
難しい内容になるので飛ばしてもらっても良いのですが興味のある人向けに解説します。
シャーディングを用いてデータを分割して保持すると、データ抽出時に計算量を減らすことができます。
GA4のデータの例では日付別にデータが分割されていて、末尾に日付が付加された独立したテーブルが日数分ある形になっています。それぞれの日付のテーブルにアクセスすることで単日分のデータ探索でデータの抽出を行うことができます。
一方でまとまった日数に対してクエリを実行したい場合もあり、その場合には「*」を利用することで複数のテーブルを一つのテーブルのように扱うことができます。
ただし、全てのテーブルを探索することになるため以下のようにデータの処理の量は増えることになります。
実はデータの処理量を減らす分割方法についてはパーティショニングというものもあり、こちらの方が基本的に使い勝手が良いためシャーディングの構造を作ることは少ないと思います。
SELECT文に組み合わせる「句」
💡 「句」とはSQLに加えることのできるコードのまとまりで他にWHERE句やEXISTS句などがあります。
WHERE句でレコードを指定する：クエリ No.4
WHERE句は取得するデータを絞り込みたいときに利用します。
FROM句の後に記載した判定式が正となるレコードのみを返します。
今回の例ではevent_nameが「page_view」のデータだけを指定します。
SELECT
event_date,event_timestamp,event_name
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210118`
WHERE
event_name = "page_view"
ORDER句でレコードの順序を指定する：クエリ No.5
ORDER句は取得するデータを並べ替えたいときに利用します。
FROM句の後に並べたいカラム名などを指定して並べ替えます。
昇順はASC、降順はDESCと記載します。
今回の例ではevent_timestampを昇順に並べ替えます。
SELECT
event_date,event_timestamp,event_name
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210118`
ORDER BY
event_timestamp ASC
💡 データベースでは大文字小文字の区別はない場合が多いです。例えば「SELECT」などは「select」と書いても問題なく実行することができます。
しかし、一般的に「SELECT」や「ASC」といった予約語は大文字で書くことが多く、統一しておくと視認性が上がります。
こういった取り決めをコーディング規約としてルール化しているところも多いと思われます。
まとめ
今回はSELECT文について実行することでデータを抽出してみました。
次回は今回使ったクエリを用いてデータを抽出しながらユーザー行動を分析してみましょう。
オーリーズは、クライアントの「ビジネス目標達成」に伴走するマーケティングエージェンシーです。
「代理店の担当者が自社の業界・戦略に対する理解が不足しており、芯を食った提案が出てこない」
「新規の広告出稿に関する提案が中心で、最終的なビジネスゴールに紐づく本質的な提案がもらえない」
「広告アカウントが開示されないため、情報が不透明で自社にノウハウやナレッジが蓄積しない」
既存の広告代理店に対してこのようなお悩みをお持ちの場合は、一度オーリーズにお問い合わせください。
オーリーズの広告運用支援では、①運用者の担当社数の上限を4社までに制限②担当者のKPIは出稿金額ではなくNPS（顧客満足度）③アカウントは広告主が保有することを推奨 しており、目先のコンバージョン増加にとどまらず、深い事業理解を基にしたマーケティング戦略の立案や実行支援が可能です。
オーリーズのサービス資料をダウンロードする（無料）
オーリーズのコーポレートサイト
支援事例（クライアントの声）
オーリーズブログ
