BLOG POST

Me and my friend, analytics

We’re good buds.

Kevin WangAug. 21, 2026, 9:27 a.m. GMT-4

A few days ago, Bryce sent me Dax's video about Cloudflare Pipelines. Ironically I had saw it on X earlier and doom-scrolled right past it. Silly me. So, I gave it another watch. Neat, more stuff on my stack of things to check out.

I will circle back to this...

I’ve been itching, for quite some time, to set up my own analytics for my website; effectively something that isn‘t Google Analytics. Something where I could dump arbitrary events — OpenTelemetry-like data perhaps — and also be able to query it in, ideally with SQL, but not some gnarly BigQuery SQL dialect; no thanks.

ClickHouse®#

I am partial to ClickHouse®, after having worked with their cloud offering for over a year now. I’ve simultaneously grown to love it and have been spoiled by it. And when you’ve invested a decent amount of time and energy into grinding at something, you get a little reluctant to let go of it, right? I think so.

The bulk of my ClickHouse® experience is on the querying and aggregation side, and a handful of backfills from Postgres to recreate some historical business facts in a new ClickHouse® cluster; less so on the ingestion side, though I learned the hard way early on that ClickHouse® doesn’t like it when you do ~100 single-row inserts per second; and not at all on the operating — I made an intentional choice to steer clear of operational burden and resposibilities.

That started around around July 2025, when frontier models like GPT 5.0 still kind of sucked at ClickHouse®. In fact, I don’t remember “agents” or “harnesses” being super hot yet either. So I got to spend a decent amount of time, reading docs, testing out queries through trial and error, as opposed to delegating most of it to an agent to figure out.

I built the service that aggregates the user retention and churn analytics that you see on the Clerk dashboard overview. Each vertical bar is precomputed, and would otherwise take upwards of 15-20 seconds to query for larger tenants.

Clerk dashboard; User analytics for one of my apps.

Here's the overview of the pipeline; arguably super basic. My approach was to start stupid-simple with direct queries, but if they ran too slow — > ~1-2s — for our largest tenants, that would be a non-starter and would warrant pre-computation, hence the materialized views below. Eventually I landed on the following architecture, 100% on ClickHouse® primitives.

Back to ClickHouse® for personal projects though, I cannot justify paying for it. Their cheapest cloud offering starts at $161/month for an always-on cluster; Way too expensive. I also won’t go into the embarrassing number of times I’ve fired off desperated cries to GPT 5.4, 5.5, and 5.6 in hopes of some magically-simple way to self-host ClickHouse® as cheaply as possible, only to arrive at the conclusion of, ”Ain’t nobody got time fo dat...”

Personal analytics#

Ok, so, what can I use for my humble little website that is low-touch, and cheap?

Here is what I have tried or considered:

TinyBird: Basically gives you a full pipeline — ingestion endpoint, storage, and retrieval. Despite being a very cool product, it has always felt a bit too abstracted to me. Cool to play with, but felt like there was too much magic going on, and never really stuck with me. Their events API was always a killer feature though. Ingestion is challenging to get right.

Workers Analytics Engine by CloudFlare: This is another flavor of ClickHouse®, basically, but also felt slightly too awkward for my taste. This would put the onus on me to handle write the ingestion logic — buffering, batching, error handling — which is something I was not super interested in rebuilding just for my website.

Cloudflare Pipelines#

Alas, we arrive back at the top: Cloudflare Pipelines

Everything about them is perfect for my personal website usage.

They’re pretty cheap: unlimited ingress, and free 50gb of both SQL transforms and sink egress. Who doesn’t love $free.99?
They’re terraform-able: Who doesn’t appreciate infrastructure-as-code?

Am I sounding like an advertisement yet?

The biggest feature for me is the simple HTTPS endpoint for ingestion. A quick test with hey showed 100% successful responses at 10k requests and 80 concurrent connections. Nice.

hey -n 10000 -c 80 \
  -m POST \
  -H "Content-Type: application/json" \
  -d '[{"value":{"example":"json_value"}}]' \
  $INGESTION_ENDPOINT

A close second is the ease of querying the final data that sits in an R2 bucket, via SQL. These queries are kind of slow, but that’s totally acceptible for my use case.

Overall, it’s a nice package that delivers simplicity while taking care of an otherwise complex technical problem. Great value, I’d say.

Here’s the overview of the analytics for this site.

You can see the querying side of things at /analytics.

Pipeline Schema#

{
  "fields": [
    { "name": "schema_version", "type": "int32", "required": true },
    { "name": "event_id", "type": "string", "required": true },
    { "name": "anonymous_id", "type": "string", "required": false },
    { "name": "user_id", "type": "string", "required": false },
    { "name": "session_id", "type": "string", "required": true },
    { "name": "page_view_id", "type": "string", "required": false },
    { "name": "sequence", "type": "int64", "required": true },
    {
      "name": "occurred_at",
      "type": "timestamp",
      "unit": "millisecond",
      "required": true
    },
    { "name": "type", "type": "string", "required": true },
    { "name": "path", "type": "string", "required": true },
    { "name": "data", "type": "json", "required": true },
    { "name": "country_code", "type": "string", "required": false },

    { "name": "consent_analytics", "type": "bool", "required": false },
    { "name": "cookies_enabled", "type": "bool", "required": false },
    { "name": "do_not_track", "type": "string", "required": false },
    {
      "name": "global_privacy_control",
      "type": "bool",
      "required": false
    },

    { "name": "page_origin", "type": "string", "required": false },
    { "name": "referrer_origin", "type": "string", "required": false },

    { "name": "user_agent_original", "type": "string", "required": false },
    {
      "name": "browser_brands",
      "type": "list",
      "required": false,
      "items": { "type": "string" }
    },
    { "name": "browser_mobile", "type": "bool", "required": false },
    { "name": "browser_platform", "type": "string", "required": false },
    { "name": "browser_language", "type": "string", "required": false },
    { "name": "webdriver", "type": "bool", "required": false },

    { "name": "timezone", "type": "string", "required": false },
    {
      "name": "timezone_offset_minutes",
      "type": "int32",
      "required": false
    },

    { "name": "screen_width_px", "type": "int32", "required": false },
    { "name": "screen_height_px", "type": "int32", "required": false },
    { "name": "viewport_width_px", "type": "int32", "required": false },
    { "name": "viewport_height_px", "type": "int32", "required": false },
    { "name": "device_pixel_ratio", "type": "float64", "required": false },
    { "name": "max_touch_points", "type": "int32", "required": false },
    {
      "name": "hardware_concurrency",
      "type": "int32",
      "required": false
    },
    { "name": "device_memory_gib", "type": "float64", "required": false }
  ]
}

Querying the R2 data catalog#

eval $(cat .env.production.local)
# R2_BUCKET_NAME
# R2_TOKEN - for `sql` authentication
export WRANGLER_R2_SQL_AUTH_TOKEN=$R2_TOKEN

# Get warehouse name
pnpx wrangler r2 bucket catalog get $R2_BUCKET_NAME
export WAREHOUSE=...

# Discover the catalog.
pnpx wrangler r2 sql query "$WAREHOUSE" "SHOW TABLES IN default;"
# Save a table name
export R2_TABLE_NAME=...
pnpx wrangler r2 sql query "$WAREHOUSE" "DESCRIBE default.$R2_TABLE_NAME;"

# Query
pnpx wrangler r2 sql query "$WAREHOUSE" "
  SELECT event_id, occurred_at, type, path, country_code
  FROM default.$R2_TABLE_NAME
  WHERE __ingest_ts >= now() - INTERVAL '48 hours'
  AND __ingest_ts < now() + INTERVAL '1 hour'
  ORDER BY occurred_at DESC
  LIMIT 5;
"

# Read 411 kB across 45 files from R2
# On average, 31.1 kB / s
# ~15s

# Explain
pnpx wrangler r2 sql query "$WAREHOUSE" "
  EXPLAIN
  SELECT event_id, occurred_at, type, path, country_code
  FROM default.$R2_TABLE_NAME
  WHERE __ingest_ts >= now() - INTERVAL '48 hours'
  AND __ingest_ts < now() + INTERVAL '1 hour'
  ORDER BY occurred_at DESC
  LIMIT 5;
"

# Aggregate
pnpx wrangler r2 sql query "$WAREHOUSE" "
  SELECT
  COUNT(*) AS total_events,
  COUNT(DISTINCT session_id) AS total_unique_sessions
  FROM default.$R2_TABLE_NAME;
"

# Speed it up
pnpx wrangler r2 sql query "$WAREHOUSE" "
  SELECT
  COUNT(*) AS total_events,
  approx_distinct(session_id) AS total_unique_sessions
  FROM default.$R2_TABLE_NAME;
"

https://developers.cloudflare.com/r2-sql/sql-reference/

Terraforming#

# Importing...
# terraform import cloudflare_r2_data_catalog.website_v2 \
#   "$CF_ACCOUNT_ID/$CF_BUCKET_NAME"
# terraform import cloudflare_pipeline_sink.website_v2 \
#   "$CF_ACCOUNT_ID/$CF_SINK_ID"
# terraform import cloudflare_pipeline.website_v2 \
#   "$CF_ACCOUNT_ID/$CF_PIPELINE_ID"
#
# Refreshing state...
# terraform plan -refresh-only

terraform {
  required_version = ">= 1.0.0"

  required_providers {
    cloudflare = {
      source  = "cloudflare/cloudflare"
      version = "~> 5"
    }
  }
}

provider "cloudflare" {
  api_token = var.cloudflare_api_token
}

variable "cloudflare_api_token" {
  description = "Cloudflare API token (cfut_, not cfat_) Needs Pipelines, R2, and Data Catalog permissions."
  type        = string
  sensitive   = true
}

variable "r2_data_catalog_token" {
  description = "Sink token with R2 Admin Read & Write permission."
  type        = string
  sensitive   = true
}

variable "cloudflare_account_id" {
  description = "Cloudflare account ID that owns the analytics pipeline."
  type        = string
}

# Loaded from production.auto.tfvars
variable "names" {
  type = object({
    bucket_name   = string
    namespace     = string
    pipeline_name = string
    table_name    = string
  })
}


# This resource enables and protects the existing catalog for the R2 bucket.
resource "cloudflare_r2_data_catalog" "website_v2" {
  account_id  = var.cloudflare_account_id
  bucket_name = var.names.bucket_name

  lifecycle {
    prevent_destroy = true
  }
}

resource "cloudflare_pipeline_sink" "website_v2" {
  account_id = var.cloudflare_account_id
  name       = "${var.names.pipeline_name}_sink"
  type       = "r2_data_catalog"

  format = {
    type            = "parquet"
    compression     = "zstd"
    row_group_bytes = 256 * 1024 * 1024
  }

  schema = {
    fields = []
  }

  config = {
    account_id = var.cloudflare_account_id
    bucket     = var.names.bucket_name
    namespace  = var.names.namespace
    table_name = var.names.table_name
    token      = var.r2_data_catalog_token

    rolling_policy = {
      file_size_bytes  = 100 * 1024 * 1024
      interval_seconds = 300
    }
  }

  depends_on = [cloudflare_r2_data_catalog.website_v2]

  lifecycle {
    # Note: must be replaced. Cannot be Updated.
    # Cannot be Deleted either if in use.
    prevent_destroy = true
  }
}

resource "cloudflare_pipeline" "website_v2" {
  account_id = var.cloudflare_account_id
  name       = var.names.pipeline_name
  # This sql file feels fragile because it itself has
  # hardcoded sink and stream names
  sql = trimspace(file("${path.module}/website_pipeline_v2.sql"))

  depends_on = [cloudflare_pipeline_sink.website_v2]

  lifecycle {
    prevent_destroy = true
  }
}

output "analytics_stream_name" {
  description = "The structured stream that is managed by Wrangler."
  value       = "${var.names.pipeline_name}_stream"
}

output "analytics_sink_id" {
  description = "Cloudflare ID of the R2 Data Catalog sink."
  value       = cloudflare_pipeline_sink.website_v2.id
}

output "analytics_pipeline_id" {
  description = "Cloudflare ID of the analytics pipeline."
  value       = cloudflare_pipeline.website_v2.id
}