🍒

【解説】Lookerで期間比較(PoP)をBigQueryで実装する

7 min read

Lookerの期間比較(Period over Period)をこちらの記事をもとに実装してみました。

この記事では8つの方法が記載してありますが、ここでは3つ目の「Current Period and Previous Period」での実装方法をわかりやすく解説していきたいと思います。

記事内に記載されているものをBigQueryで使えるように書き換えていますので参考になれば幸いです。

利用するテーブル

created_atというTIMESTAMP型のカラムのみがある、eventsというテーブルやっていきます

ベースとなるView

  dimension_group: created_at {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month_num,
      month,
      quarter,
      year,
      day_of_year,
      day_of_week
    ]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count
  }

期間比較の実装

今回はLookerの記事で紹介されている3つ目のテンプレートフィルタを利用して実装していきますが、記事内で記載されている必要最低限のdimensionとmeasureのみを実装しています。

1.比較用の期間フィルタの設定

まずは、期間比較の際に利用する、created_atのフィルタを設定します。
sql: ${period_filtered_measures} IS NOT NULL ;; については後ほど解説します。

  filter: current_date_range {
    type: date
    description: "期間比較用のcreated_atのフィルタ"
    sql: ${period_filtered_measures} IS NOT NULL ;;
  }

2.フィルタで設定した期間と比較用期間の開始・終了日を算出

  • 手順
    • current_date_rangeで設定した期間の開始、終了を取得します(current_range_start_date, current_range_end_date)
    • 開始日、終了日から期間内の日数を計算します(days_in_period)
    • 上記2つから比較用の期間の開始日、終了日を算出します(previous_range_start_date, previous_range_end_date)
  • ポイント
    • Lookerでは「次の日以前」という指定の仕方もできるため、エラー回避の目的で{% date_start current_date_range %}がnullの場合に2001-01-01の日付を入れています
    • また、「次の日以降」という指定もできるため、{% date_end current_date_range %}がnullの場合は現在時刻を入れています
  dimension_group: current_range_start_date {
    #hidden: yes
    type: time
    timeframes: [raw,date]
    group_label: "POP hidden"
    description: "current_date_rangeのStart"
    sql:
      CASE
        WHEN {% date_start current_date_range %} IS NULL THEN TIMESTAMP('2001-01-01')
        ELSE {% date_start current_date_range %}
      END;;
  }

  dimension_group: current_range_end_date {
    #hidden: yes
    type: time
    timeframes: [raw,date]
    group_label: "POP hidden"
    description: "current_date_rangeのEnd"
    sql:
      CASE
        WHEN {% date_end current_date_range %} IS NULL THEN CURRENT_TIMESTAMP()
        ELSE {% date_end current_date_range %}
      END;;
  }

  dimension: days_in_period {
    #hidden: yes
    type: number
    group_label: "POP hidden"
    description: "フィルタした期間の日数"
    sql: TIMESTAMP_DIFF(${current_range_end_date_raw}, ${current_range_start_date_raw}, DAY);;
  }

  dimension_group: previous_range_start_date {
    #hidden: yes
    type: time
    timeframes: [raw,date]
    group_label: "POP hidden"
    description: "days_in_periodで計算した比較用の前期間のStart"
    sql: TIMESTAMP_SUB(${current_range_start_date_raw}, INTERVAL ${days_in_period} DAY);;
  }

  dimension_group: previous_range_end_date {
    #hidden: yes
    type: time
    timeframes: [raw,date]
    group_label: "POP hidden"
    description: "days_in_periodで計算した比較用の前期間のEnd"
    sql: TIMESTAMP_SUB(${current_range_end_date_raw}, INTERVAL ${days_in_period} DAY);;
  }

これらは、実際にはhidden: yesとして利用するものですが、確認のため実際の値を見てみます。
current_date_rangeで5/20-5/30を指定しています。
current_rangeのstartとendに5/20,5/30の日付が、そして、previous_rangeのstartとendに5/10,5/20の日付が入っていることがわかります。

3.指定期間と比較期間を同一の行で比較するためのdimensionを作成する

current_date_rangeとprevious_rangeを比較するためのdimensionを作成していきます。
具体的には、current_date_range内の日付が指定した期間の開始日から数えて何番目か、previous_range内の日付が、比較期間の開始日から数えて何番目かを計算していきます。
さきほどの5/20-5/30の例でいうと、

  • 指定期間: 5/20→1, 5/21→2,.....5/30→10
  • 比較期間: 5/10→1, 5/11→2,.....5/20→10

というふうに番号を振っていきます。

  dimension: day_index_in_period {
    #hidden: yes
    type: number
    group_label: "POP hidden"
    description: "フィルタの期間及び、比較用の前期間が何番目なのかを算出。対象日と比較する日を揃えるために利用"
    sql:
      {% if current_date_range._is_filtered %}
        CASE
          WHEN {% condition current_date_range %} ${created_at_raw} {% endcondition %}
            THEN DATE_DIFF(${created_at_date}, ${current_range_start_date_date}, DAY) + 1
          WHEN ${created_at_raw} between ${previous_range_start_date_raw} and ${previous_range_end_date_raw}
            THEN DATE_DIFF(${created_at_date}, ${previous_range_start_date_date}, DAY) + 1
          END
      {% else %}
        NULL
      {% endif %}
      ;;
  }

Explore上で見ると以下のようになります。この番号をもとに比較することができるようになります。

4.比較用の日付dimensionを作成する

3で作成した番号をもとに、実際の分析の際に利用する日付dimensionを作成します。
具体的には、current_range_startに3で算出した番号を加算していくことで、分析用の日付dimensionを作成しています。
また、フィルタ(current_date_range)が指定されていない場合は、created_atをそのまま利用することで、フィルタなしでもcreated_atとして利用することができます。

  dimension_group: created_date {
    type: time
    label: "期間比較用のcreated"
    description: "day_index_in_periodをもとに期間比較用のcreatedを作成"
    sql:
      {% if current_date_range._is_filtered %}
        TIMESTAMP_ADD(${current_range_start_date_raw}, INTERVAL ${day_index_in_period} - 1 DAY)
      {% else %}
        ${created_at_raw} -- current_date_rangeのフィルタがないときに、created_at_rawを指定することで、フィルタなしでも当該ディメンションが利用可能になる
      {% endif %}
      ;;
    timeframes: [
      date,
      hour_of_day,
      day_of_week,
      day_of_week_index,
      day_of_month,
      day_of_year,
      week_of_year,
      month,
      month_name,
      month_num,
      year]
  }

5.measureをフィルタするためのdimensionを作成する

実際に分析で利用する指標を、指定した期間と比較用の期間でフィルタするためのdimensionを作成していきます。
created_atが指定した期間内あればThisを、比較用の期間内であればLastを返しています。

  dimension: period_filtered_measures {
    type: string
    group_label: "POP hidden"
    description: "measureのフィルタ用ディメンジョン。指定した期間内であればThisを比較用の期間であればLastを返す"
    sql:
      {% if current_date_range._is_filtered %}
        CASE
          WHEN {% condition current_date_range %} ${created_at_raw} {% endcondition %}
            THEN 'This'
          WHEN ${created_at_raw} between ${previous_range_start_date_raw} and ${previous_range_end_date_raw}
            THEN 'Last'
          END
      {% else %}
          'This'
      {% endif %}
      ;;
  }

手順3の確認の際に利用したExploreに、作成したperiod_filtered_measuresを追加すると以下のようになります。

ここで作成したdimensionをcurrent_date_range内に、sql: ${period_filtered_measures} IS NOT NULL ;;と入れることで指定した期間及び比較対象の期間外のデータは取得しないように絞り込むことができます。

6.measureの追加

最後に期間比較の分析で利用するmeasureを追加します。
今回は単純にレコード数(Count)を指標にしています。選択した期間では、period_filtered_measuresThisとなるような、比較期間では、Lastとなるようなフィルタを設定します。

  measure: count_selected_period {
    type: count
    filters: [period_filtered_measures: "This"]
  }
  measure: count_previous_period {
    type: count
    filters: [period_filtered_measures: "Last"]
  }

実装結果

実際に作成したLookMLで見てみたいと思います。

これだけだと値が正しいかどうかわからないので、5/10-5/30までの数値も見てみたいと思います。

ちょっとわかりにくいですが、5/29の値と5/19の値が横に並んで比較することができています。

Discussion

ログインするとコメントできます