Lookerで経営データ分析！#3BigQueryでデータマート作成
前回#2ではLooker分析の全体アーキテクチャを公開しました。今回はその中でもBigQueryでのデータマート作成について掲載します。
データマート作成の目的として、Lookerで可視化するために売上などの各種指標がSUMするだけで算出できるように、RawデータをSQLでデータ整形します。（Looker上でもLookMLでデータ整形、算出ロジックの設定出来ますが、Looker上でしかデータの参照が出来なくなるので、BigQuery上でデータマートの作成はやったほうが良いです。）
売上
BigQueryで条件分岐を行うときはIF分、CASE式が便利です。
上記SQLではsales_category（売上区分）列が施術売上、店販売上、役務売上(チケット)、キャンセル手数料のいずれかの場合、売上に計上するようにしています。逆に掛け金などの金額は売上から除いています。
この辺りの考え方は業種、使っているシステム、クライアントの見たい数値、で変わってくるので事前確認が必要です。
契約数、解約数、来店数
契約、解約、来店を条件分岐で判定して、BOOL型でフラグを立てます。
Rawデータの明細が１売上１レコード単位なので、上記のSQLのフラグを
ユニークな顧客でCOUNTすれば、契約数、解約数、来店数が算出できます。
顧客属性（年齢、年代、居住地）
Rawデータに顧客マスタがあるので、JOINして年齢、郵便番号、住所が分かります。年齢だと分析の際、粒度が細かすぎる問題があるので、年代の中間テーブルを作成して、顧客の年代を付与します。（10代、20代…）
また顧客の居住地に関して、都道府県、市区町村ごとに分析したいので、郵便番号、都道府県、市区町村の中間テーブルを作成して、顧客の居住都道府県、市区町村が分かるようにします。
↓日本郵便株式会社のHPからDLできるCSVデータを利用しました。
https://www.post.japanpost.jp/zipcode/download.html
注意点として、同一郵便番号で複数の町名・番地があり得るので、今回知りたいのは市区町村までだったので、GROUP BYして一意になるようにしてます。
平日・休日区分
売上などの指標を平日・休日別に分析するために土日および祝日の条件分岐を入れます。土日についてはBigQueryのEXTRACT()関数で判定、祝日は内閣府のHPより祝日スケジュールのCSVで中間テーブルを作成して判定しました。（Googleカレンダーの祝日を連携できないか調べたのですが無理でした泣）
https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html
まとめ
データマートで作成している指標とディメンションはこんな感じです！（本当はもっとあるのですが割愛します…）
これだけでも単に売上と言っても、10代の売上、20代の売上、平日の売上、休日の売上、世田谷区の顧客の売上、北区の顧客の売上…と様々な切り口で分析できます。こんなデータがLooker上だとリアルタイムでフレキシブルに確認できるので、やっぱりBIツールって便利だなあとしみじみ思いました。。
