2023年1月18日

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


Content
こんにちは、佐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日までの累計を出したい。

【課題】

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

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

【解決法】

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

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

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

 

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

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

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

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

実際に実装する

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

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

 

  1. 「指定月ディメンション」の追加・編集
    • カスタムフィールドの[+追加]ボタンから[カスタムdimension]を選択
    • 「指定した月の値の行では”yes”を返し、それ以外の行では”no”を返す」yesnoディメンションを作成
      ここでは、指定月に3月を指定する。
    • if(events.month_formatted = 3, yes, no)
  2. 月累計を「指定月ディメンション」で絞り込むよう修正
    • 月累計の表計算を編集し、計算タブから[カスタム式]を選択

    • 手順1で作成した 指定月ディメンション] が”yes”の場合は既存の累積値を返し、それ以外の場合は0を返すように修正

  3. 指定月の範囲だけ表示するようにビジュアリゼーションの表示範囲を変更
    • 手順1で作成した [指定月ディメンション] の歯車アイコンをクリック、[ビジュアリゼーションで”No”を非表示にする] を選択


  4. 完成!

 

【補足】

手順1. “「指定月ディメンション] の追加”では、指定月をコード上に埋め込みしています。

もし表示する月を変える場合は、以下ステップを実行してください

  1. 「指定月ディメンション」を編集
  2. 月の値を編集補足_画像1
  3. 検索を再実行

まとめ

集計の条件が異なる累計を同一のグラフに表示するには、大きく3つの変更で実装できました。

  1. フィルタでの指定を共通の項目のみに変更する
  2. yesnoディメンションを作成し、異なる条件の判定をする
  3. yesnoディメンションを利用し、表計算で指定した条件のみ、累計するように変更する

yesnoディメンションを採用した理由は主に2つでした。

  1. 累計の表計算で、if文の条件分岐に使うため
  2. ビジュアリゼーションで、noの値を非表示にすることができるため

データを絞らずにビジュアリゼーションで見たい範囲を変えたいときは、yesnoディメンションを検討してみてはいかがでしょうか。

最後までお読みいただきありがとうございました。

困っている方の一助になれたのであれば幸いです。

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

Category Google Cloud

ご意見・ご相談・料金のお見積もりなど、
お気軽にお問い合わせください。

お問い合わせはこちら