Can we add visit count to website metrics chart?

[zavan]

I'm talking about this chart:

Currently it shows Visitors and Views, but now that v2.11 introduced a simple way of calculating visits via visit_id, we could do something like this to add a visit count stat in getSessionStats:

diff --git a/src/queries/analytics/sessions/getSessionStats.ts b/src/queries/analytics/sessions/getSessionStats.ts
index c977187d..45c49fcd 100644
--- a/src/queries/analytics/sessions/getSessionStats.ts
+++ b/src/queries/analytics/sessions/getSessionStats.ts
@@ -23,7 +23,8 @@ async function relationalQuery(websiteId: string, filters: QueryFilters) {
     `
     select
       ${getDateQuery('website_event.created_at', unit, timezone)} x,
-      count(distinct website_event.session_id) y
+      count(distinct website_event.session_id) y,
+      count(distinct website_event.visit_id) z
     from website_event
       ${joinSession}
     where website_event.website_id = {{websiteId::uuid}}

In fact I did just this and that's the only change I have in my fork.

Of couse since visits last at most 1 hour, this is only really useful for daily+ charts, on hourly charts visits and visitors are always the same.

[zavan]

As a PoC I wrote something that returns a lot more time-series info:

SELECT
  TO_CHAR(t.date, 'YYYY-MM-DD') date,
  COUNT(DISTINCT t.session_id) visitors,
  COUNT(DISTINCT t.visit_id) visits,
  SUM(t.visit_views) views,
  SUM(CASE WHEN t.visit_views = 1 THEN 1 ELSE 0 END) bounces,
  EXTRACT(EPOCH FROM (SUM(t.max_time - t.min_time) / SUM(t.visit_views))) avg_visit_seconds
FROM (
  SELECT
    DATE_TRUNC('day', e.created_at AT TIME ZONE 'utc') date,
    e.session_id,
    e.visit_id,
    COUNT(e.event_id) visit_views,
    MIN(e.created_at) min_time,
    MAX(e.created_at) max_time
  FROM
    website_event e
  WHERE
    e.website_id = '70e5b858-157f-4976-ad11-2ce4382d88b7'
    AND e.created_at BETWEEN '2023-11-01T00:00:00-06' AND '2024-04-15T12:00:00-05'
    AND e.event_type = '1'
  GROUP BY 1, 2, 3
) t GROUP BY 1 ORDER BY 1 ASC