デザイナーでもできる！サービス開発者のためのSQLデータ集計入門
こんにちは、デザイナーの橋本直樹 @hashcc です。今はクックパッドアプリでレシピを保存するときの体験のデザインに関わっています。
ふだん、僕がサービスの改善に関わる上で、よく行うのがデータ集計と分析です。
例えば、「レシピ投稿が伸び悩んでいる」のなら、原因となる離脱ポイントを集計/分析して見つける必要がありますし、立てた施策の評価にも同じく必要になるでしょう。
クックパッドではエンジニアに限らず、ディレクターやデザイナーも職域に関わらず、データ集計/分析を行っているのを目にします。
とはいえ、もっと多くの人が身につけられると良いなと思って社内で入門記事を書いてみたら反応も良かったので、今回は趣向を変えて社内で書いた記事を（一部修正して）公開してみます！
気をつけてはいるのですが、もし間違いを見つけたらコメントいただけると助かります😊
データ集計ってどうやるの？
※ 基礎的なことを話すので、SQLの書き方に興味がある人は飛ばしてくださいね😉
サービスは必要に応じて利用ログをデータベースに送っています。そして、データはSQLというルールでデータを抜き出すことができます。SQLでできることは多いのですが、ちょっとずつ覚えていけばOKです。
データベースはこんな感じの2次元の表になっています。この1つの表をテーブルと言います、エクセルで言うシートに近いですね。
縦は列で、横は行、各行のことをレコードともいいます。
これはユーザデータが入ったテーブルですね。試しにここからIDと名前を一覧表示してみましょう。
これは日本語で言うとこんな感じです。
users テーブルから、ID と ニックネーム を表示したい
SQLで書くとこんな感じです（ちなみにこのSQL式のことをクエリと言います）
select の後に表示したいデータを指定し、 from から表示したいデータがある場所を指定するイメージです。 select では ,で表示したいデータを複数指定できます。
しかし、データを抜き出すときは条件がつくことのほうが多いでしょう。例えば上記の一覧からレシピ投稿をしたことのある人に限定したいときはどうなるでしょう？
まず、日本語で書くとこんな感じです。
users テーブルから、ユーザー投稿数が0でない人の、ID ・ニックネーム・レシピ投稿数 を表示したい
SQLで書くとこんな感じです。
where というのが出てきましたね。 where の後に条件をつけて結果を絞り込むことができます。
ちなみに 0 はデータベースでは null という表記になっているので is not null (nullでない) という書き方をしています。逆は is null です。
SQLを書くときに、慣れていない時や複雑なデータ抜き出しをする時は、一旦日本語で抜き出したいことを表現したあとに、SQLに翻訳するとミスも減るのでオススメです😀
なお、上記のSQLは次のように書いても同じになります。ここでの * は「すべて」という意味で、全列のデータを自動的に表示してくれます。
いろいろな条件指定のやり方
☝🏻 IDが1の人のデータを確認
☝🏻 レシピ投稿数が2以上の人を確認
☝🏻 登録日が2021年以降の人を確認
☝🏻 登録日が2021年7月の人を確認
☝🏻 ニックネームにゲストを含む人を確認
% は任意の文字（0文字も含みます）という意味です
like '小泉%' は小泉で始まるものに一致します（例：小泉、小泉純一郎、小泉進次郎）
like '%豆腐' は豆腐で終わるものに一致します（例：豆腐、木綿豆腐、絹豆腐）
like '%Youtube%' はYoutubeを含むものに一致します（例：Youtube、Youtuber、公式Youtubeアカウント）
ニックネームにゲストを含み、かつ登録日が2021年以降の人を確認
↑のように、複数の条件がある場合は and で両方当てはまるもののみを確認できます。
複雑になってくると書くことが増えてはくるのですが、基本はこの select / from / where で抽出できます。ですので、まずはこの3つを意識しましょう。
あるページの1ヶ月の訪問人数 (UU) を算出する (distinct)
では、ここからもう少し実務的な話も交えていきましょう。
例えば、こういう風に訪問人数を出してみる、というのはよくある話ですね。
👦「 X っていうページで A という導線を出したいと思ってます」
👩🏻「うーん、ユーザーシーン的には Y のページのほうが良くない？」
👦「でも、そのページ割と深いところにあるから人少なそうで・・」
👩🏻「んー、どれくらい来てるか見てみればいいんじゃない？」
データベースにはこんな感じに、各ページにアクセスしたときのログデータが入っているpvテーブルがあって、そこにはページ名や訪問者のユニークIDが入っているとします。
「どれくらい来てるか見てみればいいんじゃない？」ということですが、求めたいページのアクセス数に加え、サイト全体のアクセス数も出したほうが良さそうですね。
まず、サイト上の2021年9月のアクセス数を出してみましょう。いつものように日本語で言うとこんな感じです。
pv テーブルから、2021年9月1日 から 2021年9月30日 までのレコードの数を表示したい
SQLで書くとこんな感じです。
* はすべてという意味で、 count(x) は 列 x のレコード数を数えてほしいという意味です。
つまり、 count(*) はすべてのレコードを教えてほしい（ where が先に処理されるので、ここでは2021年9月のレコードすべて）という意味になります。
ところで、これはすべてのアクセスを数えている総アクセス数(PV)になります。訪問人数 (UU)を数えたい場合は次のようにします。
distinct x は 列 x で同じものがある場合に除外をしてくれます。すると、ここではユニークIDの数（=UU）を求めることができます。
これに条件を加えると、 search ページのUUになります。
あとは同じ手順で item ページのUUも確認できますね。
それぞれでてきた数値を全体の数値で割ってみれば規模感が出てくると思います。
大量のページの訪問人数 (UU) をそれぞれ算出する (group by)
さきほどは算出するページが2,3個くらいのケースを見てきました。
しかし、例えば「商品詳細ページが3000個あって、その中の月間訪問人数トップ3を知りたい」というケースだとどうでしょう？さすがに3000回似たようなことをするのは厳しそうです・・
この場合は group by というのを使って、上記のキーワード毎にアクセス数を出すことで解決できます。まず日本語です。
pv テーブルから、 2021-09-01 から 2021-09-30 の間にページ名が item のもののうち、キーワード別にキーワードとそのレコードの数（重複なし）を表示したい
＜最終的に表示したいもの＞
- キーワード
- 該当数
SQLで書くとこんな感じです。
こうすると次のような結果が出てきます（余談ですが、上記のように ... as x のようにしてカラムに表示させる名前をつけることができます ）
・・うん？ なんか順番がめちゃくちゃですね。
こういうときは並び順を指定しましょう。 order by x と指定すると列x で集計結果全体を並び替えしてくれます。
・・並び替えられたけど、小さいもの順になっちゃってますね。
order by は小さいもの順に並び替えられるようになっているので、大きいもの順に並べ替えられるようにするには order by x desc と書きましょう。
desc 指定 は数字が大きいもの順、日付が古いもの順に並び替えるものです。
小さいもの順を示す asc 指定とこんがらがちなのですが、僕は「机(DESK)には大きくて古い荷物が置かれがち」と覚えています（受験ぽい）
異なるテーブルのデータを連結する (join)
さて、はじめのほうでこんな感じの users テーブルをお見せしたと思います。
でも、ユーザーに紐づくデータってある程度の規模のサービスであれば、相当な数に渡るはずです。それを1つのテーブルに収めようとするなら、データの取扱がとても難しくなってしまうでしょう。
なので、ここに入っている「レシピ投稿数」も、基本的には users テーブルにはなくて、こんな風にレシピデータとして別のテーブルに分離されていたりします。
なので、最初に示したような「ユーザー情報にレシピ投稿数を表示したい」場合、両方のテーブルの情報を連結する必要があります。SQLには join というデータを紐付ける方法があるので、それを使ってみましょう。
まずやることを日本語で書くとこうなります。
1. recipes テーブルをユーザーIDごとにまとめて、ユーザーIDとレシピ投稿数を表示したい
2. 1 に users テーブルからニックネーム,登録日時を一緒に表示したい（1 のユーザーIDと users テーブルのユーザーIDで紐付ける）
＜最終的に表示したいもの＞
- ユーザーID
- ニックネーム
- 登録日時
- レシピ投稿数
まず 1 から書いていきましょう。
するとこんな感じになります
これに users テーブルの情報を連結します。 join は from のあとに書きます。
1 に users テーブルからニックネーム,登録日時を一緒に表示したい（1 のユーザーIDと users テーブルのユーザーIDで紐付ける）
join はちょっとだけややこしいですが、こんな感じに書きます。
from テーブル1
join 連結したいテーブル(テーブル2)
on テーブル1.対応する列名 = テーブル2.対応する列名それと、select users.ユーザーIDというのがありますが、これはユーザーIDが users テーブルにも recipes テーブルにも存在するので、どちらのユーザーID列を表示すればいいかわからないためです。
なので テーブル名.列名 のように、列名の前にテーブル名を指定してあげます（他は特にかぶっていないのでなくてもOKです）
ところで、この集計結果には最初に示したテーブルにいた ユーザーID: 3 の方が出てきていませんね。
join はさきほどの例だと、recipesテーブル（の集計結果）にも、usersテーブルにも、共通して存在するユーザーIDを紐付けたので、recipesテーブル（の集計結果）に存在しないID:3の方は出てこないというわけなのです。
では、共通して存在するユーザーではなく、 users テーブルにある 全ユーザーの情報 と recipes テーブルの集計結果を紐付けるのはどうすればよいでしょう？
この場合は書き方を少し変えてあげます。
Before
After
この right join というのは、最初に持ってきたテーブル（ recipes）を左側、次に持ってきたテーブル（ users）を右側とした時に、右側のテーブルの情報を全部持っていくぞ！という意味です。
逆の left join もありますし、両方のテーブル全部持っていきたいときは full join を行います。
複雑なクエリをわかりやすく整理する（with）
さて、ここからはちょっと複雑になります。
join は1回ではなく複数回行うことができます。例えばさきほどの集計結果に tsukurepos テーブルにあるつくれぽの投稿数も一緒に表示したいとしましょう（「つくれぽ」はレシピへの口コミのようなものです）
改めて日本語で表現するとこんな感じですね。2と3が増えてちょっと変わりました
1. recipes テーブルをユーザーIDごとにまとめて、ユーザーIDとレシピ投稿数を表示したい
2. tsukurepos テーブルをユーザーIDごとにまとめて、ユーザーIDとつくれぽ投稿数を表示したい
3. 1と2を結合したテーブルを用意したい（片方/両方が空白でもデータが欠落しないようにしたい）
4. 3 に users テーブルからニックネーム,登録日時を一緒に表示したい（3 のユーザーIDと users テーブルのユーザーIDで紐付ける）
＜最終的に表示するもの＞
- ユーザーID
- つくれぽ数
- レシピ数
- ニックネーム
- 登録日時
まず1はさっき書きましたね。こんな感じです。
次に2はこんな感じですね、1とやってることはほとんど変わりませんね。
では、これらを join しちゃいましょう。
今回は2つのテーブルの情報をどちらも持っていきたいので、 full join しましょう。
ただ、今回少し事情が違うのは、集計結果同士で join をするってことですね。
こんな感じに書ければよいのですが、これだと集計されていない tsukurepos テーブルがjoinされちゃっているので間違いです・・
なので、上記の tsukurepos を集計したものに置き換えてあげます。
そうすると、こんな感じに集計されます。
とはいえ、さっきのクエリは見づらいですよね・・。週末に書いて、週明けに見たら「あれ、これ何してるんだっけ？」となりそうです。
SQLには、1つのクエリの結果に名前をつけて一時的に保存できる with という機能があります。さきほどの 1 と 2 をそれぞれ with で整理してみましょう。
これを join するとこうなります。
どうでしょう？ 集計結果に名前をつけて処理することでシンプルにできましたね。
この調子で user テーブルの結合もしちゃいましょう。full join のあとにそのまま結合してもいいのですが、recipes + tsukureposの集計結果を一度変数にしてからのほうがより見やすいです。
そうすると、こういう集計結果が出てくるでしょう。
with の良いところは、各クエリを切り出すことでそこだけ単体で実行できることです。
例えば、上記のクエリを書いてる途中に「あれ、レシピ数の集計なんかミスってる？」と思ったら、以下のようにすると、複数のクエリがあってもレシピ数集計だけシュッと算出することができます。
個人的な経験としては、よほど簡単な集計でない限り join は日常的に行いますし、「追加であのテーブルの join も必要だった！」みたいなことも多いです。
ですので、クエリを with で名付けしておく癖をつけて、見通しを常によくすることを意識しています。
日毎のCTRを算出する (数値計算)
最後によくあるCTRの算出をやってみましょう。まず、前提としてはこんな感じです。
- 各itemページに「レシピの保存ボタン」「共有ボタン」がある
- 日毎のPVのうち、それぞれどれくらい押されているか割合を知りたい
- 表示期間は2021年1月1日〜2021年10月31日
使うテーブルは以下の2つです
- サイトの全アクセスを収集している pv テーブル
- タップイベントなど設定したイベントログを収集している event_log テーブル
まず、やりたいことを言語化しましょう。
1. pv テーブルから、日ごとにitemページにアクセスしたPVを算出する
2. event_log テーブルから、日ごとの「レシピ保存」「共有」のイベント数をそれぞれ算出する
3. イベント数をPVで割って割合を算出する
＜最終的に表示したいもの＞
- 日
- PV数
- レシピ保存数
- レシピ保存割合
- 共有数
- 共有割合
ここまで来た人は、1,2の部分は一度演習としてSQLを書いてみるといいでしょう。
書いてみた結果はこんな感じになります。
round(cast(save_recipe as read) / cast(pv as real) * 100) as レシピ保存割合,このあたりを補足します。
- 数値には小数点を扱えるもの（小数点数型）と扱わないもの（整数型）があります。ここではPV数・レシピ保存数・共有数は整数型です。
- 仮にPV数が200、保存数が10だとして 10 / 200 = 0.05ですが、整数型だとこれは小数点以下がなくなって 0 になってしまいます。ですので、それぞれを小数点型に変更してあげる必要があります。
- cast(A as real) でAが小数点型であることを明示できるので、そうしています。
- その後に、%であるとわかりやすいように数値を100倍しています。
- このままだと、割り切れない割合は33.33333みたいに見づらくなるので、小数点以下を round()で囲って四捨五入しています。
実際にクエリを書くにあたって
ここまで大まかにSQLの書き方を話してきました。僕はこれでだいたい8割型の集計作業はできるのですが、前提として記法以外に以下も理解する必要があります。
1. どういうアプリケーションを使って算出するか？
2. 集計に必要なデータはどのテーブルのどの列にあるのか？
3. データベースの種類はなにか？
1. どういうアプリケーションを使って算出するか？
僕はMacでは Postico と Bdash という2つのデスクトップアプリを使っています。
両方とも無料（Posticoは一部機能は有料）のアプリで、PosticoはMacのみに、BdashはWindows, Mac, Linuxで動作します。
Postico （スクリーンショットは公式サイトより引用）
Bdash（スクリーンショットは公式レポジトリより引用）
集計作業には主にBdashを使っています。Bdashは単純にSQLクエリが書ける以上に「Excelのようなグラフ生成ができる」「集計結果をアップし、URLで簡単に社内共有ができる」の2点が圧倒的に便利です。
ただ、BdashにはSQLを書かずにテーブルがどうなっているかシュッと見る機能がないため、Posticoを併用して補っている感じです。
2. 集計に必要なデータはどのテーブルのどの列にあるのか？
いままで集計に必要なテーブルがわかっている前提で書いていましたが、実際は自分で理解する必要があります。知りたいデータがどこにあるかは、ログを実装したエンジニアや、データベースを管理しているチームが知っているでしょうし、聞いてみましょう。
また、場所がわかったあとにそれぞれのセルがどういう意味を持つについて、クックパッドではdmemoというシステムで情報共有をしています。
3. データベースの種類はなにか？
今まで一切触れてこなかったですが、データベースと言っても種類がいくつかあります。
- MySQL
- PostrgreSQL
- SQLite
- Amazon Redshift
- ...
基本的な書き方は同じなのですが、データベースによって記法が違うところもある（方言みたいなものです）ので、自分がアクセスしようとしているデータベースがどれか理解しておく必要があります。
実は1で紹介したアプリも扱えるデータベースが違っていて、例えばPosticoだとAmazon Redshift/PostgreSQLをサポートしていて、MySQLはサポートしていなかったりします。
おしまい
といろいろ書きましたが、SQL（に限らず）身につける一番の手段は実際に書いてみることです。
調査したい手頃なサイズのタスクを見つけて書くのを繰り返してみるといいと思います。
今回はデータ集計/分析に必要な基礎的なことを抜き出して書いてみましたが、より知りたい人は以下の書籍を読んでみると良いでしょう。
----
クックパッドのデザイナーには、デザインはもちろん、様々なスキルを持って領域にとらわれず幅広く活躍しているデザイナーがいます。もし、ご興味を持っていただけた方は、下記リンク先をご覧ください。
今回はデザイン以外の視点からサービス開発に役立つ話を書いてみましたが、Cookpad Design Magazineにはクックパッドのプロダクト開発におけるデザインの話も多く投稿されています。こちらもぜひフォローしてみてください 😀