SQLTech Blog　

連載：SQLのウィンドウ関数を利用した分析―継続率を利用したアクセスログの分析

はじめに

みなさん、こんにちは! DATUM STUDIOの宇佐見です。

普段の業務ではデータアナリストとして、他の企業さまから依頼を受けてデータ分析のお手伝いやデータ分析基盤の構築をさせていただいております。SQLを利用した様々な分析に携わることが多いため、そのノウハウを前回に引き続き紹介できればと思います。

やること紹介

本記事では3回にわけてウィンドウ関数を利用したSQLのケーススタディを紹介いたします。実際のアクセスログを利用して、実践ですぐ活用できることがコンセプトです。前提知識としてはSQLを利用した基礎的な集計を行えるレベルを想定しています。

第2回目は、「継続率を利用したアクセスログの分析」です。ユーザごとの週次のアクセス状況から新規、継続、復活のユーザの比率を求めます。実務でも非常に多く利用されていますが、ウィンドウ関数を利用すると比較的簡単に求めることができます。

以下を新規、継続、復活、離脱の定義とします。

  • 新規:以前の週に利用がないが、集計対象の週には利用があるユーザ
  • 継続:集計対象の前週に利用があり、集計対象の週にも利用があるユーザ
  • 復活:集計対象の前々週には利用があり前週に利用がないが、集計対象の週にも利用があるユーザ

新規、継続、離脱のイメージ図

なお集計を開始した週については全ユーザが新規扱いとなります。

環境とデータソース

実行環境は前回に引き続きAWSのRedshiftを利用します。

データソースに関しても前回と同様にDATUMSTUDIOのホームページのアクセスログを利用します。アクセスログにおけるIPアドレスをハッシュ化し、それをユーザIDと見なしています。

以下のようなデータ形式となっております。

     access_time     |             user_id
---------------------+----------------------------------
 2017-05-21 03:11:18 | 3f3fcac868404d0ae7e8ad90be6fc4d8
 2017-05-21 03:11:21 | 3f3fcac868404d0ae7e8ad90be6fc4d8
 2017-05-21 03:11:21 | 3f3fcac868404d0ae7e8ad90be6fc4d8
 2017-05-21 03:23:50 | e07e844ba0c20d195f4701fb4f14451e
 2017-05-21 03:24:26 | efcdf099ce3f9be9a0f9444f06e2679d
 2017-05-21 03:24:27 | efcdf099ce3f9be9a0f9444f06e2679d
 2017-05-21 03:24:28 | efcdf099ce3f9be9a0f9444f06e2679d
 2017-05-21 03:28:11 | 5cba30f1752bae2a77f05007311b95c3
 2017-05-21 03:28:11 | 5cba30f1752bae2a77f05007311b95c3
 2017-05-21 03:28:13 | 5cba30f1752bae2a77f05007311b95c3
 2017-05-21 03:28:19 | 50dda73283d5a5d3bdc3e01c787a03a0
 2017-05-21 03:28:22 | 5da679a7d0ce86eed996bb4c568765f7

ウィンドウ関数の説明については前回の投稿を参照してください。

・連載：SQLのウィンドウ関数を利用した分析―セッションを利用したアクセスログの分析

継続率を利用したアクセスログの分析

前回と同様に本記事では、簡単なクエリからスタートしその過程を説明しながら、ゴールである「継続率を利用したアクセスログの分析」のクエリを完成させます。

まずはページビューごとのデータから、ユーザごとの週次のアクセス情報に集約します。DATE_TRUNC関数を利用して日付をその週の月曜日になるよう切り捨てます。更にCAST関数でTIMESTAMP型からDATE型に変換します。

SELECT
  CAST(DATE_TRUNC('week', access_time) AS DATE) AS access_week,
  user_id,
  COUNT(*)
FROM
  datum_access_log
GROUP BY
  CAST(DATE_TRUNC('week', access_time) AS DATE),
  user_id
;

access_week |             user_id              | count
-------------+----------------------------------+-------
2017-05-15  | 3f3fcac868404d0ae7e8ad90be6fc4d8 |   135
2017-05-15  | 94206ce9bf9de4874af016067d414d7e |     1
2017-05-15  | d1522ce8a41506346c24c6b1579566dd |    25
2017-05-15  | e07e844ba0c20d195f4701fb4f14451e |    14
2017-05-15  | efcdf099ce3f9be9a0f9444f06e2679d |     3
2017-05-15  | 5cba30f1752bae2a77f05007311b95c3 |     3
2017-05-15  | 50dda73283d5a5d3bdc3e01c787a03a0 |     4
2017-05-15  | 5da679a7d0ce86eed996bb4c568765f7 |    14

先程のクエリは副問い合わせにまとめておきます。

続いてウィンドウ関数を利用して各ユーザが初めて利用した週、前回利用した週を取得します。初めて利用した週についてはFIRST_VALUE関数を、前回利用した週についてはLAG関数を用います。LAG関数の結果NAになることを防ぐためにNVL関数も適応しました。

WITH weekly_access_data AS (
  SELECT
    CAST(DATE_TRUNC('week', access_time) AS DATE) AS access_week,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('week', access_time) AS DATE),
    user_id
)
SELECT
  *,
  FIRST_VALUE(access_week) OVER(PARTITION BY user_id) AS first_access_week,
  NVL(LAG(access_week) OVER (PARTITION BY user_id ORDER BY access_week), '1970-01-01') AS previous_access_week
FROM
  weekly_access_data
;

access_week |             user_id              | count | first_access_week | previous_access_week
-------------+----------------------------------+-------+-------------------+----------------------
2017-06-05  | 00001cc54dfaf28cd922158caaa3ad1f |    25 | 2017-06-05        | 1970-01-01
2017-06-12  | 00023345b78c5b5ad98aaffc99a9528e |    21 | 2017-06-12        | 1970-01-01
2017-06-05  | 0004e74949a9e134f8e14638c181dd95 |     3 | 2017-06-05        | 1970-01-01
2017-06-19  | 001533de27d9a51c5359d885a59c6d58 |     1 | 2017-05-29        | 2017-06-12
2017-06-12  | 001533de27d9a51c5359d885a59c6d58 |     3 | 2017-05-29        | 2017-06-05
2017-06-05  | 001533de27d9a51c5359d885a59c6d58 |     1 | 2017-05-29        | 2017-05-29
2017-05-29  | 001533de27d9a51c5359d885a59c6d58 |     2 | 2017-05-29        | 1970-01-01
2017-05-29  | 001e411e16969083a7abf1434dc77500 |     3 | 2017-05-29        | 1970-01-01
2017-06-05  | 002e402a04b53a4f6d500078f9deb99b |     3 | 2017-06-05        | 1970-01-01

初めて利用した週と今週を比較して、等価ならばTRUE、非等価ならばFALSEを記録する新規フラグを立てます。

同様に前回利用した週と前週を比較して、等価ならばTRUE、非等価ならばFALSEを記録する前週利用フラグを立てます。


WITH weekly_access_data AS (
  SELECT
    CAST(DATE_TRUNC('week', access_time) AS DATE) AS access_week,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('week', access_time) AS DATE),
    user_id
)
SELECT
  *,
  access_week = FIRST_VALUE(access_week) OVER(PARTITION BY user_id) AS first_access_flag,
  DATEADD(week, -1, access_week) = NVL(LAG(access_week) OVER (PARTITION BY user_id ORDER BY access_week), '1970-01-01') AS previous_access_flag
FROM
  weekly_access_data
;

access_week |             user_id              | count | first_access_flag | previous_access_flag
-------------+----------------------------------+-------+-------------------+----------------------
2017-06-05  | 00001cc54dfaf28cd922158caaa3ad1f |    25 | t                 | f
2017-06-12  | 00023345b78c5b5ad98aaffc99a9528e |    21 | t                 | f
2017-06-05  | 0004e74949a9e134f8e14638c181dd95 |     3 | t                 | f
2017-05-29  | 0012c93b674f56d085fd2b17161829ab |     3 | t                 | f
2017-06-19  | 001533de27d9a51c5359d885a59c6d58 |     1 | f                 | t
2017-06-12  | 001533de27d9a51c5359d885a59c6d58 |     3 | f                 | t
2017-06-05  | 001533de27d9a51c5359d885a59c6d58 |     1 | f                 | t
2017-05-29  | 001533de27d9a51c5359d885a59c6d58 |     2 | t                 | f
2017-05-29  | 001e411e16969083a7abf1434dc77500 |     3 | t                 | f

可読性を上げるために先程のクエリを副問い合わせにまとめます。そして新規フラグ、前週利用フラグを利用して継続フラグ、復活フラグを立てます。継続フラグは前週利用フラグがTRUEの場合に立てます。復活フラグは新規フラグがFALSE、前週利用フラグがFALSEの場合に立てます。これでユーザ1人ごとの1週間の動向が分かります。

WITH weekly_access_data AS (
  SELECT
    CAST(DATE_TRUNC('week', access_time) AS DATE) AS access_week,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('week', access_time) AS DATE),
    user_id
), weekly_access_flag AS (
  SELECT
    *,
    access_week = FIRST_VALUE(access_week) OVER(PARTITION BY user_id) AS first_access_flag,
    DATEADD(week, -1, access_week) = NVL(LAG(access_week) OVER (PARTITION BY user_id ORDER BY access_week), '1970-01-01') AS previous_access_flag
  FROM
    weekly_access_data
)
SELECT
  access_week,
  user_id,
  first_access_flag,
  previous_access_flag AS countinue_access_flag,
  (first_access_flag = FALSE AND previous_access_flag = FALSE) AS return_access_flag
FROM
  weekly_access_flag
;

access_week |             user_id              | first_access_flag | countinue_access_flag | return_access_flag
-------------+----------------------------------+-------------------+-----------------------+--------------------
2017-06-05  | 00001cc54dfaf28cd922158caaa3ad1f | t                 | f                     | f
2017-06-12  | 00023345b78c5b5ad98aaffc99a9528e | t                 | f                     | f
2017-06-05  | 0004e74949a9e134f8e14638c181dd95 | t                 | f                     | f
2017-05-29  | 0012c93b674f56d085fd2b17161829ab | t                 | f                     | f
2017-06-19  | 001533de27d9a51c5359d885a59c6d58 | f                 | t                     | f
2017-06-12  | 001533de27d9a51c5359d885a59c6d58 | f                 | t                     | f
2017-06-05  | 001533de27d9a51c5359d885a59c6d58 | f                 | t                     | f
2017-05-29  | 001533de27d9a51c5359d885a59c6d58 | t                 | f                     | f
2017-05-29  | 001e411e16969083a7abf1434dc77500 | t                 | f                     | f

最後に新規フラグ、継続フラグ、復活フラグごとに週次で集約を行います。

WITH weekly_access_data AS (
  SELECT
    CAST(DATE_TRUNC('week', access_time) AS DATE) AS access_week,
    user_id,
    COUNT(*)
  FROM
    datum_access_log
  GROUP BY
    CAST(DATE_TRUNC('week', access_time) AS DATE),
    user_id
), weekly_access_flag AS (
  SELECT
    *,
    access_week = FIRST_VALUE(access_week) OVER(PARTITION BY user_id) AS first_access_flag,
    DATEADD(week, -1, access_week) = NVL(LAG(access_week) OVER (PARTITION BY user_id ORDER BY access_week), '1970-01-01') AS previous_access_flag
  FROM
    weekly_access_data
), weekly_access_flag_2 AS (
  SELECT
    access_week,
    user_id,
    first_access_flag,
    previous_access_flag AS countinue_access_flag,
    (first_access_flag = FALSE AND previous_access_flag = FALSE) AS return_access_flag
  FROM
    weekly_access_flag
)
SELECT
  access_week,
  SUM(CASE WHEN first_access_flag = TRUE THEN 1 ELSE 0 END) first_access_user_count,
  SUM(CASE WHEN countinue_access_flag = TRUE THEN 1 ELSE 0 END) countinue_user_count,
  SUM(CASE WHEN return_access_flag = TRUE THEN 1 ELSE 0 END) return_user_count
FROM
  weekly_access_flag_2
GROUP BY
  access_week
;

access_week | first_access_user_count | countinue_user_count | return_user_count
-------------+-------------------------+----------------------+-------------------
2017-05-15  |                    XXXX |                  XXX |               XXX
2017-05-22  |                    XXXX |                  XXX |               XXX
2017-05-29  |                    XXXX |                  XXX |               XXX
2017-06-05  |                    XXXX |                  XXX |               XXX
2017-06-12  |                    XXXX |                  XXX |               XXX

※具体的なユーザ数が分かってしまうため、数字は伏せさせていただきます。

離脱数についてはエクセルなどを用いて「前週の総利用者数 – 今週の継続者数」から算出することをオススメします。 (クエリでは、データのない項目を集計することは難しいため)

最後に

今回もシンプルなクエリで表現することができました。次回はソーシャルゲームで利用されるFQ5の集計を紹介できればと思います。

データはあるけど集計環境が作れない、複雑な集計を計画できても実行できないなどでお困りの場合は、ぜひDATUM STUDIOにお声がけください。

このページをシェアする：


DATUM STUDIOは、クライアントの事業成長と経営課題解決を最適な形でサポートする、データ・ビジネスパートナーです。
データ分析の分野でお客様に最適なソリューションをご提供します。まずはご相談ください。

Contact

サービス・研修・アドバイザリーに関するお問い合わせはこちら

Explore Jobs

採用に関する情報はこちら

関連記事

前へ 次へ