joshmn / ahoy_captain

A full-featured, mountable analytics dashboard for your Rails app, powered by the Ahoy gem.
MIT License
356 stars 18 forks source link

Unique Visits always shows equal to Total Visitors #38

Open ScotterC opened 9 months ago

ScotterC commented 9 months ago

On my deployment these numbers are always the same even though when they should be different. I've verified the results being different for a given month when comparing these queries:

# @presenter.unique_visitors
SELECT SUM(count_visitor_token) AS total_unique_visitors
FROM (
    SELECT COUNT(DISTINCT "ahoy_visits"."visitor_token") AS "count_visitor_token", 
           DATE_TRUNC('day', "ahoy_visits"."started_at"::timestamptz AT TIME ZONE 'America/New_York')::date AS "date_trunc_day"
    FROM "ahoy_visits"
    LEFT OUTER JOIN "ahoy_events" ON "ahoy_events"."visit_id" = "ahoy_visits"."id"
    WHERE ("ahoy_visits"."started_at" > '2023-11-13 00:01:00' AND "ahoy_visits"."started_at" < '2023-12-13 14:20:00')
      AND ("ahoy_events"."time" > '2023-11-13 00:01:00' AND "ahoy_events"."time" < '2023-12-13 14:20:00')
      AND ("ahoy_visits"."started_at" IS NOT NULL)
    GROUP BY DATE_TRUNC('day', "ahoy_visits"."started_at"::timestamptz AT TIME ZONE 'America/New_York')::date
) AS daily_counts;

vs

# @presenter.total_visits
WITH 
  "current" AS (
    SELECT COUNT(DISTINCT "ahoy_visits"."id") 
    FROM "ahoy_visits" 
    LEFT OUTER JOIN "ahoy_events" 
      ON "ahoy_events"."visit_id" = "ahoy_visits"."id" 
    WHERE ("ahoy_visits"."started_at" > '2023-11-13 00:01:00' 
      AND "ahoy_visits"."started_at" < '2023-12-13 14:20:00') 
      AND ("ahoy_events"."time" > '2023-11-13 00:01:00' 
      AND "ahoy_events"."time" < '2023-12-13 14:20:00')
  ), 
  "compare" AS (
    SELECT COUNT(DISTINCT "ahoy_visits"."id") 
    FROM "ahoy_visits" 
    LEFT OUTER JOIN "ahoy_events" 
      ON "ahoy_events"."visit_id" = "ahoy_visits"."id" 
    WHERE ("ahoy_visits"."started_at" > '2023-10-13 09:42:00' 
      AND "ahoy_visits"."started_at" < '2023-11-13 00:01:00') 
      AND ("ahoy_events"."time" > '2023-10-13 09:42:00' 
      AND "ahoy_events"."time" < '2023-11-13 00:01:00')
  ) 
SELECT 
  current, 
  compare 
FROM 
  current, 
  compare;

I'm having a really hard diagnosing where the problem is in the ViewComponents. At the Stats level the queries look fine which is how I derived the ones above but I got lost in ComparableContainerComponent and ran out of time for this problem today.