Deleting spurious data

[adamu]

I'd like to delete some spurious data from the Clickhouse DB, specifically:

A specific URL on a specific day.
https://plausible.adamu.jp/retirewiki.jp?period=day&keybindHint=C&date=2024-10-15&filters=((is,page,(/w/index.php)))

Does anybody have any hints where to start with this or potential gotchas.

The background is that when upgrading to the version of Plausible that moved the exclusions filters server-side, I didn't set it up correctly, so ended up with thousands of entries to index.php that I'd like to remove.

[ruslandoga]

👋 @adamu

You might be able to connect to ClickHouse and delete these records there:

$ cd plausible-ce
$ docker compose exec plausible_events_db clickhouse client -d plausible_events_db

:) delete from events_v2 where timestamp between '2025-10-15' and '2025-10-16' and pathname = '/w/index.php';

:) delete from sessions_v2 where start between '2025-10-15' and '2025-10-16' and pathname = '/w/index.php';

Please double check these queries before executing. Maybe with a corresponding SELECT statement. Note that the dates here are GMT, and the date in URL is local for the site timezone.

[adamu]

Hi @ruslandoga, thanks for the help!

There is no pathname column on sessions_v2, and deleting the records from events_v2 seemed to blank out the session_v2's entry_page column for the corresponding records.

However, I managed to clean it up by deleting the records from sessions_v2 where entry_page = ''.

delete from events_v2 where pathname = '/w/index.php';
delete from sessions_v2 where entry_page = '';

Now no random spike in the history :) https://plausible.adamu.jp/retirewiki.jp?period=all

Thanks again!