BigQuery Export機能を使用して、GoogleのDWHであるBigQueryへのデータの連携を行った後、目的に応じたデータを抽出したり、分析用のデータマートを作成するためにはSQLというデータベース操作用の言語を使用する必要があります。
プログラミング言語と比べると覚えるべき事は少なく、概観を知っておくだけでもデータ分析という文脈において役立つ場面は多いです。
本記事では「GA4データ分析を始める」と題して、下記の全3回の内、
第2回目のGA4データへのSQLの基本について解説します。
連載一覧
第1回 GA4とBigQueryを連携するメリットと連携方法をわかりやすく解説！
第2回【実例つき】GA4データへのSQLの基本について
第3回 GA4データを目的別に分析する方法をコード付きで解説！
GA4データへのSQL実行
BigQueryに連携したGA4データに対してSQLは、
BigQuery画面 > 左側のテーブル一覧から目的のGA4データのテーブル（analytics_XXX.events_）を選択 > クエリ
より実行できます。
BigQueryのドライラン機能により、右上に実行するSQLのデータ量が表示されるので、「実行」ボタンをクリックする前に想定以上のデータ量になっていないか確認しましょう。
定額ではなくオンデマンド料金でBigQueryを契約している場合、大体$5.00 per TB（毎月1TBまでは無料）の分析料金がかかります。
SQLを実行する際は、DMLというデータの内容自体を更新してしまうステートメントを実行しないように注意しましょう。
DMLは、INSERT（新規挿入）、UPDATE（更新）、DELETE（削除）などの句です。
SQL作成の便利ツール
SQL初学者の方や、なるべく楽をしたい方にはSQL生成系のツールもおすすめです。
・GA4 SQL（https://www.ga4sql.com/）
上記はGA4専用のSQL生成サービスで、DimensionsやMetrics、期間を入力することで、対応するSQLを生成します。
・ChatGPTなど生成AIの利用
「pagePath毎にPV数を集計するSQLを作成して」
など目的を入力して、大まかなSQLのテンプレートを生成することが可能です。
GA4データへのSQLの基本 PART1
GA4データへのSQLの基本について解説します。
下記のSQLはGA4データから、page_location（ページURL）毎のPV数を抽出するSQLです。
SELECT
COUNT(*) AS pageviews,
CAST(CONCAT(LEFT(event_date,4),'-',RIGHT(LEFT(event_date,6),2),'-',RIGHT(event_date,2)) AS date) AS date,
(SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location') AS page_location
FROM
analytics_XXX.events_*
WHERE
_TABLE_SUFFIX BETWEEN '20240101'
AND '20240103'
AND event_name = 'page_view'
GROUP BY
date,
page_location
ORDER BY
date ASC,
pageviews DESC
SQLはSELECTやWHEREなどのいくつかの句によって成り立ちます。
それぞれの句の役割を見ていきましょう。
SELECT句：
SELECT句では抽出する項目を選択します。
(SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = ‘page_location’) AS page_location
event_paramsという項目は配列構造（複数行のデータ項目が１つのまとまりとして１行に格納されているデータ構造）となっているため、UNNEST句でフラット化を行わなければ、page_locationという項目を抽出できません。
このような配列構造の項目を多数含んでいることがGA4データの特徴であり、難しさです。
COUNT(*) AS pageviews,
このCOUNT()は集計関数です。date、page_locationでGROUP BYしたデータをカウントしてPV数を抽出します。
GA4データへのSQLの基本 PART2
FROM句：
FROM句ではデータを抽出するデータセットとテーブルを指定します。「events_」の（アスタリスク）はBigQueryではワイルドカードを意味しており、events_と前方一致するテーブルを指します。
GA4連携データは「events_20240101」のような日付で区切られたパーティションテーブルとなっているため、このようなFROM句でのテーブル指定を行います。
WHERE句：
WHERE句では抽出するデータの条件を指定します。
_TABLE_SUFFIX BETWEEN ‘20240101’ AND ‘20240103’
パーティションテーブルの期間の指定は上記のように_TABLE_SUFFIX BETWEEN XXX AND YYY という形で行います。
パーティションテーブルの期間の指定を行わないと、全期間のテーブルが対象となり、分析データ量が大きくなってしまうので注意しましよう。
AND event_name = ‘page_view’
複数条件を追加する場合は「AND（かつ）」か「OR（または）」で条件を接続します。
今回はPV数を抽出するために、イベントをpage_viewイベントに絞って抽出します。
GROUP BY句：
GROUP BY句は特定の項目でグループ化して集計を行いたい場合に使用します。
GROUP BY
date,
page_location
今回はdate（日付）、page_location（ページURL）毎のPV数を抽出するために、この２つの項目でGROUP BYを行います。
ORDER BY句：
ORDER BY句では抽出した結果のデータが見やすくなるように並び替え（ソート）を行います。
ORDER BY
date ASC,
pageviews DESC
ASCは昇順ソート、DESCは降順ソートです。今回は日付順に、PV数が多いものから順に上に来るように並び替えを行います。
GA4データへのSQLの基本 PART3
さらにSQLの関数を使用してデータの抽出を工夫してみましょう。
・ページURLが「https://XXX-jp.com/product_detail/」から始まるデータのみ抽出するWHERE
(SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location') LIKE 'https://XXX-jp.com/product_detail/%'
LIKEは部分一致検索を行う句です。%はワイルドカードであり、0文字以上の任意の文字列を指します。つまり、
LIKE ‘https://XXX-jp.com/product_detail/%’
は、「https://XXX-jp.com/product_detail/」と前方一致するデータを指します。
・ページURLによってデータのラベリングを行うSELECT
CASE
WHEN ( SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location') LIKE 'https://XXX-jp.com/product_detail/%' THEN '商品詳細'
WHEN ( SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location') LIKE 'https://XXX-jp.com/search/%' THEN '検索ページ'
WHEN REGEXP_CONTAINS(( SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location'), 'https://XXX-jp.com/$') THEN 'トップページ'
ELSE
'その他'
END
AS page_location
ページURLによっていくつかのカテゴリ分けを行いたい場合、上記のようなCASE句を使用します。
REGEXP_CONTAINS()は正規表現を用いたパターン一致で、上記の場合、’https://XXX-jp.com/’と一致するという条件になります。
このようにSQLの句や関数を知っておくことで、より目的に即したデータの抽出が可能になります。
SQLで抽出したデータの利用
SQLで抽出したデータは、「結果を保存」よりCSVエクスポート、スプレッドシートへのエクスポート、BigQueryテーブルとして保存等の操作が行えます。
このようにSQLの結果を別テーブルとして保存することで、分析者が共用で使用するデータマートの作成や、分析結果の可視化、共有が可能です。
まとめ
「GA4データ分析を始める」の第２回として、GA4データへのSQLの基本について解説しました。
SQLを学ぶと、自然とデータやデータ構造への理解が深まり、探索的にデータを取り出したり、様々なカットで分析ができるようになる実感を得られると思います。
最初は、配列構造の項目を含んでいたり、パーティション化されているGA4データについて難しい印象を持つこともあるかと思いますが、簡単なSQLの構文から少しずつ慣れていくと良いでしょう。
第3回ではSQLでの分析や、BIツールへの連携など分析についてより実践的な内容を解説します。そちらもぜひご覧ください。
連載一覧
第1回 GA4とBigQueryを連携するメリットと連携方法をわかりやすく解説！
第2回【実例つき】GA4データへのSQLの基本について
第3回 GA4データを目的別に分析する方法をコード付きで解説！
KUIXではただ導入・開発するだけでなく、導入後の利活用を実現することに着眼した、データレイク・DWH・データマート・BIツールの選定・導入からレポート作成、運用、啓蒙・展開までトータルのコンサルテーションなどを行っています。
「データマート/データレイク/DWH/BIツールを導入したい」
「すでに導入しているがデータ活用、展開が進んでいない」など
お困りの方は、ぜひお気軽にご連絡ください！お問い合わせはこちらから
