SQL

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

はじめに

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

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

やること紹介

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

第3回目は、「FQ5を利用したアクセスログの分析」です。ゲームやアプリの業界においてしばしば使われる指標の1つにFQ5があります。(5の代わりに7や10などを利用するバリエーションもあります。)FQ5は当日を含む過去5日間に何回利用したかをユーザごとにカウントし、日付ごとに集計します。単純なユーザ数では広告や曜日の効果を受けてしまうため、この指標を用いると影響を減らすことができます。詳細については以下の資料が参考になるかと思います。

FQ5もウィンドウ関数を利用すると比較的簡単に求めることができます。

環境とデータソース

実行環境は前回に引き続きAWSのRedshiftを利用します。データソースに関しても前回と同様にDATUMSTUDIOのホームページのアクセスログを利用します。アクセスログにおけるIPアドレスをハッシュ化し、それをユーザIDと見なしています。以下のようなデータ形式となっております。

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

FQ5を利用したアクセスログの分析

FQ5ということでユーザ1人あたりの1日のアクセスのデータがベースとなります。前回と同じように1人あたりの1日のアクセス回数を以下のクエリで集計します。

上記のクエリを副問い合わせにまとめます。そのユーザの対象日から1回前の利用日を求めます。前回、前々回に引き続きLAG関数を利用しています。LAG関数はウィンドウ関数の中で利用頻度の高いものの1つです。

同様に2~4回前の利用日も求めます。

1~4回前の利用日が4日前の日付以内かどうか判定します。このあとに集計しやすくなるようbool型からint型へ変換しておきます。

ウィンドウ関数を使った処理は複雑なことができる分、1行あたりが長くなりがちです。そのため本当は副問い合わせにする必要がない処理についても、副問い合わせにすることで可読性を上げることができます。

そして1人ごとに集計日から4日前以内の利用日数を集計します。対象日には必ず利用しているため、1を加えて5日間の利用回数を計算します。

最後に上記クエリを副問い合わせにまとめ、対象日と5日間の利用回数ごとにユーザ数を集約すれば完成です。

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

最後に

3回に渡った連載も今回が最後です。この連載を通してウィンドウ関数を利用すれば複雑な集計も簡単できることを分かっていただけたならば幸いです。

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

12/11(水)セミナーを開催いたします!



こんなお悩みや課題はありませんか?
✓データ活用するためには何を始めればよいかわからない
✓データマネジメントを行うために必要なことが知りたい
✓実績のある会社にAI構築をお願いしたいがなかなか見つからない

こんなお悩みをお持ちの方に向けて、12月11日(水)表参道にて、無料セミナーを開催いたします。
今回はSupership グループのSupership株式会社・DATUM STUDIO株式会社・ちゅらデータ株式会社の3社で開催します。

▼▼▼▼詳細は以下バナーをクリック!▼▼▼▼

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