はじめに
入会ページのフォーム通過率を計測しようとしたところ、複雑なビジネス要因のために黒魔術のようなSQLを書くことになったため、簡単に計測できるような分析用のテーブルを作ることにした。
分析基盤は概ね整いつつあるが、ビジネスロジックが複雑であるため、BIが有効に使えていないような場合において、ETL、ELTを行うことによって効率的に分析する手段の参考になれば。
サブスクリプションビジネスで、入会ページのCVRを計測することを例に記載する。
分析の前提
どのような分析をするか理解しやすくするため、ビジネス背景、分析基盤、課題について記載する。
ビジネス背景
サブスクリプションビジネスの主な活動は、新規ユーザー獲得し、解約ユーザーを減らし、一人あたりのLTVを向上させるなどである。今回の分析対象となる新規ユーザー獲得について、以下を想定している。
- Web広告やTVCM、リアルメディアなどの様々な媒体で認知を促し、トライアルページに誘導する
- ユーザーがトライアルページに来て、トライアルを申し込む
- トライアルユーザーに対してメール、電話、DMなどで入会を促す
- ユーザーが入会ページに来て、フォームに必要情報を入力して入会を完了する
全く未知の人が入会ページに訪れるのでなく、トライアルによって情報が得られているユーザーが訪問することが、特徴である。
トライアルした人の中から入会する人を増やすため、入会ページのフォーム通過率を可視化してあげていくことにする。具体的には入会ページのCVRを
入会完了したセッション数/入会ページに訪問したセッション数
で計測する。
分析基盤の概要
トライアルで入力された情報や、本ユーザー登録の情報は、基幹DBデータに記録される。
一方でサイトのアクセスログはデータレイクに記録される。
分析用にPostgreSQLのDWHがあるが、全ての情報が集約されているわけではなく、必要な情報は追加でDWHにロードする必要があった。
アクセスログは1PV1レコードで、以下のような形式で記録されているとする
| device_id | session_id | user_id | url | title | access_time |
|---|---|---|---|---|---|
| device1 | session1 | user1 | host.com/regist_intro1?from_param=mail1 | 入会案内 | 2022-04-01 12:10:10.000 |
| device1 | session1 | user1 | host.com/regist_form | 入会情報入力 | 2022-04-01 12:10:30.000 |
| device1 | session1 | user1 | host.com/regist_finish | 入会情報完了 | 2022-04-01 12:10:55.000 |
| device2 | session2 | user2 | host.com/regist_intro2?from_param=gdn | 入会案内内 | 2022-04-01 12:15:10.000 |
| device2 | session2 | user2 | host.com/help | ヘルプページ | 2022-04-01 12:15:40.000 |
| device2 | session3 | user2 | host.com//regist_intro2?from_param=ydn | 入会案内 | 2022-04-03 12:00:40.000 |
直面した課題
アクセスログをDWHに取り込んで、本登録したセッション数/入会ページに訪問したセッション数を出して終わりだと思っていた。
しかしここが迷宮の入り口だった。
-
ユーザーに応じて入会ページをカスタマイズした結果、入会ページのURLが山のように増えていた
-
さらにユーザーが複数の入会ページを参照したり、途中で戻って別の入会ページを参照したりする人がいたなどがいたため、どの入会ページから入会完了となったのか分かりにくなった
入会前に訪問した入会ページを特定するために、window関数で順序付ける必要があった -
さらに、ユーザーが最適なページに自動 到達できるようにリダイレクトが設定された結果、ユーザーが自分の意思で離脱したのか、システムでリダイレクトされたのか分かりにくくなった
タイムスタンプの差分を見て、滞在時間が僅かの場合はリダイレクトとして、ユーザーが自分で離脱したわけではないので、CVRの計算には含めないようにする必要があった
名著「ビッグデータ分析・活用のためのSQLレシピ」を使いながら、黒魔術のようなSQLを書くことになった。
例：CVRを見たいだけなのにひどく複雑化したSQL
--入会フォーム毎のセッション数を集計
with v_regist_intro_summary as(
select
t.url as regist_url
,count(distict t.session_id) as intro_session_cnt
from
session_table t
where
t.access_time <@ tsrange('2022-04-01', '2022-04-08', '[)')
and t.url like '%regist_intro%' --入会完了ページ
group by
t.url
)
--入会完了ページに辿り着いたセッションidを特定
,v_regist_session_ids as (
select
distinct t.session_id as session_id
from
session_table t
where
t.access_time <@ tsrange('2022-04-01', '2022-04-08', '[)')
and t.url like '%regist_finish%' --入会完了ページ
)
--入会完了ページにきたセッションを抽出し、アクセス時間で並べてどの入会ページから来たか特定する
, v_regist_finish as (
select
t1.uid
,t1.url
,lag(t1.url, 2) over (PARTITION by t1.user_id order by t1.access_time) as regist_url --2つ前の入会ページ
from
session_table t1
inner join
v_regist_user_ids t2
on t1.user_id = t2.user_id
where
t1.access_datetime <@ tsrange('2022-04-01', '2022-04-08', '[)')
and (
t1.url like '%regist_intro%' --入会ページ
or t1.url like '%regist_finish%' --完了ページ
)
)
--完了ページのセッション数を入会ページ毎に分類して集計
,v_regist_finish_summary as (
select
t.regist_url
,count(distinct t.session_id) as finish_session_cnt
from
v_regist_finish t
where
t.url like '%regist_finish%'--完了ページ
group by
t.regist_url
)
select
tb1.intro_session_cnt
,tb1.finish_session_cnt
from
v_regist_intro_summary tb1
left join
v_regist_finish_summary tb2
on tb1.regist_url = tb2.regist_url
;
※3のリダイレクトは考慮されていない
やったこと
ビジネスサイドのリクエストに対して、複雑なSQLを書かずに汎用的に対応できる状態とする。さらにBIで可視化してビジネスサイドが自分で利用できるようにしたい。
そのためにまず必要な情報をDWHにロードして、利用しやすいように集約して変換し、上記課題が前処理された分析テーブルを作成することにする。
ステップ1 ダッシュボードの要件を定めてフォーマットを作る
まずはビジネスサイドが活動を行うための、ダッシュボードのフォーマットを作る。
着目する指標やどういった切り口でブレイクダウンしたいのかを事前に理解しておく。
後述するが、このステップが簡単なようにで一番重要なポイントであった。
今回はトライアルの情報の切り口で分けて、CVRを見たい。
また
- 入会ページを見た後、別の入会ページに移動してしまった場合、
- リダイレクトが行われた場合
などのイレギュラーが発生した場合に、除外するのか合計に入れるのかなど、取り扱い方も定めておく。
ここを丁寧に擦り合わせておくことで、テーブルの実用性が大きく増す。
さらにいつどういう頻度でダッシュボードをみるかも確認し、データの更新タイミングを定める。
ステップ2 フォーマットに沿って抽出しやすいテーブルを設計する
ダッシュボードのフォーマットができたら、分析の切り口や指標を元に、ダッシュボードの内容を簡単に抽出できるようなテーブルを設計する。
正規化された細かいテーブルを作るのでなく、分析に必要な項目が一つのテーブルに全て揃ったような、スタースキーマのテーブルを作る。
今回はCVRを簡単に可視化できるように、1PV1レコードの形式でなく、入会ページ1アクセス毎に1レコードとして、フォーム入力、入会完了のどこまで進んだが分かるような形式にした。
またリダイレクトの有無もフラグで一目でわかるようにしておく。
| device_id | session_id | user_id | trial_type | trial_time | regist_path | from_param | title | is_form_access | is_finish | regist_intro_access_time | regist_finish_time | leave_to_url | is_redirect |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| device1 | session1 | user1 | trial_A | 2022-3-30 13:00:10.000 | host.com/regist_intro1 | mail1 | 入会案1内 | 1 | 1 | 2022-04-01 12:10:10.000 | 2022-04-01 12:10:55.000 | 0 | |
| device2 | session2 | user2 | trial_B | 2022-3-31 14:00:10.000 | host.com/regist_intro2 | gdn | 入会案内2 | 0 | 0 | 2022-04-01 12:15:10.000 | host.com/help | 0 | |
| device2 | session3 | user2 | trial_B | 2022-3-31 14:00:10.000 | host.com/regist_intro2 | ydn | 入会案内2 | 0 | 0 | 2022-04-03 12:00:40.000 | 0 |
リダイレクトをwhereで除外した上で、入会ページでgroup byして訪問数や入会数を集計すれば、入会ページごとのCVRがすぐに出せる、というゴールをイメージしておく。
ステップ3 必要なデータソースをDWHにロードする
分析テーブルが設計できたら、必要なデータソースを準備する。
今回はデータレイクにあるアクセスログと、基幹DBにあるトライアルの情報や入会情報などである。
それぞれDWHにロードして情報を集約する。
ステップ4 DWHでデータを加工してテーブルを作成する
必要なデータをDWH上に集約したら、分析テーブルを作っていく。
処理が複雑であるため、いくつか何段階に処理を分けて中間テーブルを作成しながら進める。
今回は主に以下のように段階を踏んで整備した。
- アクセスログから入会に関連する情報を抽出
- 入会ページごとにセッションを区切る
- 入会したか離脱したか、等の情報を整理する
- トライアルや入会の情報を追加する
ステップ5 ワークフローエンジンで自動化する
毎日や毎週など、利用目的に応じた頻度でデータが自動で生成されるようにするため、ワークフローエンジンを使って、上記の処理を自動実行できるようにする。
ステップ6 BIで可視化する
出来上がったテーブルを利用する。
簡単なSQLでビジネスサイドのリクエストに応えられるようになった。
毎回抽出しなくて良いように、BIで自動表示されるようにする。
例 シンプルになったSQL
SELECT
url
,trial_type
,count(distinct session_id) as session_cnt
,count(distinct case when is_form_access = 1 then session_id end) as form_cnt
,count(distinct case when is_finish = 1 then session_id end) as finish_cnt
,1. * count(distinct case when is_finish = 1 then session_id end) / count(distinct session_id) as CVR
FROM
regist_session_summary_table
WHERE
is_redirect = 0
GROUP BY
url
,trial_type
アウトプット
| url | trial_type | session_cnt | form_cnt | finish_cnt | CVR |
|---|---|---|---|---|---|
| host.com/regist_intro1 | trial_A | 100 | 50 | 40 | 0.40 |
| host.com/regist_intro1 | trial_B | 200 | 110 | 100 | 0.50 |
| host.com/regist_intro2 | trial_A | 300 | 250 | 60 | 0.20 |
| host.com/regist_intro2 | trial_B | 400 | 350 | 100 | 0.25 |
最後に感想など
半ば精神論だが、分析者目線で正確なデータを見やすいダッシュボードにすることも大切だが、ビジネスサイドの目線で、本気で数値責任を追ったつもりで考えると良いと思った。
データソースの取得しやすさなどのエンジニアリング視点から考えたり、関連するデータや切り口を漫然と考えるより、
あと1週間でこの数値を何%上げないといけない
というようなプレッシャーをビジネスサイドと共に感じた方が、活動に則したダッシュボードが作れた。
なのでステップ１、ステップ2の設計がポイントであった。
今回の失敗例をあげると、主要なCVRだけを見れるようにしていたが、ビジネスサイドと共にCVRが低い要因を深掘りしたところ、離脱先などを知りたいとなり、データが足りずに設計し直すこととなってしまった。
まだまだ未整備なデータがたくさんあるので、今後どんどん分析環境を整備しようと思う
また、サブスクビジネスでは追うべき指標はある程度共通なので、業界で共通利用できるようにパターン化してまとめていきたい。