Lookerで前年実績を算出する方法/PoP分析（Period Over Period）試してみた

1．はじめに

こんにちは、杉山です。
先月Webinarで配信された『AI for BI Innovation Roadmap Webinar』で今後のロードマップ（※）に新規measureとしてperiod_over_periodが発表されました！これまで前年比や前年差といった過去の値をLookerで算出するとなると、PoP分析をはじめSQLでもくもくと派生テーブルを書いたりする必要がありましたが…だいぶ作業が楽になりそうですね◎
今回はそんな嬉しい発表を待ちつつも、いますぐLookerで過去の値を算出したい！とお困りの方向けにサンプルコードを交えて実装方法をご紹介いたします！

※ロードマップの情報は、タイムラインやGAをコミットしたものではございません。

2．前年実績を算出する

過去の値を算出するということで、第2章では試しに前年実績（前年同日実績）を算出する方法を考えてみたいと思います。

1. DWHで前年実績の値を持つ
2. PoP分析を使用して前年実績の値を算出する
3. 派生テーブルとSQLの組み合わせ

メンテナンス性やパフォーマンスを考慮すると圧倒的に1.DWHで前年実績の値を持つ方法を推奨します。
ただ、2と3の方法でLookerを用いた前年実績の算出は可能なので今回は2.PoP分析を使用して前年実績の値を算出する方法を深堀りしていきたいと思います！

3．PoP分析を試してみる

今回参考にしたブログはGoogle Cloud CommunityのMethods for Period Over Period (PoP) Analysis in Looker – Method 3: Current Period and Previous Periodです。

– 手順

最終的に使用するExploreでは他にも結合しているviewはありますが、PoP分析について定義しているviewは以下の通りです。今回は接続先がBigQueryのため、その他のDWHに接続する場合下記SQLの変更が必要になる場合があります。

view: pop {
  sql_table_name: `sample.looker.sales` ;;


  dimension: primary_key {
    hidden: yes
    primary_key: yes
    sql: CONCAT(${TABLE}.salesdate,${TABLE}.storeid,${TABLE}.customerid,${TABLE}.productid) ;;
  }

  dimension_group: sale_dt {
    label: "売上日"
    type: time
    sql: ${TABLE}.salesdate ;;
  }

  dimension: storeid {
    type: string
    label: "店舗ID"
    sql: ${TABLE}.storeid ;;
  }

  dimension: customerid {
    type: string
    label: "顧客ID"
    sql: ${TABLE}.customerid ;;
  }

  dimension: productid {
    type: string
    label: "商品ID"
    sql: ${TABLE}.productid ;;
  }

  dimension: amount {
    type: number
    label: "数量"
    sql: ${TABLE}.amount ;;
  }

 ## ------------------ ここまでは通常のview定義 以下POP分析用の定義 ------------------ ##

  filter: current_date_range {
    type: date
    view_label: "_PoP"
    label: "現在(基準となる日付期間)"
    sql: ${period} IS NOT NULL ;;
  }

  parameter: compare_to {
    view_label: "_PoP"
    label: "比較期間"
    description: "「現在(基準となる日付期間)」フィルタとセットで利用する"
    type: unquoted
    allowed_value: {
      label: "過去の期間"
      value: "Period"
    }
    allowed_value: {
      label: "過去の週"
      value: "Week"
    }
    allowed_value: {
      label: "過去の月"
      value: "Month"
    }
    allowed_value: {
      label: "過去の四半期"
      value: "Quarter"
    }
    allowed_value: {
      label: "過去の年"
      value: "Year"
    }
    default_value: "Period"
  }



  ## ------------------ 非表示項目のDIMENSIONS　主に日数計算用  ------------------ ##

  dimension: days_in_period {
    hidden:  yes
    view_label: "_PoP"
    description: "基準期間の日数を計算"
    type: number
    sql: DATE_DIFF(DATE({% date_end current_date_range %}), DATE({% date_start current_date_range %}),DAY) ;;
  }

  dimension: period_2_start {
    hidden:  yes
    view_label: "_PoP"
    description: "前の期間の開始日を計算"
    type: date
    sql:
            {% if compare_to._parameter_value == "Period" %}
            DATE_SUB (CAST({% date_start current_date_range %} AS DATE), INTERVAL ${days_in_period} DAY)
            {% else %}
            DATE_SUB (CAST({% date_start current_date_range %} AS DATE), INTERVAL 1 {% parameter compare_to %})
            {% endif %};;
  }

  dimension: period_2_end {
    hidden:  yes
    view_label: "_PoP"
    description: "前の期間の終わりを計算"
    type: date
    sql:
            {% if compare_to._parameter_value == "Period" %}
            DATE_SUB (CAST({% date_start current_date_range %} AS DATE), INTERVAL 1 DAY)
            {% else %}
            DATE_SUB (DATE_SUB(CAST({% date_end current_date_range %} AS DATE), INTERVAL 1 DAY) , INTERVAL 1 {% parameter compare_to %})
            {% endif %};;
  }

  dimension: day_in_period {
    hidden: yes
    view_label: "_PoP"
    description: "各期間の開始からの日数、これを使用してイベントの日付を同じ軸に揃える。軸は 1、2、3 などと表示"
    type: number
    sql:
        {% if current_date_range._is_filtered %}
            CASE
            WHEN {% condition current_date_range %} CAST(${sale_dt_raw} AS TIMESTAMP){% endcondition %}
            THEN DATE_DIFF(${sale_dt_date}, CAST({% date_start current_date_range %} AS DATE), DAY) + 1
            WHEN ${sale_dt_date} BETWEEN ${period_2_start} AND ${period_2_end}
            THEN DATE_DIFF(${sale_dt_date}, ${period_2_start}, DAY) + 1
            END
        {% else %} NULL
        {% endif %}
        ;;
  }

  dimension: order_for_period {
    hidden: yes
    view_label: "_PoP"
    type: number
    sql:
            {% if current_date_range._is_filtered %}
                CASE
                WHEN {% condition current_date_range %} CAST(${sale_dt_raw} AS TIMESTAMP) {% endcondition %}
                THEN 1
                WHEN ${sale_dt_date} BETWEEN ${period_2_start} AND ${period_2_end}
                THEN 2
                END
            {% else %}
                NULL
            {% endif %}
            ;;
  }

  ## ------------------ DIMENSIONS ------------------ ##

  dimension_group: date_in_period {
    description: "期間を比較する場合、これをグループ化ディメンションとして使用。前の期間を現在の期間に合わせて配置"
    label: "当期"
    type: time
    sql: DATE_ADD(CAST({% date_start current_date_range %} AS DATE), INTERVAL ${day_in_period} - 1 DAY) ;;
    view_label: "_PoP"
    timeframes: [
      date,
      hour_of_day,
      week,
      day_of_week,
      day_of_week_index,
      day_of_month,
      day_of_year,
      week_of_year,
      month,
      month_name,
      month_num,
      year]
  }


  dimension: period {
    view_label: "_PoP"
    label: "期間評価用"
    description: "基準の期間と比較期間を元に当期・前期・その他の期間のいずれに該当するかを評価する"
    type: string
    order_by_field: order_for_period
    sql:
            {% if current_date_range._is_filtered %}
                CASE
                WHEN  {% condition current_date_range %} CAST(${sale_dt_raw} AS TIMESTAMP){% endcondition %}
                THEN 'This {% parameter compare_to %}'
                WHEN ${sale_dt_date} BETWEEN ${period_2_start} AND ${period_2_end}
                THEN 'Last {% parameter compare_to %}'
                END
            {% else %}
                NULL
            {% endif %}
            ;;
  }


  ## ---------------------- MEASURES ---------------------------- ##

  dimension: period_filtered_measures {
    hidden: yes
    description: "フィルター用"
    type: string
    sql:
            {% if current_date_range._is_filtered %}
                CASE
                WHEN {% condition current_date_range %} CAST(${sale_dt_raw} AS TIMESTAMP) {% endcondition %}
                THEN 'This'
                WHEN ${sale_dt_date} BETWEEN ${period_2_start} AND ${period_2_end}
                THEN 'Last'
                END
            {% else %}
                NULL
            {% endif %} ;;
  }

  measure: current_period_sales {
    view_label: "_PoP"
    label: "当期発生売上実績"
    type: sum
    sql: ${amount};;
    filters: [period_filtered_measures: "This"]
  }

  measure: previous_period_sales {
    view_label: "_PoP"
    label: "前期発生売上実績"
    type: sum
    sql: ${amount};;
    filters: [period_filtered_measures: "Last"]
  }

  measure: sales_pop_change {
    view_label: "_PoP"
    label: "現在/過去の比率"
    type: number
    sql: CASE WHEN ${current_period_sales} = 0
                THEN NULL
                ELSE (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales} ,0)) - 1 END ;;
    value_format_name: percent_2
  }

}

– 結果

上記定義したviewをExploreで表示してみた例が以下の通りです。

念のため通常のviewを利用して当期発生売上実績と前期発生売上実績を確認してみましたが、どちらの値も問題ないですね！◎

参考ブログでは比較期間のフィルタに「過去の年」「過去の四半期」「過去の月」「過去の週」「過去の期間」が用意されています。デフォルトで「過去の日」の選択肢はありませんが、前日比較を行いたい場合は比較期間のフィルタに「過去の日(Day)」を追加することで選択可能です。挙動としては「過去の週(Week)」 , 「過去の月(Month)」などと同じなので「過去の期間(Period)」のように改めてDimensionを作成する必要はありません。

「過去の日」を選択してみたExploreが以下の通りです。
2020/10/01のデータのみ格納されているため想定通りの結果が表示されています！

元のデータが良くないですが…通常のviewを利用した結果と同じになりました◎

4．さいごに

今回はLookerで前年実績を算出する方法としてPoP分析をご紹介しましたがいかがでしたでしょうか？
ややこしいSQLが並ぶためとっつきにくかったり、実際難易度は少し高めかもしれませんが、サンプルコードが用意されているので是非必要な場面でお試しいただければと思います！