【解説】Lookerで期間比較（PoP）をBigQueryで実装する
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の記事で紹介されている３つ目のテンプレートフィルタを利用して実装していきますが、記事内で記載されている必要最低限の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）
- 上記２つから比較用の期間の開始日、終了日を算出します（previous_range_start_date, previous_range_end_date）
- ポイント
- Lookerでは「次の日以前」という指定の仕方もできるため、エラー回避の目的で
{% date_start current_date_range %}がnullの場合に2001-01-01の日付を入れています - また、「次の日以降」という指定もできるため、
{% date_end current_date_range %}がnullの場合は現在時刻を入れています
- Lookerでは「次の日以前」という指定の仕方もできるため、エラー回避の目的で
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_measuresがThisとなるような、比較期間では、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