BigQueryの実践小技集

最終更新日：2018.3.9

StandardSQL（標準SQL）とLegacySQL（レガシーSQL）
BigQueryの思想
必要な列だけをピックアップする
特定の日付を指定して抽出
特定の条件にマッチするレコードを抽出
条件抽出したテーブルについて、基礎集計する
0-1のフラグ立て
ユーザー単位集計：PV、SC 、DC
ページ単位集計：PV、UU
ページのABC分析
ユーザーの来訪日数間隔
カスタムディメンション（customDimentions）の抜き方
URL文字列の分割と結合

【注意】　BigQueryはスキャンしたデータの容量に応じて課金される「従量課金制」のサービスです。つまり使った分だけ費用がかかるということです。本webサイトに掲載されている例をそのまま実行した場合、大量のデータをスキャンすることになる可能性があります。練習する際には、事前に練習用の小規模データを用意するなどした方がよいかもしれません。本webサイトは操作例を示すことを目的に情報公開しており、実際に利用するかどうかはご自身の責任範囲で判断をお願いします。

また本ページはBigQueryを練習しつつ、覚え書き程度で書いている部分もあります。そのため、細かな実行結果の確認を行えていないところがあります。あらかじめ承知のうえ、ご参照ください。

BigQueryはGoogle Analytics（GA）と連携可能で、GAで閲覧しているビューの「元データ」をBigQuery側で保管しておくことができる（GAのある特定のビューをBigQuery側へ転送しておかなければならない）。したがって、（通常そのようなことをする意味はあまりないが）GAの画面で見ている集計結果をBigQuery上でも再現することができる。BigQueryを操作するメリットはGA側では見切れない細かな情報を確認できることである。少し専門的にいえばGAの元データを利用して、閲覧ユーザーのスコアリング（ロジスティック回帰分析）やクラスタリング（クラスター分析）ができるということである。ただし、こうした多変量解析手法を適用するためには、大きくいって（１）元データを何かしらの条件で行もしくは列を抽出し、（２）分析単位（後述するが多くはfullVisitorIdかVisitId）ごとにグループ集計する必要がある。GigQuery上ではかなり複雑なデータ処理ができるが、もし既存のツール（RやPythonなど）操作に慣れているのであればデータの条件抽出など一次加工だけBigQueryで済まし、得意なツールで細かいデータ加工をするのもよいだろう。ここでは特に3つの観点に着目した実践的な小技を紹介していく。

多変量解析を行うことを目的として、大規模なアクセスログデータを分析可能な小規模データへと条件抽出する
BigQuery上で素早く基礎的な要約統計量を確認する
BigQueryならではの留意点を理解する

１．StandardSQL（標準SQL）とLegacySQL（レガシーSQL）

専門的な知識を無視して簡単にいってしまうと、まず大前提としてBigQueryとはMySQLのようなものである。というよりも、MySQLとほとんど同じといってもよいかもしれない。したがって、既にMySQLの操作を経験したことがある人にとっては、さほどBigQueryの操作に悩まされることは少ないだろう。しかし、BigQueryにはStandardSQLとLegacySQLというものが存在しており、構文が少々違うのが難点である。本記事を執筆している時点（2017.10.7）では、web上で収集できる多くの情報はLegacySQLに関するものである。また日本語で執筆されたBigQueryの書籍も少なく、唯一、実践的に利用できそうな本は一冊のみである（これは私の主観的意見に過ぎないかもしれない）。

加嵜長門、田宮直人「ビッグデータ分析・活用のためのSQLレシピ」マイナビ出版（2017）・・・【参考書籍1】

本webサイト内で記載している内容は、この書籍をかなり参考にしているものもあり、現時点では（BigQueryを利用するユーザーにとっては）欠かせない一冊であると断言できる。この本は「インプット→操作（構文例）→アウトプット」という3点が明確に記されているため、意味が分からなくても書いてあるとおりに練習していけば自然と身につくものも多いと思われる。一方で、少々システマティックな観点でBigQueryの仕様が詳細に書かれたものとしては（英語の書籍になってしまうが）以下のものがおすすめできる。

Jordan Tigani, Siddartha Naidu「Google BigQuery Analytics」WILEY（2014）・・・【参考書籍2】

さて少々話が本筋から脱線してしまったが、【参考書籍1】で紹介されている構文はStandardSQLである。おそらくMySQLの経験者が直感的に受け入れやすい構文はLegacySQLである。LegacySQLは、誤解を恐れずにいうとMySQLとほとんど何も変わらない。ところがStandardSQLは180度まったく異なる書き方の特徴があるといっても過言ではなく、かえってMySQLなどの経験者にとっては、とっつきにくい部分があるかもしれない。あくまでも主観的な意見ではあるが、現時点（2017.10.7）で実務的に利用できそうな書籍が【参考図書1】のみであることを考えると、StandardSQLに慣れていった方が得策であると思われる。本webサイトではStandardSQLの構文を載せるように試みている。その意図としては、本質的に同じものを構文が違うという理由だけで（単に2つとも知っておきたいという理由だけで）両方とも身につけようとするのは非効率であろう、というものである。あくまでも目的は冒頭部分で挙げた3つの観点を実現することであり、これが重要であることを断っておく。

<<　ページトップへ戻る

２．BigQueryの思想

具体的な話をする前にもう一点だけ述べておくべきことがある。それはBigQueryは分散処理をすることを前提に設計（開発）されているということである。GAのデータ（正確にはGAのビューのデータ）をBigQueryに転送すると、そのテーブルは日別（一日単位に区別されたアクセスログ）で保持される。例えば何かしらの集計をしようとしたとき、1ヶ月分のアクセスログデータをまとめて集計するという発想はなく、一日ごとに集計したものを積み重ねるという発想になることを気にとめておく方がよい。そのため、ある条件にマッチするレコードを1ヶ月分全て取り出そうとすると、データの容量が大きすぎて抽出することができないという事態が発生してしまう。私の経験的に、分析に必要な元データを抽出使用とする場合に、せいぜい5日分のデータを取り出すのが限度である（もちろん抽出する条件によって異なることは言うまでもない）。換言すれば、あまり大きなデータを抽出して既存のツール（RやPythonなど）でデータ加工をすることは、場合によっては難しいということである。その場合、できるだけ細小な情報に絞ってデータ抽出する工夫が必要である。

<<　ページトップへ戻る

３．必要な列だけをピックアップする

最初だけStandardSQLを理解するためにLegacySQLとの比較をしておこう。通常、何もしなければBigQueryはLegacySQLでの利用になっている。StandardSQLを実行するためには、BigQueryのQuery Table画面のオプションから「Use Legacy SQL」のチェックを外さなければならない。BigQueryのQuery Tableの画面は初期の状態だと以下のように見えているだろう（画面一部のスクリーンショット）。ここに「Show Options」というボタンがあるので、ここをクリックする。

すると折りたたまれていた画面が開くので、オプション項目の一番下にある「Use Legacy SQL」についているチェックを外す。これを外すことでStandardSQLでの実行が可能になる。なお「Hide Options」のボタンをクリックすれば、オプション画面が折りたたまれる。

まずは非常にシンプルな操作として1日分のログからfullVisitorId、visitId、hits.page.pagePathの3列を抽出してみる。LegacySQLでは以下のように書けばよい。

# 3つの列を抽出(LegacySQL)

SELECT fullVisitorId, visitId, hits.page.pagePath FROM [プロジェクト名:データセット名.ga_sessions_20171006]

一方でStandardSQLでは以下のように書くが・・・

# 3つの列を抽出(StandardSQL)　これはエラーになってしまう

SELECT fullVisitorId, visitId, hits.page.pagePath FROM `プロジェクト名.データセット名.ga_sessions_20171006`

残念ながらこれを実行するとエラーが出てしまう（実際に実行してみてほしい）。理屈抜きにして、まずどうすればよいかだけ述べてしまうならば以下のように書けばよい。

# 3つの列を抽出(StandardSQL)　これは成功する！

SELECT fullVisitorId, visitId, hits.page.pagePath FROM `プロジェクト名.データセット名.ga_sessions_20171006` , UNNEST(hits) AS hits

実はBigQueryでは「ネストされた（入れ子になった）変数」というものがある。これはBigQueryのデータの持ち方の話なのだが、簡単に言うと「hits」という1つの箱の中に、「hits.hitNumber」や「hits.time」という変数が区分けして格納されているイメージである（このような説明はかえって分かりにくかったかもしれない）。つまり「hits」というのは単なる箱のラベルに過ぎず、実際に変数として操作するのは「hits」という箱の中にある「hits.hitNumber」である。そしてこのような構造で保存されている変数は、UNNEST()という関数を用いて「このhitsという箱の中に存在する変数hits.hitNumberはネストを解除してください」という命令をするのである。ネストされた変数かどうかはBigQueryの画面上で確認できる（下のスクリーンショット）。この「RECORD-REPEATED」となっているのが、ネストされたものであることを示す（hitsは一つの箱）。その中に「hits.***」が複数あるので、これらは全てネストされた変数であると受け止めればよい。これらの変数を使用する際にはUNNEST()で、箱の名前：hitsを指定すればよい。

<<　ページトップへ戻る

４．特定の日付を指定して抽出

GAのデータを取り扱うときに特徴的なのが「特定の日付を指定する」という操作である。日付を指定する方法はいくつかあるが、基本的には日数を指定して抽出する方法か期間を指定して抽出する方法の2つを覚えておけば問題ないであろう。

# 直近7日間のデータから、特定4列を抽出する

SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath 
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))



# 日付の開始と終了を指定する場合
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath 
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170707'

<<　ページトップへ戻る

５．特定の条件にマッチするレコードを抽出

これもGAデータの特徴であるが、計測されるレコードには「PAGE」と「EVENT」というものがある。いわゆる「ページを閲覧した」という行動は「PAGE」として計測される。一方で、例えば「何かのボタンをクリックした」という操作は「EVENT」として計測される。経験的に多くの場合において分析に利用するのは「PAGE」のレコードだけで十分である。ただし、場合によっては「予約フォーム完了」などのアクションは「EVENT」として計測されているケースもあるため、必ずしも「PAGE」だけで十分というわけではない。

# typeが「PAGE」のレコードだけを抽出
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath, type 
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170707'
        AND type='PAGE'

この程度の条件抽出ならば比較的わかりやすい方であるが、もう少し難しい条件抽出にも挑戦してみよう。例えば、よくある処理は「社内ユーザーは除く」というようなものであろう。社内ユーザーかどうか判断する一つの方法として「社内イントラネットを閲覧している」というものがあるかもしれない。細かいことをいえば社内ユーザーでもイントラを閲覧していないなど考えられるが、ここはあくまでも操作の例示のため、そういう意地悪なことは考えないこととする。

ここで注意してほしいのが「イントラ閲覧ユーザーを除外」するのであって、「イントラ閲覧のレコードを除外」するのではないことである。つまり一度、イントラ閲覧ユーザーのIDリストを作成した上で、そのIDリストにマッチするレコードは除外するという手順をふまなければならないのである。以下にはよく使う応用例を掲載している（急に難しく感じると思うが、慌てずに見てほしい）。

# 冒頭の WITH はおまじないと思っておけばよい。
WITH

# 【１】除外したいユーザーのIDリストを作成する。このIDリストの名前を id_list とする。
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

# 【２】分析に使用するデータを用意(ここから分析対象となるユーザーを抽出する)
# 　この分析データの名前を fill_dat とする。
fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
)

# 【３】最後にfill_datとid_listを照合して、両テーブルに存在「しない」レコードだけを抽出する。
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE fill_dat.fullVisitorId = id_list.fullVisitorId
    )
;

ちなみにSQLではEXISTSとINは似たような文脈で使われることがあるが、この2つの違いをシンプルに説明している人がいる（https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1439604954）。

さて、すこし上記のスクリプトについて補足説明しておこう。まず【１】の部分であるが、ここでは分析対象としたユーザー（fullVisitorId）のリストを作成している。日本語で表現すると少し冗長だが「20170701から20170707の期間において、intraという文字列を一部含むページURLを閲覧しておらず、かつwww.katasho.co.jp/tsuyoshi/という文字列を一部含むページURLを閲覧しているユーザーIDリスト」を作っているのである。ここの条件を増やしたければANDやORで条件を書き足していけばよい。

つぎに【２】の部分であるが、ここは特に難しいことはないだろう。単に特定の列と行を条件抽出しているだけである。気づいたかもしれないが、全体としては長いスクリプトになっているように見えるが、1つ1つの作業を分割して書いているだけである。実はこういう書き方ができるのでStandardSQLは便利である（冒頭のおもじないであったWITHはこういう書き方をしても大丈夫なようにするものである）。【３】はfill_datとid_listのfullVisitorIdを照合して、一致しないレコードだけを「fill_dat」から抽出するというものである。EHERE EXISTS( )の中に書かれているSELECT 1 FROMの「1」は、これもまたおまじないだと受け取ってもらえばよい。これを「*」にしても同じ結果が得られるが、「1」にしておいた方が若干、処理が速くなる（BigQueryは高速なのでこの程度の処理ならば1秒も変わらない）。

<<　ページトップへ戻る

６．条件抽出したテーブルについて、基礎集計する

多くの場合、先ほど紹介した程度の処理をBigQuery上でやっておき、そのテーブルをエクスポートしてしまえば、R等で比較的容易にデータ処理することができるだろう。ただし、もう少し対象を絞っておきたいこともあるだろう。例えば、PVが1以下のユーザー（つまり直帰ユーザー）は除外しておくといった処理である。この場合、ユーザーごとのPV数を集計しておき、PVが1以上であるユーザーのIDリストを作成しておく必要がある。IDリストさえ作成できれば、先ほど（上記５）と同じ理屈でデータ抽出ができよう。

なお念のために言及しておくと、正確に直帰ユーザーを除外しようとすると単にPVを集計しただけでは不十分である。今回の例では期間を指定して複数日にわたってのログを対象としている。そのため、1日あたりのPVを算出したうえで、もっと正確にいえば1セッションあたりのPVを算出したうえで判断しなければならない。またさらに正確なことをいえば、複数日もしくは複数セッションにわたって、そのいずれもがPV1以下のユーザーというのが純粋な直帰ユーザーである。ここではそこまで厳密な抽出はしない。

# 冒頭の WITH はおまじないと思っておけばよい。
WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE fill_dat.fullVisitorId = id_list.fullVisitorId
    )
), 
#### ----- 分析用データの一次加工　ここまで



# ユーザーごとのPV数を集計(要はレコード数のカウント)。この結果を pv_syuke として保持
pv_syukei AS(
SELECT fullVisitorId, COUNT(fullVisitorId) AS CNT FROM ana_dat
    GROUP BY fullVisitorId
),

# さらにPVが2以上であるユーザーのIDリストを pv_2ijyou で保持
pv_2ijyou AS(
SELECT fullVisitorId FROM pv_syukei WHERE CNT >= 2
)

# 先ほどと同じくEXISTSを使ってデータ抽出する
SELECT * FROM ana_dat 
    WHERE EXISTS(
        SELECT 1 FROM pv_2ijyou WHERE pv_2ijyou.fullVisitorId=ana_dat.fullVisitorId
    )

;

人によっては「なぜこんな冗長な書き方をするのか？」と思うかもしれない。たしかに、これほど長い行数のスクリプトを書かなくても同じ結果を得ることは可能である。ただし、このような書き方をすることで作業ステップを細かく区切って、上から順番に何をしているのかを読み取りやすくすることができる。また、間違いの発見をしやすかったり、部分的に修正を加えることで似たようなことがすぐできるのも利点である。当然、他の第3者へスクリプトを渡しても、その人が使いやすいかどうかも重要な観点として気にとめておく必要があるだろう。最近はコンピュータの性能があがっていることもあり、あまりハイパフォーマンスな（処理が高速であるような）スクリプトを無理して書く必要はない。むしろ可読性の方を優先すべきである（両立した書き方が一番いいのはいうまでない）。

<<　ページトップへ戻る

７．0-1のフラグ立て

データ加工の王道でもある0-1フラグの立て方を紹介する。方法はいくつかあるが、ここでは最もシンプルで分かりやすい方法と思われるものを紹介する。また、レコード単位で0-1フラグを立てておき、その後にユーザー単位に集計する方法も併せて紹介する。

WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE fill_dat.fullVisitorId = id_list.fullVisitorId
    )
)
#### ----- 分析用データの一次加工　ここまで



# あるレコードについて、特定の文字列を含む場合は「1」、そうでない場合は「0」を返す
# hits.page.pagePathではなく、pagePathであることに注意
SELECT 
    fullVisitorId,
    pagePath, 
    CASE WHEN pagePath LIKE '%katasho.co.jp/tsuyoshi/%' THEN 1 ELSE 0 END AS RF_flag
FROM ana_dat
ORDER BY fullVisitorId
;

WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE fill_dat.fullVisitorId = id_list.fullVisitorId
    )
),
#### ----- 分析用データの一次加工　ここまで



# 0-1フラグ立ての結果テーブル
RF_flag_tbl AS(
SELECT 
    fullVisitorId,
    pagePath, 
    CASE WHEN pagePath LIKE '%katasho.co.jp/tsuyoshi/%' THEN 1 ELSE 0 END AS RF_flag
FROM ana_dat
ORDER BY fullVisitorId
)

# ユーザーごとに集計（合計）
SELECT 
    fullVisitorId, SUM(RF_flag) AS RF 
FROM RF_flag_tbl 
    GROUP BY fullVisitorId
    ORDER BY RF DESC
;

WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE fill_dat.fullVisitorId = id_list.fullVisitorId
    )
),
#### ----- 分析用データの一次加工　ここまで



# 0-1フラグ立ての結果テーブル
RF_flag_tbl AS(
SELECT 
    fullVisitorId,
    pagePath, 
    CASE WHEN pagePath LIKE '%katasho.co.jp/tsuyoshi/%' THEN 1 ELSE 0 END AS RF_flag
FROM ana_dat
ORDER BY fullVisitorId
)

# ユーザーごとに集計（合計ではなく0-1フラグとして）
SELECT 
    fullVisitorId, 
    SUM(RF_flag) AS RF_sum, 
    SIGN(SUM(RF_flag)) AS RF_01 
FROM RF_flag_tbl 
GROUP BY fullVisitorId
ORDER BY RF_sum DESC
;

<<　ページトップへ戻る

８．ユーザー単位集計：PV、SC、DC

ユーザー単位の集計でよく出てくる指標3つ：PV（ページ閲覧数, Page View）、SC（セッション数, Session Count）、DC（訪問日数, Day Count）を例にとって、グループ集計する方法を例示する。

WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE fill_dat.fullVisitorId = id_list.fullVisitorId
    )
)
#### ----- 分析用データの一次加工　ここまで



# ユーザーごとのPV(ページ閲覧数)、SC(セッション数)、DC(来訪日数)の集計
SELECT 
    fullVisitorId, 
    COUNT(fullVisitorId) AS PV,
    COUNT(DISTINCT(visitId)) AS SC,
    COUNT(DISTINCT(date)) AS DC
FROM ana_dat 
    GROUP BY fullVisitorId
;

<<　ページトップへ戻る

９．ページ単位集計：PV、UU

引き続いてページ単位の集計でよく出てくる指標2つ：PV（ページ閲覧数, Page View）とUU（ユーザー数, Unique User）を例にとって、グループ集計する方法を例示する。

WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE fill_dat.fullVisitorId = id_list.fullVisitorId
    )
)
#### ----- 分析用データの一次加工　ここまで



# ページごとのPV(ページ閲覧数)とUU(ユニークユーザー数)の集計
# hits.page.pagePathではなくpagePathであることに注意
SELECT 
    pagePath, 
    COUNT(pagePath) AS page_PV,
    COUNT(DISTINCT(fullVisitorId)) AS UU
FROM ana_dat 
    GROUP BY pagePath
    ORDER BY page_PV DESC
;

<<　ページトップへ戻る

１０．ページのABC分析

ABC分析とは古典的な手法で、簡単にいえば「商品を売れている順にA-B-Cのランクをつける」というものである（http://www.itmedia.co.jp/im/articles/0503/22/news124.html）。小売の分野ではよく使われる手法であるが、アクセスログ解析でも商品をページ、売上（販売点数）をPVやUUで置き換えれば同じことができる。

WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE id_list.fullVisitorId = fill_dat.fullVisitorId
    )
),
#### ----- 分析用データの一次加工　ここまで



# 手順1:　ページURLとPVを集計。これを monthly_pv としておく。
monthly_pv AS(
    SELECT
        pagePath, COUNT(fullVisitorId) AS PV
    FROM
        ana_dat
    GROUP BY pagePath
),

# 手順2:　手順1のテーブルに基づき、各URLのPV構成比と累積和を計算
pv_composition_ratio AS(
    SELECT
        pagePath,
        PV,
        100.0 * PV / SUM(PV) OVER() AS composition_ratio,
        100.0 * SUM(PV) OVER(ORDER BY PV DESC) / SUM(PV) OVER() AS cumulative_ratio
    FROM 
        monthly_pv
)

# 手順3:　手順2のテーブルに、累積和に基づきABCランクを付与して表示させる。
SELECT * ,
    CASE WHEN cumulative_ratio BETWEEN  0 AND  70 THEN 'A'
         WHEN cumulative_ratio BETWEEN 70 AND  90 THEN 'B'
         WHEN cumulative_ratio BETWEEN 90 AND 100 THEN 'C'
    END AS abc_rank
FROM pv_composition_ratio 
ORDER BY PV DESC
;

<<　ページトップへ戻る

１１．ユーザーの来訪日数間隔

例えば1週間のなかで何日おきにwebサイトへ来訪しているかをユーザーごとに計算したいことがある。またそれをユーザーごとにグループ集計することで、ユーザーの平均来訪日数を計算することもできる。ここでは「8桁の固定桁で表現された文字列の日付データ」から計算する方法を紹介する。GAの「date」というカラムは8桁固定の文字列で表現されているからである。この場合「yyyy-mm-dd」というハイフン区切りのデータに置換した上で日付型に変換する必要がある。

WITH

#### ----- 分析用データの一次加工　こから
id_list AS(
    SELECT fullVisitorId FROM
        `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
            AND hits.page.pagePath LIKE '%intra%'
            OR hits.page.pagePath LIKE '%5sec%'
        GROUP BY fullVisitorId
),

fill_dat AS(
SELECT 
    fullVisitorId, visitId, date, hits.page.pagePath
FROM `プロジェクト名.データセット名.ga_sessions_*` , UNNEST(hits) AS hits
    WHERE _TABLE_SUFFIX BETWEEN '20171001' AND '20171007'
        AND type='PAGE'
),

ana_dat AS(
SELECT * FROM fill_dat 
    WHERE NOT EXISTS(
        SELECT 1 FROM id_list WHERE id_list.fullVisitorId = fill_dat.fullVisitorId
    )
),
#### ----- 分析用データの一次加工　ここまで



# 日付の差分を計算するために、一旦、8桁固定の文字列をハイフンつなぎのデータ形式に換える
ana_dat2 AS(
SELECT 
    fullVisitorId, 
     CONCAT(substr(date,1,4), '-', substr(date,5,2), '-', substr(date,7,2)) AS dd
FROM ana_dat
GROUP BY fullVisitorId, dd
ORDER BY fullVisitorId, dd
)

# DATE_DIFF関数を利用して来訪日数の間隔を計算する
SELECT
    fullVisitorId, dd,
    DATE_DIFF(date(timestamp(dd)), LAG(date(timestamp(dd))) OVER(PARTITION BY fullVisitorId ORDER BY dd), DAY) AS lead_time 
FROM ana_dat2
;

<<　ページトップへ戻る

１２．カスタムディメンション（customDimentions）の抜き方

カスタムディメンションの解説は割愛するとして、標準SQLで例えばhits.customDimentions.index='1'に該当する（対応する）hits.customDimentions.valueの値をとってくるコードを記す。正確な説明ではないが、indexとは「ハコの名前（番号）」と考えればよく、例えばindex='1'にはClientIDが入っているかもしれない（ユーザーが好きな番号に好きな値を入れるように定義できる）。あるいはindex='2'には会員のステータスが入っているかもしれない。ただし、BigQueryでは「縦持ち（入れ子）」の状態になっており、これを「横展開」してやらないと、データをとってくることができない（データのイメージは下図を参照。正確な図ではない。）。Rでいえばリスト形式でデータが保持されており、それをunlist()でベクトル化するような動作に似ている。

## BigQueryでClientIDとfullVisitorIdを取得する方法（標準SQL）

    SELECT
      custDim.value AS clientId,
      fullVisitorId,
      #date,
      hits.page.pagePath
    FROM
      `プロジェクト名.データセット名.ga_sessions_*`, UNNEST(customDimensions) custDim, UNNEST(hits) hits
    WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170702'
      AND custDim.index = 1 
;


#### 【説明】
#      4行目、9行目、11行目に注目。
#      まずhits.customDimentions.**はインデックス別に縦に保持されている
#      →　http://ex-ture.com/blog/2017/12/13/unnest-ga360-bq-data-with-standardsql/
#      したがって、こういう変数はUNNEST()で横展開しないといけない
#      4行目、11行目は横展開した変数を9行目で「custDim」と名付けているので、custDim.**のように指定する
#      なお本来hits.customDimentions.indexは文字型だが横展開したことで数値型になっていることに注意
#      例えば .index='1'　ではうまく実行されない

#      ちなみにこの手の話題はGoogleで調べればけっこう簡単に探せたりする
#      →https://marketing-sphere.blogspot.jp/2017/10/bigquerycustom-dimensionsquery.html

<<　ページトップへ戻る

１３．URL文字列の分割と結合

Google AnalyticsのデータをBigQueryへ転送している場合、閲覧されたURLは「hits.page.pagePath」という変数に保管されている。BigQuery側ではさらに「hits.page.pagePath1, 2, 3, 4」と第4階層まで区切られたものが用意されている。ただし、第4階層は純粋に「/」で区切られたものではない。例えばhttp://www.katasho.co.jp/toukei/syouhin/books/sp0091.htmなどとあった場合、第1階層は「/www.katasho.co.jp/」と区切られている。通常だと第4階層というと「/books/」までだと思うところだが、「/books/sp0091.htm」まで（第4階層以下全部？）の文字列が格納されていたりする。だから厳密に「/区切りでの第4階層」をとれないことがある。

ここではある文字列（前述したURLのような/で区切られた文字列など）を特定の区切り文字で分割し、また結合するような操作方法を紹介する。なお、BigQueryでは文字列を操作する関数がいくつか用意されているが、標準SQLでは使えないものも多い。例えば、https://cloud.google.com/bigquery/query-reference?hl=ja#stringfunctionsに書かれているINSTR()やLEFT()は使用できない。

使う文字列操作関数：

SPLIT()　・・・　ある区切り文字で、文字列を分割する。
LTRIM()　と　RTRIM()　・・・　左端（先頭）もしくは右端（末尾）の空白や文字列を除去する。
CONCAT()　・・・複数の文字列を結合する。

# standardSQL

## BigQueryのサンプルデータ。このURL文字列を操作する。
SELECT 
    repository.url 
FROM `bigquery-public-data.samples.github_nested` 
LIMIT 10
;

# standardSQL

## SPLIT()を使うと、指定した区切り文字で文字列を分割できる。
#  　分割された文字列は配列として保持されている。
#  　上記Row1のurlは/が4つあったので、5つの要素をもつ配列に分解されたというわけ。
SELECT 
    SPLIT(repository.url, '/') 
FROM `bigquery-public-data.samples.github_nested` 
LIMIT 10
;

# standardSQL

## 配列は[ ]とOFFSET()を利用することで、特定の要素を抜き出せる。
#  　配列の要素番号は0から始まっているので注意。
SELECT 
    SPLIT(repository.url, '/')[OFFSET(0)]
FROM `bigquery-public-data.samples.github_nested` 
LIMIT 10
;

# standardSQL

## COCAT()を使うと複数の文字列を結合することができる。
#  　例えばurlの冒頭のhtt://は除き、第2階層までのurlを表してみる。
SELECT 
    CONCAT( SPLIT(repository.url, '/')[OFFSET(2)], 
            '/', 
            SPLIT(repository.url, '/')[OFFSET(3)]
    )
FROM `bigquery-public-data.samples.github_nested` 
LIMIT 10
;

# standardSQL

## SUBSTR()を使うと、開始と終了の位置を指定して文字列を切り取ることができる。　
SELECT 
    SUBSTR(repository.url, 1, 10)
FROM `bigquery-public-data.samples.github_nested` 
LIMIT 10
;

BigQueryの説明ではないが、SUBSTR()の用法について分かりやすいページがいくつかある。例えばhttp://www.phppro.jp/phpmanual/php/function.substr.htmlはPHPの説明ページだが、とても分かりやすい。

# standardSQL

## RTRIM()やLTRIM()は右端もしくは左端のスペースを除去する関数だが、
#  　第2引数に文字を指定することで以下のような使い方ができる。 　
SELECT 
    LTRIM(repository.url, 'h')
FROM `bigquery-public-data.samples.github_nested` 
LIMIT 10
;

# 上記の例は文字列の左端(先頭)からhを除去している。
# 同様にして、右端(終端)から / を除くようなこともできる。
# 　その場合は　RTRIM(repository.url, '/')　と書く。

<<　ページトップへ戻る