BigQueryで売上などのKPIを積み上げ集計をする方法

8584 ワード
analytics SQL BigQuery monitoring KPI SQL テキストリンク

はじめに

ビジネスやプロダクトKPIをモニタリングしたい時、リリース〜現在までの顧客数や月次売上を積み上げで見たいことありますよね？
特定の地点のデータを見るのは簡単なのですが、月ごとの推移を見るのが意外と大変なので、自分がやった方法をまとめてみます

前提

出したいデータのイメージ

横軸→リリースしたときから現在
縦軸→その月時点での数値

CRMツールだったり、プロダクトのDBのデータを色々こねくり回してやろうとするのですが、多くのケースで

売上日 顧客ID 金額
2018/01/01 1 100,000
2018/02/20 2 200,000
2018/03/23 3 300,000
2018/04/13 1 150,000

のような形になっているのではないでしょうか。

これを

月 顧客ID 金額
2018/01 1 100,000
2018/02 1 100,000
2018/02 2 200,000
2018/03 1 100,000
2018/03 2 200,000
2018/03 3 300,000
2018/04 1 250,000
2018/04 1 200,000
2018/04 1 300,000

のようにその月時点のデータ（月ごとに複数のレコードがある場合は加算していく）を表示したいものとします

積み上げ集計の方法

1.特定期間の月ごとのレコードを生成する

SELECT
          EXTRACT(YEAR FROM term) AS year
          ,EXTRACT(MONTH FROM term) AS month
        FROM UNNEST(GENERATE_DATE_ARRAY('xxx', CURRENT_DATE)) AS term
        GROUP BY 1,2

まずはこのように特定期間の月ごとのレコードを生成します

月
2018/01
2018/02
2018/03
2018/04

2.顧客のID一覧を取得する

その後に全顧客のIDを取得します

例）

SELECT
          id
        FROM customer
        GROUP BY 1
顧客ID
1
2
3

3.CROSS JOINする

これを CROSS JOINします。そうすると

月 顧客ID
2018/01 1
2018/01 2
2018/01 3
2018/02 1
2018/02 2
2018/02 3
2018/03 1
2018/03 2
2018/03 3
2018/04 1
2018/04 2
2018/04 3

のような、月*顧客IDのレコードが生成されます。

4.トランザクションデータを紐付ける

これに対して、売上データなどのトランザクションデータを紐付けていきます

月 顧客ID 金額
2018/01 1 100,000
2018/01 2 0
2018/01 3 0
2018/02 1 100,000
2018/02 2 200,000
2018/02 3 0
2018/03 1 100,000
2018/03 2 200,000
2018/03 3 300,000
2018/04 1 250,000
2018/04 2 200,000
2018/04 3 300,000

あとは月ごとにGROUP BYしてグラフ化すれば完成です。
顧客IDもあるので、特定の顧客のみにフィルタすることもできます

おわりに

こういったSQLで同じテーブル同士を結合する累積も試してみたのですが、テーブル内にない月は表現することができず
色々試してこのやり方にたどり着きました。
おそらくもっといい方法があるはず、、、とは思うのですが、同じように困っている方もきっといると思うので、参考になれば幸いです