DataStudio で条件付きの割合スコアカードを作る

f:id:pokutuna:20200706071147p:plain
こういうやつ

Google DataStudio1 でこういう表示をしたい場合は多い。目標の達成割合とか、アプリケーションログから何らかのアクションを取ったユーザの割合を出したりだとか。とりあえず使うだけなら簡単に使える DataStudio だけど、これはシンプルな表示のわりに妙なテクが必要なので書いておく。

サンプルデータとして、GitHubリポジトリとライセンスの情報が入った公開データセットを使う、なぜならスキーマがめちゃ簡単だから2bigquery-public-data.github_repos.licenses

f:id:pokutuna:20200706071810p:plain
repo_name と license だけ

ここでは、MIT ライセンスを応援しているとして、全体に対する MIT ライセンスの割合を表示したダッシュボードを作りたいとする。表で割合を出すのは簡単。一方スコアカードにするのは難しい、license="mit" みたいなフィルタを作ってから、これを適用すると分母となる全体の値が取れないことに気づく。

f:id:pokutuna:20200706072956p:plain
ヒートマップつき表

データを統合 / 混合データソース

(おすすめではない)

目を疑うけどこういう機能がある。

f:id:pokutuna:20200706075726g:plain
データを統合

license="mit" のフィルタを適用したスコアボードと、全体の値の 2つ選択して右クリックからデータを統合すると出てくる。びっくりするけど、混合データソースを作って集計するショートカットになってる。

統合キーも無いので、JOIN じゃなくて SELECT 2 本相当、こういうクエリに相当するはず。

SELECT
  mit / total
FROM (
  SELECT
    (SELECT COUNT(*) FROM bigquery-public-data.github_repos.licenses) as total,
    (SELECT COUNT(*) FROM bigquery-public-data.github_repos.licenses WHERE license = "mit") as mit
)

簡単なのはよいけど、都度混合データソースを作ってしまうし、あまりおすすめではない。スコアカードはいくつも置くことが多いし、ノリノリで作ってると 混合データ (21) みたいな状態になる。既に混合データソースがあって整備されているならそれを使うべきだし、内部的にデータソースごとにキャッシュが作られている感があるので、クエリコストの面でも良くないと思う。

CASE WHEN でマップ

(おすすめ)

個人的によく使うのがこの方法。
SUM(CASE WHEN license="mit" THEN 1 ELSE 0 END) / Record Count のような計算式を書く。

f:id:pokutuna:20200706093906p:plain
計算式で指標を作る

あとで編集するときに何だこりゃとならないし、混合データソースにゴミが増えない。

1発で複数の条件で集計した値を取りたい場合に投げるこういうクエリを DataStudio でやってる感じ。

 SELECT
   COUNT(*) as all_user,
   COUNT(CASE WHEN age >= 20 THEN 1 ELSE null END) as adult,
   COUNT(CASE WHEN age < 20 THEN 1 ELSE null END) as child,
 FROM ...

DataStudio のフィルタは SQLWHERE 句に相当するので、クエリ全体に適用する条件にはフィルタを使って、計算フィールドで SELECT するフィールドに式を書くイメージです。

もし似たような集計を何度もやる場合は、データソースに計算フィールドとして 1 or NULL のフィールドを作っておくと便利。型は数値 デフォルトの集計は 件数 で集計フィールドを作る。

f:id:pokutuna:20200706082933p:plainf:id:pokutuna:20200706093150p:plain
計算フィールドを作っておく

ここで COUNT を使っているのは 1 or NULL にマップしてるから。SUM でも大抵は問題ないけど SUM(NULL)NULL なのでフィルタされた行が 0 行の場合エラーになる可能性がある。集計方法は UI 上で 合計(SUM)件数(COUNT)個別件数(COUNT DISTINCT) を切り替えられるので、フィールドを作るなら 1 or NULL にマップする方が他の集計にも使いやすいことが多い気がするのでそうしている。


  1. 国内では何らかの商標と被ったのかデータポータルに改名されたけど、ページによっては表記がバラついてるしドメインdatastudio.google.com のままだし、みんなデータスタジオと依然呼び続けている状況。

  2. ちょっと古いのかフィルタされているのか、22k 近く fork されている DefinitelyTyped の行数が 647 だったけど気にしない。(WHERE repo_name LIKE "%/DefinitelyTyped" とかで)