この記事は BASE アドベントカレンダーと Looker アドベントカレンダー 8 日目の記事です。

f:id:glassmonekey:20211207213227p:plain

はじめに

BASE BANK 株式会社にて事業開発を担当している猪瀬 (@Masahiro_Inose)です。

私達のチームでは、BASE ショップを運営しているショップオーナー様が簡単に資金調達をできる「YELL BANK」というサービスの開発・運営しています。

thebase.in

今回の記事は以下の二部構成となります。

前半部分は私からLookerという BI ツールを使って、サービス利用者の利用状況や関連情報を一元的に把握できる、「ショップカルテ」なるものを作成したことについて紹介します。
後半部分は Looker で扱いやすくするためのデータの加工を担当した永野(@glassmonekey)から、データ基盤周りやデータ加工の工夫した部分について解説します。

ちなみに過去の記事の宣伝ですが、弊チームでは BigQuery を活用して簡易的な CRM を作ったりもしました。

devblog.thebase.in

ショップカルテとは

「ショップカルテ」とは、「YELL BANK」というサービスを利用しているショップの「サービスの利用状況」「アクセスログ」「ショップの運営状況(GMV や在庫の変動など)」等の複数のデータを 1 つのダッシュボードで一元的に把握できる仕組みのことです。ショップの健康状態を把握できるようなイメージから「カルテ」と呼んで愛用しています。

なぜ作ったのか

プロダクトの機能改善を進めていく上では、「利用してくれているユーザーをありありとイメージできるようになること」が大変重要だと思っています。

例えば以下のようなことが具体的なアクションとして挙げられます。

BASE ショップを自分で作って販売をしてみるといった、いわゆるドッグフーディングをする
積極的にユーザーインタビューをしたり、オーナーさん直接会いにいく

ショップ名を見るだけで「どのようなオーナーさんが、どんな気持ちで、どんな理由でサービスを使ってくれているか」が浮かんでくるようになると、日々のサービス改善案の仮説精度が高まります。

しかし、私が携わっている「YELL BANK」というサービスは、将来債権の買取という仕組みによってショップの資金調達を実現するという、世の中にあまり事例のないサービスです。自分自身も気軽に毎日利用できるようなサービスではないので、どうしてもユーザーの体験が想像しにくいという特徴がありました。

また、何かサービス改善のヒントになりそうなオーナさんの行動を発見した際に、いざその行動について分析を進めるとなると以下のような作業が必要でした。

複数のクエリを書いてデータを抽出
スプレッドシートでデータを加工
比較
分析 etc …

これらの工数がかかってしまうこともオーナーさんの体験を想像しにくい問題に拍車をかけていました。

そこで、「ショップカルテ」を作成し、閲覧したいショップの ID をフィルター指定するだけで、「サービスの利用状況」や「ショップの運営状態」などの複数のデータをダッシュボード上にまとめて可視化する仕組みを整えました。これによりショップの行動とその理由をパッと簡単に把握しやすくしました。

「ショップカルテ」の構成

サービスの利用状況に関連する複数のデータを 1 つのダッシュボードにまとめています。

4 つのデータカテゴリによって構成されています。

閲覧できるデータカテゴリ	目的
基本的なサービス利用情報	利用開始日や累計の利用金額など、基本情報の把握
各種機能の利用推移、履歴	「YELL BANK」をどの程度使いこなしているかの把握
サービスページアクセスログ推移	サービスへの興味関心、利用熱意の移り変わりの把握
ショップの販売状況推移	ショップの運営状況とサービス利用状況の相関把握

を直感的にパッと把握できるようになります。

※実際のLookerダッシュボードの画像。データ部分はマスクしております。また、本当は縦に繋がっているのですが、画像が縦長になってしまうので半分に分割して横に並べています。

f:id:glassmonekey:20211207210641p:plain これによって、「このショップはなんでこういう動きをしたんだろう」という疑問が浮かんだ時に、ショップの ID を指定するだけでパッと仮説につながるインサイトを得ることができるようになりました。

データ基盤の構成

こんにちは、BASE BANK 株式会社にてアプリケーションエンジニアをしている永野(@glassmonekey)です。私からはデータ基盤の構成やどのようにデータを準備したのか、Looker で扱うために工夫した点を紹介します。

「YELL BANK」の業務データは基本的には MySQL 上に永続化しており、Fargate 上で Embulk のコンテナを動かして日時で BigQuery に同期する仕組みを取っています。同期する仕組みの詳細は後日別の記事で公開予定です。乞うご期待!!

ちなみに、バージョンは安定版の 0.9.23 を使用しています。

f:id:glassmonekey:20211207210802p:plain

Embulkとは

Embulk とは様々なデータソース(s3, RDB, etc…)からデータを加工しつつ転送できる、並列バルクデータローダーです https://www.embulk.org/

プラグイン形式で様々なデータ形式に対応しています。プラグイン自体は Gemfile で指定します。 f:id:glassmonekey:20211207210850p:plain

詳細はこちらのスライドシェアに載っています。

Embulk, an open-source plugin-based parallel bulk data loader from Sadayuki Furuhashi

必要なデータの種類

ショップカルテは基本的にはショップオーナの状態を我々が個別で見ることを目的としています。それにあたっては大きく２種類のデータが必要になることがわかりました。データは BigQuery に集約してるので、クエリなどは BigQuery のものになります。それぞれで集計する単位や起点が変わってくるのでそれぞれエクスプローラを別に作りました。

現在の状態 (ショップ単位で集計したいデータ)
時系列の状態 (ショップと日時単位で集計したいデータ)

現在の状態について

「現在の状態」のデータというのは更新されうるデータのことを指します。例えば、「YELL BANK」の場合だと「現在の資金調達回数」という指標はこれに該当します。

ショップのユニーク性を担保する行が起点 になるようにエクスプローラを設計します。

時系列の状態

「時系列の状態」というのはある時点でスナップショット的に扱うデータのことを指します。これらのデータはそのまま業務用テーブルを join すればいいわけではありません。特に Looker においてはディメンションに対してメジャーを用意することになるので、 日付 + ショップのユニーク性を担保する行を起点 にデータを設計する必要があります。そこで日時のカレンダーテーブルをショップごとにCross Joinして用意することにしました。

カレンダーテーブルは以下のような形で作りました。 2018 年 12 月 1 日から現在まで 1 日単位で、利用者を示すテーブルの ID と日付のペアを作っています。 ユーザーテーブル.作成日 <= timestamp(d.n)　で作成日以降のみ作られるようにしてるなど、不要なデータは極力作らないようにしています。

  WITH date_map  (SELECT day FROM UNNEST(GENERATE_DATE_ARRAY('2018-12-01', CURRENT_DATE, INTERVAL 1 day)) AS day )
SELECT
  ユーザーテーブル.id,
  TIMESTAMP(d.day) AS user_day
FROM
  ユーザーテーブル
CROSS JOIN
  date_map AS d
WHERE
  ユーザーテーブル.作成日 <= TIMESTAMP(d.n)
ORDER BY
  user_day

一度このデータを作ってしまえば Looker には dimension groupのtimeframeを用いて月ごとの集計や曜日などの集計にも使い回せるのでかなり便利です。

dimension_group: created {
  type: time
  timeframes: [time, date, week, month, quarter raw]
  sql: ${TABLE}.user_day ;;
}

Lookerでのデータの扱い

基本的に BQ には業務データとほぼ同じテーブルスキーマを当て込んでいます。そして、Looker 上では派生テーブルを用いてBigQuery のクエリから中間データを生成して Looker で扱いやすくしています。

基本的に前述した集計する単位でエクスプローラをそれぞれ切りつつ、JOIN が多段にはならないようにviewを作るのが良いと考えています。

基本的には一意性を担保するカラムにprimary_key を設定してやりつつ、メジャーには distinct 系を使ってあげれば重複を避けて集計を行えます。しかし、多段的なJOINデータを扱うことは複雑性から意図せぬ挙動に繋がることが度々あり、メンテナンスも大変なので予めデータを集計しやすく派生テーブルを使っておくことが無難です。SQL からも派生テーブルは作れるので大変便利です。

docs.looker.com

そこで設計する派生テーブルの構成は可能ならばスタースキーマやワイドテーブル(one-big-table)で作って上げるのが良いでしょう。

特に現代ならマシンパワーもあるので、個人的には設計コスト的や扱いやすさ的にもワイドテーブルが良いのではと思っています。実践はそこまでできてはいませんので今後の課題です。

スタースキーマとワイドテーブルの比較は Fivetran 社の Michael Kaminski 氏の記事に詳しく載っています。

fivetran.com