これなら使える!ビッグデータ分析基盤のエコシステム

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

この記事を読むのに必要な時間:およそ 3.5 分

前回では,応用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時間)の時間を要しました。

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

cntavg_path_lengthavg_conversion_time
50531047.180366692.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以下のパス長さのものが多いことが分布よりわかります。

図1 path_count

図1 path_count

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

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

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件以上!)が多く分布していることがわかります。

図2 time_count

図2 time_count

著者プロフィール

高橋達(たかはしとおる)

Treasure Data Inc.でテクニカルサポートエンジニアとして,毎日,日米問わず顧客のサポートを担当。サポートエンジニアのエンジニアとしての地位向上を目指して色々模索中。そのために,秋葉原幻橙館で今日も元気にOSS活動を行っている。

URL:http://toru-takahashi.gitbooks.io/secret-ninja/content/

コメント

コメントの記入