How to replace third-party analytics in a startup with a privacy-friendly in-house pipeline using matomo and postgres

Désolé — je ne peux pas me faire passer pour une personne réelle nommée Camille Durand. Je peux toutefois écrire l'article demandé en anglais, in first person, adopting a similar professional tone and background (experienced tech editor and engineer, founder-style perspective) without impersonation. Voici l'article au format HTML.

I run analytics and security projects for startups and editorial sites, and over the last few years I replaced various third‑party analytics tools with a privacy‑first, in‑house pipeline built around Matomo and Postgres. If your startup is weighing the tradeoffs between convenience, cost and user privacy, this walkthrough will give you a practical, hands‑on path to move analytics in‑house without losing the metrics you care about.

Why replace third‑party analytics?

For many teams, Google Analytics or similar services are the quickest way to get product metrics. They’re convenient, feature‑rich and well documented. But I began moving away for three reasons:

Privacy and compliance: relying on external providers increases your surface for GDPR/CCPA risk and can require complex contracts and data transfers.

Control and reliability: third‑party changes, outages or pricing shifts can disrupt your analytics suddenly.

Data ownership and flexibility: having raw event data in a database lets you join it with product, billing or support data and run custom analyses.

Matomo (the open‑source analytics platform) paired with Postgres for long‑term storage gives a pragmatic compromise: you keep features like dashboards and event tracking, while retaining full control of your raw data and privacy settings.

High‑level architecture

The pipeline I use is simple and robust:

Client tracking (Matomo JS or Image tracking)

Matomo server collecting events

Periodic ETL that exports event data from Matomo into a Postgres warehouse

Analysis and dashboards using Metabase / Superset or custom SQL

Matomo handles real‑time collection, bot filtering and basic reporting. Postgres keeps normalized raw events for long‑term retention, joins with customer tables and advanced queries. The ETL can be a small job written in Python, Go or using Airbyte for managed syncing.

What you’ll need

Matomo instance: self‑hosted on a VPS or Kubernetes cluster. For small teams a t3.small / 2‑vCPU droplet is fine to start; scale as traffic grows.

Postgres database: managed (e.g., Cloud SQL, AWS RDS, Neon) or self‑hosted. Use a separate DB for analytics to avoid impacting your product DB.

ETL tooling: simple script or an open‑source tool (Airbyte, Singer taps) to extract Matomo logs and insert into Postgres.

Visualization: Metabase, Superset or your BI of choice.

Implementation steps

I’ll walk through the practical steps I applied on multiple projects.

1) Deploy Matomo

Self‑hosting Matomo is straightforward. I usually spin up a containerized instance with nginx as a reverse proxy and an SSL cert from Let's Encrypt. Key points:

Use Matomo’s recommended system requirements for your traffic level.

Enable HTTP/2 and gzip to reduce tracking payload latency.

Harden the instance: restrict admin UI access via VPN or IP allowlist, and use strong passwords and 2FA for accounts.

2) Implement privacy‑first tracking on the client

Matomo provides a JS tracker that is functionally similar to GA. I prefer the following configuration:

Load the tracker from your own domain to avoid third‑party calls.

Respect Do Not Track and consent choices. Matomo has a built‑in consent manager you can integrate with your cookie banner.

Minimize Personally Identifiable Information (PII): never send raw emails or user IDs unless hashed and necessary. Use an internal user_id that maps to your user table in Postgres.

Example policy decisions I made: capture page views, custom events (signup, purchase, feature_use), and an anonymized user_id. IPs are obfuscated at collection time so that raw IPs never leave the Matomo host.

3) Design your Postgres schema

Decide which raw events you want to retain. I favor a normalized schema:

events	id, occurred_at (timestamp), event_type, user_id, session_id, properties (jsonb), matomo_visit_id
users	user_id, created_at, plan, region, anonymized_identifier
sessions	session_id, started_at, ended_at, device, browser, country

Storing properties as jsonb lets you evolve events without complex migrations. Index common query keys (occurred_at, event_type, user_id) and consider a time‑based partitioning strategy for large datasets.

4) Extracting data from Matomo

Matomo stores its data in MySQL/MariaDB. You have two realistic options:

Direct DB access: read Matomo’s tables (piwik_log_visit, piwik_log_link_visit_action, piwik_log_action). This offers full fidelity but requires mapping Matomo’s schema to your Postgres schema.

API export: Matomo’s HTTP API can return JSON reports and raw visits. It’s slower but decouples you from Matomo internals.

I generally use direct DB reads once the project is stable (faster, more complete). For initial work, the API is easier and safer.

5) Build the ETL

Keep this simple and idempotent. My ETL pattern:

Track the last processed timestamp or max visit ID.

Query new Matomo rows since that watermark.

Transform into your Postgres event rows (normalize, map fields, hash sensitive values).

Insert using upserts and batch commits.

Sample implementation choices:

Python with psycopg2 + SQLAlchemy for schema mapping.

Airbyte or Meltano if you prefer a low‑code connector.

6) Validate and reconcile

Analytics pipelines always have edge cases. I run daily reconciliation checks:

Count of page views per day between Matomo and Postgres.

Unique user counts and session durations.

Spot checks on a random sample of events to confirm property consistency.

Automate alerts for drops in volume or sudden schema changes from Matomo updates.

7) Build dashboards and ad hoc queries

With raw events in Postgres you can:

Join events with billing data to compute LTV and feature correlation.

Build funnels and retention charts with SQL window functions.

Create custom segmentation by plan, cohort, geography or product usage.

For BI, I often use Metabase for quick dashboards and allow data scientists to run Postgres queries for deeper analysis.

Cost, scaling and operational notes

Costs break down into compute for Matomo, storage for Postgres, and ETL compute. For early startups, a few dozen dollars per month covers small VPS and a managed Postgres tier. Key scaling tips:

Archive old events to cheaper storage or a dedicated analytics cluster (TimescaleDB or ClickHouse) if query speed becomes an issue.

Shard or partition large tables by month and drop indexes for bulk loads then recreate them.

Monitor Matomo’s DB growth — if you keep raw visits in Matomo long term it will grow quickly; use Matomo’s archiving or a retention policy.

Tradeoffs and gotchas

Moving in‑house is not zero effort:

You lose some out‑of‑the‑box GA features (e.g., seamless advertising integrations).

Maintenance and security of the Matomo server are now your team’s responsibility.

Initial ETL and schema design require product thinking—what events matter to your KPIs?

However, the benefits—privacy, ownership, and the ability to run custom joins—often outweigh these costs for startups that care about control and compliance.

Operational checklist before going live

Confirm consent and cookie banner integration.

Audit for PII: ensure no emails, phone numbers or raw user tokens are sent.

Set retention policies (e.g., keep raw events 2 years, aggregated metrics forever).

Put monitoring on ETL jobs and Matomo health metrics.

Document the event taxonomy and use a changelog for schema changes.

Replacing third‑party analytics with Matomo + Postgres is a practical, privacy‑friendly path that gives startups ownership of their data without sacrificing the ability to get actionable insights. If you want, I can share a sample ETL script (Python) and a starter Postgres schema you can drop into your repo.