[Looker] Period over Period 分析をLookMLで実装する その参 #looker

この記事では、こちらの記事で紹介されている、前年同日比もしくは前月同日比分析（Period over Period 分析）の8つのユースケースのSQLを、SnowflakeのSQLに対応したものに書き換えて試してみる第三弾です。

• Period over Period 分析をLookMLで実装する その壱

• Period over Period 分析をLookMLで実装する その弐

第三回も、引き続きparameterパラメータを使用して比較対象の期間を指定しますが、抽出期間はテンプレートパラメータを使用することで前期（Previous period）が使用できるようになります。

記事にもあるとおり、難点は実装が複雑になることなのですが、このコードが理解できると、隠しフィールドで列の値を評価するというテンプレートフィルターの特性を身に付けることもでき、LookMLでの開発の柔軟性も爆上がりします。

LookMLの実装

前提条件

ECサイトの注文商品のテーブルの注文日（created）を元に、期間を指定して比較を出します。

view: order_items {
  sql_table_name: public.order_items ;;

  dimension: id {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.id ;;
  }
  dimension_group: created {
    type: time
    view_label: "_PoP"
    sql: ${TABLE}.created_at ;;
    convert_tz: no
  }
  measure: count {
    label: "Count of order_items"
    type: count
    hidden: yes
  }
  measure: count_orders {
    label: "Count of orders"
    type: count_distinct
    sql: ${order_id} ;;
    hidden: yes
  }
  measure: total_sale_price {
    label: "Total Sales"
    view_label: "_PoP"
    type: sum
    sql: ${sale_price} ;;
    value_format_name: usd
    drill_fields: [created_date]
  }
}

実装 期間指定

まず、抽出期間とそれと比較する期間を決めるテンプレートフィルターとパラメーターを定義して、それらの情報を元に当期(this period)か前期(last period)かを評価するdimensionを定義します。

  # 抽出期間
  filter: current_date_range {
    type: date
    sql: ${period} IS NOT NULL ;;
  }

  # 比較期間
  parameter: compare_to {
    type: unquoted
    default_value: "Period"
    allowed_value: {
      label: "Previous Period"
      value: "Period"
    }
    allowed_value: {
      label: "Previous Week"
      value: "Week"
    }
    allowed_value: {
      label: "Previous Month"
      value: "Month"
    }
    allowed_value: {
      label: "Previous Quater"
      value: "Quater"
    }
    allowed_value: {
      label: "Previous Year"
      value: "Year"
    }
  }

 # 抽出期間と比較期間を元に当期と前期とその他の期間を評価
 dimension: period {
   type: string
   order_by_field: order_for_period
   sql:
      {% if current_date_range._is_filtered %}
        CASE
        WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
        THEN 'This {% parameter compare_to %}'
        WHEN ${created_date} between ${period_2_start} and ${period_2_end}
        THEN 'Last {% parameter compare_to %}'
        END
      {% else %}
        NULL
      {% endif %}
    ;;
  }

ハイライトしている3行目がミソで、current_date_rangeを評価する新たなdimensionを定義して、その期間に入っていないものを除外することで、対象期間と比較期間のみをもってきています。

Explore画面で抽出期間と比較期間の選択画面が表示されます。

ここでperiodディメンションを選択すると以下のようなSQLが生成されます。（見やすいように簡略化しています）

WHERE (
  CASE
    WHEN ((( created_at ) >= ((DATE_TRUNC('month', CURRENT_DATE())))
      AND ( created_at ) < ((DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE()))))))
    THEN 'This Period'
    WHEN ( created_at )
          between (DATEADD(DAY, -(DATEDIFF(DAY, TO_DATE(DATE_TRUNC('month', CURRENT_DATE()))
                          , DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE()))))
                          , DATE_TRUNC('month', CURRENT_DATE())))
          and (DATEADD(DAY, -1, DATE_TRUNC('month', CURRENT_DATE())))
    THEN 'Last Period'
  END
) IS NOT NULL

次に、比較期間を抜き出す箇所（上記LookMLコードの42行目のハイライト）を定義します。

  # 抽出期間の日数を計算（隠しdimension）
  dimension: days_in_period {
    hidden: yes
    type: number
    sql: DATEDIFF(DAY, TO_DATE({% date_start current_date_range %}), TO_DATE({% date_end current_date_range %})) ;;
  }

  # 前期の開始日を取得（隠しdimension）
  dimension: period_2_start {
    hidden: yes
    view_label: "_PoP"
    description: "Calcurates the start of the previous period"
    type: date
    sql:
      {% if compare_to._parameter_value == "Period" %}
      DATEADD(DAY, -${days_in_period}, to_date({% date_start current_date_range %}))
      {% else %}
      DATEADD({% parameter compare_to %}, -1, to_date({% date_start current_date_range %}))
      {% endif %}
    ;;
  }

  # 前期の終了日を取得（隠しdimension）
  dimension: period_2_end {
    hidden: yes
    view_label: "_PoP"
    description: "Calcurates the end of the previous period"
    type: date
    sql:
      {% if compare_to._parameter_value == "Period" %}
      DATEADD(DAY, -1, to_date({% date_start current_date_range %}))
      {% else %}
      DATEADD({% parameter compare_to %}, -1, DATEADD(DAY, -1, to_date({% date_end current_date_range %})))
      {% endif %}
    ;;
  }

比較期間にPrevious PeriodというLookMLデフォルトのtimeframeにはない期間が設定されているため、まずdays_in_periodで抽出期間を計算しておいて、period_2_startとperiod_2_endでユーザー側はデフォルトのtimeframeか否かを意識することなく比較期間の開始日と終了日をもってくることができます。

次に、当期と前期を同じ時間軸で扱えるdimension_groupを準備します。何もせずに注文日（${created_at}）を使用してしまうと前期と当期で別の時間軸で計算されてしまいます。（下図 右側受注Date）

dimensionの値を合わせるための計算を下記LookMLで定義しています。

  # 各期間がスタートから何日目かを計算する
  dimension: day_in_period {
    hidden: yes
    type: number
    sql:
      {% if current_date_range._is_filtered %}
        CASE
        WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
          THEN DATEDIFF(DAY, to_date({% date_start current_date_range %}), ${created_date}) + 1
        WHEN ${created_date} between ${period_2_start} and ${period_2_end}
          THEN DATEDIFF(DAY, ${period_2_start}, ${created_date}) + 1
        END
      {% else %}
        NULL
      {% endif %}
    ;;
  }
  
  # ${day_in_period}で計算した日数から当期の日付を計算する
  dimension_group: date_in_period {
    label: "Current Period"
    type: time
    sql: DATEADD(DAY, ${day_in_period} -1, to_date({% date_start current_date_range %})) ;;
  }
  
  # ${created}をユーザーからは隠しておく
  dimension_group: created {hidden: yes}

ExploreでCurrent Period（date）を選択すると、以下のSQLが生成されます。（見やすいように簡略化しています）

DATEADD(DAY, (CASE
                WHEN ((( created_at ) >= ((DATE_TRUNC('month', CURRENT_DATE())))
                  AND ( created_at ) < ((DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE()))))))
                THEN DATEDIFF(DAY, DATE_TRUNC('month', CURRENT_DATE()), (created_at )) + 1
                WHEN (created_at )
                  between (DATEADD(DAY, -(DATEDIFF(DAY, DATE_TRUNC('month', CURRENT_DATE()), DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())))), DATE_TRUNC('month', CURRENT_DATE())))
                  and (DATEADD(DAY, -1, DATE_TRUNC('month', CURRENT_DATE())))
                THEN DATEDIFF(DAY, (DATEADD(DAY, -(DATEDIFF(DAY, DATE_TRUNC('month', CURRENT_DATE()), DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())))), DATE_TRUNC('month', CURRENT_DATE()))), ( created_at )) + 1
              END) -1, DATE_TRUNC('month', CURRENT_DATE()))

これで1つのdimensionだけで当期と前期を計算することができるようになりました。（下図 Current Period Date）

実装 集約計算

次は集計結果の出し方です。方法は2つあって、ひとつは第一弾と第二弾と同様、dimensionをpivotして当期と前期を分けて表示させる方法（measureは前提条件の${total_sale_price}を使用）で、もう一つはこれまで使用できなかったfiltered measureを使用して、当期の計算をするmeasureと前期を計算するmeasureをそれぞれ定義する方法です。

dimensionをpivotする方法

当期(This period)と前期(Last period)をPivotするためのdimensionと、このdimensionの表示順を"This ~"から"Last ~"にする隠しdhimensionを作成します。

  # current_date_rangeの期間であれば'This ~'、period_2_startとperiod_2_endの間であれば'Last ~'
  dimension: period {
    type: string
    order_by_field: order_for_period
    sql:
        {% if current_date_range._is_filtered %}
          CASE
          WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
          THEN 'This {% parameter compare_to %}'
          WHEN ${created_date} between ${period_2_start} and ${period_2_end}
          THEN 'Last {% parameter compare_to %}'
          END
        {% else %}
          NULL
        {% endif %}
    ;;
  }
 
  #  period dimension内のorder_by_fieldで指定
  dimension: order_for_period {
    hidden: yes
    type: number
    sql:
      {% if current_date_range._is_filtered %}
        CASE
        WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
        THEN 1
        WHEN ${created_date} between ${period_2_start} and ${period_2_end}
        THEN 2
        END
      {% else %}
        NULL
      {% endif %}
    ;;
  }

ExploreのフィールドピッカーにPeriodフィールドが選べるようになるので、Pivotをして、出したい粒度のtimeframeを選択して、計算したいmeasureを選択すれば完了です。

filtered measureを利用する方法

次に、当期か前期かを評価する隠しdimensionを定義して、その値でfilterをかけることで各期間の計算を出します。

  # THEN以下で指定した値を元にフィルターをかける
  dimension: period_filtered_measures {
    hidden: yes
    description: "We just use for the filterd measures"
    type: string
    sql:
        {% if current_date_range._is_filtered %}
          CASE
          WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
          THEN 'this'
          WHEN ${created_date} between ${period_2_start} and ${period_2_end}
          THEN 'last'
          END
        {% else %}
          NULL
        {% endif %}
    ;;
  }

  # Filtered measures
  measure: current_period_sales {
    type: sum
    value_format_name: usd
    sql: ${sale_price} ;;
    filters: [
      period_filtered_measures: "this"
    ]
  }
  measure: previous_period_sales {
    type: sum
    value_format_name: usd
    sql: ${sale_price} ;;
    filters: [
      period_filtered_measures: "last"
    ]
  }
  # 前年比
  measure: sales_pop_change {
    type: number
    value_format_name: percent_2
    sql:
        CASE
        WHEN ${current_period_sales} = 0
        THEN NULL
        ELSE (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales}, 0))
        END
    ;;
  }

filtered measureを利用すると、LookMLで当期と前期のmeasureを参照できるようになるので、前期比のmeasureを予め用意しておくことができます。
また、pivot専用のdimensionを用意する必要もなく、Exploreでの操作はシンプルになります。

テンプレートフィルターで選択した値を評価する隠しdimensionを利用して動的に計算対象を変える方法はPoP分析意外にも応用が効きます。Exploreでフィールドを選び直さずにフィルターだけでdimensionの項目を変えられてしまうので、ダッシュボード上でも動的に項目を変えられることができるようにもなります。

まとめ

その1とその2に比べてLookMLの実装は複雑になりますが、Exploreでの操作はシンプルになります。

また繰り返しになりますが、filtered measureを使用する実装方法は、他のユースケースでも応用が効くやり方なので、考え方だけでも参考になると思います！