Querying Classicpress database for page hits

Now that I am running classicpress, I miss the statistics that I received at wordpress.org. I don’t want to install third party cookies or other adware stuff, so I was looking to see how to solve that completely locally. Here’s how I got it working, withouts ads, all local, for free.

I installed the “WP Post Statistics (Visitors & Visits Counter)” plugin (not the pro version), which immediately starts counting and can show some statistics in the admin dashboard. You can find these statistics in the classicpress database too, so I plan on creating a metrics endpoint so that I can scrape this from my metrics server.

For those interested, this is how you get some of these metrics from the classicpress database (a mariadb database in my case):

select posts.id, posts.post_date, posts.post_title, count(visits.id) as visitcount
from cp_posts as posts
join cp_poststats_visits as visits on posts.id = visits.post_id
group by posts.id, posts.post_date, posts.post_title
order by visitcount desc;

This produces output somewhat like this:

+------+---------------------+-----------------------------------------------+------------+
| id   | post_date           | post_title                                    | visitcount |
+------+---------------------+-----------------------------------------------+------------+
| 1418 | 2011-08-27 14:57:33 | How to fix a Krups XN2001 Nespresso machine   |         14 |
| 2418 | 2024-09-01 17:35:45 | Remove DataTool Alarm                         |         13 |
| 2474 | 2025-10-20 19:54:18 | Gardena Sileno verbindingsprobleem opgelost   |         13 |
| 2392 | 2022-01-30 14:12:06 | Exit WordPress                                |         12 |
| 2358 | 2021-05-23 10:32:10 | When munin df does not list all disks         |          7 |
...

There is even information about countries and cities:

select country, count(*) from cp_poststats_visits group by 1 order by 2 desc;

+-----------------+----------+
| country         | count(*) |
+-----------------+----------+
| The Netherlands |      102 |
| United States   |       20 |
| Spain           |        2 |
| France          |        1 |
| Greece          |        1 |
+-----------------+----------+

I know, the numbers are not impressive yet (welcome back Greece 🙂 ), but this new server has only been up for 2 days after www.rolfje.com being offline for quite some time now. It looks like it will pick up traffic again, as all content is available at the original urls.

Of course there are more tables to explore in your classicpress database (be careful, create backups):

show tables

+----------------------------+
| Tables_in_classicpress     |
+----------------------------+
| cp_commentmeta             |
| cp_comments                |
| cp_links                   |
| cp_object_relationshipmeta |
| cp_object_relationships    |
| cp_options                 |
| cp_postmeta                |
| cp_posts                   |
| cp_poststats_visits        |
| cp_term_relationships      |
| cp_term_taxonomy           |
| cp_termmeta                |
| cp_terms                   |
| cp_usermeta                |
| cp_users                   |
+----------------------------+

Have fun digging around!