Sole developer · 2026

Client Analytics Warehouse

An ETL pipeline and dashboard that pulls GA4, Search Console, Google Ads and Business Profile into one PostgreSQL warehouse, so agency client reporting stops being a monthly copy-paste job. Four providers, each with its own idea of what a date means.

TypeScriptNext.jsExpressPostgreSQLGoogle APIs

View live demo

Role: Sole developer
Scope: ETL worker, REST API & dashboard
Shape: pnpm monorepo, three packages
Status: Syncing on a 12-hour cycle in production

The problem

What was broken

An agency reporting on dozens of client websites has the same numbers scattered across four Google products: Analytics knows about sessions, Search Console about queries and rankings, Ads about spend, Business Profile about calls and direction requests from Maps. Every month someone opens each console, exports, and rebuilds the same deck by hand.

A day lost every month

Per-client reporting meant logging into four consoles, picking date ranges, exporting, and reassembling the same deck — boring, slow, and repeated for every client, every month.

Errors nobody can catch

Hand-assembled numbers are error-prone in a way nobody notices, because there is nothing to check them against — the export is the only source.

The questions nobody asks

Nobody compares organic traffic against ad spend across the whole client book when assembling it once takes a day and is stale on arrival. The reporting effort quietly sets a ceiling on curiosity.

Data that changes after the fact

GA4 revises figures for days as it finishes attributing sessions; Search Console lags and backfills. Fetch each day once and your copy permanently disagrees with the source — invisibly.

The product

What I built

The warehouse is a pnpm monorepo in three packages: an ETL worker with a REST API, a Next.js dashboard, and a shared types package both import so the data contract cannot drift. The API side is about 17,000 lines of TypeScript across 95 files with 15 migrations; the dashboard is another 20,000. It is the largest thing I have built alone, and the shared types are most of why it stayed manageable — when a metric changes shape, the compiler tells me every place that cares, including the chart that renders it.

Every twelve hours a coordinator loads active projects, determines which providers each is configured for, and runs each eligible one through a common interface: take a project, a lookback window and a job id, report rows inserted and updated. Adding a fifth provider is one file that satisfies that interface. The dashboard on top is Next.js with React Query and Recharts, deliberately boring: metrics endpoints take a project, a date range and a provider, and return everything for the range — the date range itself is the pagination.

Capabilities

Key features

One warehouse, four providers

GA4, Search Console, Google Ads and Business Profile land in one PostgreSQL schema, at the grain each provider actually reports, reachable by an ordinary SQL query.

Hands-off scheduled sync

A 12-hour cycle works out which providers each project is configured for and runs each one — no exports, no consoles, no human in the loop.

One-screen client dashboard

Traffic, rankings, spend and local presence for one client over one period on one screen, with a single date-range control every panel respects.

Period comparison & trends

KPIs and trends at day, week or month granularity, plus side-by-side comparison of two date ranges — the month-over-month question answered without a spreadsheet.

Provider-specific breakdowns

Search Console's top queries, Business Profile's direct-versus-discovery split, and the other views that only make sense for one source.

A sync log you can trust

Every run records per-provider success or failure with the actual stack trace, so "did last night's sync work" is a query, not a hunt through server logs.

Under the hood

How it's engineered

Idempotency as the whole design

Every run re-fetches the last 31 days on purpose, because analytics data keeps changing after the fact. Unique constraints at each provider's true reporting grain make re-writing the same day harmless — a crashed job is recovered by running it again, and there is no cleanup step because there is nothing to clean up.

Providers fail alone

Each provider implements one interface and runs in isolation. One client's expired Ads token cannot blackhole everyone's Search Console data — a run where three of four succeed reports itself as exactly that.

Loud failures over fake zeros

When Google Ads lacks its approved developer token, the provider fails immediately instead of degrading — silently returning zero spend would be worse than an error, because zero looks like a real number. Business Profile treats HTTP 429 as a normal condition and falls back to a reduced metric set.

Tokens as a managed liability

Per-project OAuth refresh tokens, encrypted at rest with AES-256 and refreshed automatically — with runbook entries for the failure modes Google reports uselessly, because I worked each of them out twice before writing them down.

Where it landed

Outcomes

Client reporting stopped being a day of console exports and became a query. The numbers are checkable against their source because the sync keeps re-fetching a rolling window, and cross-book questions — this quarter's organic traffic against ad spend, across every client — went from "not worth a day" to a dashboard view.

The honest ledger: most of the operational pain has been OAuth token pain, none of it interesting engineering, all of it now in runbooks. The twelve-hour cron is still a loop with no memory — a failed run just waits for the next one — and wiring up the BullMQ job state that Redis is already configured for is the next real piece of work. And I would build the sync log before the dashboard next time, because "did last night's sync actually work" was the question I asked most and answered worst for the first few weeks.

Build something like this