Marketing · Analytics · Dashboards

Every client we onboard has the same setup: Search Console open in one tab, Google Analytics 4 in another, Meta Ads Manager in a third. Three dashboards, three login flows, three different concepts of "yesterday" because the Search Console two-day reporting lag desyncs everything. The marketing person spends fifteen minutes a day translating between them and writes down three numbers in a Notion doc. We built our marketing analytics rollup because we got tired of being the person someone screenshots to whenever the Search Console interface changes its date picker.

The tab-switching tax

Most agency clients pay for at least one analytics SaaS. Looker Studio, HubSpot Marketing Hub, Triple Whale, Northbeam. They all promise the same thing: connect your channels, see your numbers, make decisions. They cost between fifty and five hundred US dollars a month, and ninety per cent of them are abandoned within sixty days. We watch the dashboards in the Slack channel get more and more lonely. Nobody opens them. The marketing person eventually starts pasting screenshots from the native Google and Meta interfaces back into Slack because that's what they trust.

The reason is unglamorous: those SaaS tools are designed to be impressive, not to be used. They have eighteen widgets per dashboard, three different ways to compare last-30-days, and a setup wizard that takes ninety minutes. What our clients actually want is a single page with the four or five numbers that matter, refreshed daily, where the historical chart shows the trend so they don't need to memorise last week's number. So we built that.

What "rollup" actually means

The word "rollup" gets thrown around so loosely that it's worth pinning down what we mean. In our system, a rollup is a daily snapshot of one source's key metrics, stored in a single row in Postgres. For Search Console that's clicks, impressions, CTR, and average position. For GA4 it's sessions, conversions, page views, and bounce rate. For Meta Ads it's spend, impressions, conversions, and reach. One row per source per project per day. That's the whole storage model.

The trick is that all three sources roll up into the same table — analytics_snapshots — keyed by source. So when the dashboard wants to render last 30 days of sessions, it pulls thirty rows where source = google_analytics and reads the sessions field from each row's metrics JSONB. When it wants Search Console clicks, same query, different source filter. The aggregate KPI strip at the bottom of the dashboard reads from all three sources and sums them. One schema. Three providers. No joins, no view materialisation, no warehouse layer.

Why OAuth, not CSV uploads

The first thing every analytics tool offers is a CSV upload. Drag your weekly export in, and we'll chart it. We considered this for about an hour. It's a terrible user experience. The marketing person has to remember to do the export every week. They forget. The data goes stale. They start mistrusting the dashboard. They stop opening it. We've watched this happen on three previous projects.

OAuth is the only path that actually works long-term. The user clicks "Connect Google Search Console" once, grants read-only access to their property, and from that point on the daily cron pulls fresh data automatically. They never think about it again. The downside is that OAuth is six different rabbit holes you have to dig through to get to a working refresh-token flow, and that's why most agencies don't build it. We did, because we got tired of the CSV problem.

Three providers, three slightly different OAuth shapes

Google Search Console and Google Analytics share a single OAuth client. You request one scope set, get one consent screen, store one refresh token. From the user's perspective they're "connecting Google" once, even though our schema treats them as two separate connections. We do this because the scopes are different (webmasters.readonly versus analytics.readonly) and because most marketing tools store them separately anyway, which means the user can revoke one without losing the other.

Meta is its own beast. Their OAuth is conceptually similar but the tokens are long-lived (sixty days) and there's no refresh-token grant — instead you re-exchange the access token itself. The Insights API also wants you to specify an ad account ID, which the user has to pick from a list of accounts they have access to. So Meta gets a property-picker step that Google Search Console doesn't need. Three connections, three slightly different post-OAuth flows, all converging on the same marketing_connections row schema.

Encrypted refresh tokens in Postgres bytea

OAuth refresh tokens are credentials. If someone gets your refresh token, they have the ability to silently pull your Search Console data forever, or until you remember to revoke. So we encrypt them at rest using AES-256-GCM before they hit the database. The encryption key is stored as a Netlify environment variable. The ciphertext lives in a bytea column. The IV lives next to it. If the database leaks, the tokens are still safe because the key isn't in there.

This sounds straightforward. It wasn't. Supabase JS serialises Node Buffers as JSON objects, which means our first attempt at storing the ciphertext stored the literal string {"type":"Buffer","data":[...]} as the bytes of the bytea column. Decryption failed with the cryptic message "Invalid initialization vector". The fix was to serialise to the Postgres-native bytea hex-input format (\xDEADBEEF) instead of letting supabase-js stringify a Buffer. Two days of debugging that we wrote up separately because it'll catch you off-guard if you don't know to look for it.

The daily cron is idempotent on purpose

The fetch jobs run nightly at 02:00, 02:15, and 02:30 UTC for Search Console, GA4, and Meta respectively. Each job pulls the last thirty days of data and upserts into analytics_snapshots keyed on (project_id, source, period_start, period_end). The unique constraint guarantees that re-running the job ten times in a row produces exactly the same result as running it once. We use this property aggressively: when a user first connects a provider, we trigger the cron manually so they see thirty days of history immediately instead of waiting until tomorrow morning.

Idempotency also means we can fix a bug, redeploy, and re-fire yesterday's job without worrying about double-counting. The cost of re-fetching thirty days from the Google API is about two seconds. The cost of the upsert is a single round-trip to Postgres. Neither is anything to optimise — they're trivially cheap, and the trade-off pays for itself the first time the Search Console API returns a weird late-arriving correction for a date you already pulled.

Expandable chart cards: the design decision

Looker Studio gives you a grid of fifteen widgets. We give you four. The reason is attention economics. The marketing person is going to spend forty-five seconds on this page before context-switching to something else. If we make them scan fifteen widgets, they'll bounce. If we make them scan four big numbers with a small trend line under each, they'll absorb everything in one breath.

Each card starts collapsed: a label, the big rolled-up number for the last thirty days, and a sparkline. That's enough for the daily glance-and-go. If something looks weird — clicks dropped twelve per cent, conversions doubled, ad spend spiked — the user clicks the card to expand it. The sparkline morphs into a full line chart with axes, daily tooltips, and a 260-pixel tall canvas. The animation is a single LineChart component that just toggles axis visibility and forces a remount via key to replay the left-to-right stroke trace. Recharts handles the drawing; we just orchestrate the transition.

What we are not building yet

We're deliberately not building three things that the SaaS tools have. We're not building cross-channel attribution — figuring out which channel deserves credit for a conversion is a research project disguised as a feature, and most of the published methods are flawed. We're not building forecasting — the marketing person trusts their own gut more than a regression line, and we agree with them. And we're not building campaign-level breakdowns — those live in the native interfaces, where the user has to be anyway when they're making creative decisions.

What we're building is a daily glance. Did the numbers go up or down. By how much. Is there a trend or is yesterday an outlier. That's it. The dashboard is the answer to a single question every morning. If we add too much, we lose that property.

The verdict

We spent about ten hours building the first version of this dashboard. The OAuth flows took five of those hours, mostly because of the encryption fix mentioned above and a navigator-locks deadlock in supabase-js that hung the queries silently. The data layer was an afternoon. The UI was an evening. The Lookups Studio equivalent would cost the client around $500 a month for the equivalent of three viewing seats plus the data connectors. The breakeven point against agency rates is somewhere around month four, but the bigger win is that the dashboard is now part of the same codebase as the marketing site, the lead form, and the client portal. One auth system. One deploy. One mental model.

Marketing-analytics rollup is a Pro-tier feature in our Velocity X template, so the client pays nothing per channel and we don't pay anything per project. The next iterations will add Pipedrive and HubSpot as additional rollup sources, which we're holding off on until we have actual demand. The principle stays the same: one Postgres table, one daily cron, four numbers that matter.