[Looker] Period over Period 分析をLookMLで実装する その壱 #looker

この記事では、こちらの記事で紹介されている、前年同日比もしくは前月同日比分析といったPoP分析（Period over Period 分析）の8つの実装ユースケースのSQLを、SnowflakeのSQLに対応したものに書き換えて実装してみます。

第一回は、dimension groupのtimeframeを利用したシンプルな実装です。

Liquidを使わずにとてもシンプルに実装できる一方、ダッシュボードフィルターやフィルターメジャーが利用できないのが難点です。

LookML

使用データ

ECサイトデータを利用して、注文日の売上の各種PoP(前週同日比(WTD)、前月同日比(MTD)、前年同日比(YTD))を出します。

  dimension_group: created {
    label: "受注"
    type: time
    # timeframeを指定しないと全てのtimeframeが表示されます。
    sql: ${TABLE}.created_at ;;
  }
  
  measure: total_sale_price {
    label: "総売上"
    type: sum
    value_format_name: usd
    sql: ${sale_price} ;;
  }

dateやmonthだけではなく、timeframeのオプションにはdate_of_week(曜日)やdate_of_month(1~31)、date_of_year(1~335/336)という項目があるので、これらを利用します。

比較する日を抜き出す

考え方としては、WTD(前週同日比)、MTD(前月同日比)、YTD(前年同日比)を出すために、それぞれdimensionを定義して、sqlパラメーターでCURRENT_TIMESTAMPと上記のcreated_raw(受注日時生データ)を比較して評価するします。

  dimension: wtd_only {
    group_label: "To-Date Filters"
    label: "WTD"
    view_label: "_PoP"
    type: yesno
    sql: (EXTRACT(DOW FROM ${created_raw}) < EXTRACT(DOW FROM CURRENT_TIMESTAMP)
            OR
         (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM CURRENT_TIMESTAMP)
              AND
          EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP))
            OR
         (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM CURRENT_TIMESTAMP)
              AND
          EXTRACT(HOUR FROM ${created_raw}) = EXTRACT(HOUR FROM CURRENT_TIMESTAMP)
              AND
          EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP))
        )
    ;;
  }

  dimension: mtd_only {
    group_label: "To-Date Filters"
    label: "MTD"
    view_label: "_PoP"
    type: yesno
    sql: (EXTRACT(DAY FROM ${created_raw}) < EXTRACT(DAY FROM CURRENT_TIMESTAMP)
            OR
          (EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP))
            OR
          (EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) = EXTRACT(HOUR FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP))
        )
    ;;
  }

  dimension: ytd_only {
    group_label: "To-Date Filters"
    label: "YTD"
    view_label: "_PoP"
    type: yesno
    sql: (EXTRACT(DOY FROM ${created_raw}) < EXTRACT(DOY FROM CURRENT_TIMESTAMP)
            OR
          (EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM CURRENT_TIMESTAMP))
            OR
          (EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(HOUR FROM ${created_raw}) = EXTRACT(HOUR FROM CURRENT_TIMESTAMP)
              AND
           EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM CURRENT_TIMESTAMP))
        )
    ;;
  }

Exploreで可視化する

1. PoPの種類を選択する

   先ほどLookMLで定義したdimensionが下の画像のように出てきますので、比較したい粒度のdimensionをフィルターをかけます。
   フィルターをかけるとyesが選択されて出てきます。

   

2. x軸になるtimeframeを選択する(Pivotしないdimension)

   下のPivotする方と紛らわしいですが、軸になるのは[Day of xxx]です。なので1で選択した粒度に応じて、[Day of Week]か[Day of Month]、[Day of Year]を選択します。

3. Pivotするtimeframeを選択する

   Pivotするtimeframeは[Week]、[Month]、[Year]です。

dimension_groupでtimeframeを指定しないとこんな感じで全部出てきます・・・。

上記の手順により、売上データを各PoPで可視化しました。

WTD(前週同日比)

MTD(前月同日比)

YTD(前年同日比)

まとめ

一番シンプルな実装だったので、開発側の手間はあまりかかりませんでしたが、Exploreで可視化する時には手順を知っていないとスムーズにはできなさそうだと感じました。
お手軽にPoPレポートを作成するだけならばこれでも十分だと思います。

次回はparameterを利用してExploreのフィルターで期間を指定する方法です！