【Google Cloud】集計方法の異なる累計をひとつのグラフで表示する【Looker】

こんにちは、佐Tです！

Lookerを業務で使っていて、実際に悩んでいたことを元に、今回の記事を作成しました。

これから、Lookerの技術に関する記事などを投稿していく予定です。

早速ですが、Lookerを使っていて集計方法の異なる累計をひとつのグラフにまとめたいと思ったことはありませんか？

この記事では、月ごとの累計(1か月分の累計)と、年ごとの累計(1/1から指定月までの累計)をひとつのグラフにまとめて表示する例を紹介します。

カスタムディメンションと表計算を用いて、ひとつのグラフにまとめてみましょう！
(↓完成予想図)

・現状の計算方法
・問題点
・課題と解決方法
・実際に実装する
・まとめ

現状の計算方法

【使用データ】
Google Cloud BigQueryの公開データから、EC購買分析データを使用します。
データセット："bigquery-public-data.ga4_obfuscated_sample_ecommerce"
テーブル："Events"

【LookMLの定義】 ビュー名："Events.view"

定義	名称(LookML)	概要	対応するカラム名(BigQuery)	補足
ディメンション	year_formatted	年	event_date	※1 YYYY
ディメンション	month_formatted	月	event_date	※1 MM
ディメンション	day_formatted	日	event_date	※1 dd
ディメンション	event_name	ECサイトでのユーザーによるアクション	event_name
ディメンション	event_value_in_usd	イベントごとの売上金額(USD)	event_value_in_usd
メジャー	sum_event_value_in_usd	売上金額の合計	event_value_in_usd	ディメンション"event_value_in_usd"の合計

※1・・・文字列event_dateを日付型のディメンショングループに変換

【Looker Exploreの定義】
フィルター設定：

event_name　　が次の値に等しい (=)　purchase
year_formatted　が次の値に等しい (=)　2021

ディメンション：

year_formatted
month_formatted
day_formatted

メジャー：

sum_event_value_in_usd

表計算：

年累計　running_total(sum_event_value_in_usd)
月累計　running_total(sum_event_value_in_usd)

【用語補足】
ディメンショングループ：LookMLで設定する。日付やタイムスタンプの単位ごとにディメンションを作成・管理できる。

running_total：累積合計のこと。前の行の値に本行の値を足していく計算方法。
Lookerの表計算では上から下で固定されている(並び順を変えたら足していく値の順番も変わる)

問題点

2種類の累計を同じグラフで表示しようと、単純にExploreで両方の表計算を選び、
フィルターで年・月の範囲を絞ってしまうと、年累計と月累計が同じ結果になってしまいます。

【なぜ年累計と月累計の値が同じになってしまうか】
前章で、月累計と年累計の表計算を定義しました。
内容はどちらも売上金額の累計を算出する表計算です。
年累計にはフィルターで年を指定、月累計にはフィルターで年と月を指定して、それぞれの累計を計算することができます。
しかし、2種類の累計を同じグラフで表示する場合、同一のフィルター設定を参照するため、年累計と月累計に同じフィルターの設定が反映され、同一の値になってしまいました。

よって、同じグラフに表示するにはフィルター以外の方法で月累計の集計条件を変える必要があります。

※失敗例

年：2021、月：3で絞り、年累計と月累計を同時に選択

課題と解決方法

まずは、現状を整理します。
【行いたいこと】

指定した年月の年累計と月累計を同時にグラフに出したい。
※例　2022年3月を指定した時、年累計には2022年1月1日～2022年3月31日までの累計を、月累計には2022年3月1日～2022年3月31日までの累計を出したい。

【課題】

Lookerのフィルターは全てのディメンションに同じように影響するため、年累計は年で絞り込み、月累計は年と月での絞り込みが出来ない。
LookerはLookごとにフィルター指定を分けられるため、年累計用Lookと月累計用Lookに分ければほしい情報が得られる。しかし今回は同一グラフ上で表示したい。

これらの課題を解決するために、yesnoディメンションとLookerの [ビジュアリゼーションで"No"を非表示にする] 機能を組み合わせます。

【解決法】

フィルターでの絞り込みは年の指定のみに変更する。
このままでは1月から12月までの情報がビジュアリゼーションに表示されるため、指定月の情報のみ表示し、それ以外は非表示にする。
月累計の計算範囲を、フィルター指定ではなく、指定月のデータのみを計算するように変更する。

指定月はカスタムディメンションの一種、yesnoディメンションを用います。指定月は"yes"、それ以外の場合は"no"を返します。

このyesnoディメンションに対して、[ビジュアリゼーションで"No"を非表示にする] を指定すると、表示したい範囲だけに絞ることが出来ます。

各機能の役割をまとめると、以下の通りです。

【指定月ディメンション】 カスタムディメンション：月を指定し、指定された月は"yes"、それ以外は"no"を返すディメンション
ビジュアリゼーション："No"を非表示

【月累計】 表計算：指定月ディメンションで"yes"の値の場合、running_total(売上金額)
フィルター：年

【年累計】 表計算：running_total(売上金額)
フィルター：年

実際に実装する

問題点と解決方法がわかったところで、実際にLooker Exploreで修正する様子を、手順とともに紹介します！

まず、該当のプロジェクトのExploreを開き、データエリアの結果タブを開いた状態から始めます。

「指定月ディメンション」の追加・編集
- カスタムフィールドの[+追加]ボタンから[カスタムdimension]を選択
- 「指定した月の値の行では"yes"を返し、それ以外の行では"no"を返す」yesnoディメンションを作成
  ここでは、指定月に3月を指定する。
- if(events.month_formatted = 3, yes, no)
月累計を「指定月ディメンション」で絞り込むよう修正
- 月累計の表計算を編集し、計算タブから[カスタム式]を選択
- 手順1で作成した指定月ディメンション] が"yes"の場合は既存の累積値を返し、それ以外の場合は0を返すように修正
指定月の範囲だけ表示するようにビジュアリゼーションの表示範囲を変更
- 手順1で作成した [指定月ディメンション] の歯車アイコンをクリック、[ビジュアリゼーションで"No"を非表示にする] を選択
完成！