Attività di visita di gruppo

Comunità Dati e reportistica
1

Attività di visita di gruppo

Sto lavorando su query che analizzano l’attività dei gruppi.

  • Visitatori unici
  • Visite totali
  • Visite da mobile rispetto a desktop
  • Percentuale di membri del gruppo che visitano
  • Tempo medio di lettura per visita
  • Post medi letti per visita
  • Tempo medio di lettura per membro
  • Post medi letti per membro

Domande

  1. Sospetto che la tabella user_visits registri un solo record per ogni user_id unico al giorno, dato che visited_at è di tipo DATE e non DATETIME o TIMESTAMP, anche se l’utente effettua più visite nella stessa giornata da diversi tipi di dispositivo. Quando eseguo la mia query per GIORNO, vedo che il totale dei membri unici è uguale al numero totale di visite. Qualcuno può confermare se la mia ipotesi è corretta? Se lo è, la domanda successiva diventa: cosa succede al valore di mobile se l’utente visita tre volte in un giorno, prima da laptop, poi da desktop e infine da telefono, in qualsiasi ordine?
  2. Stranamente, noto anche che AVG(uv.posts_read) non ha lo stesso valore di SUM(uv.posts_read)/COUNT(DISTINCT(uv.user_id)), quindi sarei curioso di sapere dove la mia query sta sbagliando o se sto interpretando male la tabella user_visits o i suoi campi. Qualcuno può intervenire per spiegare la differenza tra questi due calcoli?

Query:

Riepilogo 
-- [params]
-- stringa null :group_name = NomeDelTuoGruppo
-- data :start_date = 2019/09/01
-- data :end_date = 2019/10/01
-- stringa 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

Sì, è corretto.

Se l’utente legge post quando visita da un nuovo dispositivo, la colonna mobile viene aggiornata in base all’ultimo dispositivo utilizzato per la visita. Ad esempio: se un utente inizia la giornata leggendo 2 post da un browser desktop, verrà creato un ingresso user_visits per l’ID dell’utente con posts_read impostato a 2 e mobile impostato a false. Se l’utente accede poi da un dispositivo mobile e legge altri 3 post, l’ingresso user_visits per quel giorno verrà aggiornato con posts_read: 5 e mobile: true. Puoi verificare questo comportamento con il Data Explorer, assicurandoti semplicemente che l’utente stia leggendo post che non aveva già letto in precedenza.

3

Grazie, hai qualche idea sull’ultima domanda? Sono un sostenitore della qualità dei dati rispetto a quella scarsa, e questo fa sorgere dubbi quando i risultati sono simili ma non identici, soprattutto alla luce della tua spiegazione secondo cui esiste un solo uv.id per ogni uv.user_id in user_visits per ogni uv.visited_at.

Forse non è molto utile, ma ecco un esempio dai miei dati:

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 dividi con interi, PostgreSQL restituirà un intero. Prova qualcosa come SUM(posts_read)::float / COUNT(DISTINCT(user_id)) e vedi se noti ancora una differenza. Potrebbe essere necessario arrotondare il risultato a due cifre decimali.