Go to Qiita Advent Calendar 2024 Top
LoginSignup
9
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

LookerAdvent Calendar 2019

Day 23
@tsucasain株式会社ZOZO

LookerにおけるGAデータ管理

Last updated at Posted at 2019-12-23

Looker Advent Calendar 2019の23日目の記事になります。

ZOZOテクノロジーズでLookerに触れているデータエンジニア 遠藤です。

投稿2回目は
ZOZOテクノロジーズ #2 Advent Calendar 2019に加えて
Looker Advent Calendar 2019にもJOINしたので
Lookerユーザー向けに少し込み入ったことを書きたいと思います。

はじめに

ZOZOテクノロジーズではLookerを"データガバナンスツール"として使用しています。
（弊社におけるLooker利用法の詳細は**こちらからご覧ください **）

ZOZOでは、サービスに関するあらゆるデータをBigQueryに集約しています。

なかでも、Google Analytics（GA）のデータは、以下の理由で効率的に扱うには難しくなっています。

  • GAデータが複数のデータセットに散在しているケースがある
  • GAデータ総量の規模感が大規模すぎるため（弊社だとテラバイト級）高コストのクエリを投げるトラブルを減らす取り組みが必要

そこで、Looker上でGAデータをどう効率的に管理していくか？について報告します。

Looker Blocks

LookerはLooker Blocksと呼ばれるLooker上で利用できるテンプレートが多数用意されています。
（Looker Blocksの詳しい説明は、@rinn650さんのLooker Advent Calendar 2019 19日目の記事に書かれています）

Looker Blocksでは
GAデータ ga_sessionsに対するLooker Blocks
が既に用意されています。

このLooker Blocks中のga_customize.view内（下記コード参照）のsql_table_nameにGAテーブル名を記せば、
ga_sessionsの各カラムが自動的にdimension設定されて、すぐにexploreが可能になります。

ga_customize.view
view: ga_sessions {
  extends: [ga_sessions_base]
  # The SQL_TABLE_NAME must be replaced here for date partitioned queries to work properly. There are several
  # variations of sql_table_name patterns depending on the number of Properties (i.e. websites) being used.

  # SCENARIO 1: Only one property
  sql_table_name: `bigquery-public-data.google_analytics_sample.ga_sessions_*` ;;

「GAデータ独特のSTRUCT・ARRAY各構造を擁したカラムのdimension設定、どうLookerで書くの？？？」
という問いにLooker導入時の最初から取り掛かるとかなり面食らうので、
このLooker Blocksを利用するのが吉です。

複数データセットのGAデータを1つのviewで管理する

GAデータが複数のBigQueryデータセットに分かれて格納されていた場合、view: ga_sessionsのコピーを量産することは
「LookMLの利点：”再利用性”」を考慮すると好ましくありません。
（Looker Blocksではextendsを用いてdimensionの再利用は既にやってくれています）

再利用性を高めるために、parameterを用いてview: ga_sessionsのみで完結するようにします。

ga_customize.view(version2)
view: ga_sessions {
  extends: [ga_sessions_base]

  sql_table_name: `bigquery-public-data.{% parameter service_name %}.ga_sessions_*` ;;

  parameter: service_name {
    label: "サービス名"
    type: unquoted
    allowed_value: {
      label: "Service 1"
      value: "google_analytics_sample_1"
    }
    allowed_value: {
      label: "Service 2"
      value: "google_analytics_sample_2"
    }
    allowed_value: {
      label: "Service 3"
      value: "google_analytics_sample_3"
    }
  }

上記のように設定すると、explore画面上部のFILTERS内で「サービス名」を選択するだけで任意のサービスのGAデータが利用できます。
advent_calendar_20191223_1.png

日付範囲指定を入れる意識をしたい

GAデータを格納するテーブルga_sessionsにおいて高コストのクエリを実行しないようにするには、
_TABLE_SUFFIXの日付範囲を設定することが望ましいとされています。

今回のGAデータ ga_sessionsに対するLooker Blocksでいうと、
explore画面上で「dimensionsから"partition_date"をマウスオーバー → 右側のFILTERボタンをクリック」で実現できるのですが、
下図のようにexolore画面UI中に設定リンクが埋もれてしまい、日付範囲指定をうっかり忘れかねない状態になります。。。

advent_calendar_20191223_2.png

一方、Lookerにはalways_filterやalways_sql_whereで強制的に範囲指定できる機能は備わっていますが、

  • explore画面上部のFILTERS欄にフィルタをかけている旨が表示されない
  • 日付範囲をデフォルトから変更するにはLookMLをわざわざ直さなければいけない

ため、ここでは適していません。

  • 日付範囲未設定時は直近2日間ぐらいのデータに常にしぼっているように
  • explore画面UI上で日付範囲設定をするべきことを常にFILTERSに表示
  • always_filter・always_sql_whereのような厳しすぎるフィルタは避けたい
  • 日付範囲設定を明示的に行うときはexplore画面UI上ですぐに編集できるように

以上4点の要件を満たすには、、、
conditionally_filter が有効です。

ga_customize.view(version3)
explore: ga_sessions_block {
  extends: [ga_sessions_base]
  extension: required

  conditionally_filter: {
    filters: {
      field: ga_sessions.partition_date
      value: "2 days"
    }
    unless: [ga_sessions.partition_date]
  }
}

上記のように書くと、下図のように日付範囲未設定時でもデフォルトで直近2日間の日付範囲設定がされている旨がexplore画面に表示されます。

advent_calendar_20191223_3.png

複数データセットを同時に使用したいケースにも対応する

複数データセットに分かれて格納されている各GAデータを1つのviewで管理することは先述しましたが、
複数データセットのGAデータを同時に使用するケースでも*ga_customize.view(version2)*を改良することで実現できます。

ga_customize.view(version4)
view: ga_sessions_service_all {
  derived_table: {
    sql: SELECT *, _TABLE_SUFFIX
         FROM `bigquery-public-data.google_analytics_sample_1.ga_sessions_*`
         UNION ALL
         SELECT *, _TABLE_SUFFIX
         FROM `bigquery-public-data.google_analytics_sample_2.ga_sessions_*`
         UNION ALL
         SELECT *, _TABLE_SUFFIX
         FROM `bigquery-public-data.google_analytics_sample_3.ga_sessions_*` ;;
  }
}

view: ga_sessions {
  extends: [ga_sessions_base]

  sql_table_name: {% if service_name._parameter_value == 'service_all' %}
                    ${ga_sessions_service_all.SQL_TABLE_NAME}
                  {% else %}
                    `bigquery-public-data.{% parameter service_name %}.ga_sessions_*`
                  {% endif %} ;;

  parameter: service_name {
    label: "サービス名"
    type: unquoted
    allowed_value: {
      label: "Service ALL"
      value: "service_all"
    }
    allowed_value: {
      label: "Service 1"
      value: "google_analytics_sample_1"
    }
    allowed_value: {
      label: "Service 2"
      value: "google_analytics_sample_2"
    }
    allowed_value: {
      label: "Service 3"
      value: "google_analytics_sample_3"
    }
  }

*ga_customize.view(version4)*は具体的には以下の3点を改良しています。

  1. view: ga_sessions_service_allを作成（derived_tableにUNION ALL句のsqlのみを記す。そのときのSELECT句に_TABLE_SUFFIXも追加）
  1. view: ga_sessions内のparameter: service_nameにlabel: "Service ALL"を追加
  2. view: ga_sessions内のsql_table_nameにlabel: "Service ALL"を選択した場合の設定${ga_sessions_service_all.SQL_TABLE_NAME}を追加（その際にパラメータ記法を用いてif文で分岐させている）

上記のように設定すると、explore画面では"Service ALL"という項目が追加されます。
advent_calendar_20191223_4.png

なお、サービス名を"Service ALL"と指定した時のクエリ例が以下になります。

WITH ga_sessions_service_all AS (SELECT *, _TABLE_SUFFIX
         FROM `bigquery-public-data.google_analytics_sample_1.ga_sessions_*`
         UNION ALL
         SELECT *, _TABLE_SUFFIX
         FROM `bigquery-public-data.google_analytics_sample_2.ga_sessions_*`
         UNION ALL
         SELECT *, _TABLE_SUFFIX
         FROM `bigquery-public-data.google_analytics_sample_3.ga_sessions_*`
        )
SELECT
	ga_sessions.fullVisitorId  AS ga_sessions_full_visitor_id
FROM
                    ga_sessions_service_all
                    AS ga_sessions
WHERE
	(((TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'^\d\d\d\d\d\d\d\d')))  ) >= ((TIMESTAMP(FORMAT_TIMESTAMP('%F %T', TIMESTAMP('2019-12-23 00:00:00')), 'Japan'))) AND (TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'^\d\d\d\d\d\d\d\d')))  ) < ((TIMESTAMP(FORMAT_TIMESTAMP('%F %T', TIMESTAMP_ADD(TIMESTAMP('2019-12-23 00:00:00'), INTERVAL 1 DAY)), 'Japan'))))
GROUP BY 1
ORDER BY 1

なお、このクエリを実行したときのスキャンデータ量は、_TABLE_SUFFIXの日付範囲設定が有効になっていて、各データセットにおける日付範囲設定期間内データ量の合計となります。

おわりに

本記事では、LookerにおけるGAデータ管理の一例を以下の点を意識しながら紹介させていただきました。

  • 複数のデータセットに散在しているGAデータの効率的な管理
  • BigQuery実行時にスキャンデータ量を減らすようにする

Lookerは表現の自由度が高いので他に管理できる方法があると思いますが、あくまでも一例として捉えて頂けると幸いです。

このようなLooker管理の小ワザをまとめているところが今までなくて結構苦労していたのですが、
今月、日本語版のLoooker Forumができたとのことですので、これを機に充実することを願って。。。

まとめ

  • GAデータの基本的な探索はLooker Blocksを使用する
  • parameterを使って各データセットのGAデータを出し分けることでGAデータを1つのviewで管理
  • conditionally_filterでしっかり・ゆるふわなフィルタリングが可能
  • 今回紹介した手法は複数データセットのGAデータを同時に使用したいケースにも対応可能
9
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?