Marketing Operations Dashboard
Purpose
This recipe produces a multi-channel marketing operations dashboard that aggregates spend, impressions, clicks, and conversions from all active ad platforms, overlays Google Analytics 4 session data, calculates channel-level ROAS and blended CAC, visualizes attribution across touchpoints, and tracks budget consumption vs plan. [src1]
Prerequisites
- Google Analytics 4 property with data collection active — GA4 setup guide
- Ad platform accounts with API access — Google Ads (developer token), Meta Business (system user token), LinkedIn/TikTok as applicable
- PostgreSQL database — free tier at Supabase or Neon
- Dashboard tool account — Retool, Metabase, or Google Looker Studio
- Monthly marketing budget breakdown by channel
- Conversion tracking configured in GA4 — at least sign_up, purchase, or lead events firing
Constraints
- Google Analytics 4 Data API: 10,000 requests/day per property. Batch dimensions into single requests. [src1]
- Meta Marketing API: 200 calls/hour per ad account. Use batch endpoints for multi-campaign pulls. [src2]
- Attribution windows differ across platforms. Normalize before cross-channel comparison. [src3]
- Cookie consent reduces trackable data by 20-40% in EU markets. Account for unattributed conversions. [src5]
- Budget figures must reconcile with accounting within 48 hours to prevent overspend decisions.
Tool Selection Decision
Which path?
├── User is non-technical AND budget = free
│ └── PATH A: No-Code Free — Google Looker Studio + native connectors
├── User is non-technical AND budget > $0
│ └── PATH B: No-Code Paid — Looker Studio + Supermetrics ($39/mo)
├── User is semi-technical or developer AND budget = free
│ └── PATH C: Code + Free — Metabase + PostgreSQL + n8n
└── User is developer AND budget > $0
└── PATH D: Code + Paid — Retool + PostgreSQL + Airbyte + dbt| Path | Tools | Cost | Speed | Output Quality |
|---|---|---|---|---|
| A: No-Code Free | Looker Studio + GA4/Ads connectors | $0 | 3-4 hours | Basic — Google channels only |
| B: No-Code Paid | Looker Studio + Supermetrics | $39/mo | 4-6 hours | Good — multi-channel, limited models |
| C: Code + Free | Metabase + PostgreSQL + n8n | $0 | 8-10 hours | Good — full SQL, custom attribution |
| D: Code + Paid | Retool + PostgreSQL + Airbyte + dbt | $50-100/mo | 6-8 hours | Excellent — automated transforms |
Execution Flow
Step 1: Design the Multi-Channel Data Model
Duration: 45-90 minutes · Tool: SQL client
Create unified schema: channel_spend (normalized ad platform data), ga4_events (analytics), marketing_budget (plan), attribution_touches (multi-touch credit), content_performance (organic). [src1]
Verify: All 5 tables created. · If failed: Check PostgreSQL permissions.
Step 2: Build Multi-Platform Data Sync
Duration: 2-4 hours · Tool: n8n, Airbyte, or custom scripts
Rate limit: Google Ads = 15,000/day, Meta = 200/hour, GA4 = 10,000/day
Configure ETL pipelines for each ad platform: Google Ads (GAQL queries), Meta Marketing API (insights endpoint), GA4 Data API (runReport). Schedule daily sync at 6:00 AM. [src2]
Verify: channel_spend has data for each platform after first sync. · If failed: Check OAuth tokens and API endpoint versions.
Step 3: Build Attribution and Analytics Queries
Duration: 1-2 hours · Tool: SQL
Create five core queries: channel performance (ROAS, CPA, CTR), budget vs actual, daily spend trend with 7-day moving average, funnel conversion by source/medium, and content performance ranking. [src3]
Verify: Queries return data for each configured platform. · If failed: Check platform name consistency between tables.
Step 4: Assemble the Dashboard UI
Duration: 1-2 hours · Tool: Retool, Metabase, or Looker Studio
Layout: KPI row (total spend, blended ROAS, blended CAC, conversions, budget remaining), channel ROAS chart, budget vs actual chart, daily spend trend, funnel table, content performance table, campaign detail table with date range and platform filters. [src4]
Verify: All sections render. KPI values match platform totals within 5%. · If failed: Check query bindings.
Step 5: Configure Budget Alerts
Duration: 30-60 minutes · Tool: n8n + Slack/Email
Alert conditions: channel consumed >80% budget with >10 days remaining, ROAS below 1.0, daily spend >2x 7-day average.
Verify: Test alert fires with sample data. · If failed: Check Slack webhook or SMTP config.
Step 6: Deploy and Share Access
Duration: 30 minutes · Tool: Dashboard platform settings
Share URL, set role-based permissions, configure weekly email subscription.
Verify: Non-admin marketing team member can view dashboard.
Output Schema
{
"output_type": "marketing_operations_dashboard",
"format": "deployed web application",
"components": [
{"name": "channel_performance", "type": "table", "description": "Platform-level spend, ROAS, CPA, CTR"},
{"name": "budget_tracker", "type": "chart", "description": "Budget vs actual spend by channel"},
{"name": "spend_trend", "type": "chart", "description": "Daily spend with 7-day moving average"},
{"name": "funnel_analysis", "type": "table", "description": "Source/medium funnel conversions"},
{"name": "content_ranking", "type": "table", "description": "Top content by conversion rate"},
{"name": "kpi_cards", "type": "metrics", "description": "Total spend, ROAS, CAC, conversions"}
],
"refresh_interval": "60 minutes",
"data_source": "PostgreSQL synced from ad platforms and GA4"
}Quality Benchmarks
| Quality Metric | Minimum Acceptable | Good | Excellent |
|---|---|---|---|
| Data freshness | < 24 hour lag | < 6 hour lag | < 3 hour lag |
| Spend accuracy | Within 10% of platform totals | Within 5% | Within 1% |
| Channel coverage | Google Ads + 1 channel | All paid channels | + organic + referral |
| Attribution model | Last-click only | First + last touch | Multi-touch linear |
| Budget reconciliation | Weekly | Daily | Real-time sync |
If below minimum: Check ETL job logs. Spend discrepancies usually indicate timezone mismatches or currency conversion issues.
Error Handling
| Error | Likely Cause | Recovery Action |
|---|---|---|
| Google Ads API 403 | Developer token not approved | Apply for basic access at Google Ads API Center |
| Meta API 190 (token expired) | System user token expired (60-day) | Generate new long-lived token via Business Settings |
| GA4 API quota exceeded | Too many requests in 24-hour window | Reduce frequency, batch dimensions into fewer requests |
| Spend data missing for platform | ETL job failed or API changed | Check ETL execution logs, verify API versions |
| Attribution double-counting | Overlapping attribution windows | Deduplicate conversion_id in attribution table |
| Budget shows negative remaining | Month boundary date filter issue | Verify DATE_TRUNC alignment with billing cycle |
Cost Breakdown
| Component | Free Tier | Paid Tier | At Scale ($50K+/mo) |
|---|---|---|---|
| Dashboard tool | $0 (Looker Studio / Retool free) | $10/user/mo | $100/mo |
| Database | $0 (Supabase) | $25/mo | $25/mo |
| ETL automation | $0 (n8n self-hosted) | $20/mo | $50/mo |
| Data connector (Supermetrics) | N/A | $39/mo | $99/mo |
| Total | $0 | $55-95/mo | $175-275/mo |
Anti-Patterns
Wrong: Comparing ROAS across platforms without normalizing attribution windows
Google's 30-day click attribution and Meta's 7-day click / 1-day view attribution measure different things. Comparing raw ROAS leads to systematically underfunding the platform with the shorter window. [src3]
Correct: Normalize to a consistent attribution window
Set all platforms to the same window (e.g., 7-day click) for comparison, or use a separate multi-touch model that credits all touchpoints consistently.
Wrong: Using platform-reported conversions as source of truth
Each platform claims credit for the same conversion. Summing Google + Meta conversions double-counts 20-40% of actual conversions. [src5]
Correct: Use GA4 or server-side tracking as single source of truth
Attribute conversions from one system (GA4 or your database), then map platform-reported metrics to that baseline.
When This Matters
Use when a startup is spending on two or more marketing channels and needs unified visibility into performance, attribution, and budget consumption. Requires at least one ad platform with active campaigns and GA4 collecting conversion events. This recipe builds the dashboard — for marketing strategy or channel selection, use a playbook card instead.