Lookerで経営データ分析!#3BigQueryでデータマート作成

前回#2ではLooker分析の全体アーキテクチャを公開しました。今回はその中でもBigQueryでのデータマート作成について掲載します。
データマート作成の目的として、Lookerで可視化するために売上などの各種指標がSUMするだけで算出できるように、RawデータをSQLでデータ整形します。(Looker上でもLookMLでデータ整形、算出ロジックの設定出来ますが、Looker上でしかデータの参照が出来なくなるので、BigQuery上でデータマートの作成はやったほうが良いです。)
売上
BigQueryで条件分岐を行うときはIF分、CASE式が便利です。
上記SQLではsales_category(売上区分)列が施術売上、店販売上、役務売上(チケット)、キャンセル手数料のいずれかの場合、売上に計上するようにしています。逆に掛け金などの金額は売上から除いています。
この辺りの考え方は業種、使っているシステム、クライアントの見たい数値、で変わってくるので事前確認が必要です。

契約数、解約数、来店数
契約、解約、来店を条件分岐で判定して、BOOL型でフラグを立てます。
Rawデータの明細が1売上1レコード単位なので、上記の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ツールって便利だなあとしみじみ思いました。。