テストパターンとページを指定したセッション数、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