Back

Data engineering my way to analytics

Open water

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:

Project structure
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 dbt

This 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)

Caddy log
{
  "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
}
Stored payload
{
  "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_visitor count 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 Referer attribute and the source GET parameter (useful because RSS readers have very variable Referer values 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.

  1. The raw data is ingested, append-only, into a raw.caddy_requests table where the JSON is stored as-is inside a payload JSON column.
  2. The raw data is incrementally parsed into a stg_caddy__requests table (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.
  3. The intermediate table int_requests__classified joins the staged data with the fixed seed that maps each subdomain to an application. That gives me the app column, 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.
  4. The classified requests are then appended to a fact table fct_requests where each row is a separate request.
  5. Finally, the derived fact table acts as our source of truth to produce 'marts' i.e. consumable aggregated tables:
    • mart_daily_top_paths contains the most visited paths per day (max 10),
    • mart_daily_summary contains all the aggregated statistics for a full day.
InputsStaging · incrementalIntermediate · viewMartsraw.caddy_requestsevent_idVARCHARPKingested_atTIMESTAMPlog_fileVARCHARpayloadJSONapp_hostsmatch_typeVARCHARhostVARCHARappVARCHARstg_caddy__requestsevent_idVARCHARPKingested_atTIMESTAMPlog_fileVARCHARloggerVARCHARclient_hashVARCHARmethodVARCHARhostVARCHARuriVARCHARpathVARCHARqueryVARCHARsourceVARCHARstatusINTEGERbytesBIGINTduration_msDOUBLEuser_agentVARCHARreferrerVARCHARtls_alpnVARCHARprotocolVARCHARrequest_tsTIMESTAMPTZrequest_dateDATEint_requests__classifiedevent_idVARCHARPKingested_atTIMESTAMPlog_fileVARCHARloggerVARCHARclient_hashVARCHARmethodVARCHARhostVARCHARuriVARCHARpathVARCHARqueryVARCHARsourceVARCHARstatusINTEGERbytesBIGINTduration_msDOUBLEuser_agentVARCHARreferrerVARCHARtls_alpnVARCHARprotocolVARCHARrequest_tsTIMESTAMPTZrequest_dateDATEappVARCHARclient_typeVARCHARrequest_kindVARCHARstatus_classVARCHARreferrer_hostVARCHARreferrer_groupVARCHARdevice_typeVARCHARis_ai_crawlerBOOLEANis_botBOOLEANis_page_viewBOOLEANis_browser_page_viewBOOLEANfct_requestsevent_idVARCHARPKingested_atTIMESTAMPlog_fileVARCHARrequest_tsTIMESTAMPTZrequest_dateDATEclient_hashVARCHARprotocolVARCHARmethodVARCHARhostVARCHARappVARCHARuriVARCHARpathVARCHARqueryVARCHARsourceVARCHARstatusINTEGERstatus_classVARCHARbytesBIGINTduration_msDOUBLEuser_agentVARCHARreferrerVARCHARreferrer_hostVARCHARreferrer_groupVARCHARtls_alpnVARCHARrequest_kindVARCHARclient_typeVARCHARdevice_typeVARCHARis_ai_crawlerBOOLEANis_botBOOLEANis_page_viewBOOLEANis_browser_page_viewBOOLEANmart_daily_summaryreport_dateDATEPKrequestsBIGINTbrowser_requestsBIGINTmobile_browser_requestsBIGINTdesktop_browser_requestsBIGINTvisitorsBIGINTbrowser_page_viewsBIGINTpage_views_per_visitorDOUBLEfeed_requestsBIGINTgit_requestsBIGINTbot_requestsBIGINTai_crawler_requestsBIGINTbot_share_pctDOUBLEother_requestsBIGINTerrors_4xxBIGINTerrors_5xxBIGINTbytes_servedHUGEINTtop_appVARCHARtop_pathVARCHARtop_referrerVARCHARtop_sourcesVARCHARlast_request_atTIMESTAMPmart_daily_top_pathspath_keyVARCHARPKreport_dateDATEappVARCHARpathVARCHARpage_viewsBIGINTpath_rankBIGINT parse + normalizeclassifyhost → apppublishaggregate by dayrank top 10

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.py reads 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.py reads 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:

Email report
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                                  6

What 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!


  1. 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. ↩︎

  2. 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. ↩︎