はじめに

複数のデータを集計して1つのグラフで表示したい時ってありますよね？私はあります。
でも日ごとに集計するとデータが存在しない日が欠けてしまって、うまく出せないーということがあります。
昔からそんなシーンが稀に良くありましたが、ググってもバシッと見つかることが無く、無駄に時間を割いたりしていました。

ということで書いておきたいと思います。
もし同じ悩みで困っている人の助けになったら嬉しいです。

環境情報

BigQuery
- データセット名は data_set としています
Looker studio

前提

データは以下のような情報となっていることを想定しています。

customerデータ
- id, name, status, create_date, update_dateを持っている
- カスタマーが登録した場合にデータが挿入される
providerデータ
- id, name, status, create_date, update_dateを持っている
- カスタマーが登録した場合にデータが挿入される

概要
サービスにproviderとcustomerのロールがそれぞれあり、登録した場合にレコードが追加される。
サービスの状況を把握するため日ごとの登録者数を可視化したい。

BigQueryでデータを集計

withの利用

集計にあたり複数のテーブルを利用するため、それぞれ内容を解りやすくするためにwith句を利用します。構文はこんな感じです。

-- 1つ目
with [仮テーブル名] as (
-- 抽出SQL
)

--2つ目以降
, [仮テーブル名] as (
-- 抽出SQL
)

日ごとに集約するためにカレンダーデータを用意

特定の日（この例では 2020-01-01）を起点として実行した日までの日付データを網羅します。

with calendar AS (
  SELECT
    create_date
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 DAY)) AS create_date
)

customerとproviderを集計

それぞれ、create_date でグループ化してレコード数をカウントします。

, provider as (
  select 
    date(create_date) create_date,
    count(*) provider_count
  from `data_set.provider` 
  group by create_date
)

, customer as (
  select 
    date(create_date) create_date,
    count(*) customer_count
  from `data_set.customer` 
  group by create_date
)

日で結合

カレンダーとして抽出した calendar を軸にそれぞれの集計結果を結合します。
対象日のデータが存在しない場合は 0 となるようにするため、表結合は left outer join を使い、null の場合は 0 となるようにしておきます。

select
  calendar.create_date,
  ifnull(provider_count, 0) provider_count,
  ifnull(customer_count, 0) customer_count,
from calendar
  left outer join provider on calendar.create_date = provider.create_date
  left outer join customer on calendar.create_date = customer.create_date

これで日ごとに複数のデータの集計結果がまとまりました。

ビューとして保存

作成したクエリを実行して期待通りの結果が得られたら ビューを保存 を行っておきます。
BigQueryからはこのビューを参照します。

Looker studioで表示する

ここからはLooker studioでの作業となります。
基本的な操作は出来る前提として簡略化しているので、もし操作方法が解らない場合は他のガイドを平行で参照してください。

レポートを新規作成

新しいレポートを作成します。内容は割愛します。

データソースを追加

データを追加 からデータソースとしてBigQueryを選択し、先ほど保存したビューを選択します。
特に編集はしないのでそのまま追加します。

折れ線グラフを追加

レポートに折れ線グラフを追加、

グラフの設定でディメンションを create_date に、指標に customer_count と provider_count にします。並べ替えは create_date の昇順にすると良いです。

もし集計結果の合計値の差が大きい場合は片方のデータを 右側 にしておくと、メモリの範囲が2つに分かれてグラフが見やすくなります。

グラフの表示結果はこんな感じ。

おわりに

基本的な使い方は以上ですが、ユーザのデータ数だけでなく、トランザクションデータなど集計対象のデータを更に結合していくことで、ユーザ数の推移とトランザクションの推移を同時に可視化するなど、応用が利くかと思います。