Web interface reporting different stats from what I get from database

[rouilj]

If I look in the web interface at the totals graph from 13:00-14:00 I see 6 views in 4 visits. I assume this means I should have 6 entries in the hits table associated with 4 entries in the sessions table.

If I use the following command:

./goatcounter-dev-linux-amd64 db query "select distinct(hex(session)) as '' from hits where created_at >= '2022-05-01 13:00' AND created_at < '2022-05-01 14:00';" | while read session; 
   do test -z "$session" && continue;
        echo $session;
        sh ./session_trail.sql $session;
   done

where session_trail.sql is:

#! /bin/sh
./goatcounter-dev-linux-amd64 db query "select
       hits.created_at, paths.path
      from paths
        inner join hits on hits.path_id = paths.path_id
       where hex(hits.session) == '$1'
       order by created_at;"

I see the following:

AA2019BFD53D4CF6984782F3C9000EC7
created_at           path
2022-05-01 13:38:19  /roundup.sourceforge.io

935571A3699E43CDABE0EDC5DE3509D1
created_at           path
2022-05-01 13:52:50  /roundup.sourceforge.io/docs/customizing.html
2022-05-01 13:52:50  /roundup.sourceforge.io/docs/customizing.html?highlight=nosy
2022-05-01 13:52:51  /roundup.sourceforge.io/docs/customizing.html
2022-05-01 13:52:52  /roundup.sourceforge.io/docs/customizing.html

AAF4935E063942F3975770DD97164055
created_at           path
2022-05-01 13:56:50  /roundup.sourceforge.io

This confirms the 6 page views but looks like there are only three visits.

I wondered if there was an additional session that was created before my 13:00-14:00 window that may be showing up in the graph but not in my sql report. Changing the start time to 03:00 still only shows the same three sessions (visits) and 6 page views.

Have I discovered a bug?

Also this is an attempt to see what pages were visited in a session. I think I should be able to do this in a single SQL query (maybe using a CTE??) but my SQL skills are very basic. Any hints here?

Thanks. -- rouilj

[rouilj]

Here is another example. The Totals graph tells me that there are 13 visits and 18 views:

(I am UTC-4 timezone.) It looks like the bar on the totals graph should report 4 visits and 18 pageviews:

./goatcounter-dev-linux-amd64 db query "select distinct(hex(session)) as '' from hits where created_at < '2022-05-03 16:00' and created_at >= '2022-05-03 15:00';" | sed '/^\s*$/d' | xargs --verbose -l ./session_trail.sql
./session_trail.sql D95B3A634DCC4093971D895663943F51
created_at           path
2022-05-03 15:07:24  /www.roundup-tracker.org
2022-05-03 15:07:34  /www.roundup-tracker.org/docs/design.html
2022-05-03 15:08:28  /www.roundup-tracker.org
2022-05-03 15:08:33  /www.roundup-tracker.org/code.html

./session_trail.sql 4E1B876CAE974019B1B3BE7F3818DD32
created_at           path
2022-05-03 15:09:10  /roundup-tracker.org
2022-05-03 15:09:14  /roundup-tracker.org/docs.html
2022-05-03 15:09:43  /roundup-tracker.org/docs/design.html
2022-05-03 15:09:53  /roundup-tracker.org/docs.html
2022-05-03 15:09:53  /roundup-tracker.org
2022-05-03 15:09:56  /www.roundup-tracker.org/code.html
2022-05-03 15:09:59  /www.roundup-tracker.org
2022-05-03 15:10:05  /www.roundup-tracker.org/docs/design.html
2022-05-03 15:10:06  /www.roundup-tracker.org

./session_trail.sql 50C625B17865452CBC964C12244228F4
created_at           path
2022-05-03 15:30:29  /roundup-tracker.org
2022-05-03 15:30:34  /roundup-tracker.org/docs.html
2022-05-03 15:30:36  /roundup-tracker.org
2022-05-03 15:30:38  /roundup-tracker.org/docs/design.html

./session_trail.sql 3C43DEAE81314C47A7A43D8AC7C65DDF
created_at           path
2022-05-03 15:51:54  /roundup.sourceforge.io

I verified that there are only 4 unique session in that timeperiod.

goatcounter version reports:

version=dev; go=go1.17.6; GOOS=linux; GOARCH=amd64; race=false; cgo=true.

Hmm, version doesn't seem to be useful there. I downloaded 2.2.3 and verified that I am running 2.2.3.

[arp242]

"Unique visits" are counted per-path, rather than per-site. This includes the totals overview. It doesn't actually use the session column for this but first_visit which is calculated when the pageview comes in. The session column isn't actually used all that much.

So basically, it's working as intended if I follow your SQL correctly. That said, I think adding a "show global visits per site" option would be useful; it's come up before. I think it can be added without too much effort.

[rouilj]

Thanks for your reply.

"Unique visits" are counted per-path, rather than per-site.

by Unique visits do you mean the visits number in the graph? Using the first example, I can get the numbers to work out by counting each unique paths in each session.

So I get:

1 visit (unique URL/path) and 1 pageview in session AA201 2 visits and 5 pageviews in session 935571 since customizing.html is viewed 4 times but only 1 of these is counted 1 visit and 1 pageview in AAF49

total 4 visits and 6 pageviews?

In my second example the only way I can get 13 views (uniqpue paths) is by counting unique path per session as above. If I remove the duplicate paths in each session, I get 5 views.

If the session changes, does it generate an new entry for the path with a new first_visit? The session isn't used directly but allows duplicate paths with different first_visit values.

Have a great week.

[arp242]

I can get the numbers to work out by counting each unique paths in each session.

Yeah, that's pretty much how it works right now. The UI never queries the hits table directly but rather the hit_counts and hit_stats "pre-computed" views, where it stores things per-path.

The ability to easily view things per-path was a major reason I wrote this in the first place, as pretty much $everything_else that I could find was oriented towards per-site, but I was/am interested in how many people read my weblog articles.

I did some work on adding a site-global "totals" view last week.

If the session changes, does it generate an new entry for the path with a new first_visit?

Yes.