I have more unique visitors than total pageviews

[aliceisjustplaying]

Past Issues Searched

• I have searched open and closed issues to make sure that the bug has not yet been reported

Issue is a Bug Report

• This is a bug report and not a feature request, nor asking for self-hosted support

Using official Plausible Cloud hosting or self-hosting?

Self-hosting

Describe the bug

Set up Plausible on my VPS with a Caddy reverse proxy, everything works, except for the fact that it's telling me I have more unique visitors than total pageviews which is mathematically impossible. Honestly I'm kind of flabbergasted here.

(It's also either massively overcounting visits compared to CF Analytics or I just have a lot of people using various forms of adblockers, so not really reporting that as a bug. But maybe a clue?)

Expected behavior

I do not have more unique visitors than total pageviews

Screenshots

Environment

- OS: MacOS
- Browser: Chrome
- Browser Version: 130.0.6723.117
- Plausible version: CE 2.14

[ruslandoga]

👋 @aliceisjustplaying

Thank you for the report! It does seem super strange :)

This might be due to how Plausible splits visits and pageviews into two separate tables (sessions_v2 and events_v2) and how ClickHouse then compacts sessions_v2. To know for certain, we'd need to run some queries on the database.

$ cd plausible-ce # or wherever you cloned plausible/community-edition
$ docker compose exec plausible_events_db clickhouse client -d plausible_events_db

We can start with these ones

:) set param_site_id = <your site id>;

To find out site_id you can use something like this

:) select site_id, groupArray(distinct hostname) from events_v2 group by site_id;

Now, to the actual queries. These would try to find discrepancies in visitor counts between the two tables.

-- all events
:) select count(*) from events_v2 where site_id = {site_id:UInt64};
-- pageviews (should be the same as TOTAL PAGEVIEWS on the dashboard)
:) select count(*) from events_v2 where site_id = {site_id:UInt64} and name = 'pageview';
-- uncompacted visits
:) select count(*) from sessions_v2 where site_id = {site_id:UInt64};
-- compacted visits
:) select count(*) from sessions_v2 final where site_id = {site_id:UInt64};
-- unique visitors according to the events table across all event types
:) select count(distinct user_id) from events_v2 where site_id = {site_id:UInt64};
-- unique "page" visitors according the events table
:) select count(distinct user_id) from events_v2 where site_id = {site_id:UInt64} and name = 'pageview';
-- uncompacted visitors
:) select count(distinct user_id) from sessions_v2 where site_id = {site_id:UInt64};
-- compacted visitors (should be the same as TOTAL VISITORS on the dashboard)
:) select count(distinct user_id) from sessions_v2 final where site_id = {site_id:UInt64};
-- the difference in visitors between sessions and events tables
:) select count(distinct user_id) from sessions_v2 final where user_id not in (
     select distinct user_id from events_v2 where name = 'pageview' and site_id = {site_id:UInt64}
   ) and site_id = {site_id:UInt64};

[aliceisjustplaying]

Hi! Appreciate the help. Query outputs:

select count(*) from events_v2 where site_id = {site_id:UInt64};

SELECT count(*)
FROM events_v2
WHERE site_id = {site_id:UInt64}

Query id: e0a5faba-2416-4224-8406-31766cac6916

   ┌─count()─┐
1. │     982 │
   └─────────┘

1 row in set. Elapsed: 0.005 sec.

select count(*) from events_v2 where site_id = {site_id:UInt64} and name = 'pageview';

SELECT count(*)
FROM events_v2
WHERE (site_id = {site_id:UInt64}) AND (name = 'pageview')

Query id: 301c7c34-cc11-40a2-8bb2-ca7ec65a44b6

   ┌─count()─┐
1. │     707 │
   └─────────┘

1 row in set. Elapsed: 0.007 sec.

select count(*) from sessions_v2 where site_id = {site_id:UInt64};

SELECT count(*)
FROM sessions_v2
WHERE site_id = {site_id:UInt64}

Query id: 1ed6dcb2-1bf4-44ae-a749-ca73aef8a0bd

   ┌─count()─┐
1. │     949 │
   └─────────┘

1 row in set. Elapsed: 0.005 sec.

select count(*) from sessions_v2 final where site_id = {site_id:UInt64};

SELECT count(*)
FROM sessions_v2
FINAL
WHERE site_id = {site_id:UInt64}

Query id: 174692cf-f66b-47c1-b217-da8186d7af82

   ┌─count()─┐
1. │     951 │
   └─────────┘

1 row in set. Elapsed: 0.038 sec.

select count(distinct user_id) from events_v2 where site_id = {site_id:UInt64};

SELECT countDistinct(user_id)
FROM events_v2
WHERE site_id = {site_id:UInt64}

Query id: b8fbbaac-fb03-4283-859d-da84547ce7a3

   ┌─countDistinct(user_id)─┐
1. │                    947 │
   └────────────────────────┘

1 row in set. Elapsed: 0.008 sec.

select count(distinct user_id) from events_v2 where site_id = {site_id:UInt64} and name = 'pageview';

SELECT countDistinct(user_id)
FROM events_v2
WHERE (site_id = {site_id:UInt64}) AND (name = 'pageview')

Query id: db68a2a0-a12c-47cb-b881-785783b81890

   ┌─countDistinct(user_id)─┐
1. │                    688 │
   └────────────────────────┘

1 row in set. Elapsed: 0.008 sec.

select count(distinct user_id) from sessions_v2 where site_id = {site_id:UInt64};

SELECT countDistinct(user_id)
FROM sessions_v2
WHERE site_id = {site_id:UInt64}

Query id: 032f5516-2b3a-4998-a7e1-2ddffa05aaa8

   ┌─countDistinct(user_id)─┐
1. │                    949 │
   └────────────────────────┘

1 row in set. Elapsed: 0.006 sec.

select count(distinct user_id) from sessions_v2 final where site_id = {site_id:UInt64};

SELECT countDistinct(user_id)
FROM sessions_v2
FINAL
WHERE site_id = {site_id:UInt64}

Query id: 2034a403-ff28-4ce0-8257-1eca1c4583bf

   ┌─countDistinct(user_id)─┐
1. │                    950 │
   └────────────────────────┘

1 row in set. Elapsed: 0.008 sec.

The last query errors out:

select count(distinct user_id) from sessions_v2 final where user_id not in (
     select distinct user_id from events_v2 and name = 'pageview' and site_id = {site_id:UInt64}
   ) and site_id = {site_id:UInt64};

Syntax error: failed at position 126 ('name') (line 2, col 49):

select count(distinct user_id) from sessions_v2 final where user_id not in (
     select distinct user_id from events_v2 and name = 'pageview' and site_id = {site_id:UInt64}
   ) and site_id = {site_id:UInt64};

Expected one of: FINAL, SAMPLE, table, table function, subquery or list of joined tables, array join, LEFT ARRAY JOIN, INNER, ARRAY JOIN, JOIN, PREWHERE, WHERE, GROUP BY, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, FETCH, SETTINGS, UNION, EXCEPT, INTERSECT

Screenshot of the dashboard at this moment:

[ruslandoga]

From the first two queries it seems like there are some custom events present. They can account for the difference.

[ruslandoga]

That is, the difference you are seeing is visitors which only send custom events and no pageviews.

Most likely :)

[ruslandoga]

Here's the fixed last query

select count(distinct user_id) from sessions_v2 final where user_id not in (
  select distinct user_id from events_v2 where name = 'pageview' and site_id = {site_id:UInt64}
) and site_id = {site_id:UInt64};

[ruslandoga]

I'm moving this to discussions since this doesn't look like an issue with the code.

[aliceisjustplaying]

Last query:

select count(distinct user_id) from sessions_v2 final where user_id not in (
  select distinct user_id from events_v2 where name = 'pageview' and site_id = {site_id:UInt64}
) and site_id = {site_id:UInt64};

SELECT countDistinct(user_id)
FROM sessions_v2
FINAL
WHERE (user_id NOT IN (
    SELECT DISTINCT user_id
    FROM events_v2
    WHERE (name = 'pageview') AND (site_id = {site_id:UInt64})
)) AND (site_id = {site_id:UInt64})

Query id: 131b2cd8-1fe9-4e1e-92bd-d26ba5f36d9f

   ┌─countDistinct(user_id)─┐
1. │                    280 │
   └────────────────────────┘

1 row in set. Elapsed: 0.019 sec. Processed 2.10 thousand rows, 34.66 KB (110.61 thousand rows/s., 1.83 MB/s.)
Peak memory usage: 190.39 KiB.

[aliceisjustplaying]

From site settings, I do not have custom events turned on for gathering. The file included on the website is https://plausible.bsky.sh/js/script.outbound-links.js

[ruslandoga]

Seems like Outbound Link: Click is the reason for the discrepancy you are seeing. I can't think of a scenario where a link click is possible without seeing any pages, but somehow "Outbound Link: Click" is sent without a corresponding "pageview".

[aliceisjustplaying]

Wouldn't that indicate an actual bug?...

[ruslandoga]

It could, but since the problem hasn't been reported before, it's equally likely to be a configuration / adblocker issue.

[aliceisjustplaying]

How do even I go debugging this?

[ruslandoga]

You can start with getting more insight into your current data, e.g. looking through the visitors that only have "Outbound Link: Click" events and trying to find something in common between them.

select * from events_v2 where site_id = {site_id:UInt64} and user_id not in (
  select distinct user_id from events_v2 where site_id = {site_id:UInt64} and name = 'pageview'
) order by timestamp format Vertical;

It might also be interesting to check if there are users with both events. If there aren't any it would look more like a bug or configuration issue and less like an adblocker issue.

There is probably a better way to write it, but this should work:

select count(*) from events_v2 where site_id = {site_id:UInt64} and user_id in (
  select distinct user_id from events_v2 where site_id = {site_id:UInt64} and name = 'pageview'
) and user_id in (
  select distinct user_id from events_v2 where site_id = {site_id:UInt64} and name != 'pageview'
);