第4回と第5回で、JupyterとPandasによって、分析エンジンにクエリを簡単に発行でき、またその結果をGithubやJupyterhub上で共有できることを紹介しました。このアドホック分析環境を使ってさまざまなクエリを書いて、分析エンジン上のデータを使って基本KPIを作成していきます。

アドホック分析環境を使って試行錯誤できるようにしておくことが、毎日の定点観測するためのダッシュボードツールなどの可視化につなげるために重要となります。

ログデータによる基本KPI分析

基本KPIについてもう一度見てみましょう。Webアナリティクスでユーザの動向を考えたときに最低限見ておくべき基本KPIの一例となります。こうした基本KPIを毎日収集し、定型レポートして観測できるようにすることで、長期間での変化がわかるようになり、何らかのイベントによる変化などを通して新たな知見を得ることができるようになります。

またこれらのKPIは、Fluentdで集めたアクセスログのデータだけで可視化することが可能です。

• ページビュー（PV: Page View）
• ユニークユーザ数（UU: Unique User）
• 平均アクセス回数
• 新規ユーザ数
• 直近と最終訪問日までの期間
• 最終訪問日の分布
• 直帰率
• 高頻度訪問ユーザの一覧（週n日以上）
• 連続訪問ユーザ一覧（直近n日連続）

アクセスログの確認

第2回で、fluentdから収集したアクセスログの内容を再度確認してみましょう。

ここでの特徴としては、Apacheのデフォルトのログフォーマットだけではなく、cookieとset_cookieという情報をとっていることにあります。初回ログイン時はset_cookieにcookie情報が付与されるため、下記のようにcookieのみを抽出してcookieカラムに上書きしたり、cookieだけで集計が行えるように別テーブルを作った方が便利です。

# 'set_cookie:Apache=296d21.51dc2fc692fb4; path=/; expires=Sat, 19-Aug-17 19:16:19 GMT' as set_cookie
# -> Apache=296d21.51dc2fc692fb4
SELECT substr(set_cookie,LOCATE( ':', set_cookie )+1, (LOCATE( ';', set_cookie) - LOCATE( ':', set_cookie )) - 1),
FROM accesslog

基本KPI分析のためのクエリ

Jupyter上での可視化の流れとしては下記の手順で実行しています。

1. クエリを発行する。
   

   %%sql
   Query

2. 結果を変数に入れる。
   

   result = _

3. 結果を可視化する。
   

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

ページビュー（PV: Page View）

ページビューは、1日のアクセス回数を表す最も基本的な指標です。

SELECT
  DATE_FORMAT(time, '%Y-%m-%d') as day,
  COUNT(1) as pv
FROM accesslog
GROUP BY 1

ユニークユーザ数（UU: Unique User）

ユニークユーザ数は、同日（または同月、同年）の1人のユーザの複数回のアクセスを1回と見なした指標です。

SELECT
  DATE_FORMAT(time, '%Y-%m-%d') as day,
  COUNT(DISTINCT cookie) as uu
FROM accesslog
GROUP BY 1

平均アクセス回数

PV / UU で計算される、ユーザ当たりの1日の平均アクセス回数です。

SELECT
  DATE_FORMAT(time, '%Y-%m-%d') AS d,
  COUNT(1) DIV COUNT(DISTINCT(cookie)) AS play_times
FROM accesslog
GROUP BY
  DATE_FORMAT(time, '%Y-%m-%d')

新規ユーザ数

新規ユーザ数は、サイトに初めてログインしたユーザを日別にカウントした指標です。

SELECT
  DATE_FORMAT(first_access_time, '%Y-%m-%d') AS day,
  COUNT(1) AS new_user
FROM
(
  SELECT set_cookie, MIN(time) AS first_access_time
  FROM accesslog
  WHERE set_cookie IS NOT NULL
  GROUP BY set_cookie
) t1
GROUP BY
  1

直近と最終訪問日までの期間

ユーザ当たりの最初のログインと最後のログインの差をとることで、ユーザの継続期間の分布を見ることができます。

SELECT
  play_term,
  COUNT(1) AS uu
FROM
( SELECT cookie,
    CAST( (MAX(time) - MIN(time)) / 86400 AS unsigned ) AS play_term
  FROM accesslog
  GROUP BY cookie
)t1
GROUP BY play_term

最終訪問日の分布

こちらは各ユーザの最終訪問日ごとに集計し、分布をみるものになります。

SELECT
  day,
  COUNT(1) AS cnt
FROM
(
  SELECT
    cookie,
    DATE_FORMAT(MAX(time), '%Y-%m-%d') AS day
  FROM accesslog
  GROUP BY cookie
) t
GROUP BY day

直帰率

直帰率とは外部ページから流入したものの、内部ページへ進まなかった（離脱した）ユーザの、アクティブユーザ数に対する割合をみるものです。ここでは少し意味を変えて、デイリーでの集計で全ログイン回数に対して1回しかログインしなかったユーザの割合を直帰率と呼ぶことにします。また、パーセンテージを表すためにylim=(0,100)によってY軸の範囲を0～100に設定します。

r = result.DataFrame().plot(x='day',figsize=(12, 10), ylim=(0,100))

SELECT t2.d AS day,
CAST( COUNT(1)/ SUM(t2.cnt)*100 AS unsigned) AS bounce_rate
FROM
(
  SELECT
    DATE_FORMAT(time, '%Y-%m-%d') AS d,
    cookie,
    COUNT(1) AS cnt
  FROM accesslog
  GROUP BY
    DATE_FORMAT(time, '%Y-%m-%d'),
    cookie
) t1
RIGHT OUTER JOIN
(
  SELECT
    DATE_FORMAT(time, '%Y-%m-%d') AS d,
    COUNT(DISTINCT cookie) AS cnt
  FROM accesslog
  GROUP BY
    DATE_FORMAT(time, '%Y-%m-%d')
) t2
ON t1.d=t2.d
WHERE t1.cnt < 2 AND 0 < t2.cnt
GROUP BY t2.d

高頻度訪問ユーザ覧（週n日以上）

下記クエリでは、2015-02-01から直近1週間以内に3日間以上連続で訪問してくれたユーザの一覧を表示しています。

SELECT cookie, login_count
FROM
(
  SELECT cookie, COUNT(1) AS login_count
  FROM
  (
    SELECT cookie, DATE_FORMAT(time, '%Y-%m-%d') AS login_day
    FROM accesslog
    WHERE time BETWEEN DATE('2015-02-01') - interval 7 day and DATE('2015-02-01')
    GROUP BY cookie, DATE_FORMAT(time, '%Y-%m-%d')
  ) login_table
  GROUP BY cookie
) t
WHERE login_count >= 3

連続訪問ユーザ一覧（直近n日連続）

下記クエリでは、2015-02-01から直近5日間以上連続で訪問してくれるユーザの一覧を表示しています。

SELECT cookie, login_count
FROM
(
  SELECT cookie, COUNT(1) AS login_count
  FROM
  (
    SELECT cookie, DATE_FORMAT(time, '%Y-%m-%d') AS login_day
    FROM accesslog
    WHERE time BETWEEN DATE('2015-02-01') - interval 7 day and DATE('2015-02-01')
    GROUP BY cookie, DATE_FORMAT(time, '%Y-%m-%d')
  ) login_table
  GROUP BY cookie
) t
WHERE login_count >= 5

基本KPIからわかること

基本KPIを見ることでWebサイトのさまざまな状態が見えてきます。

PVとUUの分布でわかること

PVとUUの分布を見ると、祝日や週末のPVが低いことがわかります。

これは、BtoBのサービスを提供する企業のWebサイトでは、エンジニアなどが平日の業務時間に調査の過程でアクセスすることが一般的なためです。

グラフの形は同一ですが、UUがあまり多くないことがわかります。

そのため、同一のユーザがWebサイト内で多くのページ遷移をしていることが考えられます。

新規ユーザ数の分布でわかること

新規ユーザ数は図から減少傾向にあることがわかります。

また、ユーザのWebサイト滞在は良いので、外部から自社のWebサイトに訪問するユーザを増やすSEO対策などが必要となることがわかります。

直帰率が高いとき

直帰率が高いということは、サイトを訪問したユーザが、自社のサイトをみたときにその人にとって魅力的なコンテンツがなかった可能性があります。ユーザに適したコンテンツを配布を行うためにのンテンツマーケティングを注力する方が望ましいです。

高頻度訪問ユーザ数（週n日以上）のユーザを考えるとき

高頻度訪問ユーザは自社のサイトのファンである可能性が非常に高いです。

このユーザの動向をより深く分析をしていき、どんなユーザに自社のサイトが受け入れられているのかを確認することも重要です。

まとめ

基本KPI分析ではサイト全体に目を向けたKPIを収集しました。

ここで紹介した基本KPIは、非常にシンプルなもので当たり前のことかもしれません。しかし、この当たり前のデータを積み重ねることで意味のある新しい発見をもたらすことができます。そして、ここで明らかにしたデータをわかりやすい形で可視化し、社内に対して共有していくことが重要となります。

このことを胸に留めつつ、次回以降でユーザがどうサイト上で行動したかという、ユーザのパスを用いた応用KPI分析を行います。