この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
以前のブログでLookerの記事と見せかけて、実際はデータ分析の一連の流れをおさらいするという内容のブログを書きました。
今回は、前回急拵えでベタ書きで応用がきかない方法で定義してしまったLookMLを、もう少しユーザーが柔軟に使用できる形に修正する方法をご紹介します。
具体的にはLiquid（リキッド）やフィルターパラメータなどを使用して、LookMLでSQLを動的に操るあれです。
もしもダッシュボードを見たユーザーがらこんな要望がきたら？
前回のブログのダッシュボードを見たユーザーから下記要望がきたとします。
- 前年比で出してるのを全四半期で見たい
- 前月比で売り上げが上がった商品を確認したい
期間比較をもっと柔軟したいという要望ですね。今回はExploreを操作するユーザーだけではなく、ダッシュボードユーザーも期間比較ができるように実装します。
LookMLで期間比較を実装する
Looker上で期間比較をする方法はいくつかありますが、今回はLookMLで実装する方法をご紹介します。
英語ですが、LookMLで期間比較(PoP)を実装する方法を紹介している記事があります： Methods for Period Over Period (PoP) Analysis in Looker
こちらのMethod 3までは以前ブログに書いているので、日本語が言い方はこちらをどうぞ：
- Period over Period 分析をLookMLで実装する その壱
- Period over Period 分析をLookMLで実装する その弐
- Period over Period 分析をLookMLで実装する その参
修正前のLookMLを確認
まずは、前回のブログの際に定義したLookMLを確認します。
前回は、前年と比べて売り上げが伸びている商品をピックアップしたかったので、フィルタードメジャーに販売年をベタ書きしてその差を出していました。
measure: 2020_total_sale {
label: "2020合計総額"
type: sum
sql: ${sale_dollars} ;;
filters: [order_year: "2020"]
}
measure: 2021_total_sale {
label: "2021合計総額"
type: sum
sql: ${sale_dollars} ;;
filters: [order_year: "2021"]
}
measure: diff_2020_2021 {
label: "合計総額(2021-2020)"
type: number
value_format_name: usd
sql: ${2021_total_sale} - ${2020_total_sale} ;;
}これではユーザーから他の期間で比較したいと言われた時にいちいちLookMLを書き足さないといけません。そこで、Lookerに組み込まれているLiquidを利用してユーザーがフィルターに入力した値に応じて期間比較ができるように実装します。
Liquidを利用して実装
まずは、比較期間と対象期間を入力するフィルターだけのフィールドを定義します。（詳しくはこちら）
# viewファイル
parameter: period {
label: "期間"
type: unquoted
default_value: "month"
allowed_value: {label: "月" value: "month"}
allowed_value: {label: "四半期" value: "quatrer"}
allowed_value: {label: "年" value: "year"}
}
filter: extract_period {
label: "対象期間"
type: date
datatype: date
sql:
{% condition extract_period %} ${order_raw} {% endcondition %}
{% if period._is_filtered %}
OR(
(${order_raw} >= DATE_ADD({% date_start extract_period %}, INTERVAL -1 {% parameter period %}))
AND
(${order_raw} < DATE_ADD({% date_end extract_period %}, INTERVAL -1 {% parameter period %}))
)
{% endif %}
;;
}このparameterパラメータとfilterパラメータは、Exploreにフィルターオンリーフィールドと呼ばれるフィルター専用のフィールドを作ります。
通常のフィールド経由でのフィルターと違い、parameterとfilterで定義したフィルターはliquid式でその値を参照することができます。そのため、extract_periodで{% parameter period %}と参照していますが、生成されるSQLにはperiod parameterで選択した期間が入ります。
実際の画面で確認してみましょう。
上の図では、直近の終了した3ヶ月間を集計して、前年の同時期と比較した数字が返ってきます。見づらいですが、画面上部の期間フィルターでは「年」を選択して、対象期間では「in the past 3 months」を選択しています。
画面下部の生成されたSQLでは上のLookMLではLiquidで表されていた箇所にフィルターで入力した項目が入っているのがわかります。
直近の終了した四半期とその前の四半期を比較したい場合には、上の図のように選択します。生成されたSQLが最初の図と変わっているのがわかるでしょうか。
このようにしてLiquidを使うとユーザーがUI上で入力した値に応じてSQLを動的に変化させることができます。
これで期間比較の期間をずらす部分は出来上がりました。次に前期と当期の差を計算するメジャーを定義します。
前期と当期の差を計算するメジャーを定義すると書きましたが、そのための事前準備が必要です。というのは、フィルタードメジャーでフィルターを設定できるのはディメンションの値です。
しかし、現状ユーザーが対象期間フィルターで指定した期間（当期）と対象期間（前期） を区別できるディメンションがありません。
そこで、前期と当期を区別できる隠しディメンションを定義します。
（隠しディメンションという機能があるわけではなく、ユーザーに表示する必要のないフィールドは隠してUIをスッキリさせようというのがLookerのベストプラクティスとしてあります。）
前期と当期を区別するディメンション自体はユーザーには必要がないので、hiddenパラメータを定義します。
dimension: is_this_period {
hidden: yes
type: yesno
sql: {% condition extract_period %} ${order_raw} {% endcondition %} ;;
}Explore画面で実際にis_this_periodを選択してどのような動きをするのかを確認します。
きちんと2021年にはYesが入って、2020年にはNoが表示されています。
裏ではどんなSQLが生成されているかを確認します。
CASE文が生成されて対象期間フィルターで指定した過去3ヶ月の場合にはYes、違う場合にはNoを返すSQLが生成されています。
このディメンションを使用して、今度こそ前期と当期の差を計算するフィルタードメジャーを定義します。
measure: this_total_sale {
label: "合計総額(当期)"
type: sum
sql: ${sale_dollars} ;;
filters: [
is_this_period: "yes"
]
}
measure: last_total_sale {
label: "合計総額(前期)"
type: sum
sql: ${sale_dollars} ;;
filters: [
is_this_period: "no"
]
}
measure: diff_2020_2021 {
label: "合計総額(前期-当期)"
type: number
value_format_name: usd
sql: ${last_total_sale} - ${this_total_sale} ;;
}同じディメンションを軸にしてフィルターをかける場合、そのディメンションをExplore側の操作でピボットしてしまうという方法もあります。ですが、今回の場合最初にほしい数字として前期と当期の売上の差があったので、LookMLで事前定義できる方法を選択しました。
Exploreでも商品名ディメンションと合計総額(前期-当期)メジャーを選択するだけでいいので、UXも向上します。
お手軽ですね。
.......果たしてそうでしょうか？ユーザーからしたらよくわからないフィールドが増えて、裏で何が起こってるのかもわからないし、結局何なんだってなってしまうと思います。
Quick Start analysesを定義する
事前にフィールドやフィルターを選択した状態のExploreを用意しておけるのがQuick Start analysesです。
詳しくは下記ブログをご確認ください。
事前に期間比較ができる状態のExploreをQuick Start analysesで用意しておけば、ユーザーはまさにお手軽に期間比較を求めることができます。
実装
手順は2つです。まず、ExploreでQuick Start analysesとして設定したいフィールドやフィルターを選択して実行します。次にそのLookMLを取得して、queryパラメータをexploreに定義します。
上の図のExploreの設定をQuick Start analysesとして定義します。
まずは、実行ボタン横のギアマークをクリックして、Get LookMLをクリックします。
Get LookMLというポップアップが出てくるので、真ん中のAggregate Tableを選択して、その内容をコピーします。
コピーしたら開発画面に移動して、modelファイルを開きます。該当のexploreにペーストします。
Quick Start analysesで必要なのはqueryパラメータの内容のみなので、不要なパラメータを消してしまいます。そして、queryパラメータに名前がついていないので、適当名前を入力します。
ここで注意点は、Exploreで指定している行制限は反映されていないので、limitパラメータを追加します。（追加しない場合は、行制限らんが空欄になるので、最大5000行が返されます。）
explore: iowa_liquor_sales {
query: top_x_diff_to_last_period {
label: "前期比売上上位"
dimensions: [item_description]
measures: [diff_2020_2021]
filters: [
iowa_liquor_sales.extract_period: "3 months ago for 3 months",
iowa_liquor_sales.period: "year"
]
timezone: "Asia/Tokyo"
limit: 20
}
}変更を保存して、再度Exploreを開きます。
いつものExplore画面ではなく、Quick Startという画面が表示されます。このように事前定義をしておくことで、ユーザーをどのフィールドを使えばいいのか迷わせずにほしい情報へ導くことができます。
ダッシュボードにフィルターを追加する
Exploreが利用できるユーザーであれば、ある程度の期間比較は日付ディメンションをピボットすればできてしまいます。しかし、ダッシュボードユーザーはどうでしょうか？
ダッシュボードユーザーが操作できるのは、ドリルダウンかフィルターのみです。今回実装した方法では、ダッシュボードユーザーも期間を指定することができます。
フィルターの設定方法は、通常のダッシュボードフィルターと同様です。まず、ダッシュボードを編集モードにして、左上のFiltersからAdd Filterを選択します。
フィルターに追加したいフィールドを探して、選択すると、下の図のような設定画面が表示されるので、デフォルトの値を設定して、Addを押せば追加完了です。
フィルターの値を変えて、Updateを押せばタイルの内容が変化します。
終わり
Liquidはどのようなユースケースにおいても必要不可欠なものではないと思います。ですが、Liquidを利用することでユーザーはより柔軟にデータを探索することができるようになります。社内のLooker利用が進み、ユーザーも新しいものへの抵抗が和らいでいる場合には導入を検討してみてもいいかもしれません。
3
