A performance marketing agency was spending every Monday morning pulling numbers from four platforms into a spreadsheet before client reports could start. Five analyst hours, every week, across 23 accounts. We automated the entire pipeline — data is ready at 6am before anyone opens their laptop.
Client
Peak Agency
Year
2025
Type
API Integration
Stack
GA4 API · Meta Marketing API · Google Ads API · Stripe · PostgreSQL
Timeline
6 weeks
The Problem
FiveHoursEveryMonday.BeforetheWorkCouldStart.
Peak manages paid media for 23 clients across Meta, Google, and Shopify. Every Monday, a senior analyst pulled the previous week's numbers from Meta Ads Manager, Google Ads, GA4, and Stripe — copying figures into a master spreadsheet, cross-referencing for discrepancies, and formatting per-client summaries before the team could begin writing recommendations.
The process took four to six hours depending on how cooperative Meta's dashboard was that morning. Client reports that should go out Monday afternoon routinely slipped to Wednesday. The analyst doing the pull was their most experienced media buyer — they were spending a quarter of their week on data entry.
4–6 hrs of manual data pull
Senior analyst occupied every Monday morning — unavailable for strategy, client calls, or campaign optimisations.
Reports slipping to Wednesday
Monday data prep pushed report writing to Tuesday, delivery to Wednesday — two days late, every week.
No true ROAS figure
GA4 revenue is modelled. Meta ROAS is self-reported. Neither matched Stripe's actual charge data — but no one was connecting them.
Copy-paste errors
Transposing numbers across four platforms into one spreadsheet, 23 times, weekly — errors were caught after clients had seen them.
The Approach
FourPrinciplesThatShapedtheBuild.
Stripe as the ground truth for revenue
GA4 revenue is attribution-modelled. Meta and Google report revenue based on their own attribution windows, which overlap and double-count. Stripe records actual money that moved. All ROAS figures in the dashboard are calculated against Stripe — not the platform's self-reported numbers.
On first run, Stripe-anchored ROAS was 18–40% lower than platform-reported ROAS across accounts. Clients had been making budget decisions on inflated numbers.
Daily snapshots over live API queries
Live querying all four APIs for 23 accounts on every dashboard load would hit rate limits immediately and produce inconsistent data across a single page view. Nightly snapshots run once, hit the APIs once, and serve consistent data all day from PostgreSQL.
Google Ads API has a strict daily quota that would be exhausted by 8am on live queries. Snapshots use the quota once per day at 3am.
Custom dashboard over Looker Studio
Looker Studio was the obvious choice and the first thing we prototyped. It couldn't express the attribution bridge — joining GA4 sessions to Stripe charges via UTM parameters requires SQL that Looker Studio's data source model can't produce. A custom Next.js dashboard was the only way to get the true ROAS figure.
The prototype took two days in Looker Studio. The limitation was found on day two. Building custom took three weeks but produced numbers Looker Studio structurally cannot.
Benchmark against the client's own baseline
Industry benchmarks are context-free. A 2.5× ROAS is excellent for one category and alarming for another. Each client's dashboard compares the current week against their own 90-day rolling average — giving account managers meaningful context without needing to remember industry norms.
Account managers reported this single feature reduced their Monday morning cognitive load more than anything else in the dashboard.
How It Works
InsidethePipeline.
GA4 — sessions and conversions
The GA4 Data API is queried nightly via the runReport method, pulling session data, conversion events, and revenue by channel for all 23 client properties. GA4's sampling kicks in above certain thresholds — the query is split into date ranges that stay below the sampling threshold to ensure accuracy. Results are written to a normalised sessions table in PostgreSQL.
GA4 Data API
Inngest cron
PostgreSQL
Meta — campaign spend and ROAS
The Meta Marketing API is queried at the campaign, ad set, and ad level for all linked client accounts. Meta enforces strict rate limits per ad account — requests are batched and distributed across a 3-hour window to avoid throttling. Spend, impressions, clicks, and Meta-reported ROAS are written to the campaigns table alongside the account ID and date.
Meta Marketing API
Inngest
PostgreSQL
Google Ads — keyword and campaign performance
Google Ads performance data is pulled via the Google Ads Query Language (GAQL) API. Keyword-level data, quality scores, campaign spend, and conversion values are fetched per client MCC. The Google Ads API has a strict daily quota — a token bucket approach distributes requests across the day to ensure all 23 accounts complete within quota.
Google Ads API (GAQL)
Token bucket rate limiting
PostgreSQL
Stripe — actual revenue
Stripe is the ground truth for revenue. For clients with Stripe connected, the Events API pulls successful charge events, refunds, and subscription revenue for the period. Stripe revenue is the anchor for all ROAS calculations — overriding GA4's modelled revenue figures, which are subject to attribution and sampling errors.
Stripe Events API
PostgreSQL
Attribution bridge
Connecting ad spend to Stripe revenue requires UTM parsing. The pipeline joins GA4 session data (which carries UTM parameters) to Stripe charges using a sessionisation model and a configurable attribution window. The result is a true ROAS figure per channel, per campaign — numbers that GA4 and Meta can't produce independently.
PostgreSQL
UTM parsing
Attribution logic
Dashboard and delivery
A Next.js dashboard pulls from PostgreSQL at 6am daily, presenting each client's data in a consistent format: spend by channel, ROAS by campaign, week-over-week trends, and benchmark comparisons against the client's own 90-day baseline. A PDF export function generates client-ready reports — formatted and branded — in one click.
Next.js
Recharts
PostgreSQL
PDF export
Results
NumbersWorthShippingFor.
Monday data prep (was 4–6 hrs)
0hrs
Reports delivered (were Wednesday)
Mon
Client accounts fully automated
23
ROAS via Stripe (first time available)
True
Key Decisions
CallsWorthExplaining.
Custom Next.js dashboard over Looker Studio
BenefitThe attribution bridge — joining GA4 sessions to Stripe charges via UTMs — requires SQL joins across multiple data sources. Looker Studio can't express this. The custom dashboard was the only path to the true ROAS figure the agency needed.
Trade-offThree weeks to build versus two days for a Looker Studio dashboard. Any future feature (new metric, new chart type) requires a code change. For an agency that doesn't have an in-house developer, that's an ongoing dependency.
PostgreSQL warehouse over a BI tool data model
BenefitA normalised PostgreSQL schema across all four sources means any future BI tool (Metabase, Superset, Redash) can connect immediately. The data model isn't locked to one visualisation layer — the agency can add tools without re-ingesting data.
Trade-offPostgreSQL needs to be maintained, backed up, and scaled. A managed warehouse (BigQuery, Snowflake) would remove operational overhead at a higher cost. For 23 clients and current data volume, PostgreSQL is well within capacity — revisit at 200+ clients.
Inngest for scheduling over Vercel Cron alone
BenefitVercel Cron triggers the job but has no retry logic, no execution history, and no per-step observability. When the Meta API returns a 503 at 3am, Inngest retries the affected accounts — Vercel Cron would silently skip them and the data would be missing all day.
Trade-offAdditional vendor. For a simple pipeline with a single retry, Vercel Cron plus manual error logging would be sufficient — Inngest is probably over-engineered at this scale. The observability benefit won out.