この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
この記事では、こちらの記事で紹介されている、前年同日比もしくは前月同日比分析(Period over Period 分析)の8つのユースケースのSQLを、SnowflakeのSQLに対応したものに書き換えて試してみる第三弾です。
第三回も、引き続き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を使用する実装方法は、他のユースケースでも応用が効くやり方なので、考え方だけでも参考になると思います!