はじめまして！Livesense not engineers Advent Calendar 2018の2日目を担当します、新卒1年目の伊藤と申します。普段は転職会議というメディアのビジネスグループに所属し、既存の収益基盤の強化を図るべく営業から分析まで幅広く業務をこなしています。
普段からリブセンスの職種にこだわらない「越境的な働き方」は合理的で好きだなぁ～と思うことも多いので、非エンジニア向けのアドベントカレンダーと聞いて参加せずにはいられませんでした！！
（@inagakkieさん、主催頂きありがとうございます<(_ _)>）
今回は、実際に業務の中で使用している「SQL」をテーマとし、初学者向けにWebサイト分析の基礎でもある、「CVRの算出方法」について書いてみようと思います。
※SELECT、FROM、WHEREなど基本的な構文・関数は学習済みで、実際に分析に応用してみたい！という読者向けの内容となっていますのでご容赦ください。
##技術は伝承するもの
今年の5月、配属されたての頃は全く書けず苦労しましたが、一つ一つ積み重ねることで今では息を吸うようにSQLが書けるようになってきました。
キュゥべえもビックリのショボさです。
上記を見て頂ければ分かるように、ここまで来るまでの道のりは険しいものでしたが、根気よく指導してくれた先輩方がいたからこそSQLという技術が好きになりました。
技術は伝承されていくものだと勝手に思っているので、自分が教えてもらったからには次の誰かに伝えていく所存です！
初投稿にて拙い部分もあるかと思いますが、最後まで読んでいただけると嬉しいです。
#CVRの算出は分析の基礎
このページをご覧になっている皆様の中には、職場などにて「CVRが12%で...」というような会話を聞いたことがある方も多いでしょう。CVRはサイトがあげた成果（収益）を図る上でも重要な指標となり、以下のような式で表すことがで来ます。
「CVR（コンバージョン率）＝CV（コンバージョン数） / 訪問数（セッション数）」
つまり、サイトを訪れたユーザーの内の何人が望む行動をとってくれたのかを測る指標となっています。
企業の採用サイトを例として考えてみましょう。
採用サイトが閲覧された回数を100、サイトからエントリーシートを提出された回数を20とすると、CVRは「20/100 = 0.2 = 20%」となります。
このCVRという指標を改善できるようにサイトページの改善を行っていくことで、より成果の高いサイトを構築することができます。
##SQLで算出してみる
今回は簡略化を図るため、以下のテーブル（「recruit」テーブルとする）を集計期間対象のアクセスログとし、クエリを作成していきます。
カラムの定義は以下とします。
- user_id = ユーザーのid
- page_type = ページの種類
- application_confirmは応募フォーム入力ページ
- application_completeは応募フォーム完了ページ
- hit_time = ページが開かれた時間
CVRを算出するために必要な情報としては、上述の通り
「CVR（コンバージョン率）＝CV（コンバージョン数） / 訪問数（セッション数）」
ですので、apply（応募完了数）をpv（応募フォーム閲覧数）で割り返し、クエリは以下のようになります。
以下が出力される結果です。
上記クエリについて、順を追って説明していきます！
##条件付きで合計値を計算する
まずは、FROM以下の条件にてテーブルを指定します。
また、出力したい結果の一つとして、pv（応募フォーム閲覧数）をCOUNTを用いてSELECT分以下に記載します。ここまでは基礎的な構文と合計値の考え方を知っていれば、難無く書くことができると思います。
次にapply（応募完了数）を数えるためには、応募完了ページを訪れたログのあるユーザーに絞る必要が出てきます。そこで使用するのが「CASE句」です。
エクセルで言うところのIF文と似たような役割を持っており、条件によってアウトプットする値を変えることができる関数です。
CASE式で条件分岐をSQLに任せる
https://qiita.com/sfp_waterwalker/items/acc7f95f6ab5aa5412f3
このCASE句を用いることで、page_type = 'application_complete' という条件を満たすユーザーをダブりなく数え上げます。
##比率を計算する
ここまでで、apply（応募完了数）をpv（応募フォーム閲覧数）の算出が完了しました。
後は手計算なりエクセルなりでCVRを計算することができますが、今回はSQLを用いて比率の計算までを行なっていきたいと思います！
SQL内では四則演算をすることが出来るので、単純位格とCVRは以下になります。
apply / pv
しかし、ここである問題が生じます。。
PostgresSQLの「/」では、整数同士の割り算をするときに割った余りを切り捨てて整数部分だけを返す決まりとなっているため、上記の式で返ってくる値は「0」になってしまいます。
このような場合は左右どちらかの値を少数型の値にしてあげる事で問題は解決されます！
よってここでは、applyのデータ型をinteger型（整数）から、float（浮動小数点数）に変更し、
apply::FLOAT / pv
と書き換えます。
そして、今回は小数点第二位以下で四捨五入をするため、ROUND関数を用います。
ROUND(apply::FLOAT / pv, 2)
これで、CVRのカラムに「0.20」という結果を返す事ができます！
##終わりに
今回はWebサイトの成果を測る指標として基本となる「CVR」のSQLを用いた算出方法を紹介しました。
データ型や関数もいくつか紹介しましたが、もっと詳しく勉強してみたいという方は、以下の書籍を参考にしてみることをオススメします！
来年はもっと、コアな内容に挑戦できるよう精進します...！
ここまで読んでくださった方々へ、ありがとうございました☕️