BigQueryでGoogle Analyticsのデータを整理して取り出す

欲しいデータ

BigQueryにインポートされているGoogle Analyticsのデータを整理して取り出したい。
データのスキーマ：https://support.google.com/analytics/answer/3437719

サブクエリをひとつずつメモ

(1) データ取得開始日、終了日を定義

CREATE TEMPORARY FUNCTION START_DATE()  AS ('20181216');
CREATE TEMPORARY FUNCTION END_DATE()  AS ('20181223');

(2) 除外したいユーザーリストを作成
pagePathに特定の単語を含むユーザーをリストアップ

WITH 
## 除外ユーザーリストを作成
ignore_users AS(
    SELECT 
        fullVisitorId 
    FROM `<BigQueryのテーブル名>`,unnest(hits) AS hits 
        WHERE _TABLE_SUFFIX BETWEEN START_DATE() AND END_DATE()
            AND hits.page.pagePath LIKE '%XXXXXX%'  # pagePathに'XXXXX'を含む人を除外したい
            OR  hits.page.pagePath LIKE '%YYYYYY%'  # pagePathに'YYYYY'を含む人を除外したい
            OR  hits.page.pagePath LIKE '%ZZZZZZ%'  # pagePathに'ZZZZZ'を含む人を除外したい
        GROUP BY fullVisitorId
),

(2) (1)で作成したユーザーを除外、pagePathからパラメーターを削除
'?'が出現したらpagePathを切り取り、0番目の項目のみを残す
例：https://qiita.com?id=xxxxxxx → https://qiita.com

## 特定ユーザーを除外、pagePathのパラメータを削除
delete_parameter AS(
    SELECT 
        fullVisitorId,
        SPLIT(hits.page.pagePath, '?')[OFFSET(0)] AS pagePath #URLのパラメーターを削除
    FROM `<BigQueryのテーブル名>`,unnest(hits) AS hits 
      WHERE _TABLE_SUFFIX BETWEEN START_DATE() AND END_DATE() 
      AND hits.type = 'PAGE'  
      AND fullVisitorId NOT IN(SELECT fullVisitorId FROM ignore_users) #特定ユーザーを除外
    GROUP BY fullVisitorId, pagePath
),

(3) パラメーター削除後のページでPV数がTOP200のページを抽出

## PV数がTOP200のページを抽出
page_extract AS(
    SELECT 
        pagePath,
        COUNT(*) AS pagePathCount
    FROM delete_parameter
    GROUP BY pagePath
    ORDER BY pagePathCount DESC
    LIMIT 200
),

(4) 対象ユーザーと対象pagePathでtransactionデータを作成
直帰ユーザーを取り除く用にユーザーごとのページ閲覧数をcountしておく

## 対象ユーザーと対象pagePathでtransactionデータを作成
## ユーザーごとのページ閲覧数をcount
count_pv AS(
SELECT
    fullVisitorId,
    pagePath,
    COUNT(pagePath) OVER (PARTITION BY fullVisitorId) AS countPagePerUser
FROM delete_parameter 
WHERE pagePath IN(SELECT pagePath FROM page_extract)
)

(5) 直帰ユーザーを除き、データを整形

## 2ページ以上見たuserのデータのみを抽出
SELECT
    fullVisitorId,
    pagePath
FROM count_pv
WHERE countPagePerUser >= 2
ORDER BY fullVisitorId

まとめると

CREATE TEMPORARY FUNCTION START_DATE()  AS ('20181216');
CREATE TEMPORARY FUNCTION END_DATE()  AS ('20181223');

WITH 
## 除外ユーザーリストを作成
ignore_users AS(
    SELECT 
        fullVisitorId 
    FROM `<BigQueryのテーブル名>`,unnest(hits) AS hits 
        WHERE _TABLE_SUFFIX BETWEEN START_DATE() AND END_DATE()
            AND hits.page.pagePath LIKE '%XXXXXX%'  # pagePathに'XXXXX'を含む人を除外したい
            OR  hits.page.pagePath LIKE '%YYYYYY%'  # pagePathに'YYYYY'を含む人を除外したい
            OR  hits.page.pagePath LIKE '%ZZZZZZ%'  # pagePathに'ZZZZZ'を含む人を除外したい
        GROUP BY fullVisitorId
),

## 特定ユーザーを除外、pagePathのパラメータを削除
delete_parameter AS(
    SELECT 
        fullVisitorId,
        SPLIT(hits.page.pagePath, '?')[OFFSET(0)] AS pagePath #URLのパラメーターを削除
    FROM `<BigQueryのテーブル名、(1)のテーブル名と一緒>`,unnest(hits) AS hits 
      WHERE _TABLE_SUFFIX BETWEEN START_DATE() AND END_DATE() 
      AND hits.type = 'PAGE'  
      AND fullVisitorId NOT IN(SELECT fullVisitorId FROM ignore_users)
    GROUP BY fullVisitorId, pagePath
),

## PV数がTOP200のページを抽出
page_extract AS(
    SELECT 
        pagePath,
        COUNT(*) AS pagePathCount
    FROM delete_parameter
    GROUP BY pagePath
    ORDER BY pagePathCount DESC
    LIMIT 200
),

## 対象ユーザーと対象pagePathでtransactionデータを作成
## ユーザーごとのページ閲覧数をcount
count_pv AS(
SELECT
    fullVisitorId,
    pagePath,
    COUNT(pagePath) OVER (PARTITION BY fullVisitorId) AS countPagePerUser
FROM delete_parameter 
WHERE pagePath IN(SELECT pagePath FROM page_extract)
)

## 2ページ以上見たuserのデータのみを抽出
SELECT
    fullVisitorId,
    pagePath
FROM count_pv
WHERE countPagePerUser >= 2
ORDER BY fullVisitorId

大量のデータをぱぱっと整理してくれてBigQueryにはいつも感謝です！！ありがとう！！