第8回　アクセスログとマスタデータを使った応用KPI分析～実践編

前回では、応用KPI分析の準備としてコンバージョンテーブルを作成するところまでを紹介しました。今回はこのコンバージョンテーブルを使って、実際に応用KPI分析を実行していきます。

コンバージョンパスに着目した応用KPI分析

まず、Webサイト全体のコンバージョン回数とパスの平均パス長、平均コンバージョン時間を算出します。一般に平均パス長とコンバージョン時間は、値が小さいほどコンバージョンへの効率が良いものと判断できます。

SELECT
  COUNT(1) AS cnt,
  AVG(path_length) AS avg_path_length,
  AVG(last_time-landing_time) AS avg_conversion_time
FROM (
  SELECT
    cookie, cv_id,
    MAX(node_id) AS path_length,
    MIN(time) AS landing_time,
    MAX(time) AS cv_time
  FROM conversion_path
  GROUP BY cookie, cv_id
) t1
JOIN (
  SELECT
    cookie,
    cv_id,
    MAX(path) as last_node,
    MAX(time) as last_time
  FROM conversion_path
  WHERE 1 < node_id AND cv_flag = 0
  GROUP BY 1,2
) t2 ON (t1.cookie=t2.cookie AND t1.cv_id=t2.cv_id)

結果例

2015年1月には5053回のコンバージョン（サインアップ）がありました。また、平均的に1047回のページ遷移の末にコンバージョンしました。コンバージョンまでに平均的に66692秒（＝18時間）の時間を要しました。

※ これらの値はサンプルデータに基づくもので、実際の値ではありません。

cnt	avg_path_length	avg_conversion_time
5053	1047.1803	66692.8858

コンバージョンパス長の分布

先ほどは「平均」パス長さを求めましたが、パスの長さを「分布」として見る事でさらに多くの情報を得ることが可能です。

SELECT
  path_length,
  COUNT(1) AS cnt  
FROM (
  SELECT
    cookie, cv_id,
    MAX(node_id) AS path_length
  FROM conversion_path
  GROUP BY cookie, cv_id
) t1
GROUP BY path_length

result = _
result.DataFrame().plot(x='path_length', xlim=(0,200), ylim=(0,200), figsize=(12, 10))

可視化してみると、平均パス長では1000という大きな値を出しましたが、実際には100以下のパス長さのものが多いことが分布よりわかります。

コンバージョン時間の分布

同様にコンバージョン時間の分布も見てみましょう。

SELECT CEIL(cv_time/width)*width AS x, COUNT(1) AS cnt
FROM
(
  SELECT (last_time-landing_time)/3600 AS cv_time
  FROM  (
      SELECT
        cookie, cv_id,
        MAX(node_id) AS path_length,
        MIN(time) AS landing_time,
        MAX(time) AS cv_time
      FROM conversion_path
      GROUP BY cookie, cv_id
    ) temp1
    JOIN (
      SELECT
        cookie,
        cv_id,
        MAX(path) as last_node,
        MAX(time) as last_time
      FROM conversion_path
      WHERE 1 < node_id AND cv_flag = 0
      GROUP BY 1,2
    ) temp2 ON (temp1.cookie=temp2.cookie AND temp1.cv_id=temp2.cv_id)
) t1,
(
  SELECT POW(10,floor(LOG10(MAX( (last_time-landing_time)/3600 )))-1) AS width
  FROM  (
    SELECT
      cookie, cv_id,
      MAX(node_id) AS path_length,
      MIN(time) AS landing_time,
      MAX(time) AS cv_time
    FROM conversion_path
    GROUP BY cookie, cv_id
  ) temp3
  JOIN (
    SELECT
      cookie,
      cv_id,
      MAX(path) as last_node,
      MAX(time) as last_time
    FROM conversion_path
    WHERE 1 < node_id AND cv_flag = 0
    GROUP BY 1,2
  ) temp4 ON (temp3.cookie=temp4.cookie AND temp3.cv_id=temp4.cv_id)
) t2
GROUP BY ceil(cv_time/width)*width
ORDER BY x

result = _
result.DataFrame().plot(x='x', figsize=(12, 10))

コンバージョン時間もパス長と同じように、大きな平均値とは反して10時間までの値（特に10時間以内が3000件以上！）が多く分布していることがわかります。

コンバージョン率（全体）

非コンバージョンを保持していましたので、コンバージョン率の算出が可能です。

SELECT cv_cnt, uncv_cnt, cv_cnt*1.0/(cv_cnt+uncv_cnt) AS cv_ratio
FROM
(
  SELECT
    COUNT(1) as cv_cnt
  FROM (
    SELECT
      cookie,
      cv_id
    FROM conversion_path
    GROUP BY 1,2
  ) t1
) t2 JOIN (
  SELECT
    COUNT(1) as uncv_cnt
  FROM (
    SELECT cookie
    FROM non_conversion_path
    GROUP BY 1
  ) t3
) t4 ON 1=1

コンバージョン率は9％と、なかなか良い数字を出していることがわかります。

cv_cnt	uncv_cnt	cv_ratio
5359	46500	0.10333

パス類型

さて今まで全体のコンバージョンパスを見ていましたが、ここではパスを類別して、それぞれの特徴を持つパスでのコンバージョン率を見ていくことも可能です。たとえば下記のようなシチュエーションごとに集計することで、パスを元にしたユーザの動向を分析することが可能です。

スルーコンバージョン

自社のコンバージョンにつながりそうなページを取り上げて、そのページを通って（スルーして）コンバージョンした率を見てみます。ここでは、"case-studies"（＝事例紹介）のページを通ってから、サインアップしたユーザの割合を見てみます。

SELECT cv_cnt, uncv_cnt, cv_cnt*1.0/(cv_cnt+uncv_cnt) AS cv_ratio
FROM
(
  SELECT
    COUNT(1) as cv_cnt
  FROM
  (
  SELECT
    cookie, cv_id
  FROM conversion_path
  WHERE path = '/treasuredata.com/jp/case-studies'
  GROUP BY 1,2) t1
) cv,
(
  SELECT COUNT(1) AS uncv_cnt FROM (
  SELECT cookie
  FROM non_conversion_path
  WHERE path = '/treasuredata.com/jp/case-studies'
  GROUP BY 1 ) t2
) uncv

こうしてみると、事例紹介のページを見たユーザのユーザのコンバージョン率は90％にも及びます。

cv_cnt	uncv_cnt	cv_ratio
3338	303	0.91678

ランディングコンバージョン

ランディングページは、企業にとってユーザが到達する最初のページです。たとえば、プレスリリースが挙げられます。プレスリリースは、自社ページや一般的なメディアなどに大きく掲載すべき重要なニュースです。プレスリリースを見てサイトを訪れてサインアップしてくれる人が多いことがわかれば、今後掲載するメディアを増やしたり、よりいっそうの活用を考えていけることになります。

SELECT cv_cnt, uncv_cnt, cv_cnt*1.0/(cv_cnt+uncv_cnt) AS cv_ratio
FROM
(
  SELECT
    COUNT(1) as cv_cnt
  FROM
  (
      SELECT
        cookie, cv_id, time,
        MIN(path) AS landing_path
      FROM conversion_path
      GROUP BY cookie, cv_id, time
  ) t1
  WHERE landing_path = '/treasuredata.com/jp/case-studies'
) cv,
(
  SELECT COUNT(1) AS uncv_cnt
  FROM (
      SELECT
        cookie, time,
        MIN(path) AS landing_path
      FROM non_conversion_path
      GROUP BY cookie, time
  ) t2
  WHERE landing_path = '/treasuredata.com/jp/case-studies'
) uncv

cv_cnt	uncv_cnt	cv_ratio
176	0	1.00000

この結果は非常に有意義で、press-releases からサイトに入って来た人はもれなくサインアップしてくれていることがわかります。

直前コンバージョン

コンバージョンの直前のページは、コンバージョンパスの中でも最も影響度の高いノードと見なすことができ、他のものよりも重要な指標になるかもしれません。

SELECT
  last_node,
  COUNT(1) AS cnt
FROM (
  SELECT
    cookie,
    cv_id,
    MAX(path) as last_node
  FROM conversion_path
  WHERE 1 < node_id AND cv_flag = 0
  GROUP BY 1,2
) t1
GROUP BY 1

result = _
r = result.DataFrame().set_index("last_node")
r.plot(kind='pie',  y='cnt', figsize=(15, 15))

last_node	cnt
/treasuredata.com/jp/home	3379
/treasuredata.com/en/home	1405
/treasuredata.com/jp/products	98
/treasuredata.com/en/press-releases	34
/treasuredata.com/en/learn	33

基本/応用KPI分析まとめ

第6回から3回に渡って、SQLを使ったデータ分析を進めてきました。SQLだけでもWebページ全体を表す指標を収集する基本KPI分析やユーザのパスに着目した応用KPI分析など、さまざまな分析を行うことができたかと思います。こうしたユーザの行動に着目した分析では、データ量が膨大になり、通常のRDBでは難しい処理が多々あります。しかし、連載の第1回で紹介したビッグデータ分析エンジンを使うことによってより手軽に応用KPI分析を進めることができるようになります。

さらに、こうした集計処理を自動化し、実運用していくためには、データ収集やデータ集計を定期的に更新していくためのツールが重要となります。

次回は、そのためワークフロー管理ツールについて紹介していきます。