4日目：SQLの入門(書籍の紹介)とGA4のクエリの特徴

はじめに
SQLの初心者向けの本
- SQL初心者向けの本の選び方
- SQL初心者向けの本の紹介
GA4特有のSQLのはなし
おわりに

はじめに

今回はデータの抽出に欠かせないSQLについてみていく。具体的にはSQLの基礎を固める本の紹介や選び方のポイントを紹介する。また、GA4のデータの特徴的なネスト構造についても触れる。

SQLの初心者向けの本

SQL初心者向けの本の選び方

SQLは活用の幅が広いため、一口にSQLの入門書といっても対象ユーザーが全く違うことがある。SQLのユーザーは大きく、データを分析するアナリストとデータを整備するエンジニアに分けられる。前者で必要となる知識はSELECT文を中心としたデータの抽出方法である。一方で後者ではデータの抽出だけでなく、更新・追加・削除といった操作やデータベースの定義などの操作も必要となる。

BQに格納されたGA4データを集計してレポート作成がしたいと思っているユーザーは前者の知識で十分である。また、BigQuery活用の第1歩として、難易度的にデータ集計は取り組み易いといえる。そこでここからは分析向けの本を中心に紹介していく。

本のざっくりした見分け方としては、「分析」「活用」といったワードが入っていると前者向けの本であることが多い。逆に「入門」と書かれていてもエンジニア向けの本であることもあり、データ集計以外の操作方法が詳しい書籍であることも多い。

SQL初心者向けの本の紹介

ここでは初心者向けの本として４冊紹介する。英語で例えると、1,2冊目はSQLの文法、3,4冊目はSQLのワークブックのような本である。

1冊目は「SQLデータ分析・活用入門」である。自分はこの本でSQL入門した。本の構成としては1,2章でSQLを使ったデータ分析の概要説明、3~7章でSQLの解説、8~10章でSQLを使った分析例といった構成となっている。SQLの解説ではSELECT文、集約関数、WITH句、JOIN、ウィンドウ関数といった基本的なデータ集計に必要な知識は揃っている。

<Amazonリンク>
SQLデータ分析・活用入門

2冊目は「10年戦えるデータ分析入門」である。本の構成としては、1,2章でデータベースの入門、3~10章でSQLの解説、11~13章で(今っぽい?)データベース構築の概論となっている。SQLの解説ではSELECT文、集約関数、JOIN、ウインドウ関数などを紹介している。 ※サブクエリは紹介紹介しているものの、 WITH句は紹介されていない。
この本ではデータベース構築の概論にふれている。内容としては細かい話ではなく、データウェアハウス(DWH)をはじめとする、今っぽいデータアーキテクチャを説明している。そのため、データの整備をしない人でも、データの整備を依頼する際に、参考となる。特に(ボリュームとしてはそんなに多くないが、)日本語の本としては少ないDWHについて説明している点が特徴といえる。ただ、この本は再販されていないため、中古or電子書籍となる。

<Amazonリンク>
10年戦えるデータ分析入門

3冊目は「ビッグデータ分析・活用のためのSQLレシピ」である。本の構成としては、1,2,9章でデータ分析の概要、3~8章では活用イメージごとのSQL例が書かれており、自分の課題感に合わせて、必要な情報を見ることができ、書籍のタイトル通りレシピ本のような使い方ができる。特に5章で(GA4の切り替えタイミングで注目されている)ユーザー単位の分析、6章でWebサイト分析が扱われており、参考になる。

<Amazonリンク>
ビッグデータ分析・活用のためのSQLレシピ

4冊目は「データサイエンス100本ノック構造化データ」である。こちらはデータの加工練習をしたいというユーザー向けの本である。練習問題自体は公式で配布されているので、回答例が手元に欲しいユーザーのためのものである。

<Amazonリンク>
データサイエンス100本ノック構造化データ

GA4特有のSQLのはなし

GA4データには少し特徴があり、SQLの入門書ではカバーできていないことが多い。具体的には「ネストされたデータ」「(日付)シャーディングの扱い方」「intradayテーブルへの対応」である。入門書と実際のGA4データの集計の橋渡しとなる情報を提供していく。

ネストされたデータ

SQLの入門書で扱われるテーブルの構造は基本的に1つのフィールドに1つの値が入っていることを前提としている。その一方でGA4のパラメータ関連の情報は1つのフィールドに複数の項目が入っている。イメージとしては下記のようになる。

詳細は後ほど紹介するブログが参考になるが、ポイントとしてはデータをまとめられ方はStruct型とArray型の２パターンがあり、イメージとして、Struct型ではテーブルが横に広がり、Array型ではテーブルが縦に広がる。

ここからは参考文献を紹介していく。
ネストされたデータの処理については下記が参考になる。

BigQueryの更新のドキュメントは下記である。←初見だと難しい印象
<参考ページ>
配列の操作
https://cloud.google.com/bigquery/docs/arrays?hl=ja

(日付)シャーディングの扱い方

GA4のデータは日付ごとに別テーブルに分けられている。集計期間を指定する際にこの機能が有効である。具体的には下記のような記述で集計期間を絞ることができる。

select *
from `プロジェクト名.データセット名.events_*`
where _table_suffix between "20231201" and "20231231"

また複数回、元のテーブルを参照する場合、関数を使うことで日付の指定を1箇所で管理できる。

create temporary function from_date() as ("20231201");
create temporary function to_date()  as ("20231231");

select *
from `プロジェクト名.データセット名.events_*`
where _table_suffix between from_date() and to_date()

intradayテーブルへの対応

GA4との繋ぎこみでstreamingも選択しているプロパティの場合、intradayテーブルが生成される。intradayテーブルへの対応は下記ブログが参考になった。まずはそちらを確認いただきたい。

<参考ページ>
Firebase AnalyticsからBigQueryへの日次データ同期が突如不規則になった事象に対応した話
https://tech.connehito.com/entry/2022/07/25/170714

その上で、自分は(単日ではなく)複数日を参照する必要があったため、下記のように調整したものを使うことが多い。

where (_table_suffix between from_date() and to_date() or _table_suffix between concat('intraday_',from_date()) and concat('intraday_',to_date()))

おわりに

今回はGA4のデータをSQLで分析しようと思ったユーザー向けに最初の一歩となるような情報をまとめた。「GA4特化のクエリ」の記事では具体的にGA4データをSQLで分析する例を紹介する。特にGA4から提供されているデータはイベント単位の細かいもので種類も豊富であるため、同じセッションの定義であったとしても複数の実装方法が考えられる。そのため、巷のGA4へのSQLのクエリ例は定義がまちまちである。極力GA4のツール(UI)上で確認できるデータに近いものを目指した集計をする際のポイントを伝える。

<関連ページ>
GA4特化のクエリ https://cellardoor.hatenablog.jp/entry/day6-ga4-query

X(旧:Twitter)もよろしくお願いします。

@assnowのアカウント