ユーザーによる機能利用状況を分析する
アプリケーションではサービス内で複数の機能を提供していますが、全ての機能を全てのユーザーが利用しているわけではありません。
例えば、
- それぞれの機能をどのくらいのユーザーが使っているのか？
- 特定の機能を使っていないユーザーはどのくらいか？
といった分析ができれば、機能やナビゲーションといったUI改善の有効な材料にすることができます。
そのためには各種機能の利用状況を調べ、提供している機能がユーザーに受け入れられているのかどうか、想定してる通りに使ってもらえているのかなどを確認する必要があります。
本稿では例としてストア型のアプリケーションでよく使われる「tagging（タグ付け：リスト追加や分類など）」「purchase（購入）」「review（レビュー投稿）」という3つの機能に対するユーザー行動を分析する場合について考えます。
2つや3つの複数の機能をまたがって利用している（または利用していない）ユーザー数や構成比を把握するため、3つの機能の利用有無をクロス集計します。
ここでは例えばタグ付けを行ったユーザーは「tagging」、行っていないユーザーは「not tagging」、いずれも含める場合は「all」としています。
また「user_count」ではそれぞれのユーザー数、「ratio」では該当ユーザー数の全体に対する比（構成比）を示します。
欲しい結果は以下のようになります。
done_tagging | done_purchase | done_review | user_count | ratio --------------+---------------+-------------+------------+-------- all | all | all | 4 | 100.00 all | all | not review | 1 | 25.00 all | all | review | 3 | 75.00 all | not purchase | all | 2 | 50.00 all | not purchase | not review | 1 | 25.00 all | not purchase | review | 1 | 25.00 all | purchase | all | 2 | 50.00 all | purchase | review | 2 | 50.00 not tagging | all | all | 1 | 25.00 not tagging | all | review | 1 | 25.00 not tagging | not purchase | all | 1 | 25.00 not tagging | not purchase | review | 1 | 25.00 tagging | all | all | 3 | 75.00 tagging | all | not review | 1 | 25.00 tagging | all | review | 2 | 50.00 tagging | not purchase | all | 1 | 25.00 tagging | not purchase | not review | 1 | 25.00 tagging | purchase | all | 2 | 50.00 tagging | purchase | review | 2 | 50.00
この結果を得るためのSQLは以下の通りです。
with/* （1） */
user_action as (/* （2） */
select
user_id
,sign(sum(/* （2）-（b） */
case when action_category = 'tagging' then 1 else 0 end/* （2）-（a） */
)) as has_tagging
,sign(sum(
case when action_category = 'purchase' then 1 else 0 end
)) as has_purchase
,sign(sum(
case when action_category = 'review' then 1 else 0 end
)) as has_review
from
action_history
group by user_id order by user_id
)
,action_diaglam as (/* （3） */
select
has_tagging
,has_purchase
,has_review
,count(*) as user_count
from
user_action
group by cube(has_tagging, has_purchase, has_review)
)
select/* （4） */
case has_tagging/* （4）-（a） */
when 1 then 'tagging' when 0 then 'not tagging' else 'all'
end as done_tagging
,
case has_purchase
when 1 then 'purchase' when 0 then 'not purchase' else 'all'
end as done_purchase
,
case has_review
when 1 then 'review' when 0 then 'not review' else 'all'
end as done_review
,user_count
,round(
100.0 * user_count / nullif(/* （4）-（c） */
sum(case when has_tagging is null/* （4）-（b） */
and has_purchase is null
and has_review is null
then user_count else 0
end) over ()
,0)
,2)
as ratio
from action_diaglam
order by done_tagging, done_purchase, done_review
;
（1）With句
With句はサブクエリに名前を付け、SQLの中ではなく前に記述します。ここではサブクエリをWith句によりuser_actionと名付けています。サブクエリをSQLの中で記述する場合に比べ、視認性を高めることができます。以下のように記述します。
WITH <クエリ名> AS ( select <列> ～ )
（2）ユーザーごとのアクション有無
サブクエリの中では、まずユーザー単位でログを集約します。「tagging」「purchase」「review」の3つのアクションを行ったログが存在するかどうかを、0と1のフラグで付与します。
このサブクエリによる結果テーブルは以下の通りです。
user_id | has_tagging | has_purchase | has_review ----------+-------------+--------------+------------ UID0001 | 1 | 1 | 1 UID0002 | 0 | 0 | 1 UID0003 | 1 | 1 | 1 UID0004 | 1 | 0 | 0
このコード例の出力結果の「has_tagging」「has_purchase」「has_review」カラムにおいて、それぞれタグ付け、購入、レビューの各アクションを一度でも実行したとがある場合は1、一度も実行していない場合は0となります。
（2）-（a）case式による行動有無の判定
case式の構文は2種類あり、WHENのあとに値のみ記述する構文（単純case式）と、「<項目> = <値1>」のように比較演算が利用可能な構文（検索case式）があります。この箇所では検索case式の構文を利用しています。ここではaction_categoryの判定内容に応じて該当するユーザーの行動回数を加算することで、行動の有無を判定します。
case WHEN <項目> = <値1> THEN <値4>
WHEN <項目> = <値2> THEN <値5>
ELSE <値3> END
単純case式は後述の（4）-（a）の部分で利用しているので参照してください。
（2）-（b）sign関数
signは引数の符号を返す関数です。以下のように記述します。
sign(expr)
戻り値は符号としては [ -1 | 0 | +1 ] またはNULLを返します。
expr < 0 ... -1 expr = 0 ... 0 expr > 0 ... +1 expr IS NULL ... NULL
このsign関数により行動回数のような正の整数は丸められて1になるため、行動回数ではなく行動有無を表すフラグとして利用できます。
（3）全てのアクションの組み合わせの集計
このサブクエリでは3つの機能の組み合わせの利用（アクション）を集計します。
（2）の結果からアクション別に集約するだけでは、複数の機能をまたがって利用しているユーザー数や構成比を知ることができません。例えば「購入とレビューを行ったユーザー数」など、2つ（または3つ）のアクションの組み合わせに対するユーザー数が必要です。
標準SQLに定義されていてPostgreSQL9.5以降でサポートされているCUBE関数を用いると、利用した機能の組み合わせの集計を比較的簡単に実現できます。
このサブクエリによる結果のテーブルは以下の通りです。
has_tagging | has_purchase | has_review | user_count
-------------+--------------+------------+------------
0 | 0 | 1 | 1
0 | 0 | | 1
0 | | | 1
1 | 0 | 0 | 1
1 | 0 | | 1
1 | 1 | 1 | 2
1 | 1 | | 2
1 | | | 3
| | | 4
1 | | 0 | 1
| | 0 | 1
0 | | 1 | 1
1 | | 1 | 2
| | 1 | 3
| 0 | 0 | 1
| 0 | 1 | 1
| 0 | | 2
| 1 | 1 | 2
| 1 | | 2
（3）-（a）cube関数
クロス集計するには、group by句にcube関数を使用します。cube関数は、PostgreSQL9.5で追加された新機能です。以下のように記述します。
group by cube（expr1,expr2,・・・]）
引数expr1～を集計項目として、クロス集計を行います。ここでは「has_tagging」「has_purchase」「has_review」を引数としているので、これら3つを集計項目としたクロス集計を行います。
（4）データ整形
（3）のクエリの結果でも分析は可能ですが、可読性が高い形式になるように、クエリ結果データを整形します。各アクションの組み合わせでのユーザー数の構成比も一緒に計算します。
（4）-（a）case式によるフラグから文字列への変換
ここでは単純case式の構文を利用しています。単純case式の構文は以下の通りです。
case <項目> WHEN <値1> THEN <値4> WHEN <値2> THEN <値5> ELSE <値3> END
case式を利用することにより例えばフラグが1の場合は「tagging」、0の場合は「not tagging」のように文字列に変換します。文字列に変換することで可読性が高まります。
（4）-（b）sum関数(ウィンドウ関数)による集計
sum関数がover句とともに用いられ、ウィンドウ関数（コラム参照）として利用されています。以下のように記述します。
sum ( expr1 ) over ( expr2 )
expr2では通常「PARTITION BY 項目名」などと指定することで、集計対象の行を絞ります。
今回の例ではexpr2が空なので、全ての行を対象として集計を行います。
［コラム］ウィンドウ関数
上記のようにsum()がover句とともに用いられる場合はウィンドウ関数と呼ばれる関数になります。GROUP BYによる集計がグループをまとめる（行が減る）のに対し、ウィンドウ関数は行はそのままで算出結果の列が増えます。別途集計結果を列に追加できるため、分析で活躍します。PostgreSQLのほかOracle・SQL Serverといった主なDBMSに実装されています。
（4）-（c）
ユーザー数を全体のユーザー数で割ることで構成比を求めます。
全体のユーザー数は上記のsumのウィンドウ関数とnuffif関数を用いて集計されています。nullifは指定された2つの引数が等しい場合にNULL値を返します。以下のように記述します。
nullif ( expr1 , expr2 )
まとめ
今回は複数の機能を提供しているアプリケーションにおける、ユーザーアクション分析についてのSQLを紹介しました。
ユーザー行動ログデータがあればSQLで分析が実施できます。データが利用できる場合はぜひ活用してください。
