ついに来た!LookerのPoP分析用Measure
全Looker開発者待望(筆者調べ)の新機能、PoP(Period over Period)分析用のMeasureが、ついにPreviewとして利用可能になりました!
これまでLookMLをこねくり回したり、計算式でしのいだり、なにかしらのワークアラウンドが必要だった期間比較分析の実装が、よりシンプルに行えるようになるのではと、大きな期待を寄せられている方も多いかと思います。
「これは試してみるしかない!」ということで早速、検証してみました。
実装方法と各パラメータの説明
まずは公式ドキュメントをチェックします。
以下のような形でmeasureのtypeでperiod_over_periodを指定し、
-
based_on
-
based_on_time
-
kind
-
period
という新しい4つの必須パラメータに値(サブパラメータ)を設定するだけでOKとのことで、「これまでの苦労は何だったのか・・・!」と叫びたいぐらい、革命的にシンプルに実装できるようになっています。
measure: order_count_last_year { type: period_over_period description: "Order count from the previous year" based_on: orders.count based_on_time: orders.created_year period: year kind: previous }
現在PoPがサポートされているデータベースは、Amazon Redshift、Google BigQuery (Standard SQL)、Snowflakeのみですが、今後のリリースで対応範囲が増えていくことかと思われます。
早速、viewのderived_tableでダミーデータを生成し、前日比較用のmeasureを実装してみました。まずはシンプルに、前日比較を行えるmeasureを追加しています。
view: pop_test { derived_table: { sql: -- BigQueryでのダミーデータ生成例 SELECT TIMESTAMP(generated_date, "Asia/Tokyo") AS order_date, -- 日付を種に売上金額を生成 MOD(ABS(FARM_FINGERPRINT(FORMAT_DATE('%Y-%m-%d', generated_date))), 20000) + 5000 AS sales_amount FROM ( SELECT generated_date FROM -- 過去2年間の日付を生成 UNNEST( GENERATE_DATE_ARRAY( DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 2 YEAR), DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY), INTERVAL 1 DAY ) ) AS generated_date ) ;; } dimension_group: order { type: time datatype: timestamp timeframes: [ year, fiscal_year, month, fiscal_quarter, quarter, week, date, raw ] sql: ${TABLE}.order_date ;; } dimension: sales_amount { type: number sql: ${TABLE}.sales_amount ;; hidden: yes } measure: total_sales_amount { type: sum sql: ${sales_amount} ;; label: "合計売上金額" } # 新たにリリースとなったPoPのMeasure measure: total_sales_amount_last_date { type: period_over_period based_on: total_sales_amount based_on_time: order_raw period: date kind: previous label: "前日の合計売上金額" } }
based_on
based_onパラメータでは比較対象のmeasureを指定します。
指定できるmeasureのtypeに制約があって、現状以下の集約系のtypeのmeasureしか指定できません。
試しにtype: number
のmeasureを指定したところ、LookMLの検証でエラーが発生しました。
-
average
-
average_distinct
-
count
-
count_distinct
-
max
-
min
-
sum
-
sum_distinct
## type: numberのmeasureをbased_onで指定しているNGケース dimension: sales_amount { type: number sql: ${TABLE}.sales_amount ;; hidden: yes } measure: sum_sales_amount { type: number sql: SUM(${sales_amount}) ;; label: "合計売上金額" } # PoPのメジャー measure: total_sales_amount_last_date { type: period_over_period based_on: sum_sales_amount based_on_time: order_raw period: date kind: previous label: "前日の合計売上金額" }
based_on_time
based_on_time パラメータでは、期間の軸となる type: time
の dimension_group で定義された以下の時間枠(timeframes)を指定します。
今回は order_raw を指定しましたが、どの時間枠を選んでもPoPの計算結果に違いはありませんでした。
-
year
-
fiscal_year
-
month
-
fiscal_quarter
-
quarter
-
week
-
date
-
raw
period
periodパラメータでは比較で遡る期間を指定します。例えばperiod: year
にすると前年の同期間の数字と比較することができます。以下の中から選ぶことができます。今後、1時間前との比較など細かな粒度も追加されていくのかもしれませんね。
-
year
-
fiscal_year
-
quarter
-
fiscal_quarter
-
month
-
week
-
date
kind
kindパラメータでは以下の値を選択することができます。previousさえあれば、differenceやrelative_changeは計算式でも算出できるので、measureが増えてユーザーが混乱するのを避けたい場合はpreviousだけ提供する、という形でも良さそうです。
-
previous: (デフォルト)前の期間の値
-
difference: 期間の差(前の期間から現在の期間を差し引いた値)
-
relative_change: 前の期間からの変化率({現在-前期)/前期)
Exploreで挙動を確認
早速、modelにexploreを追加して挙動を確認しましたが、何の問題もなく前日の売上金額が同じ行に表示されていました🎉
このときに、どのようなクエリが生成されているか見てみると、
WITH pop_test AS (-- BigQueryでのダミーデータ生成例 SELECT TIMESTAMP(generated_date, "Asia/Tokyo") AS order_date, -- 日付を種に売上金額を生成 MOD(ABS(FARM_FINGERPRINT(FORMAT_DATE('%Y-%m-%d', generated_date))), 20000) + 5000 AS sales_amount FROM ( SELECT generated_date FROM -- 過去2年間の日付を生成 UNNEST( GENERATE_DATE_ARRAY( DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 2 YEAR), DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY), INTERVAL 1 DAY ) ) AS generated_date ) ), pop_test__lkr_pop_457 AS ( SELECT (TIMESTAMP_TRUNC(pop_test.order_date, DAY, 'Asia/Tokyo')) AS pop_test_order_date, COALESCE(SUM(pop_test.sales_amount ), 0) AS pop_test_total_sales_amount, COALESCE(SUM(pop_test.sales_amount ), 0) AS pop_test_total_sales_amount_last_date FROM pop_test GROUP BY 1 ) SELECT (DATE(TIMESTAMP_TRUNC(pop_test_order_date, DAY, 'Asia/Tokyo'), 'Asia/Tokyo')) AS pop_test_order_date, pop_test_total_sales_amount, ((( SELECT pop_test_total_sales_amount_last_date FROM pop_test__lkr_pop_457 WHERE pop_test_order_date = (TIMESTAMP_ADD( t0.pop_test_order_date, INTERVAL -1 DAY))))) AS pop_test_total_sales_amount_last_date FROM pop_test__lkr_pop_457 AS t0 ORDER BY 1 DESC LIMIT 500
なるほど、pop_test__lkr_pop_457テーブルのpop_test_total_sales_amount_last_dateに一旦当日の売上を格納しておいて、あとから相関サブクエリでその1日前の値を引っ張ってくる仕組みになっているようです。
期間フィルタを適用した際の挙動
続いて、直近の7完了日に絞るフィルタを追加してみたところ、こちらも問題なく対象の日付のみが抽出されました。
よく見ると、一番下の行(フィルタの期間内で一番過去の日付)の前日の合計売上金額もきちんと表示されていることがわかります。
直感的には7完了日のデータのみにWHERE句で絞られるので、7日前の前日(8日前)の値はNULLになっても不思議ではない感じですが、そうはなっていません。
こちらもLookerが生成したクエリを確認してみたところ、一旦8完了日分のデータをpop_test__lkr_pop_457で抽出した上で、7完了日分のそれぞれの前日の値を相関サブクエリで引っ張ってくるのと同時に7完了日分のデータに絞る、という構造になっていました。
ここまでのことをボタンひとつでやってくれるLooker、やっぱりすごい・・・。
これまではPoPのMeasureで前日の値を表示するようにしていますが、これが他の粒度の場合でもフィルタした際に同じ用に期間を広げた上で値を持ってこれるか、検証してみました。
まずはPoPのmeasureのperiodの値をweekに変更します。
measure: total_sales_amount { type: sum sql: ${sales_amount} ;; label: "合計売上金額" } # PoPのメジャー measure: total_sales_amount_last_week { type: period_over_period based_on: total_sales_amount based_on_time: order_raw period: week kind: previous label: "前週の合計売上金額" } }
さきほどと同じく、dimensionはDate、7完了日のフィルタを適用して実行すると、
一番直近の行にだけ前週の同曜日の合計売上金額が表示されて残りはNULLになりました。クエリを確認したところ、先ほどと同じく、まず8完了日分のデータを取得していることがわかりました。
理想としては全ての行に前週同曜日の値が入って欲しいところではありますが、その場合、フィルタする期間を広げ、画像のような形で表示する行を制限するなどの対応が必要そうです。
続いて、dimensionをWeekに変更して、7完了週のフィルタを適用してみたところ、
こちらは全ての行に前週の値が表示されました。
クエリを確認したところ、まず8完了週分のデータを抽出していることがわかりました。
どうやら、選択している軸となる期間dimensionの粒度によって、PoPのmeasureのために期間フィルタの始点を過去に向けてどれだけ拡張するかが調整されているようです。
細かいところですが、すごくよく考えられていますね。
ローカライズの観点からdimension_group使っていない問題
と、ここまで検証を進めてきましたが、ひとつ大きな問題が見つかりました。
期間の軸となるdimension_groupですが、使用するとtimeframesの値に基づいて時間単位のディメンションが自動生成されますが、この場合、ディメンションのラベルが英語表記となり、手動で変更もできないというデメリットがあります。
そこで弊社では、ユーザー目線でのわかりやすさを考慮してdimension_groupは使用せず、日時系のディメンションは全ての粒度を個別のdimensionで定義して日本語のラベルやdescriptionを付与するようにしています。(日本の多くの企業でも同様の運用をされているのではないでしょうか。)
試しに以下のような形で個別に定義したtype: date
のディメンションをbased_on_timeパラメータで指定してみると・・・
dimension: order_date { datatype: timestamp type: date sql: ${TABLE}.order_date ;; } dimension: sales_amount { type: number sql: ${TABLE}.sales_amount ;; hidden: yes } measure: total_sales_amount { type: sum sql: ${sales_amount} ;; label: "合計売上金額" } # PoPのメジャー measure: total_sales_amount_last_date { type: period_over_period based_on: total_sales_amount based_on_time: order_date period: date kind: previous label: "前日の合計売上金額" }
LookMLの検証はクリアしましたが・・・
Exploreを確認すると・・・
PoPのMeasureの値が全てNULLになってしまっていました。
このときのクエリを見てみると、
WITH pop_test AS (-- BigQueryでのダミーデータ生成例 SELECT TIMESTAMP(generated_date, "Asia/Tokyo") AS order_date, -- 日付を種に売上金額を生成 MOD(ABS(FARM_FINGERPRINT(FORMAT_DATE('%Y-%m-%d', generated_date))), 20000) + 5000 AS sales_amount FROM ( SELECT generated_date FROM -- 過去2年間の日付を生成 UNNEST( GENERATE_DATE_ARRAY( DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 2 YEAR), DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY), INTERVAL 1 DAY ) ) AS generated_date ) ), pop_test__lkr_pop_457 AS ( SELECT (TIMESTAMP_TRUNC(pop_test.order_date, DAY, 'Asia/Tokyo')) AS pop_test_order_date, COALESCE(SUM(pop_test.sales_amount ), 0) AS pop_test_total_sales_amount, COALESCE(SUM(pop_test.sales_amount ), 0) AS pop_test_total_sales_amount_last_date FROM pop_test GROUP BY 1 ) SELECT (DATE(TIMESTAMP_TRUNC(pop_test_order_date, DAY, 'Asia/Tokyo'), 'Asia/Tokyo')) AS pop_test_order_date, pop_test_total_sales_amount, ((( SELECT pop_test_total_sales_amount_last_date FROM pop_test__lkr_pop_457 WHERE pop_test_order_date = t0.pop_test_order_date AND pop_test_order_date = (TIMESTAMP_ADD( t0.pop_test_order_date, INTERVAL -1 DAY))))) AS pop_test_total_sales_amount_last_date FROM pop_test__lkr_pop_457 AS t0 ORDER BY 1 DESC LIMIT 500
となっていて、pop_test_total_sales_amount_last_dateの相関クエリのWHERE句のpop_test_order_date = t0.pop_test_order_date
が邪魔をしてNULLになってしまっていることがわかりました。この条件がなければ、期待通りの結果が得られたのですが・・・😭
以上のように、ドキュメントに書かれているとおりではありますが、現状、PoPのmeasureは期間の軸としてdimension_groupしか指定できない、ということが確認できました。
dimension_groupを使用していない弊社としては、現状採用は見送り、と判断せざるを得ないかなと考えています。
PoPのMeasureであふれかえってしまう問題
もうひとつ、複数のMeasureがあるExploreにおいて、全てのMeasureにPoPを実装しておきたいとなった場合に、LookMLが超絶長くなってしまう&ExploreにおいてもPoPのMeasureで溢れかえってしまう問題が発生するのでは・・・と感じました。
比較対象する期間を指定するperiodを全7種類、kindも3種類全て用意して欲しいとなった場合には1つのMeasureに対してPoP分析用に21種類のMeasureを準備することになります。
LookMLは生成AIに書かせれば開発者側は良いとしても、ユーザーはExploreでMeasureの選択に一苦労してしまいそうです。
例えば、parameter を使って period の粒度をユーザーが選択できる形にできれば、両方の課題が改善できそうですが、ドキュメントでは「PoP 測定の各パラメータで、リキッド パラメータは使用できません。」と明言されており、こちらも実現の可能性は低そうです。
まとめ
Lookerに待望のPoP(Period over Period)分析用Measureが登場し、期間比較の実装が格段にシンプルになることが期待されました。
実際に検証したところ、基本的な動作は素晴らしく、特にフィルタ適用時の賢さには目を見張るものがありました。
しかし、大きな課題も見つかりました。期間の軸としてdimension_groupしか指定できないため、日本語ラベルのために個別のdimensionを使用している環境では、PoP Measureが正しく機能せず、現状、弊社の環境への導入は難しい状況ということもわかりました。
今後のアップデートに期待したいと思います!