Mimic Clickhouse queries' numbers to numbers reported in dashboard

[measurestrategy]

Hi, thanks in advance for the help.
I'm using Plausible Community Edition and I would like to run some queries directly in Plausible's Clickhouse db to automate some data extractions so trying basic queries to check first place if numbers are matching what we see in the dashboard and avoid the classic "numbers don't match!"

For the exercise I select a date range 2 Oct to 6 Oct
GMT+00:00 UTC in time zone configuration because timestamps are “absolute time”, or in another words, assume “always UTC”
interface reports 1,834,947 unique visitors.

This query reports 1,825,034

SELECT COUNT(DISTINCT user_id) as unique_visitors
FROM "plausible_events_db"."sessions_v2"
WHERE timestamp BETWEEN '2024-10-02 00:00:00' AND '2024-10-06 23:59:59'

In all fairness this case it is just -0.54% difference between results but sometimes is higher so is there anything I'm missing in my query that could make it match the dashboard?

Regards

[ruslandoga]

👋 @measurestrategy

Maybe timezones?

[ruslandoga]

Try this one

SELECT COUNT(DISTINCT user_id) as unique_visitors
FROM "plausible_events_db"."sessions_v2"
WHERE timestamp BETWEEN toTimeZone('2024-10-02 00:00:00', 'Europe/Vienna') AND toTimeZone('2024-10-06 23:59:59', 'Europe/Vienna')

but replacing 'Europe/Vienna' with the timezone you picked for your site during its creation (can be checked in site settings).

[ruslandoga]

And Plausible actually uses uniq instead of COUNT(DISTINCT user_id)

So you might also try

SELECT uniq(user_id) as unique_visitors
FROM "plausible_events_db"."sessions_v2"
WHERE timestamp BETWEEN toTimeZone('2024-10-02 00:00:00', 'Europe/Vienna') AND toTimeZone('2024-10-06 23:59:59', 'Europe/Vienna')

[measurestrategy]

Thanks both. Time zone was first thought but apparently not the issue.

• timeZoneOf(timestamp) shows UTC, which is expected.
• Reporting Timezone is set to GTM+00:00 UTC
• It should not be required the use of toTimeZone(timestamp, 'UTC') because reports and timestamps are in the same time zone but even if I use it, this is the query:

SELECT
COUNT(DISTINCT(user_id)) as unique_visitors
FROM "plausible_events_db"."sessions_v2"
WHERE timestamp >= toTimeZone(toDateTime('2024-10-02 00:00:00'), 'UTC') AND timestamp <= toTimeZone(toDateTime('2024-10-06 23:59:59'), 'UTC')

It still gives me 1,825,034 and not the 1,834,947 unique visitors I see in the report
If I narrow down the query by adding something like (WHERE) ... AND hostname = 'mywebsite.com' the discrepancy increases

[ruslandoga]

Ah, yes. If the site's timezone is GTM+00:00 UTC, then toTimeZone is indeed unnecessary.

Do you have system.query_log table enabled in ClickHouse? If so, you can check for the queries that Plausible makes there.

[measurestrategy]

Ah, the use of uniq(user_id) gets the numbers not perfect but a bit closer

This shows 1,832,724 compared to 1,834,947

SELECT
uniq(user_id) as unique_visitors
FROM "plausible_events_db"."sessions_v2"
WHERE timestamp >= '2024-10-02 00:00:00' AND timestamp <= '2024-10-06 23:59:59'

Thanks again.