ついに来た！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種類全て用意して欲しいとなった場合には１つのMeasureに対してPoP分析用に21種類のMeasureを準備することになります。

LookMLは生成AIに書かせれば開発者側は良いとしても、ユーザーはExploreでMeasureの選択に一苦労してしまいそうです。

例えば、parameter を使って period の粒度をユーザーが選択できる形にできれば、両方の課題が改善できそうですが、ドキュメントでは「PoP 測定の各パラメータで、リキッド パラメータは使用できません。」と明言されており、こちらも実現の可能性は低そうです。

まとめ

Lookerに待望のPoP（Period over Period）分析用Measureが登場し、期間比較の実装が格段にシンプルになることが期待されました。

実際に検証したところ、基本的な動作は素晴らしく、特にフィルタ適用時の賢さには目を見張るものがありました。

しかし、大きな課題も見つかりました。期間の軸としてdimension_groupしか指定できないため、日本語ラベルのために個別のdimensionを使用している環境では、PoP Measureが正しく機能せず、現状、弊社の環境への導入は難しい状況ということもわかりました。

今後のアップデートに期待したいと思います！