コミュニティーの皆様の知恵をお借りしたく投稿させていただきます。
実現したいこと
アクセスログデータから、○日に訪問したユーザーがn日後に再訪問している数を、コホート図で表現したいと考えております。
アクセスログデータのイメージ
訪問ユーザーID訪問日A000012020-01-21A000012020-01-22A000022020-01-21::
コホート図のイメージ
列：訪問日からの経過日数 ※ディメンション、不連続
行：訪問日 ※不連続
ラベル：訪問ユーザーIDのユニークカウント
訪問日01
2
34
5
2020-01-1710098827191612020-01-1810296878971NULL2020-01-1998929783NULLNULL2020-01-20999990NULLNULLNULL2020-01-21104100NULLNULLNULLNULL2020-01-2297NULLNULLNULLNULLNULL
※2020-01-22がアクセスログデータ上の最新日の場合のイメージです
※訪問日からの経過日数=0は訪問日当日です
質問
初回訪問日から次の訪問までの経過日数の表現はよく文献でお見かけするのですが、
単に訪問日から次の訪問までの経過日数で表現する方法がわからず、、、
皆様の知見を頂ければ幸いです。どうぞ宜しくお願いいたします。
サンプルデータを添付します。
5 件の回答
サンプルデータの提供ありがとうございます。元メッセージもそのままにしてくださって助かります。
最も安直な方法は自己結合することです。というか、1つの日付に対して複数の経過日数を持つ必要があるため、テーブル単独では不可能と思われます。
自己結合はこんな感じです。訪問者IDはイコールで繋げますが、訪問日は「不等号付き」なので注意してください。
こうすることで、ひとつの日付に対して、それ以降に再訪問する日付が全て個別のレコードとして得られます。
あとは単純に2番目のテーブルの日付から1番目のテーブルの日付を引いて経過日数を求めれば、
ユーザー数を数えることができます。
