目次 › 第 10 部 BigQuery と Looker Studio

10.5 SQL で GA4 データを分析する

よく使う集計を SQL で書けるようになります。
応用書籍換算 約 14 ページ

この章で学ぶこと

• SQL の基本の書き方
• UNNEST でイベントのパラメータを取り出す方法
• ユーザー数とセッション数を集計する方法
• ページ別の数値を集計する方法
• BigQuery の集計と GA4 の画面の数値が合わない理由

この章の SQL では、テーブル名を project.analytics_123456789.events_* と省略して書きます。実際には、自分のプロジェクト名とデータセット名に置き換えてください。

SQL の基本

SQL は、データベースからデータを取り出したり集計したりするための言語です。BigQuery では、標準 SQL（GoogleSQL）を使います。

基本の形

SELECT 列, 集計関数(列)           -- 何を取り出すか
FROM テーブル                    -- どこから
WHERE 条件                       -- どの行を対象にするか（集計の前）
GROUP BY 列                      -- 何ごとに集計するか
HAVING 条件                      -- 集計した結果の絞り込み
ORDER BY 列 DESC                 -- 並び順
LIMIT 件数                       -- 表示する件数

よく使う集計関数

例：イベント名ごとのイベント数

SELECT
    event_name,
    COUNT(*) AS event_count
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261027'
GROUP BY event_name
ORDER BY event_count DESC

ポイント BigQuery では、クエリを実行する前に、エディタの右上に 読み取るデータの量 が表示されます。慣れないうちは、_TABLE_SUFFIX で 1 日分だけに絞って試し、結果を確認してから期間を広げましょう。

UNNEST でパラメータを取り出す

前の章で学んだように、イベントのパラメータは event_params という 配列 に入っています。配列の中から特定のパラメータの値を取り出すには、UNNEST を使います。

サブクエリで 1 つの値を取り出す

最もよく使う書き方は、SELECT の中に、パラメータを取り出す サブクエリ を書く方法です。

SELECT
    event_date,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX = '20261027'
    AND event_name = 'page_view'
LIMIT 100

型がわからない値をまとめて取り出す

(SELECT COALESCE(
    value.string_value,
    CAST(value.int_value AS STRING),
    CAST(value.double_value AS STRING)
) FROM UNNEST(event_params) WHERE key = 'session_engaged') AS session_engaged

COALESCE は、引数の中で最初に NULL でない値を返す関数です。

items を展開する

商品の情報（items）を商品ごとに集計するときは、FROM の中で UNNEST して、行を展開します。

SELECT
    item.item_name,
    SUM(item.quantity) AS quantity,
    SUM(item.item_revenue) AS item_revenue
FROM `project.analytics_123456789.events_*`,
    UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261027'
    AND event_name = 'purchase'
GROUP BY item.item_name
ORDER BY item_revenue DESC

注意 FROM テーブル, UNNEST(items) のように展開すると、1 つのイベントが商品の数だけの行に増えます。この状態でイベントの数や、ecommerce.purchase_revenue（注文全体の収益）を合計すると、二重に数えてしまいます。注文の単位で集計するときは、展開しないで集計しましょう。

ユーザー数とセッション数の集計

ユーザー数

BigQuery のデータでは、ユーザーは user_pseudo_id で識別します。

SELECT
    COUNT(DISTINCT user_pseudo_id) AS users
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261027'

セッション数

GA4 には、セッションの ID の列はありません。セッションは、user_pseudo_id と ga_session_id の組み合わせ で識別します（ga_session_id は、ユーザーの中でだけ一意な値のため）。

SELECT
    COUNT(DISTINCT CONCAT(
        user_pseudo_id,
        CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)
    )) AS sessions
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261027'

日別のユーザー数とセッション数

WITH events AS (
    SELECT
        PARSE_DATE('%Y%m%d', event_date) AS date,
        user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id
    FROM `project.analytics_123456789.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261027'
)
SELECT
    date,
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNT(DISTINCT CONCAT(user_pseudo_id, CAST(ga_session_id AS STRING))) AS sessions
FROM events
GROUP BY date
ORDER BY date

WITH は、クエリの中で一時的な表（共通テーブル式、CTE）を作る書き方です。長いクエリを段階に分けて書くと、読みやすくなります。

ページ別の集計

ページ別の表示回数とユーザー数

WITH page_views AS (
    SELECT
        user_pseudo_id,
        REGEXP_EXTRACT(
            (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),
            r'^https?://[^/]+([^?#]*)'
        ) AS page_path
    FROM `project.analytics_123456789.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261027'
        AND event_name = 'page_view'
)
SELECT
    page_path,
    COUNT(*) AS views,
    COUNT(DISTINCT user_pseudo_id) AS users
FROM page_views
GROUP BY page_path
ORDER BY views DESC
LIMIT 50

ランディング ページ別のセッション数

ランディング ページは、セッションで最初に表示されたページ です。session_start のイベントの page_location を使う方法や、セッションの中で最も早い page_view を探す方法があります。

SELECT
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page,
    COUNT(*) AS sessions
FROM `project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261027'
    AND event_name = 'session_start'
GROUP BY landing_page
ORDER BY sessions DESC
LIMIT 50

ポイント よく使う集計（日別・ページ別・チャネル別など）は、毎日 1 回実行して、結果を 集計テーブル に保存する（BigQuery の「スケジュールされたクエリ」を使う）と便利です。Looker Studio からは、生データではなく集計テーブルを読むようにすると、表示が速くなり、費用も抑えられます。

画面の数値と合わない理由

BigQuery で集計した数値と、GA4 の画面の数値は、多くの場合完全には一致しません。

注意 数％程度の差は、上のような理由による 正常な差 であることがほとんどです。完全に一致させることを目指すのではなく、「BigQuery で集計する指標は、BigQuery の数値で比べる」など、比べるときの基準をそろえる ことが大切です。社内のレポートでは、どちらの数値を使っているかを明記しましょう。

まとめ

• SQL は、SELECT・FROM・WHERE・GROUP BY・ORDER BY を組み合わせて集計する
• パラメータは、(SELECT value.xxx_value FROM UNNEST(event_params) WHERE key = '...') で取り出す
• 商品は UNNEST(items) で展開するが、行が増えるので注文単位の集計に注意する
• ユーザーは user_pseudo_id、セッションは user_pseudo_id と ga_session_id の組み合わせで数える
• BigQuery と画面の数値は、推定のアルゴリズムやユーザーの識別、モデリングなどの違いで一致しない

次の章では、GA4 のデータを CRM などの社内のデータと組み合わせたり、レポートを自動化したりする方法を紹介します。