各暦月のユーザー数を遡って取得

データとレポート
1

こんにちは！私たちはDiscourseのクラウドホスト版を利用しており、プラットフォームを約1年半稼働させています。プラットフォームの導入と利用に関する履歴メトリクスをいくつか取得したいと考えています。管理レポートタブで利用可能な「サインアップ」レポートはすでに確認していますが、毎月のアクティブユーザーの総数も表示できるようにしたいです。これを実現する方法について、何かご存知の方はいらっしゃいますか？レポートやデータエクスプローラーには適切なものが見つかりません。

2

このデータエクスプローラークエリは、ユニークな月間ユーザー訪問数を返します。それでよろしいでしょうか？

--[params]
-- date :start_date = 2021-01-01
-- date :end_date = 2021-12-31
-- string_list :group_name = staff,admins

WITH not_allowed_groups AS (
    SELECT gu.user_id FROM group_users gu
    INNER JOIN groups g ON (g.id = gu.group_id)
    WHERE g.name IN (:group_name)
)

SELECT
    to_char(date_trunc('month', uv.visited_at)::DATE,'YYYY-MM') AS "month",
    date_trunc('month', uv.visited_at)::DATE AS date_user_visits,
    COUNT(distinct uv.user_id) AS qtt_user_visits
FROM user_visits uv
WHERE
    uv.user_id NOT IN (SELECT user_id FROM not_allowed_groups)
    AND uv.visited_at::DATE BETWEEN :start_date AND :end_date
GROUP BY date_trunc('month', uv.visited_at)::DATE
ORDER BY date_trunc('month', uv.visited_at)::DATE
3

ありがとうございます。しかし、正確にはそうではありません。プラットフォームを訪問したかどうかに関わらず、各暦月の末時点でアクティブ（登録済みで、非アクティブ化または削除されていない）なユーザー数を知りたいのです。

サインアップのグラフを見ていますが、ある月のサインアップ数の合計は、毎月非アクティブ化による自動的な非アクティブ化もあるため、ユーザーベースを反映していません。特定の日の過去のユーザーリストのエクスポートにアクセスするのが唯一の方法でしょうか？しかし、クラウドホストであるため、それにアクセスできるかどうかわかりません。Discourseにリクエストする必要がありますか？

4

私はDiscourseです。:slight_smile:

ここで質問するか、直接 team@discourse.org までメールでご連絡ください。よりSQLの知識がある者がお手伝いします。

「いいね！」 4
5

フォーラム初心者なので気づきませんでした。ありがとうございます！
クラウドホストサイトの過去のバックアップをリクエストに応じて提供していただけますか？

「いいね！」 1
6

事業継続性の目的でバックアップを保存しており、緊急時には復元できますが、それ以外の目的ではありません。

「いいね！」 1
7

承知いたしました。さらにアイデアがあります。AIアシスタントに尋ねました。

月ごとのアクティブユーザーのSQLクエリ AI

HAWK:
指定された月内に登録された、まだアクティブなユーザーアカウントのリストを返すデータエクスプローラークエリを作成してください。

DiscourseHelper:

検索中

検索中: 特定の月に登録されたアクティブユーザーを見つけるためのDiscourse Data Explorerクエリ

読み込み中

読み込み中: 特定の月のためのアクティブユーザー

指定された月内に登録され、現在もアクティブなユーザーアカウントのリストを返すデータエクスプローラークエリを作成するには、以下のSQLクエリを使用できます。このクエリは、アクティブユーザーとは、指定された期間内に投稿または返信を行ったユーザーであると想定しています。

--[params]
-- int :month = 5
-- int :year = 2023

SELECT u.id, u.username, u.created_at, u.last_seen_at
FROM users u
WHERE u.active = TRUE
  AND EXTRACT(MONTH FROM u.created_at) = :month
  AND EXTRACT(YEAR FROM u.created_at) = :year
  AND u.last_seen_at IS NOT NULL -- アクティブとは最近サイトにアクセスしたことを意味すると仮定
ORDER BY u.created_at

クエリの説明:

  • :month および :year は、特定の月と年をフィルタリングするために設定できるパラメータです。
  • u.active = TRUE は、ユーザーアカウントがまだアクティブであることを保証します。
  • EXTRACT(MONTH FROM u.created_at) = :month および EXTRACT(YEAR FROM u.created_at) = :year は、登録日を指定された月と年にフィルタリングします。
  • u.last_seen_at IS NOT NULL は、ユーザーがいつでもログインしていればアクティブとみなします。
  • 結果には、ユーザーID、ユーザー名、登録日、最終ログイン日が含まれます。

必要に応じてパラメータとフィールドを調整してください。さらにサポートが必要な場合はお知らせください！

何か見落としている点があれば、さらにコンテキストを提供してください。

「いいね！」 3
8

これは素晴らしいクエリの説明ですが、残念ながら求めているものではないようです。

例えば、パラメータに01と2024を設定すると、今年の1月を見ることになります。
出力には、1月に登録して現在もアクティブなユーザーがすべて含まれるということでしょうか？合っていますか？

しかし、私たちが求めているのは、ローンチ以来毎月アクティブだった（現在アクティブなユーザーではなく）ユーザー数です。そうすれば、ユーザーベースが月ごとにどのように変化したかをプロットして示すことができます。毎月の終わりには、私たちにとって次のようになります。

アクティブユーザー = 前月の値 + 新規登録 - 新規削除 - 新規非アクティブ化

Data Explorerでは、非アクティブ化やアクティブ変数に関連付けられた日付に関するものは何も見当たりません。最も可能性の高い方法は、スタッフアクションログを使用して日付別に分析することですが、エクスポート機能が機能していないと思います。スタッフアクションログの結果をエクスポートすると、奇妙なことにスタッフアクションのテーブルではなく、さまざまな投稿の内容のスプレッドシートが得られます。もしこれを手伝っていただければ、別の解決策になるかもしれません！ :slight_smile: 他にも同じような問題を抱えている人はいますか？

10

興味深い質問ですね。

まず、こちらの例を見てみました。しかし、これは削除されたユーザーを無視しています。その時点で登録されており、現在も存在するユーザーの数しか取得できず、その間に削除されたユーザーは含まれません。

そこで、その月に最後に登録されたユーザーのIDを取得するというアイデアを考えました。これはその時点でのユーザー数の最大値になります。その後、削除されたユーザー数をこの値から差し引くことができます。ただし、ボットアカウント（例：forum-helper）はIDが負の値ですが、削除されている場合はカウントされます。（しかし、これはおそらく些細な誤差でしょう）。私のクエリは以下の通りです。

-- [params]
-- date :start_date
-- date :end_date


WITH month_dates AS (
    -- 開始日と終了日の間の月末日を生成する
    SELECT DATE_TRUNC('month', generate_series)::date + INTERVAL '1 month' - INTERVAL '1 day' AS month_end
    FROM generate_series(:start_date::date, :end_date::date, '1 month'::interval)
),
recent_user AS (
    -- 各月末日について、その日より前に作成された最も新しいユーザーを見つける
    SELECT md.month_end,
           (SELECT id
            FROM users u
            WHERE u.created_at < md.month_end
            ORDER BY u.created_at DESC
            LIMIT 1) AS user_max_id
    FROM month_dates md
),
cumulative_deletion_count AS (
    -- 各月末日までの累積削除数を計算する
    SELECT md.month_end,
           (SELECT COUNT(*)
            FROM user_histories uh
            WHERE uh.action = 1 AND uh.updated_at < md.month_end) AS deletions_count
    FROM month_dates md
)
SELECT
    md.month_end,
    ru.user_max_id,
    cdc.deletions_count,
    ru.user_max_id - cdc.deletions_count AS number_of_users
FROM
    month_dates md
LEFT JOIN recent_user ru ON md.month_end = ru.month_end
LEFT JOIN cumulative_deletion_count cdc ON md.month_end = cdc.month_end
ORDER BY md.month_end

しかし、このクエリでは（非）アクティブ化を考慮していません。これらも user_histories テーブルに保存されています。しかし、これは出発点として役立つかもしれません。

「いいね！」 5
12

@marviさん、こんにちは。

@Moinさんの回答はうまくいきましたでしょうか？