Actividad de visita en grupo

Comunidad Datos e informes
1

Actividad de Visita de Grupo

He estado trabajando en consultas que analizan la actividad por grupos.

  • Visitantes únicos
  • Total de visitas
  • Visitas móviles frente a escritorio
  • Porcentaje de miembros del grupo que visitan
  • Tiempo promedio de lectura por visita
  • Promedio de publicaciones leídas por visita
  • Tiempo promedio de lectura por miembro
  • Promedio de publicaciones leídas por miembro

Preguntas

  1. Sospecho que la tabla user_visits solo captura un registro por user_id único por día, dado que visited_at es una FECHA y no una FECHA_HORA o TIMESTAMP, incluso si el usuario visita varias veces en un día desde múltiples tipos de dispositivo. Cuando ejecuto mi consulta por DÍA, veo que el total de miembros únicos es igual al número total de visitas. ¿Alguien puede confirmar si mi suposición es correcta? Si es así, la pregunta de seguimiento se convierte en: ¿qué sucede con el valor de mobile si el usuario visita tres veces en un día desde una laptop, un escritorio y luego un teléfono, en cualquier orden?
  2. Curiosamente, también veo que AVG(uv.posts_read) no tiene el mismo valor que SUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id)), por lo que me gustaría saber dónde mi consulta está tomando un camino incorrecto o si estoy interpretando incorrectamente la tabla user_visits o sus campos. ¿Alguien puede comentar sobre la diferencia entre esos dos cálculos?

Consulta:

Resumen 
-- [parámetros]
-- cadena nula :group_name = NombreDeTuGrupo
-- fecha :start_date = 2019/09/01
-- fecha :end_date = 2019/10/01
-- cadena nula :frame = día

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 Me gusta
2

Sí, eso es correcto.

Si el usuario lee publicaciones al visitar desde un nuevo dispositivo, la columna mobile se actualizará en función del último dispositivo desde el que visitó. Por ejemplo: si un usuario comienza el día leyendo 2 publicaciones en un navegador de escritorio, se creará una entrada user_visits para el id del usuario con posts_read establecido en 2 y mobile establecido en false. Si el usuario luego inicia sesión en un dispositivo móvil y lee 3 publicaciones más, la entrada user_visits del día se actualizará a posts_read: 5 y mobile: true. Esto es algo que puedes probar con el Explorador de Datos; solo debes asegurarte de que el usuario esté leyendo publicaciones que no haya leído antes.

3 Me gusta
3

Gracias, ¿tiene alguna idea sobre la última pregunta? Soy partidario de buenos datos frente a malos, y esto llama la atención al ser similares y, sin embargo, no iguales, especialmente dada su explicación de que solo hay un uv.id por uv.user_id en user_visits por uv.visited_at.

Puede que no sea muy útil, pero aquí hay una muestra de mis datos:

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

Cuando divides con enteros, PostgreSQL devolverá un entero. Prueba algo como SUM(posts_read)::float / COUNT(DISTINCT(user_id)) y mira si aún encuentras una diferencia. Es posible que necesites redondear el resultado a dos decimales.

2 Me gusta