前回の記事
【GA4】[セッション] スコープ 《シーケンス(ページ遷移)》by BigQuery & SQL
では、セッションスコープのページ遷移(page.A -> page.B)のSQL文を書いてみました。今回は、
page.A -> page.B -> purchase
という遷移があったセッション数を集計するSQL文を解説します。
今回はGoogleが用意してくれている、GA4のサンプルデータを使用します。
bigquery-public-data 内にある、ga4_obfuscated_sample_ecommerceです。
このデータは、2020年11月1日から2021年1月31日までの3ケ月分が公開されています。
このデータは、SQLで呼び出す際は、下記のようになります。
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
ページA:
https://shop.googlemerchandisestore.com/
▼
ページB:
https://shop.googlemerchandisestore.com/Google+Redesign/Apparel
▼
イベント:purchase(=購入完了)
と進んだ、セッション数を集計して見ましょう。
上記の▼の部分には、他のページの閲覧やイベントがあってもよいものとします。
冒頭で紹介した、前回のページ遷移があったセッション数を計測するSQLを使って、いったん、ページA → ページBの遷移セッション数を出してみます。
WITH PageAVisits AS (
SELECT DISTINCT
user_pseudo_id,
event_timestamp AS A_timestamp,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS params
WHERE
_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
AND event_name = 'page_view'
AND params.key = 'page_location'
AND params.value.string_value = 'https://shop.googlemerchandisestore.com/'
),
PageBVisits AS (
SELECT DISTINCT
user_pseudo_id,
event_timestamp AS B_timestamp,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS params
WHERE
_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
AND event_name = 'page_view'
AND params.key = 'page_location'
AND params.value.string_value = 'https://shop.googlemerchandisestore.com/Google+Redesign/Apparel'
)
SELECT
COUNT(DISTINCT CONCAT(A.user_pseudo_id, '_', A.ga_session)) AS transitioned_sessions
FROM
PageAVisits A
JOIN
PageBVisits B
ON
A.user_pseudo_id = B.user_pseudo_id
AND A.ga_session = B.ga_session
AND A.A_timestamp < B.B_timestamp;
集計結果は、
となりました。
上記で見た、ページA → ページBの遷移セッション数を出すSQLに加えて、新しくPurchaseEvents
というサブクエリを追加して、purchaseイベントを発火するセッションをフィルタリングし、その後でpage.A -> page.Bの遷移と結合して適切な順序でイベントが発火されたセッションをカウントします。
WITH
PageAVisits AS (
SELECT DISTINCT
user_pseudo_id,
event_timestamp AS A_timestamp,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS params
WHERE
_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
AND event_name = 'page_view'
AND params.key = 'page_location'
AND params.value.string_value = 'https://shop.googlemerchandisestore.com/'
),
PageBVisits AS (
SELECT DISTINCT
user_pseudo_id,
event_timestamp AS B_timestamp,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
UNNEST(event_params) AS params
WHERE
_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
AND event_name = 'page_view'
AND params.key = 'page_location'
AND params.value.string_value = 'https://shop.googlemerchandisestore.com/Google+Redesign/Apparel'
),
PurchaseEvents AS (
SELECT DISTINCT
user_pseudo_id,
event_timestamp AS P_timestamp,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
AND event_name = 'purchase'
)
SELECT
COUNT(DISTINCT CONCAT(A.user_pseudo_id, '_', A.ga_session)) AS transitioned_purchase_sessions
FROM
PageAVisits A
JOIN
PageBVisits B
ON
A.user_pseudo_id = B.user_pseudo_id
AND A.ga_session = B.ga_session
AND A.A_timestamp < B.B_timestamp
JOIN
PurchaseEvents P
ON
B.user_pseudo_id = P.user_pseudo_id
AND B.ga_session = P.ga_session
AND B.B_timestamp < P.P_timestamp;
上記のクエリは、page.Aからpage.Bへの遷移の後に、purchaseイベントが発火するセッションをカウントします。
集計結果は、
となりました。
今回の記事は、ここまでとなります。
前回の記事、
【GA4】[セッション] スコープ 《シーケンス(ページ遷移)》by BigQuery & SQL
では、SQLの中味について、より詳しく解説しておりますので、よろしかったら、前回の記事も是非ご覧ください。