Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@herbz(蓮 安藤)

【BigQuery_2】GA4データ詳細分析のためのクエリ

Last updated at Posted at 2022-09-02

テストパターンとページを指定したセッション数、CV件数、CVRのクエリ

このクエリは、特定のパターン振り分けられ、特定のページへの訪問を行ったセッション数、CV件数、CVを抽出するクエリ。
次項から各サブクエリに対して詳細な説明を行う。

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)

, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
FROM base
ORDER BY session_cv DESC
)

SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master

base サブクエリについて

baseサブクエリは、ネストされたイベントパラメータなどの値や、セッション数の算出に必要なフィールドを抽出し、並べるためのクエリ。
このサブクエリをメインクエリとして実行した場合は、以下のようなテーブル形式となる。

pagesフィールドに値が入力されている場合、testidは「NULL」になる。
また、testidフィールドに値が入力されている場合、pagesフィールドは「NULL」になる。
これは、GA4上で従属するイベントが異なるパラメータをディメンションに加えて「(not set)」が発生することと同じ現象。

この現象を回避するために、このクエリをサブクエリとして上位のクエリでパラメータ同士が紐づくよう設定している。

sessions user_pseudo_id pages testid session_cv
111-999 111 https://~.com NULL 1
222-888 222 NULL XXXX.0 1
333-777 333 https://~.com NULL 1
333-777 333 NULL XXXX.0 1

各カラムは上から、セッションID、ユーザーID、page_location、variant_id、CV件数を抽出する。
セッションIDやCV件数を抽出する構文については、下記を参照。
【BigQuery_1】GA4データ分析のための基本理解

WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)

master サブクエリについて

このクエリは、従属するイベントが異なるために紐づかないパラメータを紐づかせるためのクエリ。
baseサブクエリをすべて抽出し、2つのCOUNT関数を新たに追加している。次はCOUNT関数の細かい説明。

, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
FROM base
ORDER BY session_cv DESC
)

COUNT関数について

  • 特定のページURLに合致する「sessions」を「sessions」ごとにカウント
    「PARTITION BY」を「sessions」だとセッション数、「user_pseudo_id」だとユーザー数ごとにCOUNTする。
    ユーザー数とセッション数どちらを利用するかで選択が変わる。

また、IF文にある「sessions」の部分は、baseサブクエリのカラムであれば特に指定はない。
とにかく「特定のページURL」という条件を満たした行をカウントしたいため。
ただ、DISTINCTで値が変わる可能性があるため、セッションベースならセッションIDの利用が無難。

COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,

従属するイベントが異なるパラメータごとにCOUNTさせる理由

pagesとtestidは従属するイベントが異なるので、どちらかの値が入力されていれば、片方は「NULL」となる。
なので、下記テーブル両方を満たしているsessions「333-777」のように、別行ごとにpagesとtestidが入る。

しかし、baseで指定したpages（とtestid）の値に、特定のページとパターンを与えて行ごとにCOUNTすれば、同じ行として抽出される。
この方法で紐づけられないパラメータ同士を紐づけている。
下記テーブルであれば、page_countが1以上、test_countが1以上のセッションID、つまり「333-777」が条件を満たすことになる。

sessions user_pseudo_id pages testid session_cv page_count test_count
111-999 111 https://~.com NULL 1 1 0
222-888 222 NULL XXXX.0 1 0 1
333-777 333 https://~.com NULL 1 1 1
333-777 333 NULL XXXX.0 1 1 1

メインクエリについて

このメインクエリは、masterサブクエリで条件を満たすセッションIDをカウントするクエリ。
page_countが1以上、test_countga1以上が今回の対象であるため、それをIF文で指定。
また、CV件数は「session_cvが1以上」とうい条件が必要なので追加。

SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master

さらに条件を増やして詳細分析する方法

イメージとしてセグメントに条件を足すことに近い。
パターンとページの指定から、さらに特定の導線の利用の条件を追加する場合など。

追加条件
・導線のクリック

手順
1. baseサブクエリへ対象パラメータを追加
2. masterサブクエリで紐づかせるためのcount関数を追加
3. メインクエリで条件を追加

1. baseサブクエリへ対象パラメータを追加

page_location、variant_idの抽出方法と同様にネストされているので、同じ構文で対応。

before
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)
after
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
-- 導線の利用 条件ここから
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_label' AND event_name = 'GTM_クリック') AS event,
-- 導線の利用 条件ここまで
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)

masterサブクエリで紐づかせるためのcount関数を追加

page_location、variant_idの利用と同様の方法。
page_location、variant_id、click_labelがともに1以上のものが条件を満たす。

before
, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
FROM base
ORDER BY session_cv DESC
)
after
, master AS (
SELECT *,
  COUNT(DISTINCT IF(pages = 'https://XXXX.com/', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXXXXX.〇', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count,
-- 導線の利用 条件ここから
  COUNT(DISTINCT IF(event LIKE '導線Bのラベル名', sessions, NULL)) OVER(PARTITION BY sessions) AS event_count
-- 導線の利用 条件ここまで
FROM base
ORDER BY session_cv DESC
)

3. メインクエリで条件を追加

説明不要。
このメインクエリを実行すると「導線Bをクリックし」特定のページへ訪問した、特定のパターンに振り分けられたセッション数、CV件数、CVRとなる。

before
SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master
after
SELECT
-- すべてのCOUNT関数に「AND event_count>=1」がある
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND event_count>=1, user_pseudo_id, NULL))
FROM master

ディメンションを追加し網羅的に確認する方法

イメージとして、GA4へディメンションを追加することに近い。
クリックラベル別に各パターンを確認したい場合など。

追加条件
・クリックラベルのディメンション

手順
1. baseサブクエリへ対象パラメータを追加
2. メインクエリで条件を追加しGROUP BY

1. baseサブクエリへ対象パラメータを追加

page_location、variant_idの抽出方法と同様にネストされているので、同じ構文で対応。

before
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)
after
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS string)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
-- 導線の利用 条件ここから
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_label' AND event_name = 'GTM_クリック') AS event,
-- 導線の利用 条件ここまで
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
)

2. メインクエリで条件を追加しGROUP BY

before
SELECT
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)),
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL))
FROM master
after
SELECT
-- クリックラベルをディメンションとする ここから
　label,
-- クリックラベルをディメンションとする ここまで
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, sessions, NULL)) AS sessions,
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) AS cv,
  COUNT(DISTINCT IF(page_count>=1 AND test_count>=1 AND session_cv>=1, sessions, NULL)) / COUNT(DISTINCT IF(page_count>=1 AND test_count>=1, user_pseudo_id, NULL)) AS cvr
FROM master
GROUP BY label
ORDER BY 2 DESC

上記の描画イメージ

label sessions cv cvr
導線A 1000 20 0.02
導線B 800 10 0.0125
導線C 500 50 0.1

テストの効果検証で実際に利用したクエリ

テストパターンごと、ラベルごとで抽出。上記すべてのクエリの応用編的な。

描画イメージ

lael pattern0_session pattern0_cv pattern1_session pattern1_cv pattern2_session pattern2_cv
導線A 1000 50 1000 35 1000 70
導線B 800 10 800 2 800 20
導線C 500 50 500 10 500 70 
WITH base AS (
  SELECT
    CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS sessions,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_path' AND event_name = 'page_view') AS pages,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'variant_id' AND event_name = 'experiment_impression') AS testid,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_label' AND event_name = 'GTM_クリック') AS label,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'click_category' AND event_name = 'GTM_クリック') AS category,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) ELSE NULL END) OVER(PARTITION BY CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS session_cv
  FROM `PROJECT.analytics_XXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20220819'AND '20220829'
)

, master AS(
SELECT *,
  COUNT(DISTINCT IF(pages LIKE '/shop/products/alo_%', sessions, NULL)) OVER(PARTITION BY sessions) AS page_count,
  COUNT(DISTINCT IF(testid = 'XXXX.2', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count_2,
  COUNT(DISTINCT IF(testid = 'XXXX.1', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count_1,
  COUNT(DISTINCT IF(testid = 'XXXX.0', sessions, NULL)) OVER(PARTITION BY sessions) AS test_count_0
FROM base
ORDER BY session_cv DESC
)

SELECT
  label,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_0>=1, sessions, NULL)) AS pattern0_session,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_0>=1 AND session_cv>=1, sessions, NULL)) AS pattern0_cv,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_1>=1, sessions, NULL)) AS pattern1_session,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_1>=1 AND session_cv>=1, sessions, NULL)) AS pattern1_cv,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_2>=1, sessions, NULL)) AS pattern2_session,
  COUNT(DISTINCT IF(page_count>=1 AND test_count_2>=1 AND session_cv>=1, sessions, NULL)) AS pattern2_cv
FROM master
GROUP BY label
ORDER BY 2 DESC
0
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?