ユーザーページメトリクス

srinivas.chilukuri · 2024 年 10 月 31 日午前 12:52

@SaraDev
https://meta.discourse.org/u?cards=no&order=post_count で利用可能なメトリクスについて、SQLクエリを提供していただけますか？
下の画像を参照してください。

メトリクス

受け取ったいいね

与えたいいね

表示されたトピック

読まれた投稿

訪問日数

ソリューション

乾杯

カテゴリモデレーターを利用しているため、モデレータークエリを任意のグループに変更しています。

SaraDev · 2024 年 10 月 31 日午後 7:54

/u ユーザーページの統計情報は、Data Explorer の directory_items テーブルを使用して取得できます。

ユーザーディレクトリページの指標

-- [params]
-- int :period
-- Period Options:
-- 1. all
-- 2. yearly
-- 3. monthly
-- 4. weekly
-- 5. daily
-- 6. quarterly

SELECT
    di.user_id,
    COALESCE(di.likes_received, 0) AS likes_received,
    COALESCE(di.likes_given, 0) AS likes_given,
    COALESCE(di.topics_entered, 0) AS topics_viewed,
    COALESCE(di.topic_count, 0) AS topic_count,
    COALESCE(di.post_count, 0) AS post_count,
    COALESCE(di.days_visited, 0) AS days_visited,
    COALESCE(di.posts_read, 0) AS posts_read,
    COALESCE(di.solutions, 0) AS solutions,
    COALESCE(di.gamification_score, 0) AS cheers
FROM
    directory_items di
WHERE
    di.period_type = :period
ORDER BY
    di.user_id

通常の start_date および end_date パラメータの代わりに、このテーブルのデータは period_type フィールドを使用してフィルタリングできます。次の値は、ディレクトリページで利用可能なさまざまな期間に対応します。

1: 全期間
2: 年次
3: 月次
4: 週次
5: 日次
6: 四半期

このレポートの例の結果は次のようになります。

user	likes_received	likes_given	topics_viewed	topic_count	post_count	days_visited	posts_read	solutions	cheers
Username1	4	17	250	69	116	480	217	10	844100
Username2	2	5	47	0	2	43	59	1	112305
Username3	0	4	2	0	0	2	7	0	3100
…	…	..	…	…	…	…	…	…	…

srinivas.chilukuri · 2024 年 10 月 31 日午後 11:35

@SaraDev
開始日と終了日が必要です。開始日と終了日を指定して、指定された指標を取得する回避策はありますか？

ユーザー
受け取った「いいね！」の数
送った「いいね！」の数
表示されたトピック数
トピック数
投稿数
訪問日数
読んだ投稿数
ソリューション
応援

注：全ユーザーのサブセットに対して指標を取得しています。

SaraDev · 2024 年 11 月 1 日午後 8:23

サイトのユーザーのこれらのメトリクスを表示し、特定の開始日と終了日でフィルタリングしたい場合は、各メトリクスを個別のCTEで取得し、最終的なSELECTステートメントで結果を結合するクエリが必要になります。

以下はそのようなクエリの例です。

ユーザーメトリクス

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM
        user_stats us
    WHERE
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY
        us.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

クエリの説明:

パラメータ:
- :start_date および :end_date は、クエリ対象のデータの期間を定義するパラメータです。
共通テーブル式 (CTE):
- likes_received: 指定された期間内に各ユーザーが受け取った「いいね」の数をカウントします (action_type = 2)。
- likes_given: 指定された期間内に各ユーザーが付与した「いいね」の数をカウントします (action_type = 1)。
- user_metrics: 指定された期間内に最初の投稿を作成したユーザーの、表示されたトピック数、読み取られた投稿数、訪問日数などのユーザー統計を集計します。
- solutions: 指定された期間内に各ユーザーが提供したソリューションの数をカウントします (action_type = 15)。
- cheers: 指定された期間内の各ユーザーのゲーミフィケーションスコアを合計します。
最終選択:
- メインクエリは、各ユーザーのエンゲージメントメトリクス（受け取った「いいね」、付与した「いいね」、表示されたトピック、読み取られた投稿、訪問日数、提供されたソリューション、受け取った応援など）を選択します。
- LEFT JOIN を使用して、一部のカテゴリにアクティビティがないユーザーもすべて含め、COALESCE を使用してゼロで埋めます。

結果例

user_id	likes_received	likes_given	topics_viewed	posts_read	days_visited	solutions	cheers
1	10	5	20	100	15	2	30
2	0	3	5	20	5	0	10
—	—	—	—	—	—	—	—

srinivas.chilukuri · 2025 年 2 月 27 日午前 7:53

@SaraDev

上記の投稿で提供されたクエリを使用しましたが、以下の質問があります。

user_stats テーブルの user_metrics は、この情報の正しいソースですか？ user_stats は、ユーザーが Discourse に参加してからのメトリクスを要約する静的なテーブルであるため、特定の期間（例：開始日から終了日まで）のメトリクスをフィルタリングするには理想的ではない可能性があります。
時系列比較（T/S C/O）
ユーザーのセットに対して、ユーザーページで利用可能な期間データを比較したところ、重大な不一致に気づきました。

主な不一致:
- topics_entered
- posts_read_count
- days_visited
期間が限定されたユーザーメトリクスを取得するより良い方法があれば、明確にしていただけますか？

Jagster · 2025 年 2 月 27 日午後 9:01

PG::UndefinedColumn: ERROR:  column uv.topic_id does not exist
LINE 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

SaraDev · 2025 年 2 月 27 日午後 11:14

user_stats テーブルが、ユーザーが Discourse に参加してからの生涯メトリクスを要約する静的なテーブルであるという点は正しいです。

代わりに、posts_read_count や days_visited のようなメトリクスを日付でフィルタリングするには、posts の user_visits データベーステーブルを使用します。また、topics_entered メトリクスを日付でフィルタリングするには topic_views テーブルを使用します。

観測された差異は、これらの統計を日付でフィルタリングするために、他のテーブル（user_visits や topic_views）ではなく user_stats テーブルを使用したことに起因します。

これを解決するために、クエリを更新してこれらのデータベーステーブルを使用するようにできます。

以下は、クエリの更新版です。

ユーザーページメトリクス

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH likes_received AS (
    SELECT
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM
        user_actions ua
    WHERE
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.user_id
),
likes_given AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM
        user_actions ua
    WHERE
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
user_metrics AS (
    SELECT
        tv.user_id,
        COUNT(DISTINCT tv.topic_id) AS topics_viewed
    FROM
        topic_views tv
    WHERE
        tv.viewed_at BETWEEN :start_date AND :end_date
    GROUP BY
        tv.user_id
),
days_and_posts AS (
    SELECT
        uv.user_id,
        COUNT(DISTINCT uv.visited_at) AS days_visited,
        SUM(uv.posts_read) AS posts_read
    FROM
        user_visits uv
    WHERE
        uv.visited_at BETWEEN :start_date AND :end_date
    GROUP BY
        uv.user_id
),
solutions AS (
    SELECT
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM
        user_actions ua
    WHERE
        ua.action_type = 15
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        ua.acting_user_id
),
cheers AS (
    SELECT
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM
        gamification_scores gs
    WHERE
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY
        gs.user_id
)

SELECT
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(dp.days_visited, 0) AS days_visited,
    COALESCE(dp.posts_read, 0) AS posts_read,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM
    users u
LEFT JOIN
    likes_received lr ON u.id = lr.user_id
LEFT JOIN
    likes_given lg ON u.id = lg.user_id
LEFT JOIN
    user_metrics um ON u.id = um.user_id
LEFT JOIN
    days_and_posts dp ON u.id = dp.user_id
LEFT JOIN
    solutions sol ON u.id = sol.user_id
LEFT JOIN
    cheers ch ON u.id = ch.user_id
ORDER BY
    u.id

この方法では、user_visits テーブルの posts_read データには重要な違いがあることに注意してください。ユーザー自身の投稿はカウントされませんが、user_stats テーブルのデータには自己作成の投稿が含まれるため、このクエリとユーザーページでのこれらの 2 つの統計の間には、依然として違いが見られる可能性があります。

トピック		返信	表示
Daily, weekly, or total stats by user over a specified time range Data & reporting sql-query	0	138	2019 年 12 月 31 日
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	6	605	2025 年 2 月 27 日
PAID Data explorer challenge: chronological list of user topics started, replied and liked Marketplace sql-query	7	3250	2016 年 12 月 2 日
Complex user activity report requirement Data & reporting sql-query	22	1736	2021 年 8 月 5 日
Daily graph of user time online Feature	2	2129	2015 年 10 月 1 日

ユーザーページメトリクス

関連トピック