LookerにおけるGAデータ管理

[Looker Advent Calendar 2019](https://qiita.com/advent-calendar/2019/looker)の23日目の記事になります。

ZOZOテクノロジーズでLookerに触れている[データエンジニア 遠藤](https://qiita.com/tsucasa)です。

投稿2回目は
[ZOZOテクノロジーズ #2 Advent Calendar 2019](https://qiita.com/advent-calendar/2019/zozo_tech2)に加えて
[Looker Advent Calendar 2019](https://qiita.com/advent-calendar/2019/looker)にもJOINしたので
Lookerユーザー向けに少し込み入ったことを書きたいと思います。

# はじめに

ZOZOテクノロジーズではLookerを"データガバナンスツール"として使用しています。
（弊社におけるLooker利用法の詳細は**[こちらからご覧ください](https://techblog.zozo.com/entry/looker-data-processing-improvement) ![](https://b.hatena.ne.jp/entry/s/techblog.zozo.com/entry/looker-data-processing-improvement)**）

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

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

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

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

# Looker Blocks

Lookerは**Looker Blocks**と呼ばれるLooker上で利用できるテンプレートが多数用意されています。
（Looker Blocksの詳しい説明は、[@rinn650さんのLooker Advent Calendar 2019 19日目の記事](https://qiita.com/rinn650/items/071698b0178ea5c34486)に書かれています）

Looker Blocksでは
**[GAデータ ga_sessionsに対するLooker Blocks](https://looker.com/platform/blocks/source/google-analytics-premium-360-by-google)**
が既に用意されています。

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

```yaml: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`のみで完結するようにします。

```yaml: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/53646b88-1ae5-0a9c-cfe4-4489b925a9e9.png)


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

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

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

![advent_calendar_20191223_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/12e5b814-8660-4fb5-cf9c-7a56cfffd606.png)

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

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

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

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

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

```yaml: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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/2d8c3171-bb53-ee1b-c618-245364437ad4.png)

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

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

```yaml: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も追加）
2. `view: ga_sessions`内の`parameter: service_name`に`label: "Service ALL"`を追加
3. `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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/533552/7ee932d0-6321-7d32-cb6e-b97ea9af4829.png)

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

```sql
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](https://discourse.looker.com/c/japanese-forum/)ができたとのことですので、これを機に充実することを願って。。。

# まとめ

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