【BigQuery】実務で役立つデータ抽出の勘所
はじめに
自社でシステムの開発・運用を行っている企業ではCRM(顧客情報管理)やECサイトのログ、実店舗での売上明細などの様々なデータがデータ分析基盤と呼ばれる形で集約されているかと思います。
今回は集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析（以下、本書と呼ぶ）にある演習問題を解いてみたので、実務経験を踏まえて役立ちそうと感じた知見を自分なりにまとめました。
前提
あくまで巻末の演習問題を解いただけで本書の大半を占める文法解説のページは全く読んでおらず、公式ドキュメントしか読んでいません。
また、本書の内容を読んでいない方でも主張が伝わるような形で知見をまとめています。
知見①：「SQLの構文を知っている＝必要なデータ集計ができる」ではない
当然ですが実務をこなす上では、「構文を知っていること」よりも「集計に必要なデータに対するビジネスロジックの知識」がかなり重要という体感です。
本書にある演習問題にはWhere句でどのような条件で絞り込めば良いのかや、サブクエリで何を集計する必要があるかイメージが浮かびやすい問題が多いですが、実務でデータ抽出をする際には
- どのテーブル・カラムが必要か(カラム名がどんなデータなのか直感的に分かりにくい場合はビジネス上の意味も理解する必要がある)
- どのテーブル・カラムにあるデータをどのように集計すれば最終的に必要なデータの形になるか
- 複数のテーブルから集計する場合はどの方法でJOINするべきか
などの点をゴールから逆算して明確にしないと、想定とは違う結果になってしまいます(地頭が良い人は不要かもしれませんが)。また、ER図やテーブルの設計・定義に関して理解度も並行して上げていくと、上記の点はスムーズに進めやすいのではと思います。
演習問題ではこれらの点について既に明確になっているため、実務経験がない人は実感しにくいかと思います。
知見②：前提となるビジネスロジックを知る必要がある
KPIの算出
ECサイトにおける商品やレコメンドなどのクリック率や購入率をKPIとして集計し、売上効果を検証しようといった場合をイメージしてみましょう。このような際の指標としてよく使われる用語として、CVR(Conversion Rate)やCTR(Click Through Rate)といったものがあります。
詳しくはWebマーケティングのページを調べてみてください。
CVRやCTRの計算方法は以下の式で求めることができます。CVRとはサイト訪問数に対するCV数の割合を意味します。CTRとはクリック率を指すことが多く、表示させたものの効果を表す指標の一つです。インプレッション(表示された回数)の中でどのくらいクリックされたかの割合です。
CVR = CV数÷サイト訪問数（セッション数）×100
CTR = クリック数÷インプレッション数×100
集計の例として、本書で扱うsample.web_logというテーブルを用いてサイトのCVRを求める場合を考えてみます。
WITH temp AS (
SELECT
session_count,
MAX(cv_page) AS CV
FROM
(
SELECT
*,
IF(page = "/thank_you/", 1, 0) AS cv_page
FROM
sample.web_log
)
GROUP BY
session_count
)
SELECT
SUM(cv) AS CV,
COUNT(*) AS SESSION,
(SUM(cv)/COUNT(*))*100 AS CVR
FROM
temp
CV数には、ECサイトの場合は商品購入数といった数が該当します。これらの値を用いて、CVRを算出します。CVRが高ければ、そのWebサイトはCV数が取れていて売上が高い傾向にあることがわかります（逆も然り）。
さて、集計を考えていきます。購入者に表示されるページのURLには「/thank_you/」という文字列があり、表示されたページのログのデータはsample.web_logというテーブルのpageというカラムであると仮定します。購入数の集計は上記のように、WITH句内のサブクエリで集計してから、SUMで集計したものをCV数として考えることができます。サイト訪問数（セッション数）はWITH句で集計したレコードが何行あるかを数えれば良い場合はCOUNT(*)とします。
※どのように集計すれば正解になるのかはビジネスロジックに依存しますので、これは一般論ではありません。
まとめ
今回はBigqueyを用いた集計について技術的というよりビジネス寄りの内容ですが、演習問題を通じて実務でも役立つ考え方を簡単にまとめてみました。本書の演習問題を解く環境は読者特典のcsvデータを取り込んでテーブル作成すれば容易に準備できると思いますので、気になった方は本書や公式ドキュメントのテーブルの作成と使用のページを見てみてください。
Discussion