Atividade de Visita em Grupo

Comunidade Dados e relatórios
1

Atividade de Visita em Grupo

Tenho trabalhado em consultas que analisam a atividade de grupos.

  • Visitantes únicos
  • Total de visitas
  • Visitas móveis versus desktop
  • Porcentagem de membros do grupo que visitaram
  • Tempo médio de leitura por visita
  • Média de posts lidos por visita
  • Tempo médio de leitura por membro
  • Média de posts lidos por membro

Perguntas

  1. Suspeito que a tabela user_visits capture apenas um registro por user_id único por dia, dado que visited_at é do tipo DATE e não DATETIME ou TIMESTAMP, mesmo que o usuário visite várias vezes no mesmo dia, a partir de diferentes tipos de dispositivo. Quando executo minha consulta por DIA, vejo que o total de membros únicos é igual ao número total de visitas. Alguém pode confirmar se minha suposição está correta? Se estiver, a pergunta subsequente é: o que acontece com o valor de mobile se o usuário visitar três vezes em um dia, usando primeiro um laptop, depois um desktop e, por fim, um celular, em qualquer ordem?
  2. Estranhamente, também vejo que AVG(uv.posts_read) não é o mesmo valor que SUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id)), então ficaria curioso em saber onde minha consulta está indo por um caminho errado ou se estou interpretando incorretamente a tabela user_visits ou seus campos. Alguém pode comentar sobre a diferença entre esses dois cálculos?

Consulta:

Resumo 
-- [params]
-- string null :group_name = NomeDoSeuGrupo
-- date :start_date = 2019/09/01
-- date :end_date = 2019/10/01
-- string null :frame = day

with mobile as (
SELECT uv.id,
    count(DISTINCT(uv.user_id)) as UniqueMobile,
    date_part(:frame, uv.visited_at::date) as Day,
    g.name as GroupName
from user_visits uv
join users u on uv.user_id = u.id
join group_users gu on gu.user_id = u.id
join groups g on g.id = gu.group_id
where mobile = true
    and uv.visited_at >= :start_date::date
    and uv.visited_at < :end_date::date
    and g.name = :group_name
GROUP BY GroupName, Day, uv.id
    ), 
    
desktop as (
SELECT uv.id,
    count(DISTINCT(uv.user_id)) as UniqueDesktop,
    date_part(:frame, uv.visited_at::date) as Day,
    g.name as GroupName
from user_visits uv
join users u on uv.user_id = u.id
join group_users gu on gu.user_id = u.id
join groups g on g.id = gu.group_id
where mobile = false
    and uv.visited_at >= :start_date::date
    and uv.visited_at < :end_date::date
    and g.name = :group_name
GROUP BY GroupName, Day, uv.id
    )

   SELECT 
    date_part(:frame, uv.visited_at::date) as VisitDate,
    count(DISTINCT(uv.user_id)) as UniqueMembers, 
    count(uv.id) as AllVisits,
    count(m.UniqueMobile) as MobileVisits,
    count(d.UniqueDesktop) as DesktopVisits,
    round((count(DISTINCT(uv.user_id)) * 100.0) / groups.user_count, 2) as Percent,
    round(avg(uv.posts_read),2) as "Posts Read (avg Visit)",
    (interval '1' minute * ROUND((cast(AVG(uv.time_read) as decimal)/60), 2)) as "Read Time (avg Visit)",
    (SUM(uv.posts_read)/count(DISTINCT(uv.user_id))) as "Posts Read (avg Member)",
    date_trunc('second',(interval '1' minute * (ROUND((cast(SUM(uv.time_read) as decimal)/60), 2)/(count(DISTINCT(uv.user_id)))))) as "Read Time (avg Member)"

FROM users
join group_users on group_users.user_id = users.id
join groups on group_users.group_id = groups.id
LEFT join user_visits uv on uv.user_id = users.id
LEFT JOIN mobile m ON m.id = uv.id
LEFT JOIN desktop d ON d.id = uv.id
    where groups.name = :group_name
    AND uv.visited_at::date >= :start_date
    and uv.visited_at::date < :end_date
    
group by VisitDate, groups.user_count 

order by VisitDate asc
2

Sim, isso está correto.

Se o usuário ler posts ao visitar a partir de um novo dispositivo, a coluna mobile será atualizada com base no último dispositivo que ele utilizou para acessar. Por exemplo: se um usuário começar o dia lendo 2 posts em um navegador de desktop, será criada uma entrada em user_visits para o ID do usuário com posts_read definido como 2 e mobile definido como false. Se o usuário então fizer login em um dispositivo móvel e ler mais 3 posts, a entrada de user_visits para aquele dia será atualizada para posts_read: 5 e mobile: true. Isso é algo que você pode testar com o Data Explorer; basta garantir que o usuário esteja lendo posts que ainda não havia lido antes.

3

Obrigado, alguma ideia sobre a última pergunta? Sou fã de bons dados em vez de ruins, e isso levanta suspeitas ao serem semelhantes, mas não iguais, especialmente considerando sua explicação de que há apenas um uv.id por uv.user_id em user_visits por uv.visited_at.

Pode não ser muito útil, mas aqui está uma amostra dos meus dados:

VisitDate Posts Read (avg Visit) Read (avg Member)
13 1.18 1
14 4.15 4
15 7.18 7
16 6.15 6
4

Quando você divide com inteiros, o PostgreSQL retorna um inteiro. Tente algo como SUM(posts_read)::float / COUNT(DISTINCT(user_id)) e veja se ainda há diferença. Talvez seja necessário arredondar o resultado para duas casas decimais.