Data engineering my way to analytics
Introduction
It might be silly, but I find it reassuring to know that someone is reading me out there. Of course, I mostly write for myself, blogging is not something I believe should be done for 'fame' or popularity. Most of us on the indieweb probably write to the void anyway. But once in a while, someone tells me they read a post of mine (either IRL or online) and it just makes my day.
The thing is, I'm pretty blind on this website. I don't want to use any client-side analytics suite, thought I don't mind using server-side analytics. The requests reach my VPS, and my Caddy reverse proxy logs them, so I don't think there's anything wrong with looking at the data. I love data, it's who I am.
So I built a simple ETL pipeline to ingest these Caddy logs and measure some basic stats on my visitors. To follow this setup, it might be useful for you to check out my meta page describing the architecture of my VPS and of this website.
If you follow me via my RSS feed, you might notice I just added a ?source=rss parameter to each URL. That's because HTTP Referrers are not super trustworthy when it comes to distinguishing RSS access from direct requests, so this makes it a bit easier to know how many people are actually clicking on the links in their RSS feeds.
I have zero interest in knowing what you had for lunch and anything beyond the fact that you exist and read my blog. So my privacy policy is as simple as it can get: I don't build a profile of you, I don't track you across sites, and I don't share anything with anyone else. The analytics database doesn't keep your IP address: I read it only to compute a per-day keyed hash for visitor counts, then drop it.1 I do keep the request metadata that comes with the log line (page, status, user agent, referrer, and so on) to build those aggregates. You are one drop in the traffic to this website, not a person I'm trying to identify.
A simple ETL pipeline for logs
My goal was to keep the setup as simple as possible. I have a basic Python project organized like this:
root/
pyproject.toml
run.sh # Ingest, build the models, and send the report
src/
vps_analytics/
ingest.py # Ingest new log files into the raw table
report.py # Generate and mail a report based on the tables
profiles.yml # Define the duckdb connection in dbt
dbt_project.yml # dbt project definition
models/ # Where models live
macros/ # Where macros live
seeds/ # Static tables accessible in dbtThis is a combined Python + dbt project, where the Python scripts ingest.py and report.py are responsible for ingestion and reporting, while dbt handles the data modeling in the local warehouse (I chose duckdb here), and where a simple bash script run.sh orchestrates all of it.
Data modeling
I'm going to present things in a bit of a reverse order here and focus first on the data modeling aspect. In my opinion, this is the most crucial aspect of this little project, the rest is just plumbing to make it work on my VPS and generate the report I need. You could swap those with all sorts of scripts or connections to analytics tools.
When creating a data model, the first thing you need to look at is what kind of data you have. Caddy's logs are structured JSON which roughly look like this (first tab)
{
"level": "info",
"ts": "2026-11-21T08:47:34Z",
"logger": "http.log.access.access",
"msg": "handled request",
"request": {
"remote_ip": "203.0.113.42",
"remote_port": "54321",
"client_ip": "203.0.113.42",
"proto": "HTTP/2.0",
"method": "GET",
"host": "nchagnet.eu",
"uri": "/blog/you-should-try-gleam?source=rss",
"headers": {
"User-Agent": [
"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36"
],
"Referer": [
"https://example.com/article"
]
},
"tls": {
"proto": "h2",
"server_name": "nchagnet.eu"
}
},
"bytes_read": 0,
"duration": 0.003407,
"size": 39253,
"status": 200
}{
"level": "info",
"ts": "2026-11-21T08:47:34Z",
"logger": "http.log.access.access",
"msg": "handled request",
"request": {
"proto": "HTTP/2.0",
"method": "GET",
"host": "nchagnet.eu",
"uri": "/blog/you-should-try-gleam?source=rss",
"headers": {
"User-Agent": [
"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36"
],
"Referer": [
"https://example.com/article"
]
},
"tls": {
"proto": "h2",
"server_name": "nchagnet.eu"
},
"client_hash": "4b6cd74cf37dd09b23524de3fe8792bddf7d66310de9d8eda465a8b132c59b45"
},
"bytes_read": 0,
"duration": 0.003407,
"size": 39253,
"status": 200
}That's a lot of information from which we can easily extract:
- a
unique_visitorcount by looking at requests from the same IP (which I hash per day for anonymization, see stored payload),2 - the source of the request from both the
Refererattribute and thesourceGET parameter (useful because RSS readers have very variableReferervalues and don't always equate 'clicking on a link'), - browsers vs scrapers vs bots based on the
User-Agent(I mean, up to spoofing, I'm sure most bots just hide themselves but what can you do 🤷), - the page and subdomain requested,
- whether the request was successful (HTTP status code can tell us about issues like broken links),
- Mobile vs desktop clients (as well as git requests, etc.)
That's about as much as I care to measure to be honest. So, how do we get there? This is all possible with the proper data modeling.
- The raw data is ingested, append-only, into a
raw.caddy_requeststable where the JSON is stored as-is inside apayload JSONcolumn. - The raw data is incrementally parsed into a
stg_caddy__requeststable (by ingestion time, not by requests time). This is the step where- JSON data is parsed and extracted,
- URIs are normalized (trailing slashes can be quite a pain),
- hosts, paths and sources are normalized.
- The intermediate table
int_requests__classifiedjoins the staged data with the fixed seed that maps each subdomain to an application. That gives me theappcolumn, and it's also where I classify each request by client and device (mobile, desktop, AI crawler, robot, etc.) from the user agent and path. - The classified requests are then appended to a fact table
fct_requestswhere each row is a separate request. - Finally, the derived fact table acts as our source of truth to produce 'marts' i.e. consumable aggregated tables:
mart_daily_top_pathscontains the most visited paths per day (max 10),mart_daily_summarycontains all the aggregated statistics for a full day.
I made a few key decisions when building this setup:
- using a multi-stage data model: that may be overkill for this application with a single source and clean data, but it's always a good habit to have,
- use incremental tables by ingestion datetime: request logs can be ridiculously large, mostly because of bots and scrapers, but also because peak traffic can get quite intense if a post gets popular on HN. Since this reporting is going to hopefully run continuously for months, maybe years, an incremental setup is necessary to avoid reprocessing the full history on every run.
Connecting this model to logs and reports
This is where all the plumbing happens. I mentioned the two Python scripts before:
ingest.pyreads the logs line by line, hashing IPs as it does (to avoid keeping any private info), and stores them in the warehouse raw table.report.pyreads both marts tables, creates a summary text report, and emails it (to myself) using SMTP credentials.
A daily cron job runs run.sh, which does the three steps in order: ingest the new log lines, run dbt build, and send the report.
The content of the email is simple yet informative:
Subject: Caddy traffic 2026-11-20 - 11,292 requests
Caddy traffic for 2026-11-20
Requests 11,292
Browser requests 4,420
Mobile 433
Desktop 3,987
Feed requests 1,512
Git requests 2,580
Bot / script requests 2,689
AI crawlers 229
Other requests 91
Unique browser visitors 190
Browser page views 292
Page views per visitor 1.54
Bot share 23.8%
4xx responses 1,965
5xx responses 0
Bytes served 71.4 MB
Top app blog
Top path blog /
Top referrer direct
Page views by source untagged (292)
Last request (UTC) 2026-11-20T22:06:06
Top pages
1. blog / 76
2. blog /blog/you-should-try-gleam 60
3. blog /blog/ode-unsung-heroes 21
4. blog /blog/bayesian-statistics-for-confused-data-scientists 11
5. blog /links 10
6. blog /blog 9
7. blog /blog/pokemon-team-optimization 9
8. blog /blog/physics-concepts-explained/quantum-mechanics-i... 8
9. blog /blog/chef-gaston 7
10. blog /blog/desert-island-languages 6What can we say about these numbers?
At a glance, the numbers aren't too crazy:
- 60% of requests are automated in some form (git, feeds, bots).
- The number of requests is one order of magnitude larger than the number of page views (so each page needs around 15 internal requests).
- I can see directly there were no server errors (5xx). Thank god, but also most of the requests are aimed at my static website, so it was unlikely to happen anyway.
- There are quite a few 4xx errors. Most are spoofed user-agent clients, bots pretending not to be bots and probing for vulnerabilities.
- The top pages match quite well with those that are most shared (lots of views from HN or reddit, shared on my LinkedIn, etc.)
That's it: two Python entry points, a small dbt project, and a daily run script gave me server-side analytics on the cheap with no frills. I didn't need to spy, didn't need to add some evil privacy-violating script, a simple aggregate of all traffic is all I need to derive insights. Plus, I had fun building it!
-
The original Caddy log files on disk still contain your IP until Caddy rotates them. Not much I can do about that, but the analytics database itself never stores the IP, just a per-day hash. ↩︎
-
A visitor here is one distinct per-day client hash with at least one browser page view. It's approximate: NAT can collapse several people into one, a changing IP can split one person into several, and a bot with a browser user agent can slip in. ↩︎