Lookerの目標値やストップワードを、Googleスプレッドシート連携でお手軽管理【Sexy Tech for You #9】

データサイエンティストの阿部です。本記事は Looker Advent Calendar 2021の12日目に捧げます。弊社は今年からLookerを契約し、自分にとって2021年はLooker元年でした。お世話になりました。

表題について、明日から使いたくなるデータハンドリング術を紹介するYoutubeチャンネル "Sexy Tech for You" の第9話として配信しました。動画中の結果を再現するLookMLや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube

LookML

GoogleスプレッドシートをBigQueryに連携したテーブルをproject.dataset.spreadsheet_tableと想定します。それぞれのスプレッドシートの内容は、動画の1:07と2:45をご覧ください。そのテーブルを、今回はderived_tableパラメータのsqlでjoinしています。

スプレッドシートのテーブルをviewとしてmodelファイルでjoinしても大丈夫ですが、ここではコード例を1つのviewファイルとしてまとめて示すためにderived_tableパラメータでjoinしています。

また、bigquery-public-data.wikipedia.pageviews_2021はBigQueryの一般公開データセットで、誰でも利用可能です。

Googleスプレッドシートに入力した目標値を反映するviewファイル例

Wikipediaの「アドベントカレンダー」ページのpv数

view: blog_example1 {

  derived_table: {
    sql:
      SELECT
          A.*,
          target_pv
      FROM(
      SELECT
          *,
          DATE_TRUNC(DATE(datehour, 'Asia/Tokyo'),ISOWEEK) AS week_start_dt
      FROM
          `bigquery-public-data.wikipedia.pageviews_2021`
      WHERE
          datehour >= "2021-10-03"
          AND wiki IN ("ja","ja.m")
          AND title = "アドベントカレンダー"
      ) A INNER JOIN
      `project.dataset.spreadsheet_table` B ON A.week_start_dt = B.week
      ;;
  }

  ################################################
  ## primary key
  ################################################

  dimension: primary_key {
    primary_key: yes
    type: string
    sql: CONCAT(${title}, ${wiki}, ${pv_time_raw});;
  }

  ################################################
  ## time dimension
  ################################################

  dimension_group: pv_time {
    label: "pv発生時間"
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: yes
    datatype: timestamp
    sql: ${TABLE}.datehour ;;
  }

  ################################################
  ## dimension
  ################################################

  dimension: title {
    label: "ページタイトル"
    type: string
    sql: ${TABLE}.title ;;
  }

  dimension: wiki {
    label: "デバイス"
    type: string
    case: {
      when: {
        sql: ${TABLE}.wiki = "ja.m" ;;
        label: "モバイル"
      }
      else: "デスクトップ"
    }
    sql: ${TABLE}.wiki ;;
  }

  ################################################
  ## measure
  ################################################

  measure: pv {
    label: "実績pv数"
    type: sum
    sql: ${TABLE}.views ;;
  }

  measure: target_pv {
    label: "週間_目標pv数"
    type: sum_distinct
    sql: ${TABLE}.target_pv ;;
    sql_distinct_key: ${pv_time_week} ;;
  }

}

Googleスプレッドシートに入力したストップワードを反映させる viewファイル例

Wikipediaの12月3日のpv数のワードクラウド

view: blog_example2 {

  derived_table: {
    sql:
      SELECT
        A.*,
        stopword
      FROM(
      SELECT
          title,
          SUM(views) AS views
      FROM
          `bigquery-public-data.wikipedia.pageviews_2021`
      WHERE
          DATE(datehour) = "2021-12-03"
          AND wiki IN ("ja","ja.m")
      GROUP BY
          1
      ) A LEFT JOIN
      `project.dataset.spreadsheet_table`  B ON A.title = B.stopword
      ;;
  }

  ################################################
  ## primary key
  ################################################

  dimension: title {
    primary_key: yes
    label: "ページタイトル"
    type: string
    sql: ${TABLE}.title ;;
  }

  ################################################
  ## dimension
  ################################################

  dimension: stopword {
    label: "ストップワード"
    type: string
    sql: ${TABLE}.stopword ;;
  }

  ################################################
  ## measure
  ################################################

  measure: pv {
    label: "実績pv数"
    type: sum
    sql: ${TABLE}.views ;;
    filters: [stopword: "NULL"]
  }

}

補足

Googleスプレッドシートを連携したBigQueryテーブルを読み込むためには、connectionで用いているLookerのサービスアカウントに、スプレッドシートの閲覧権限を付与する必要があります。

目標値の事例では、sql_distinct_key: ${pv_time_week} ;;が重要です。元データのレコードはhoury単位なのに対して、目標値はweekly単位のためです。単純にjoinして合算してしまうと、想定外の値になってしまいます。またexploreユーザーが異なる期間粒度で集計するのを防止するためにも、measureのlabelには「週間_目標pv数」のように期間を明記するのが良さそうです。運用上の注意は必要ですが、目標値をスプレッドシートで入力できると、現場運用が捗りやすいのでオススメです。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。