Métricas da Página do Usuário

[srinivas.chilukuri]

@SaraDev
você pode fornecer a consulta SQL para as métricas disponíveis em https://meta.discourse.org/u?cards=no&order=post_count
consulte a imagem abaixo

métricas

• curtidas recebidas
• curtidas dadas
• tópicos visualizados
• posts lidos
• dias visitados
• soluções
• saudações

---

estamos utilizando moderadores de categoria, portanto, estamos modificando a consulta de moderador para qualquer grupo determinado

[SaraDev (Sara Devlaeminck)]

Olá @srinivas.chilukuri,

As estatísticas da página de usuário /u podem ser recuperadas através do Explorador de Dados usando a tabela directory_items.

Métricas da Página do Diretório de Usuários

-- [params]
-- int :period
-- Opções de Período:
-- 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

Em vez dos parâmetros típicos start_date e end_date, os dados desta tabela podem ser filtrados usando o campo period_type, onde os seguintes valores correspondem aos diferentes períodos de tempo disponíveis na página do diretório:

• 1: todo o período
• 2: anual
• 3: mensal
• 4: semanal
• 5: diário
• 6: trimestral

Exemplo de Resultados para este relatório seriam:

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]

@SaraDev
Preciso da data de início e fim. Existe uma solução alternativa para obter as métricas fornecidas com data de início e fim?

• usuário
• likes_recebidos
• likes_dados
• tópicos_visualizados
• contagem_de_tópicos
• contagem_de_posts
• dias_visitados
• posts_lidos
• soluções
• aplausos

Observação: Estou obtendo métricas para um pequeno subconjunto do total de usuários.

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

Preciso da data de início e fim. Existe uma solução alternativa para obter as métricas fornecidas com data de início e fim?

• usuário
• curtidas_recebidas
• curtidas_dadas
• tópicos_visualizados
• contagem_tópicos
• contagem_posts
• dias_visitados
• posts_lidos
• soluções
• aplausos

Se você quiser visualizar essas métricas para usuários em seu site e filtrar por datas de início e fim específicas, você precisaria de uma consulta que extraia os dados para cada métrica em uma CTE separada e, em seguida, combine os resultados em uma instrução SELECT final.

É assim que ficaria:

Métricas do Usuário

-- [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

Explicação da Consulta:

1. Parâmetros:
   • :start_date e :end_date são parâmetros que definem o intervalo de datas para os dados que estão sendo consultados.
2. Expressões de Tabela Comuns (CTEs):
   • likes_received: Conta o número de curtidas recebidas por cada usuário (action_type = 2) dentro do intervalo de datas especificado.
   • likes_given: Conta o número de curtidas dadas por cada usuário (action_type = 1) dentro do intervalo de datas especificado.
   • user_metrics: Agrega estatísticas de usuário, como tópicos visualizados, posts lidos e dias visitados, para usuários que fizeram seu primeiro post dentro do intervalo de datas especificado.
   • solutions: Conta o número de soluções fornecidas por cada usuário (action_type = 15) dentro do intervalo de datas especificado.
   • cheers: Soma as pontuações de gamificação para cada usuário dentro do intervalo de datas especificado.
3. Seleção Final:
   • A consulta principal seleciona as métricas de engajamento do usuário para cada usuário, incluindo curtidas recebidas, curtidas dadas, tópicos visualizados, posts lidos, dias visitados, soluções fornecidas e aplausos recebidos.
   • Ela usa LEFT JOIN para garantir que todos os usuários sejam incluídos, mesmo que não tenham atividade em algumas categorias, preenchendo com zeros usando COALESCE.

Exemplo de Resultados

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]

@SaraDev

@SaraDev

Métricas do Usuário

Usamos a consulta fornecida na postagem acima e temos as seguintes perguntas:

1. user_metrics da tabela user_stats é a fonte correta para esta informação? Dado que user_stats é uma tabela estática que resume as métricas de um usuário desde que ele ingressou no Discourse, pode não ser ideal para filtrar métricas dentro de um período específico (por exemplo, de uma data de início a uma data de término).
2. Comparação de Séries Temporais (T/S C/O)
   Para um determinado conjunto de usuários, comparamos os dados do período de tempo disponíveis na página do usuário e notamos discrepâncias significativas.

Principais Discrepâncias:

• topics_entered
• posts_read_count
• days_visited

Você poderia esclarecer se há uma maneira melhor de recuperar métricas de usuário limitadas no tempo?

[Jagster (Jakke Lehtonen)]

PG::UndefinedColumn: ERRO:  a coluna uv.topic_id não existe
LINHA 38:         COUNT(DISTINCT uv.topic_id) AS topics_viewed, -- Cou...

[SaraDev (Sara Devlaeminck)]

@srinivas.chilukuri

user_metrics da tabela user_stats é a fonte correta para esta informação? Dado que user_stats é uma tabela estática que resume as métricas de um usuário desde que ele ingressou no Discourse, pode não ser ideal para filtrar métricas dentro de um intervalo de tempo específico (por exemplo, de uma data de início a uma data de término).

Você está correto que a tabela user_stats é uma tabela estática que resume as métricas de vida de um usuário desde que ele ingressou no Discourse.

Em vez disso, para filtrar métricas por data, como posts_read_count e days_visited, usaríamos a tabela de banco de dados user_visits para posts. Também usaríamos a tabela topic_views para filtrar as métricas topics_entered por data.

@srinivas.chilukuri

Para um determinado conjunto de usuários, comparamos os dados disponíveis do período de tempo na página do usuário e notamos discrepâncias significativas.

As discrepâncias que você observou surgem do uso da tabela user_stats em vez de outras tabelas como user_visits e topic_views para filtrar essas estatísticas por data.

Para resolver isso, podemos atualizar a consulta para usar essas tabelas de banco de dados em vez disso:

Aqui está uma versão atualizada da consulta:

Métricas da Página do Usuário

-- [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

Note que com este método, os dados de posts_read na tabela user_visits têm uma distinção importante: não contam os posts do próprio usuário, enquanto os dados da tabela user_stats incluem posts de autoria própria, então você ainda pode encontrar uma diferença entre essas duas estatísticas nesta consulta e na Página do Usuário.