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

こんにちは。前回#2ではLooker分析の全体アーキテクチャを公開しました。今回はその中でもBigQueryでのデータマート作成について掲載します。
データマート作成の目的として、Lookerで可視化するために売上などの各種指標がSUMするだけで算出できるように、RawデータをSQLでデータ整形します。（Looker上でもLookMLでデータ整形、算出ロジックの設定出来ますが、Looker上でしかデータの参照が出来なくなるので、BigQuery上でデータマートの作成はやったほうが良いです。）

売上

 IF
           (sales_category IN ('施術売上',
               '店販売上',
               '役務売上(チケット)',
               'キャンセル手数料'),
             total,
             0) AS sales

BigQueryで条件分岐を行うときはIF分、CASE式が便利です。
上記SQLではsales_category（売上区分）列が施術売上、店販売上、役務売上(チケット)、キャンセル手数料のいずれかの場合、売上に計上するようにしています。逆に掛け金などの金額は売上から除いています。
この辺りの考え方は業種、使っているシステム、クライアントの見たい数値、で変わってくるので事前確認が必要です。

契約数、解約数、来店数

IF
           (sales_category = '役務売上(チケット)'
             AND purpose = '購入'
             AND free_code1 IS NOT NULL,
             TRUE,
             FALSE) AS contract_flag,
         IF
           (purpose = '返品・返金',
             TRUE,
             FALSE) AS cancel_flag,
         IF
           (visit = '○',
             TRUE,
             FALSE) AS visit_flag

契約、解約、来店を条件分岐で判定して、BOOL型でフラグを立てます。
Rawデータの明細が１売上１レコード単位なので、上記のSQLのフラグを
ユニークな顧客でCOUNTすれば、契約数、解約数、来店数が算出できます。

顧客属性（年齢、年代、居住地）
Rawデータに顧客マスタがあるので、JOINして年齢、郵便番号、住所が分かります。年齢だと分析の際、粒度が細かすぎる問題があるので、年代の中間テーブルを作成して、顧客の年代を付与します。（10代、20代...）
また顧客の居住地に関して、都道府県、市区町村ごとに分析したいので、郵便番号、都道府県、市区町村の中間テーブルを作成して、顧客の居住都道府県、市区町村が分かるようにします。
↓日本郵便株式会社のHPからDLできるCSVデータを利用しました。
https://www.post.japanpost.jp/zipcode/download.html

LEFT JOIN (
               SELECT
                 postal_code,
                 MAX(prefectures_jp) AS prefectures_jp,
                 MAX(municipalities_jp) AS municipalities_jp
               FROM
                 `test.test_dataset.Postal_code`
               GROUP BY
                 postal_code) AS b
             ON
               CAST(REPLACE(a.postal_code,"-","")AS int) = b.postal_code )

注意点として、同一郵便番号で複数の町名・番地があり得るので、今回知りたいのは市区町村までだったので、GROUP BYして一意になるようにしてます。

平日・休日区分

IF
           ( EXTRACT(DAYOFWEEK
             FROM
               a.date) = 1
             OR EXTRACT(DAYOFWEEK
             FROM
               a.date) = 7,
             '休日',
           IF
             ( holiday IS NOT NULL,
               '祝日',
               '平日') ) AS holiday_category

売上などの指標を平日・休日別に分析するために土日および祝日の条件分岐を入れます。土日についてはBigQueryのEXTRACT()関数で判定、祝日は内閣府のHPより祝日スケジュールのCSVで中間テーブルを作成して判定しました。（Googleカレンダーの祝日を連携できないか調べたのですが無理でした泣）
https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html

まとめ
データマートで作成している指標とディメンションはこんな感じです！（本当はもっとあるのですが割愛します...）
これだけでも単に売上と言っても、10代の売上、20代の売上、平日の売上、休日の売上、世田谷区の顧客の売上、北区の顧客の売上...と様々な切り口で分析できます。こんなデータがLooker上だとリアルタイムでフレキシブルに確認できるので、やっぱりBIツールって便利だなあとしみじみ思いました。。