マンガボックスの機能開発の企画を担当している蛭田です。
今年の4月にビジネス職としてDeNAに新卒入社をしてマンガボックスに配属されたのですが、「全員自分でクエリを書いて効果測定や課題の洗い出しをしている」企画チームで、自分も円滑に業務を進めるためには最低限みるべき数値は自分で出せるようになっておかないと・・・という状況でした。
そんな訳で業務上SQLを自分で書くことが不可欠だったのですが、何をすれば必要な数値を出せるようになるのかがわからず、特段戦略もなく暗中模索状態だったので、できるようになるまで遠回りしたな、と感じています。
あくまで「あとから」振り返ったものなので体系的な方法論ではないですが、「企画をやる上での必要最低限のSQLスキル」の身につけ方にフォーカスした一つの事例として書こうと思います。
対象読者
数値的根拠をもって企画を進める必要性があるビジネス企画職、特に、半年前の僕のようにプログラミング音痴だけど自分で分析せざるを得ない状況に放り込まれたビジネス職(≒これからマンガボックスに企画として入ってくる人)向けの記事です。
こんな人！
✔️企画の際に数値的根拠が必要
✔️SQLがわからない
✔️分析が本業ではない
※エンジニアの方々は読んでも時間の無駄になると思います・・・。
企画職がSQLを扱えたほうが良い理由
企画のプロセスは概ね以下のようになるのではないでしょうか。
①目的設定→②現状把握・問題.課題の特定→③仮説をたてる→④ソリューションを企画
この中で、
・現状を把握してあたりをつける
・筋の良い仮説を見つける
ところが施策の企画・改善の質を左右します。これを数値で出すのが
自ら考え、即数値を出すことができると、企画の質とスピードが格段に早まります(自分比)！
習得のためにやったこと
やったことは特に書くまでもないくらいシンプルです。が、暗中模索状態だと「これで大丈夫か？」と不安になることがあるので、一応書いておきます。
①本とProgateでざっくりと概要を把握。
先輩がもっていた本を借りて、SQLの概要を把握し、Progateの講座を一通りやりました。この段階で感じることは「意外と簡単なんじゃね？」という錯覚です。
②必要なクエリを書いてみる。
僕の場合、「意外と簡単なんじゃね？」というのが錯覚だったとここでわかりました。
必要なクエリを自分で書くために、
・「どんなテーブルを作れれば必要な数値を集計できるか？」を考える
・自らが関わるプロダクトで、「どんなログを取っているのか？」を把握してどこからデータを引っ張ってくるかを決める
・類似のクエリを参考にしながら自分で書いてみる
という流れで進めました。
③書ける人にレビューをしてもらう。
・「何を知るために」「こんな数値が必要なので」「こんなクエリを書きました」というフォーマットで相談を持っていきます。
「いや、この数値見ても知りたいことわからないよね」や「ここは間違っているね」「こう書いた方が早いね」などを教えてもらいました。1ヶ月間、分析担当の大先生に毎日時間をもらって壁打ちさせてもらいました。
※②→③を1ヶ月くらい繰り返しました。
行き詰まった3つの壁
少しずつSQLに慣れていく中で、苦しんだ3つのポイントがありました。
「何がわからないかよくわかっていない状態」で悩んでいたので、突破に余計な時間がかかってしまったんだと思います。
とりあえず、最低限乗り越えなければいけない3つの壁と、そこで躓いた要因を書いておきます。
①JOINの壁
2つ以上のテーブルからデータをもってくるときにJOINを使いますが、僕はこういう初歩的なところでもだいぶ苦しみました。
・INNER JOINとOUTER JOINの選択
・どのカラム同士を=で結ぶか
行き詰まるポイントはほとんどこの2点だった、とあとから思ったのでJOINがよくわからなくなったらここら辺が理解できているか、正しく書けているかを確認すると良いと思います。
②サブクエリの壁
・何かの集計をしたかったら、まず集計元のテーブルを作り、そこから抽出をする、などすることが多いので、サブクエリを使わずして求める結果が出るということはあまりないかと思います。
サブクエリが連なっていると読み解くのも億劫になってくるのですが、一つ一つのサブクエリでどんなテーブルを作っているのかを書き出しながら進めればすんなり理解できるようになります。
③WINDOW関数の壁
・PARTITION BYやら何やら、やたら色々書いてあるので心理的に抵抗を感じてしまうのですが、WINDOW関数を使えば色々なクエリを簡単に書くことができるようになります。「壁」というような難しいことはあまりないと思うのですがWINDOW関数を使えば一気にできることが広がります。
よく使うもの
flagをふってコホート分析
新機能をリリースするときなど、効果検証のためにコホート分析をすることはどんなサービスでもよくあるんじゃないかと思います。
アプリバージョンnのユーザーとn-1のユーザーの行動の違いをみるときなど、例えばVersion nを使っているユーザーに1、n-1を使っているユーザーに0などのフラグを振ることで差分を簡単にみることができます。
Aをした人がBをする確率
コホート分析と同じようなやり方でAをした人がBをする確率、などをみることができます。例えば3日リターンレートならば、「n日にアプリを起動したユーザーのうち、n+3日にアプリを起動しているユーザーの割合」になるので、n+3日後にアプリを起動しているユーザーにflag = 1を、そうでないユーザーにflag = 0をふることでリターンレートを出せるようになります。
マンガの文脈だと、他にも「初回起動時に作品Xを読んだユーザーが翌日もアプリを起動する確率」、「作品Xを読んでいるユーザーの併読傾向」など全て同じ構文で出すことができます。
めちゃくちゃ便利、と感動したのが
CASE WHEN EXISTS(SELECT 1 FROM hogehoge WHERE fugafuga) THEN 1 ELSE 0 END flag
というフラグの振り方で、これを使うとJOINしたり、サブクエリを作る手間を省くことができます。
12/21にリリースした機能の利用実態を、「初回利用」と「2回目以降」のユーザーで比較するならば、EXISTSを使うことでサブクエリを1つだけにとどめられます。
WITH aaa AS(
SELECT
a2.day,
a2.userid,
CASE WHEN EXISTS(SELECT 1 FROM a1 WHERE _PARTITIONTIME >= '2018-12-21' AND a1.uid = a2.userid AND a1.day < a2.day) THEN 0 ELSE 1 END flag
FROM a2 WHERE _PARTITIONTIME >= '2018-12-21')
SELECT
day,
COUNT(DISTINCT CASE WHEN flag = 1 THEN userid END) nuu,
COUNT(DISTINCT CASE WHEN flag = 0 THEN userid END) cuu,
COUNT(uid) / COUNT(DISTINCT uid) average_number
FROM aaa
GROUP BY 1 ORDER BY 1;
今度は「最低限レベルのクエリをかけるようになった企画」が分析観点でどんなスキルを高めると良いか？を書いてみようと思います(書けるように精進します)。