Overview
PrepQ is an EdTech business selling professional courses (CPA, EA) through paid acquisition on Meta Lead Ads and Google Ads. Leads land in Meritto CRM, where sales teams call prospects, book demo classes and close admissions. Before this platform, media buyers, sales managers and founders spent hours every morning downloading CSVs, matching UTM parameters and wrestling with spreadsheets just to calculate CAC, lead-to-connect ratios and creative fatigue. I architected and shipped an end-to-end, multi-tenant intelligence platform: a Fastify TypeScript API, a Trigger.dev sync engine, a Supabase PostgreSQL warehouse and an interactive Next.js 14 dashboard.
Problem
- Attribution blindness: ad platforms report cost-per-lead but cannot see if leads answer the phone, attend demos or pay enrollment fees.
- No visibility into speed-to-lead, or which creatives produced low-intent junk leads.
- Fragile credential handling: agency tools often store client OAuth tokens insecurely.
- Creative fatigue: high performers decayed over 14-30 days unnoticed, spiking blended acquisition cost.
Solution
A single glass-morphic, multi-tenant dashboard that aggregates daily ad spend, tracks sales velocity, maps creative fatigue and computes true CAC and ROAS.
How it works
- Trigger.dev orchestrates a scheduled morning sync across Meta Marketing API, Google Ads API and Meritto CRM, with pagination, rate-limit handling and exponential backoff.
- An ingest and attribution pipeline normalises the data and performs idempotent upserts into PostgreSQL.
- A Fastify REST API serves sub-200ms SQL aggregations, protected by Supabase Auth and granular role-based access control.
- The Next.js 14 App Router frontend renders overview and funnel KPIs, campaign performance and a creative fatigue radar.
Engineering challenges
- Zero-trust credential isolation: integration credentials live in a dedicated secrets schema with no public access policies, encrypted application-side with AES-256-GCM (IV plus auth tag), reachable only through three SECURITY DEFINER bridge functions granted to the backend service role. Tokens never reach API responses or the browser.
- Idempotent, resilient sync: an explicit state machine (pending, running, completed, failed) recorded in sync runs, plus composite unique keys on (date, source, campaign, ad set) so conflicting writes update instead of duplicating spend.
- Fast multi-dimensional funnel analytics: composite indexes on (client_id, created_at, course, status) and parameterised reporting functions that return pre-aggregated cohorts in one round trip.
Architecture principles
- Batch over streaming: teams review yesterday numbers each morning, so a scheduled batch sync beat WebSockets or Kafka on cost and fragility.
- Never guess attribution: leads without campaign or creative parameters store explicit nulls rather than fabricated sources.
- Separation of facts and metrics: impressions, clicks and spend stay immutable; CPL, ROAS and conversion velocity are computed dynamically.
Results
- 100% of manual reporting eliminated — 3+ hours of daily spreadsheet collation replaced by an automated 6:00 AM sync.
- 45% faster speed-to-lead after uncalled and delayed leads were surfaced on a dedicated metric card.
- Lower blended CAC: fatigue tracking let media buyers rotate creatives 4-6 days earlier.
- Sub-200ms analytics queries across ad spend and lead cohorts.
- 79 automated unit and integration tests, strict TypeScript throughout, Docker containerised.
- New agency clients and courses can be onboarded in minutes with no backend code changes.
