楽屋

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

はじめに

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

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

やること紹介

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

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

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

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

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

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

環境とデータソース

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最後に

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

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