コホート分析のためのダッシュボード作成方法

初めて投稿させていただきます。
現在QuickSightにてコホート分析用のダッシュボードを作成しております。
作成方法をご教示いただけますと幸いです。

使用しているデータ

以下のようにユーザID列とそのユーザの既読日時の列を持つデータを対象として分析しております。

ユーザID 既読日時
aaa 2024/12/15
bbb 2024/12/1
aaa 2024/12/9
ccc 2024/11/20
ddd 2024/12/9

実現したいこと

週ごとの既読ユーザーIDのユニーク数を3週先まで追いたいと思っています。
具体的には週ごとのユニークIDを算出し、そのIDが次の週、その次の週…といくつ存在するのかカウントし、以下のように表示をしたいです。

0週間後 1週間後 2週間後 3週間後
2024/11/17 1 0 0 0
2024/11/24 0 0 0 0
2024/12/1 1 0 0 0
2024/12/8 2 1 0 0
2024/12/15 1 0 0 0

具体的な作成例

コホート分析のアウトプット例です。

@A.Su さん、ご質問ありがとうございます。

コホート分析についてはコミュニティで掲載している以下の動画に紹介されていますのでよろしければご覧ください。(英語版になりますがご容赦ください)

具体的には、動画でも紹介されているようにminOver関数を使うことでコホートを特定するフィールドを作成することが可能です。ご提示いただいたデータセットであれば、以下のような計算フィールドになります。

コホート

minOver(既読日時, [ユーザーID], PRE_AGG)

これを以下のようにピボットテーブルに配置し、コホートおよび既読日時の集計をWEEK、ユーザーIDの集計を個別の値をカウントにすることで実現が可能です。

さらに、頂いたようにヘッダを「x週間後」と表示したい場合には、以下の計算フィールドを作成し、既読日時のフィールドと置き換えることで可能です。

経過週数

concat(toString(floor(dateDiff(truncDate('WK', minOver(既読日時, [],PRE_AGG)),既読日時)/7)),'週間後')

こちらで解決した場合には、:white_check_mark:アイコンからSolution付与お願いいたします。

2 Likes

@ytakahr さん

早速のご返信誠にありがとうございます。

共有いただいた動画を見てBIを作成してみました。
x週間後という表記にする方法についても伝授いただきましてありがとうございます。BIのユーザ目線に立った時に見やすくて素敵だと思いました。

共有いただいた動画の方法ですと、最初に既読した週からのユニークユーザ数の経過を確認できます。
一方で毎回ユーザが既読した週からのユニークユーザ数の経過を確認は確認できないため、その方法をご存じでしたらご教示いただきたいです。

@A.Su さん、ご確認ありがとうございます。
失礼しました、確かにご提示いただいたテーブルは各コホートの誕生週を起算として経過週を集計していましたね。その場合、上でお示しした経過週数の計算フィールドを以下のように修正してあげると実現できます。

コホート単位の経過週数

concat(toString(floor(dateDiff(truncDate('WK', minOver(既読日時, [コホート],PRE_AGG)),既読日時)/7)),'週間後')

minOver関数のパーティションを [コホート] に指定することで、経過週数の起算日をコホート単位にしています。

2 Likes

@ytakahr さん

再度ご教示いただきましてありがとうございました。
だいぶしたいことに近づいてきました。

行がホコートとなっていますが、これですと行が初回の既読日時となってしまい、想定されるアウトプットと異なってしまいます。
行のホコートを週単位での日付として、その週からどれくらいユニークなIDが減少しているのかを0週間後から算出したいと思っております。

期待しているイメージとしてはこちらになります。
行が週次の既読日時、列が週の経過として、値として該当行の週のユーザIDのユニーク数を表示させたいです。

もし解決策がお分かりでしたらご共有いただけますと幸いです。

@A.Su さん、改めてご確認ありがとうございます。
以下のような計算フィールドで実現できると思いますがいかがでしょうか。

  1. 計算フィールドを用意

    • コホート
      truncDate('WK', 既読日時)
      
    • 0週間後
      distinct_count(ユーザーID, [ユーザーID, コホート])
      
    • 1週間後
      ifelse(
         lead(コホート, [コホート ASC], 1, [ユーザーID]) =  min(addDateTime(1, 'WK', コホート)), 1, 0
      )
      
    • 2週間後
      ifelse(
         lead(コホート, [コホート ASC], 2, [ユーザーID]) =  min(addDateTime(2, 'WK', コホート)), 1, 0
      )
      
    • 3週間後
      ifelse(
         lead(コホート, [コホート ASC], 3, [ユーザーID]) =  min(addDateTime(3, 'WK', コホート)), 1, 0
      )
      
  2. 以下のようにピボットに配置

    ※コホートの行は折り畳み、+/-ボタンは非表示にすることでイメージに近づけています。

2 Likes

@ytakahr さん

ご教示いただきましてありがとうございました。
作成したいものがとうとう作成することができました!

何度もお聞きしてご苦労をおかけしたかと思いますが、親身になって対応いただき、誠にありがとうございました。

2 Likes