UNNESTせずに特定のページビューやイベントが発生したセッションのみ抽出する
概要
特定の要素を持つ配列全体を、EXISTS句を使って抽出します。
BigQuery Exportのデータなど、構造化されたBigQueryのデータを処理する際、元の構造を保ったまま特定の条件にあった行だけを抽出したいことがあるかと思います。
例
- 特定のページビューが発生したセッションの直帰率を調べたい
- 特定のイベントが発生したセッションのコンバージョン率を調べたい
今回はGoogleAnalyticsのサンプルデータを使用して書いていきます。
support.google.com
/google redesign/apparel/mens/mens t shirtsを閲覧したセッションのみ抽出する
#standardSQL
WITH base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "PAGE"
AND h.page.pagePath = "/google redesign/apparel/mens/mens t shirts" ) )
SELECT
*
FROM
base確認のため、サブクエリbaseに対して、fullvisitorId,visitId,hitNumber順に、hits.page.pagePath を見てみましょう。
#StandardSQL
WITH base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "PAGE"
AND h.page.pagePath = "/google redesign/apparel/mens/mens t shirts" ) )
SELECT
fullVisitorId,
visitId,
h.hitNumber,
h.page.pagePath
FROM
base AS ga,
ga.hits AS h
WHERE
h.type = "PAGE"
ORDER BY
1,
2,
3 ASC確かに、今回絞り込みたかったpagePathを含むセッションがそのまま残っているようです。
これをもとに、 `/google redesign/apparel/mens/mens t shirts`を閲覧したセッションの日毎の直帰率を調べるには、以下のように書けばよいです。
WITH
base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "PAGE"
AND h.page.pagePath = "/google redesign/apparel/mens/mens t shirts" ) )
SELECT
date,
COUNT(*) AS session,
SUM(totals.bounces) AS bounce_cnt,
SAFE_DIVIDE(SUM(totals.bounces),COUNT(*)) AS bounce_rate,
FORMAT("%3.2f %%",SAFE_DIVIDE(SUM(totals.bounces),COUNT(*) ) * 100 ) AS bounce_rate_formatted
FROM
base
GROUP BY
1
ORDER BY
1 ASC同様に、eventAction Add to Cartイベントが発生したセッションを抽出してみましょう。
以下のように書けます。
#standardSQL
WITH base AS (
SELECT
*
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*` AS ga
WHERE
_TABLE_SUFFIX >= "20170701"
#以下が絞り込みを行っている部分です。
AND EXISTS (
SELECT
h.page.pagePath
FROM
UNNEST(ga.hits) AS h
WHERE
h.type = "EVENT"
AND h.eventInfo.eventAction = "Add to Cart" ))
SELECT
*
FROM
base以上です。
