マナティ

ビッグデータ分析・活用のためのSQLレシピ

第7回 フォーム直帰率を集計する

資料請求フォームや購入フォームなどをエントリーフォームと呼びます。エントリーフォームに遷移し、個人情報を入力している際に項目が多すぎるとストレスを感じ、入力エラーが頻発すると最後まで入力することを諦め、ユーザーが離脱してしまう可能性があります。
フォーム入力完了手前で離脱するユーザーの発生を抑え、成果を向上させるために工夫することをエントリーフォームの最適化(EFO:Entry Form Optimization)と呼びます。EFOの際に用いる指標やレポートの1つに、「フォーム直帰率」があります。今回は書籍Chapter6-3「エントリーフォームを最適化する」の中から、「フォーム直帰率を集計する」を紹介します。

 入力画面へ遷移した後、入力開始や確認画面、エラー画面を表示したログがない状態のレコードをカウントしたものを「フォーム直帰数」と呼びます。
 フォーム直帰数が高い場合は、ユーザーが入力をすぐに諦めてしまうほど過剰な項目数であったり、表示レイアウトが煩雑であることなどが理由として考えられます。また、入力画面へ遷移するまでの過程でユーザーのモチベーション喚起が十分ではなく、唐突に入力フォームが表示される印象を与えているなどの問題も考えられます。

●表:フォーム直帰率

7-1.png

 フォーム直帰率は入力画面への訪問回数を母数として、フォーム直帰数の割合を集計することで計算できます。次のデータを用いて、フォーム直帰率を集計するSQLを、下記コード例に示します。

●データ:エントリーフォームにおける表示ログとエラーログ
        stamp        | session  | action |        url        | status
---------------------+----------+--------+-------------------+--------
 2016-12-30 00:56:08 | 004a26c5 | view   | /regist/input     |
 2016-12-30 00:57:17 | 004a26c5 | view   | /regist/confirm   |
 2016-12-30 00:58:57 | 004a26c5 | view   | /regist/complete  |
 2016-12-30 00:56:08 | 5a53b5c2 | view   | /regist/input     |
 2016-12-30 00:57:21 | 5a53b5c2 | view   | /regist/confirm   | error
 2016-12-30 01:00:08 | 5a53b5c2 | view   | /regist/confirm   |
 2016-12-30 01:01:33 | 5a53b5c2 | view   | /regist/complete  |
 

 まず、セッションごとに入力画面(/regist/input)の訪問回数、確認画面(/regist/confirm)や完了画面(/regist/complete)の訪問回数を、SUM(CASE ~)構文を用いてカウントして、SIGN関数を使用してフラグにしています。
 さらに、入力画面を訪問しているセッションのみに絞り込み、同様にSUM(CASE ~)構文を用いて直帰数を計算し、AVG(CASE ~)構文を用いて直帰率を計算しています。

●コード:フォーム直帰率を集計するクエリ
WITH
form_with_progress_flag AS (
  SELECT
  -- ■ PostgreSQL, Hive, Redshift, SparkSQLの場合、substringで日付部分を抽出する
    substring(stamp, 1, 10) AS dt
  -- ■ PostgreSQL, Hive, BigQuery, SparkSQLの場合、substrが利用できる
  -- substr(stamp, 1, 10) AS dt

  , session
  -- 入力画面への訪問フラグを計算
  , SIGN(
      SUM(CASE WHEN path IN ('/regist/input') THEN 1 ELSE 0 END)
    ) AS has_input
  -- 入力確認画面または完了画面への訪問フラグを計算
  , SIGN(
      SUM(CASE WHEN path IN ('/regist/confirm', '/regist/complete') THEN 1 ELSE 0 END)
    ) AS has_progress
  FROM form_log
  GROUP BY
  -- ■ PostgreSQL, Redshift, BigQueryの場合、
  -- SELECT句で定義した別名をGROUP BYに指定できる
  dt, session
  -- ■ PostgreSQL, Hive, Redshift, SparkSQLの場合、
  -- SELECT句で別名を指定する前の式をGROUP BYに指定できる
  -- substring(stamp, 1, 10), session
)
SELECT
    dt
  , COUNT(1) AS input_count
  , SUM(CASE WHEN has_progress = 0 THEN 1 ELSE 0 END) AS bounce_count
  , 100.0 * AVG(CASE WHEN has_progress = 0 THEN 1 ELSE 0 END) AS bounce_rate
FROM
  form_with_progress_flag
WHERE
  -- 入力画面に訪問しているセッションに絞り込む
  has_input = 1
GROUP BY
  dt
;

sankaku.png

     dt     | input_count | bounce_count | bounce_rate
------------+-------------+--------------+-------------
 2016-12-30 |        3068 |         2077 |       67.69

ワンポイント
 ページ閲覧のログを利用して、上記のレポートを作成すると、入力開始後の離脱なのか、入力は一切行わずに直帰したか判別できません。最初の入力項目をクリックした際や最初の入力時に、JavaScriptを用いてログを送信することで、入力すら開始していない、より厳しい条件のフォーム直帰率を集計可能です。

著者プロフィール

加嵜 長門(著者)
株式会社DMM.comラボ所属。慶應義塾大学大学院 政策・メディア研究科修士課程修了。大学院や学生ベンチャーにて、マルチメディアデータベースを対象とした検索やレコメンドアルゴリズムの研究およびサービス開発に従事し、現在DMM.comラボではビッグデータ活用基盤の構築に携わり、SparkやSQL on Hadoopを用いたレコメンド機能、ビッグデータ活用の研究開発を担当。 共著に『詳解Apache Spark』(技術評論社)。
田宮 直人(著者)
データコンサルタント。エンジニアとして大手新聞社の関連サービス、求人サービス、コミュニティサービスの開発に携わり、株式会社サイバーエージェント在籍時にデータアナリストへ転身、株式会社DMM.comラボではマーケティング開発部マネージャーとしてビッグデータ部を立ち上げる。現在はフリーランスとして、データの解析のみならず、データ解析環境の設計・構築、ログの設計、レコメンドAPIの作成など、データに関連する業務全般を担当している。